Mysql库TPS，QPS实时监控脚本
2020-09-02
加入收藏
作为一名数据库运维人员，手里都有一些工具脚本，这些都是你提升运维效率，快速排查故障的利器。
在生产上部署MySQL时，都会对同一配置的mysql数据库做QPS和TPS压测，获取QPS和TPS的容量数据，一旦上生产之后，应用的TPS，QPS达到容量的告警阀值，则会建议应用数据库进行拆分，扩容。
生产上的TPS，QPS指标对应数据库来说是非常重要，所以排查问题时，通常会实时的查看TPS，QPS指标值，下面就给大家分享一个实时查看TPS，QPS指标值的shell脚本。
脚本内容如下所示
#!/bin/bash
mysqladmin -uroot -proot --socket=/u02/run/3308/mysql.sock extended-status -i1|awk 'BEGIN{local_switch=0;print "QPS Commit Rollback TPS 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 ~ /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 Threads_con Threads_run 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=sbtest --oltp-test-mode=complex --oltp-tables-count=1 --oltp-table-size=100000 --threads=128 --oltp-nontrx-mode=select --oltp-read-only=off --max-time=40 --report-interval=5 run
[ 5s ] thds: 128 tps: 122.91 qps: 2770.42 (r/w/o: 1992.38/507.44/270.61) lat (ms,95%): 1618.78 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 128 tps: 121.83 qps: 2551.02 (r/w/o: 1795.04/511.32/244.66) lat (ms,95%): 1648.20 err/s: 0.20 reconn/s: 0.00
[ 15s ] thds: 128 tps: 138.79 qps: 2666.28 (r/w/o: 1860.92/527.98/277.39) lat (ms,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.00
SQL 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: 5475
Latency (ms):
min: 97.28
avg: 942.69
max: 15577.39
95th percentile: 1533.66
sum: 5161211.71
Threads 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/bash
mysqladmin -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了吧。
