作为一名数据库运维人员，手里都有一些工具脚本，这些都是你提升运维效率，快速排查故障的利器。

在生产上部署关系型数据库时，都会对同一配置的关系型数据库数据库做QPS和每站终端数(每个站的终端的缩写)压测，获取QPS和每站终端数(每个站的终端的缩写)的容量数据，一旦上生产之后，应用的QPS TPS达到容量的告警阀值，则会建议应用数据库进行拆分，扩容。

生产上的QPS TPS指标对应数据库来说是非常重要，所以排查问题时，通常会实时的查看QPS TPS指标值，下面就给大家分享一个实时查看QPS TPS指标值的壳脚本。

脚本内容如下所示

#!/bin/bashmysqldmin-uroot-promote-socket=/u02/run/3308/MySQL。sock extended-status-i1 | awk ' BEgin { local _ switch=0；打印“QPS提交回滚每站终端数（terminals per station的缩写）线程_con线程_ run n-} $ 2 ~/query $/{ q=$ 4-LQ；LQ=4美元；} $ 2 ~/Com _ commit $/{ c=$ 4-LC；LC=4美元；} $ 2 ~/Com _ roll back $/{ 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；打印'- nQPS提交回滚TPS线程_ con线程_运行n-'；} else { count=1；printf ' %-6d %-8d %-7d %-8d %-10d % d n '，q，c，r，c r，tc，tr；} } } '

在这里我用sysbench模拟一下业务操作

[MySQL @ localhost ~]$ sysbench/usr/share/sysbench/tests/include/OLTP _ legacy/OLTP。Lua-MySQL-host=192。168 .17 .128-MySQL-port=3308-MySQL-user=root-MySQL-password=' root '-MySQL-db=sb test-OLTP-test-mode=complex-OLTP-tables-count=1-OLTP

,95%): 1708.63 err/s: 0.00 reconn/s: 0.00[ 20s ] thds: 128 tps: 139.60 qps: 2784.88 (r/w/o: 1946.85/558.62/279.41) lat (ms,95%): 1376.60 err/s: 0.00 reconn/s: 0.00[ 25s ] thds: 128 tps: 131.90 qps: 2694.65 (r/w/o: 1890.03/541.01/263.61) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00[ 30s ] thds: 128 tps: 137.50 qps: 2774.16 (r/w/o: 1939.17/559.79/275.19) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00[ 35s ] thds: 128 tps: 142.38 qps: 2755.43 (r/w/o: 1932.54/538.13/284.76) lat (ms,95%): 1533.66 err/s: 0.00 reconn/s: 0.00[ 40s ] thds: 128 tps: 134.41 qps: 2731.10 (r/w/o: 1906.81/555.66/268.63) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00SQL statistics: queries performed: read: 76664 write: 21901 other: 10951 total: 109516 transactions: 5475 (135.35 per sec.) queries: 109516 (2707.35 per sec.) ignored errors: 1 (0.02 per sec.) reconnects: 0 (0.00 per sec.)General statistics: total time: 40.4493s total number of events: 5475Latency (ms): min: 97.28 avg: 942.69 max: 15577.39 95th percentile: 1533.66 sum: 5161211.71Threads fairness: events (avg/stddev): 42.7734/2.45 execution time (avg/stddev): 40.3220/0.11

用途TPS，QPS监控脚本监控，看一下监控内容是否和sysbench的结果有差异

[mysql@localhost ~]$ ./mysql_tps.sh ------------------------------------------------------- QPS Commit Rollback TPS Threads_con Threads_run ------------------------------------------------------- 3090 186 0 186 130 40 2661 131 0 131 130 89 2603 129 0 129 130 45 2557 92 0 92 130 123 2066 126 0 126 130 11 2638 123 0 123 130 25 2770 174 0 174 130 127 3006 172 0 172 130 39 2797 117 0 117 130 43 2247 103 0 103 130 80 2742 157 0 157 130 119 ------------------------------------------------------- QPS Commit Rollback TPS Threads_con Threads_run ------------------------------------------------------- 2974 159 0 159 130 22 2864 141 0 141 130 23 2754 130 0 130 130 122 2685 149 0 149 130 40 2809 126 0 126 130 21 2631 140 0 140 130 21 2594 126 0 126 130 23 2868 148 0 148 130 28 2696 130 0 130 130 63 2920 148 0 148 130 49 2569 127 0 127 130 37

从sysbench的结果可以看到，TPS为135.35 per sec，QPS为2707.35 per sec

这个结果和我们的脚本监控基本是一致的。

在这里如果想要看insert,update,delete,select语句的执行情况，可以将脚本进行升级，其内容如下所示

改进型TPS，QPS监控脚本

#!/bin/bashmysqladmin -uroot -proot --socket=/u02/run/3308/mysql.sock extended-status -i1|awk 'BEGIN{local_switch=0;print "QPS Commit Rollback TPS delete insert select update Threads_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 ~ /Innodb_rows_deleted$/ {deleted=$4-ldeleted;ldeleted=$4;} $2 ~ /Innodb_rows_inserted$/ {inserted=$4-linserted;linserted=$4;} $2 ~ /Innodb_rows_read$/ {read=$4-lread;lread=$4;} $2 ~ /Innodb_rows_updated$/ {updated=$4-lupdated;lupdated=$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 "------------------------------------------------------- nQPS Commit Rollback TPS delete insert select update Threads_con Threads_run n------------------------------------------------------- ";} else{ count+=1; printf "%-6d %-8d %-7d %-8d %-6d %-8d %-7d %-8d %-10d %d n", q,c,r,c+r,deleted,inserted,read,updated,tc,tr; } }}'

执行结果如下所示

[mysql@localhost ~]$ ./mysql_tps1.sh QPS Commit Rollback TPS delete insert select update Threads_con Threads_run ------------------------------------------------------- 2682 122 0 122 125 119 60109 310 130 58 3226 236 0 236 227 239 52536 396 130 36 2902 120 0 120 128 119 53944 255 130 43 2239 103 0 103 93 101 58825 198 130 80 2744 157 0 157 158 158 59333 347 130 121 2661 97 0 97 111 102 52633 196 130 59 2956 160 0 160 150 156 56371 284 130 22 2882 140 0 140 139 141 60888 277 130 27 2753 130 0 130 128 127 57236 278 130 128 2680 150 0 150 153 151 58142 302 130 40 2812 124 0 124 130 130 59764 244 130 20 ------------------------------------------------------- QPS Commit Rollback TPS delete insert select update Threads_con Threads_run ------------------------------------------------------- 2583 126 0 126 129 129 54180 260 130 22 2855 148 0 148 144 146 61005 292 130 28 2720 130 0 130 136 131 59835 278 130 63 2919 148 0 148 142 147 54369 270 130 49 2571 127 0 127 136 134 53447 276 130 37 2715 134 0 134 129 128 58469 260 130 26 2733 135 0 135 132 134 55638 268 130 77 2890 149 0 149 156 155 62622 303 130 26 2911 148 0 148 143 145 54919 285 130 44 2838 139 0 139 137 134 60621 277 130 56 2758 139 0 139 145 144 58161 275 130 45

这个脚本你get了吧。