在生产上部署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/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了吧。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至2705686032@qq.com 举报，一经查实，本站将立刻删除。原文转载： 原文出处：
