Reproduced SQL statement to calculate the qps,tps,iops performance of MySQL database

This post was last edited by LUK on 2014-9-21 22:39 Ideas: 1 focus on MySQL three performance indicators, respectively, query number, transaction number, IO request number 2 in a certain timeframe (for example, 20 seconds), the total number of three indicators in MySQL, as well as the amount of each second, while printing a current number of indicators per second, at the end of the calculation and print the time period of the total amount and volume per second 3 The statistical formula in IO is as follows: Key_reads * 2 + key_writes * 2 + key_read_requests + innodb_data_reads + innodb_data_writes + innodb_dblwr_writes + Inno Db_log_writes (the formula first from the taobaodba.com this online, learn from here) The reason Key_reads and key_writes are multiplied by 2 is that if you read or write an index block from disk, you will have at least twice times the IO request after you have read or write the block of data to disk. Total number of transactions is Com_commit+com_rollback Query number obtained by Com_select 4 Although the time range of the statistic can be specified by itself (for example, 20 seconds), the final calculation of the metric per second is based on the time lag of the MySQL uptime when the script begins execution to the end of the script execution, because the script execution consumes a certain amount of time, so that the actual time will be more than the time we specified (Amount at end-amount at start)/(at end uptime-start uptime) #!/bin/bash #filename mysqlgather.sh #param #N seconds #s Print Query #t Print Transaction #i print MySQL IO # example #./mysqlgather.sh s T i --The name of the script is mysqlgather.sh, and the execution example is./mysqlgather.sh s t i Where 20 is the time range for sampling, assuming 20s here, the time parameter must be entered, the time size can be determined by itself s means to count query and query per second (optional parameter) T means to count transaction and transaction per second (optional parameters) I means to count the number of IO requests in MySQL and IO per second (optional parameters) s t i three parameters can only enter any one or more, you can only count one or two of the three indicators If three parameters are either written or not written, the expression is to be counted s t i --selecom function Fetch Query number #qps Selcom () { Mysql-uroot-e "show global Status where Variable_name in (' Com_select ');" > Select.out Select_num= ' Grep-i "Com_select" Select.out | awk ' {print $} ' echo "Com_select: $SELECT _num" } --trans_num function Statistics Transaction number #tps Trans_num () { Mysql-uroot-e "show global Status where Variable_name in (' Com_commit ', ' com_rollback ');" > Transactions.out Commit_num= ' Grep-i "Com_commit" Transactions.out | awk ' {print $} ' Rollback_num= ' Grep-i "Com_rollback" Transactions.out | awk ' {print $} ' sum_tran=$[$COMMIT _num1 + $ROLLBACK _NUM1] echo "Transations: $SUM _tran" } --ionum function Statistics IO read/write request number #IO Ionum () { Mysql-uroot-e "show global Status where Variable_name in (' Key_reads ', ' key_writes ', ' key_read_requests ', ' Innodb_data_ Reads ', ' innodb_data_writes ', ' innodb_dblwr_writes ', ' innodb_log_writes '); "> Iops.out Keyread= ' Grep-i "key_reads" Iops.out | awk ' {print $} ' Keywrite= ' Grep-i "key_writes" Iops.out | awk ' {print $} ' Readreq= ' Grep-i "key_read_requests" Iops.out | awk ' {print $} ' Dataread= ' Grep-i "innodb_data_reads" Iops.out | awk ' {print $} ' Datawrite= ' Grep-i "innodb_data_writes" Iops.out | awk ' {print $} ' Dblwr= ' Grep-i "innodb_dblwr_writes" Iops.out | awk ' {print $} ' Logwrite= ' Grep-i "innodb_log_writes" Iops.out | awk ' {print $} ' sum_io=$[$KEYREAD * 2 + $KEYWRITE * 2 + $READREQ + $DATAREAD + $DATAWRITE + $DBLWR + $LOGWRITE] echo "IO: $SUM _io" } The--up_time function is to count the time after MySQL starts #uptime Up_time () { Mysql-uroot-e "show global Status where Variable_name in (' Uptime ');" > Uptime.out Up_time= ' Grep-i "Uptime" Uptime.out | awk ' {print $} ' } --The following program logic is to check which parameters have been entered before calculating the desired statistics num_parm=$# if [$NUM _parm = 1];then Parm1=$1 Up_time up_time1= $UP _time Selcom select_num1= $SELECT _num Trans_num sum_tran1= $SUM _tran Ionum sum_io1= $SUM _io Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Selcom Trans_num Ionum parm1=$[$PARM 1-1] Sleep 1 Done select_num2= $SELECT _num sum_tran2= $SUM _tran sum_io2= $SUM _io Up_time up_time2= $UP _time --The total amount within the statistical time range select_diff=$[$SELECT _num2-$SELECT _NUM1] trans_diff=$[$SUM _tran2-$SUM _tran1] io_diff=$[$SUM _io2-$SUM _io1] time_diff=$[$UP _time2-$UP _time1] --Count the amount per second select_persecond=$[$SELECT _diff/$TIME _diff] trans_persecond=$[$TRANS _diff/$TIME _diff] ioreq_persecond=$[$IO _diff/$TIME _diff] Echo-n "sel_s: $SELECT _persecond; trans_s: $TRANS _persecond; io_s: $IOREQ _persecond " elif [$NUM _parm = 2];then Parm1=$1 Parm2=$2 Case $PARM 2 in "S") Up_time up_time1= $UP _time Selcom select_num1= $SELECT _num Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Selcom parm1=$[$PARM 1-1] Sleep 1 Done select_num2= $SELECT _num Up_time up_time2= $UP _time select_diff=$[$SELECT _num2-$SELECT _NUM1] time_diff=$[$UP _time2-$UP _time1] select_persecond=$[$SELECT _diff/$TIME _diff] Echo-n "sel_s: $SELECT _persecond;" ;; "T") Parm1=$1 Up_time up_time1= $UP _time Trans_num sum_tran1= $SUM _tran Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Trans_num parm1=$[$PARM 1-1] Sleep 1 Done sum_tran2= $SUM _tran Up_time up_time2= $UP _time trans_diff=$[$SUM _tran2-$SUM _tran1] time_diff=$[$UP _time2-$UP _time1] trans_persecond=$[$TRANS _diff/$TIME _diff] Echo-n "trans_s: $TRANS _persecond;" ;; "I") Parm1=$1 Up_time up_time1= $UP _time Ionum sum_io1= $SUM _io Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Ionum parm1=$[$PARM 1-1] Sleep 1 Done sum_io2= $SUM _io Up_time up_time2= $UP _time io_diff=$[$SUM _io2-$SUM _io1] time_diff=$[$UP _time2-$UP _time1] ioreq_persecond=$[$IO _diff/$TIME _diff] Echo-n "io_s: $IOREQ _persecond" ;; *) Exit Esac elif [$NUM _parm = 3];then Parm1=$1 Parm2=$2 Parm3=$3 if [$PARM 2 = "s"] | | [$PARM 2 = "T"] && [$PARM 3 = "s"] | | [$PARM 3 = "T"]; Then Parm1=$1 Up_time up_time1= $UP _time Selcom select_num1= $SELECT _num Trans_num sum_tran1= $SUM _tran Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Selcom Trans_num parm1=$[$PARM 1-1] Sleep 1 Done select_num2= $SELECT _num sum_tran2= $SUM _tran Up_time up_time2= $UP _time select_diff=$[$SELECT _num2-$SELECT _NUM1] trans_diff=$[$SUM _tran2-$SUM _tran1] time_diff=$[$UP _time2-$UP _time1] select_persecond=$[$SELECT _diff/$TIME _diff] trans_persecond=$[$TRANS _diff/$TIME _diff] Echo-n "sel_s: $SELECT _persecond; trans_s: $TRANS _persecond; " elif [$PARM 2 = "s"] | | [$PARM 2 = "I"] && [$PARM 3 = "s"] | | [$PARM 3 = "I"]; Parm1=$1 Up_time up_time1= $UP _time Selcom select_num1= $SELECT _num Ionum sum_io1= $SUM _io Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Selcom Ionum parm1=$[$PARM 1-1] Sleep 1 Done select_num2= $SELECT _num sum_io2= $SUM _io Up_time up_time2= $UP _time select_diff=$[$SELECT _num2-$SELECT _NUM1] io_diff=$[$SUM _io2-$SUM _io1] time_diff=$[$UP _time2-$UP _time1] select_persecond=$[$SELECT _diff/$TIME _diff] ioreq_persecond=$[$IO _diff/$TIME _diff] Echo-n "sel_s: $SELECT _persecond; io_s: $IOREQ _persecond " Else Parm1=$1 Up_time up_time1= $UP _time Trans_num sum_tran1= $SUM _tran Ionum sum_io1= $SUM _io Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Trans_num Ionum parm1=$[$PARM 1-1] Sleep 1 Done sum_tran2= $SUM _tran sum_io2= $SUM _io Up_time up_time2= $UP _time trans_diff=$[$SUM _tran2-$SUM _tran1] io_diff=$[$SUM _io2-$SUM _io1] time_diff=$[$UP _time2-$UP _time1] trans_persecond=$[$TRANS _diff/$TIME _diff] ioreq_persecond=$[$IO _diff/$TIME _diff] Echo-n "trans_s: $TRANS _persecond; io_s: $IOREQ _persecond " Fi elif [$NUM _parm = 4];then Parm1=$1 Parm2=$2 Parm3=$3 Parm4=$4 Up_time up_time1= $UP _time Selcom select_num1= $SELECT _num Trans_num sum_tran1= $SUM _tran Ionum sum_io1= $SUM _io Sleep 1 parm1=$[$PARM 1-1] While [$PARM 1-GT 0] Do Selcom Trans_num Ionum parm1=$[$PARM 1-1] Sleep 1 Done select_num2= $SELECT _num sum_tran2= $SUM _tran sum_io2= $SUM _io Up_time up_time2= $UP _time select_diff=$[$SELECT _num2-$SELECT _NUM1] trans_diff=$[$SUM _tran2-$SUM _tran1] io_diff=$[$SUM _io2-$SUM _io1] time_diff=$[$UP _time2-$UP _time1] select_persecond=$[$SELECT _diff/$TIME _diff] trans_persecond=$[$TRANS _diff/$TIME _diff] ioreq_persecond=$[$IO _diff/$TIME _diff] Echo-n "sel_s: $SELECT _persecond; trans_s: $TRANS _persecond; io_s: $IOREQ _persecond " Else echo "You are not an input any parameter!"; Exit Fi

Reproduced SQL statement to calculate the qps,tps,iops performance of MySQL database