MySQL如何实现实时计算QPS、TPS的SHELL脚本
第一例:
#cat mysql_qps.sh
#!/bin/bash
MYSQL_CON="/usr/local/mysql/bin/mysql"
TIME="$1"
OLD_QUERY=`$MYSQL_CON -e "show global status like 'questions';" | awk -F[' ','s'] 'NR==2{print $3}'`
echo "$OLD_QUERY"
sleep "$TIME"
NEW_QUERY=`$MYSQL_CON -e "show global status like 'questions';" | awk -F[' ','s'] 'NR==2{print $3}'`
echo "$NEW_QUERY"
TIME_QUERY=`expr $NEW_QUERY - $OLD_QUERY`
QPS=`expr $TIME_QUERY / $TIME`
echo "$QPS"
查看接下来10s的QPS：
# ./mysql_qps.sh 10
第二例:通过mysqladmin间隔读取mysql的status信息，计算差值，得出统计信息。
# cat mysql_qps-tps.sh
#!/bin/bash
/usr/local/mysql/bin/mysqladmin -uroot -p'root' extended-status -i1|awk 'BEGIN{local_switch=0;print "QPSCommit RollbackTPSThreads_con Threads_run \n------------------------------------------------------- "}
$2 ~ /Queries$/{q=$4-lq;lq=$4;}
$2 ~ /Com_commit$/ {c=$4-lc;lc=$4;}
$2 ~ /Com_rollback$/{r=$4-lr;lr=$4;}
$2 ~ /Threads_connected$/ {tc=$4;}
$2 ~ /Threads_running$/{tr=$4;
if(local_switch==0)
{local_switch=1; count=0}
else {
if(count>10)
{count=0;print "------------------------------------------------------- \nQPSCommit RollbackTPSThreads_con Threads_run \n------------------------------------------------------- ";}
else{
count+=1;
printf "%-6d %-8d %-7d %-8d %-10d %d \n", q,c,r,c+r,tc,tr;
}
}
}'
运行结果如下:
# ./mysql_qps-tps.sh
QPSCommit RollbackTPSThreads_con Threads_run
-------------------------------------------------------
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
3104292 400 692 3503
2488246 305 551 3502
3394331 440 771 3503
3066279 395 674 3503
3400316 442 758 3502
3082278 415 693 3503
3533275 430 705 3503
2638249 356 605 3502
3819315 523 838 3502
3206287 449 736 3502
2949259 408 667 3502
-------------------------------------------------------
QPSCommit RollbackTPSThreads_con Threads_run
-------------------------------------------------------
2985284 399 683 3502
3042288 402 690 3502
3208282 431 713 3504
3232290 437 727 3502
3220287 435 722 3505
3270308 431 739 3502
4022373 546 919 3502
5497521 721 1242 3503
3281832 503502
4606434 602 1036 3502
1070101 129 230 3502
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------