Mysql Performance Check script-mysql Performance Check script

Mysql Performance Check script-mysql Performance Check script

#! /Bin/sh
# Ocpyang@126.com

Export black = '\ 033 [0m'
Export boldblack = '\ 033 [1; 0m'
Export red = '\ 033 [31m'
Export boldred = '\ 033 [1; 31m'
Export green = '\ 033 [32m'
Export boldgreen = '\ 033 [1; 32m'
Export yellow = '\ 033 [33m'
Export boldyellow = '\ 033 [1; 33m'
Export blue = '\ 033 [34m'
Export boldblue = '\ 033 [1; 34m'
Export magenta = '\ 033 [35m'
Export boldmagenta = '\ 033 [1; 35m'
Export cyan = '\ 033 [36m'
Export boldcyan = '\ 033 [1; 36m'
Export white = '\ 033 [37m'
Export boldwhite = '\ 033 [1; 37m'

Cecho ()

# -- Function to easliy print colored text --##
 
# Color-echo.
# Parameter $1 = message
# Parameter $2 = color
{
Local default_msg = "No message passed ."

Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is black.

Case $ color in
Black)
Printf "$ black ";;
Boldblack)
Printf "$ boldblack ";;
Red)
Printf "$ red ";;
Boldred)
Printf "$ boldred ";;
Green)
Printf "$ green ";;
Boldgreen)
Printf "$ boldgreen ";;
Yellow)
Printf "$ yellow ";;
Boldyellow)
Printf "$ boldyellow ";;
Blue)
Printf "$ blue ";;
Boldblue)
Printf "$ boldblue ";;
Magenta)
Printf "$ magenta ";;
Boldmagenta)
Printf "$ boldmagenta ";;
Cyan)
Printf "$ cyan ";;
Boldcyan)
Printf "$ boldcyan ";;
White)
Printf "$ white ";;
Boldwhite)
Printf "$ boldwhite ";;
Esac
Printf "% s \ n" "$ message"
Tput sgr0 # tput sgr0 restores the default value
Printf "$ black"

Return
}

Cechon ()

# Color-echo.
# Parameter 1 $1 = message
# Parameter 2 $2 = color
{
Local default_msg = "No message passed ."
# Doesn't really need to be a local variable.

Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is black.

Case $ color in
Black)
Printf "$ black ";;
Boldblack)
Printf "$ boldblack ";;
Red)
Printf "$ red ";;
Boldred)
Printf "$ boldred ";;
Green)
Printf "$ green ";;
Boldgreen)
Printf "$ boldgreen ";;
Yellow)
Printf "$ yellow ";;
Boldyellow)
Printf "$ boldyellow ";;
Blue)
Printf "$ blue ";;
Boldblue)
Printf "$ boldblue ";;
Magenta)
Printf "$ magenta ";;
Boldmagenta)
Printf "$ boldmagenta ";;
Cyan)
Printf "$ cyan ";;
Boldcyan)
Printf "$ boldcyan ";;
White)
Printf "$ white ";;
Boldwhite)
Printf "$ boldwhite ";;
Esac
Printf "% s" "$ message"
Tput sgr0 # tput sgr0 restores the default value
Printf "$ black"

Return
}

 

# Set mysql evn
MYSQL_USER = system # mysql Username
MYSQL_PASS = 'Password' # mysql logon User password
MYSQL_HOST = 192.168.2.188

#1. mysql version
V_01 = "select @ version ;"
V_02 = "v02. 'date when policyymm1_d1_h1_m1_s'.txt"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {v_01}" >$ {v_02}
V_03 = 'cat $ {v_02} | grep-v @ version'
Cechon "1. mysql runing version is: $ {v_03}" red
Echo ""
Rm-rf $ {v_02}

 

#2. Number of mysql Processes
Mysql_processnum = 'ps-ef | grep "mysql" | grep-v "grep" | wc-l'
Cechon "2. mysql process number is: $ {mysql_processnum}" red
Echo ""

#3. Number of mysql processes connected to the client

Conn_01 = "conn01. 'date when policyymm1_d1_h1_m1_s'.txt"
Conn_02 = "show processlist ;"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {conn_02}" | grep-v Id >$ {conn_01}
Client_conn_num = 'cat $ {conn_01} | wc-l'
Cechon "3. mysql client connect number is: $ {client_conn_num}" red
Echo ""
Rm-rf $ {conn_01}

 

