Mysql Performance Check script-part

Mysql Performance Check script-part

#! /Bin/sh # ocpyang@126.comexport 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 inblack) 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 ";; esacprintf "% s \ n" "$ message" tput sgr0 # tput sgr0 restores the default value printf "$ black" retu Rn} 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 inblack) printf "$ black"; boldblack) printf "$ boldblack"; red) printf "$ red"; boldred) printf "$ boldred ";; green) printf "$ green"; boldgreen) printf "$ bol Dgreen "; 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 ";; esacprintf "% s" "$ message" tput sgr0 # tput sgr0 restores the default value printf "$ black" return} # set mysql evnMYSQL_USER = system # Mysql username MYSQL_PASS = 'Password' # mysql username MYSQL_HOST = 192.168.2.188 #1. mysql version v_01 = "select @ version; "v_02 =" v02. 'date specified 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} "redecho" "rm-rf $ {v_02} #2. number of mysql processes in the system mysql_processnum = 'ps-ef | grep "mysql" | grep-v "grep" | wc-l 'Cechon "2. mysql process number is :$ {mysql_processnum} "redecho" #3. number of mysql processes connected to the client conn_01 = "conn01. 'date too many yymm1_d1_hsf-m=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} "redecho" "rm-rf $ {conn_01} # 4.QPS( transaction volume per second) qps_01 =" Show global status like 'questions'; "qps_re =" qpsre. 'date required policyymmd1_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' in {print '$ {qps_02}'/'$ {qps_03}'} ''# by default, shell does not support floating-point operations cechon" 4. current mysql server QPS is :$ {qps_04} "redecho" "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 between "tps_re02 =" tpsre02. 'date between "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}'} ''# by default, shell does not support floating point operations. tps_uptime = 'cat/proc/uptime | awk '{print $1} ''tps _ avg = 'awk' BEGIN {print' $ {tps_sum} '/' $ {tps_uptime} '}'' # floating point is not supported by shell by default. calculation cechon "5. current mysql server TPS is :$ {tps_avg} "redecho" "rm-rf $ {tps_re01} rm-rf $ {tps_re02} # 6.key Buffe R 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 ready "kbrd_re02 =" kbrd02. 'date ready "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]; thencechon "6.1 there is no any value! "Greenecho" "elsekbrd_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} '} ''# shell does not support floating point operation key_buffer_read_hits = 'awk' BEGIN {print' $ {kbrd_06} '* 100}'' by default }'' cechon "6.1 current mysql key_buffer_read_hits is: $ {key_buffer_read_hits} "redecho" "firm-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 ready "kbwd_re02 =" kbwd02. 'date ready "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]; thencechon "6.2 there is no any value! "Greenecho" "elsekbwd_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} '} ''# shell does not support floating point operation key_buffer_write_hits = 'awk' BEGIN {print' $ {kbwd_06} '* 100}'' by default }'' cechon "6.2 current mysql key_buffer_write_hits is: $ {key_buffer_write_hits} "redecho" "firm-rf $ {kbwd_re01} rm-rf $ {kbwd_re02} #7. innoDB Buffer hit rate # Innodb_buffer_read_hits = (1-innodb_bu Ffer_pool_reads/records) * 100% innob_01 = "show status like 'innodb _ buffer_pool_reads ';" innob_02 = "show status like 'innodb _ reads';" innob_re01 = "innob_re01. 'date too policyymm1_d1_h1_m1_s'.txt "innob_re02 =" innob_re02. 'date required policyymmd1_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]; thencechon "7. there is no any value! "Greenecho" "elseinnob_05 = 'awk' BEGIN {print '$ {innob_03}'/'$ {innob_04}'}'' # shell does not support floating point computation innob_06 = 'awk' by default' BEGIN {print '1-$ {innob_05} '} ''# shell does not support floating point computation innodb_buffer_read_hits = 'awk' BEGIN {print' $ {innob_06} '* 100}'' by default }'' cechon "7. current mysql Innodb_buffer_read_hits is: $ {innodb_buffer_read_hits} "redecho" "firm-rf $ {innob_re01} rm-rf $ {innob_re02} #8. query Cache hit rate # Query_cache_hits = (Qcache_hi Ts/(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 when policyuncm1_d1_hsf-m1_s'.txt "qc_re02 =" qc_re02. 'date when policyuncm1_d1_hsf-m1_s'.txt "qc_re03 =" qc_re03. 'date required policyymmd1_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]; thencechon "8. there is no any value! "Greenecho" "elseqc_07 = 'awk' BEGIN {print '$ {qc_04}' + '$ {qc_05}' + '$ {qc_06}' 'qc _ 08 =' awk 'in in {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} "redecho" "firm-rf $ {qc_re01} rm-rf $ {qc_re02} rm-rf $ {qc_re03}