第一例:

#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

-------------------------------------------------------

-------------------------------------------------------

-------------------------------------------------------

-------------------------------------------------------