# 4.QPS( transaction volume per second)

Qps_01 = "show global status like 'questions ';"
Qps_re = "qpsre. 'date when policyymm1_d1_h1_m1_s'.txt"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {qps_01}" | grep-v Variable_name \
| Cut-f 2 >$ {qps_re}
Qps_02 = 'cat $ {qps_re }'
Qps_03 = 'cat/proc/uptime | awk '{print $1 }''
Qps_04 = 'awk' BEGIN {print '$ {qps_02}'/'$ {qps_03}' # shell does not support floating point operations by default.
Cechon "4. current mysql server QPS is: $ {qps_04}" red
Echo ""
Rm-rf $ {qps_re}

 

# 5.TPS( transaction volume per second)
Tps_01 = "show status where Variable_name in ('com _ commit ');"
Tps_02 = "show status where Variable_name in ('com _ rollback ');"
Tps_re01 = "tpsre01. 'date when policyymm1_d1_h1_m1_s'.txt"
Tps_re02 = "tpsre02. 'date when policyymm1_d1_h1_m1_s'.txt"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {tps_01}" | grep-v Variable_name \
| Cut-f 2 >$ {tps_re01}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {tps_02}" | grep-v Variable_name \
| Cut-f 2 >$ {tps_re02}
Tps_03 = 'cat ${tps_re01 }'
Tps_04 = 'cat ${tps_re02 }'
Tps_sum = 'awk' BEGIN {print '$ {tps_03}' + '$ {tps_04}'} ''# shell does not support floating point operations by default.
Tps_uptime = 'cat/proc/uptime | awk '{print $1 }''
Tps_avg = 'awk' BEGIN {print '$ {tps_sum}'/'$ {tps_uptime}'} ''# shell does not support floating point operations by default.
Cechon "5. current mysql server TPS is: $ {tps_avg}" red
Echo ""
Rm-rf $ {tps_re01}
Rm-rf $ {tps_re02}

# 6.key Buffer hit rate

# Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%

Kbrd_01 = "show status like 'key _ reads ';"
Kbrd_02 = "show status like 'key _ read_requests ';"
Kbrd_re01 = "kbrd01. 'date too many yymm1_d1_h1_m1_s'.txt"
Kbrd_re02 = "kbrd02. 'date too many yymm1_d1_h1_m1_s'.txt"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {kbrd_01}" | grep-v Variable_name \
| Cut-f 2 >$ {kbrd_re01}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {kbrd_02}" | grep-v Variable_name \
| Cut-f 2 >$ {kbrd_re02}

Kbrd_03 = 'cat ${kbrd_re01 }'
Kbrd_04 = 'cat ${kbrd_re02 }'

If ["$ {kbrd_03}"-eq 0]; then
Cechon "6.1 there is no any value! "Green
Echo ""
Else
Kbrd_05 = 'awk' BEGIN {print '$ {kbrd_03}'/'$ {kbrd_04}' # shell does not support floating point operations by default.
Kbrd_06 = 'awk' BEGIN {print '1-$ {kbrd_05} '} ''# The shell does not support floating point operations by default.
Key_buffer_read_hits = 'awk' BEGIN {print '$ {kbrd_06}' * 100 }''
Cechon "6.1 current mysql key_buffer_read_hits is: $ {key_buffer_read_hits}" red
Echo ""
Fi
 

Rm-rf $ {kbrd_re01}
Rm-rf $ {kbrd_re02}

 

# Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%

Kbwd_01 = "show status like 'key _ writes ';"
Kbwd_02 = "show status like 'key _ write_requests ';"
Kbwd_re01 = "kbwd01. 'date has been specified yymm1_d1_h1_m1_s'.txt"
Kbwd_re02 = "maid"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {kbwd_01}" | grep-v Variable_name \
| Cut-f 2 >$ {kbwd_re01}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {kbwd_02}" | grep-v Variable_name \
| Cut-f 2 >$ {kbwd_re02}

Kbwd_03 = 'cat ${kbwd_re01 }'
Kbwd_04 = 'cat ${kbwd_re02 }'

If ["$ {kbwd_03}"-eq 0]; then
Cechon "6.2 there is no any value! "Green
Echo ""
Else
Kbwd_05 = 'awk' BEGIN {print '$ {kbwd_03}'/'$ {kbwd_04}' # shell does not support floating point operations by default.
Kbwd_06 = 'awk' BEGIN {print '1-$ {kbwd_05} '} ''# The shell does not support floating point operations by default.
Key_buffer_write_hits = 'awk' BEGIN {print '$ {kbwd_06}' * 100 }''
Cechon "6.2 current mysql key_buffer_write_hits is: $ {key_buffer_write_hits}" red
Echo ""
Fi

Rm-rf $ {kbwd_re01}
Rm-rf $ {kbwd_re02}

 

 

#7. InnoDB Buffer hit rate
# Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%
Innob_01 = "show status like 'innodb _ buffer_pool_reads ';"
Innob_02 = "show status like 'innodb _ buffer_pool_read_requests ';"
Innob_re01 = "innob_re01. 'date too many yymm1_d1_h1_m1_s'.txt"
Innob_re02 = "innob_re02. 'date too many yymm1_d1_h1_m1_s'.txt"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {innob_01}" | grep-v Variable_name \
| Cut-f 2 >$ {innob_re01}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {innob_02}" | grep-v Variable_name \
| Cut-f 2 >$ {innob_re02}

Innob_03 = 'cat ${innob_re01 }'
Innob_04 = 'cat ${innob_re02 }'
If ["$ {innob_03}"-eq 0]; then
Cechon "7. there is no any value! "Green
Echo ""
Else
Innob_05 = 'awk' BEGIN {print '$ {innob_03}'/'$ {innob_04}' # shell does not support floating point operations by default
Innob_06 = 'awk' BEGIN {print '1-$ {innob_05} '} ''# shell does not support floating point operations by default
Innodb_buffer_read_hits = 'awk' BEGIN {print '$ {innob_06}' * 100 }''
Cechon "7. current mysql Innodb_buffer_read_hits is :$ {innodb_buffer_read_hits}" red
Echo ""
Fi
Rm-rf $ {innob_re01}
Rm-rf $ {innob_re02}

#8. Query Cache hit rate
# Query_cache_hits = (Qcache_hits/(Qcache_hits + Qcache_inserts + Qcache_not_cached) * 100)

Qc_01 = "show status like 'qcache _ hits ';"
Qc_02 = "show status like 'qcache _ inserts ';"
Qc_03 = "show status like 'qcache _ not_cached ';"

Qc_re01 = "qc_re01. 'date too many yymm1_d1_h1_m1_s'.txt"
Qc_re02 = "qc_re02. 'date too many yymm1_d1_h1_m1_s'.txt"
Qc_re03 = "qc_re03. 'date too many yymm1_d1_h1_m1_s'.txt"

Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {qc_01}" | grep-v Variable_name \
| Cut-f 2 >$ {qc_re01}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {qc_02}" | grep-v Variable_name \
| Cut-f 2 >$ {qc_re02}
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {qc_03}" | grep-v Variable_name \
| Cut-f 2 >$ {qc_re03}

Qc_04 = 'cat ${qc_re01 }'
Qc_05 = 'cat ${qc_re02 }'
Qc_06 = 'cat ${qc_re03 }'

If ["$ {qc_04}"-eq 0]; then
Cechon "8. there is no any value! "Green
Echo ""
Else
Qc_07 = 'awk' BEGIN {print '$ {qc_04}' + '$ {qc_05}' + '$ {qc_06 }'}''
Qc_08 = 'awk' BEGIN {print '$ {qc_04}'/'$ {qc_07 }'}''
Query_cache_hits = 'awk' BEGIN {print '$ {qc_08}' * 100 }''
Cechon "8. current mysql query_cache_hits is: $ {query_cache_hits}" red
Echo ""
Fi
Rm-rf $ {qc_re01}
Rm-rf $ {qc_re02}
Rm-rf $ {qc_re03}

 

Server maintenance-view mysql performance metrics

Daily maintenance involves many aspects: Database status monitoring, performance analysis, SQL code analysis and optimization, etc. For database inspection and other work, you can refer to the MySQL service content provided on the website of aikesheng in Shanghai, China. You can also consult them.

In mysql, how do I add comments to the unexecuted part of the executed SQL script?

There are three annotations for mysql. You can use either of the following annotations.
1 ,#...
2 ,"--..."
3 ,/*...*/