轉載用sql語句計算出mysql資料庫的qps,tps,iops效能指標

標籤：擷取   函數   nod   tab   amp   sql語句   邏輯   mysql啟動   cas   

本帖最後由 LUK 於 2014-9-21 22:39 編輯  思路： 1 關注MYSQL三個方面的效能指標，分別為query數，transaction數,io請求數 2 在某個時間範圍內（例如20秒)，統計MYSQL中上面的三個指標的總量，以及每一秒的量 ,同時每隔一秒種列印一個當前的指標量，在最後再計算並列印時間段內總量及每秒量 3 在IO的統計公式如下： Key_reads  * 2 +  Key_writes * 2 + Key_read_requests + Innodb_data_reads + Innodb_data_writes + Innodb_dblwr_writes + Innodb_log_writes （該公式最早從taobaodba.com這個網上傳出，這裡學習借鑒） key_reads 和 key_writes都乘以2的原因是因為如果從磁碟中讀或寫索引塊之後會再去磁碟讀或寫資料區塊，所以就會有至少兩倍的IO請求. 事務數總量為 com_commit+com_rollback query數通過 com_select擷取 4 雖然統計的時間範圍可以自己指定（例如20秒)，但最終計算每秒的指標量時，是以指令碼開始執行時間至指令碼執行結束時mysql uptime的時間差來算的，因為指令碼執行會消耗一定時間，這樣，實際的時間會多於我們指定的時間 (結束時的量-開始時的量)/(結束時uptime-開始時uptime） #!/bin/bash #filename mysqlgather.sh #param #N seconds #s print query  #t print transaction  #i print mysql io # example # ./mysqlgather.sh 20 s t i   --指令碼的名字為mysqlgather.sh,執行例子為 ./mysqlgather.sh 20 s t i   其中20為取樣的時間範圍，在這裡假設20s,這個時間參數必須要輸入，時間大小可以自己定 s 表示要統計query及query per second (選擇性參數) t 表示要統計transaction及transaction per second (選擇性參數) i 表示要統計mysql中的io請求數及io per second (選擇性參數) s t i 三個參數可以只輸入其中任意一個或多個,可以只統計三個指標中的一個或者兩個 如果三個參數都寫或者都不寫，表示都要統計s t i --selecom函數取query數量 #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 $2}‘`       echo "com_select: $SELECT_NUM" } --trans_num函數 統計transaction數量 #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 $2}‘`      ROLLBACK_NUM=`grep -i "com_rollback"  transactions.out | awk ‘{print $2}‘`      SUM_TRAN=$[ $COMMIT_NUM1 + $ROLLBACK_NUM1 ]      echo "transations:$SUM_TRAN" } --ionum函數統計io讀寫請求數 #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 $2}‘` KEYWRITE=`grep -i "Key_writes"  iops.out | awk ‘{print $2}‘` READREQ=`grep -i "Key_read_requests"  iops.out | awk ‘{print $2}‘` DATAREAD=`grep -i "Innodb_data_reads"  iops.out | awk ‘{print $2}‘` DATAWRITE=`grep -i "Innodb_data_writes"  iops.out | awk ‘{print $2}‘` DBLWR=`grep -i "Innodb_dblwr_writes"  iops.out | awk ‘{print $2}‘` LOGWRITE=`grep -i "Innodb_log_writes"  iops.out | awk ‘{print $2}‘` SUM_IO=$[ $KEYREAD * 2 + $KEYWRITE * 2 + $READREQ + $DATAREAD + $DATAWRITE + $DBLWR + $LOGWRITE ] echo "io:$SUM_IO" } --up_time函數是統計MYSQL啟動後的時間 #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 $2}‘` } --下面的程式邏輯是先檢查輸入了哪些參數，再計算所想要統計的指標 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=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  selcom trans_num ionum PARM1=$[ $PARM1 - 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" elif [ $NUM_PARM = 2 ];then PARM1=$1 PARM2=$2 case $PARM2 in  "s") up_time UP_TIME1=$UP_TIME selcom SELECT_NUM1=$SELECT_NUM sleep 1 PARM1=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  selcom PARM1=$[ $PARM1 - 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=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  trans_num PARM1=$[ $PARM1 - 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=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  ionum PARM1=$[ $PARM1 - 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 [ $PARM2 = "s" ] || [ $PARM2 = "t" ] && [ $PARM3 = "s" ] || [ $PARM3 = "t" ]; then PARM1=$1 up_time UP_TIME1=$UP_TIME selcom SELECT_NUM1=$SELECT_NUM trans_num SUM_TRAN1=$SUM_TRAN sleep 1 PARM1=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  selcom trans_num PARM1=$[ $PARM1 - 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 [ $PARM2 = "s" ] || [ $PARM2 = "i" ] && [ $PARM3 = "s" ] || [ $PARM3 = "i" ]; PARM1=$1 up_time UP_TIME1=$UP_TIME selcom SELECT_NUM1=$SELECT_NUM ionum SUM_IO1=$SUM_IO sleep 1 PARM1=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  selcom ionum PARM1=$[ $PARM1 - 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=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  trans_num ionum PARM1=$[ $PARM1 - 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=$[ $PARM1 - 1] while [ $PARM1 -gt 0 ] do  selcom trans_num ionum PARM1=$[ $PARM1 - 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 have not input any parameter!"; exit; fi

轉載用sql語句計算出mysql資料庫的qps,tps,iops效能指標