江苏龙网

  1. 主页 > 生活百科 > >

运维小工具：Mysql库TPS，QPS实时监控脚本

Mysql库

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

运维小工具：Mysql库TPS，QPS实时监控脚本

文章插图
 
在生产上部署MySQL时，都会对同一配置的mysql数据库做QPS和TPS压测，获取QPS和TPS的容量数据，一旦上生产之后，应用的TPS，QPS达到容量的告警阀值，则会建议应用数据库进行拆分，扩容 。
【运维小工具：Mysql库TPS，QPS实时监控脚本】生产上的TPS，QPS指标对应数据库来说是非常重要，所以排查问题时，通常会实时的查看TPS，QPS指标值，下面就给大家分享一个实时查看TPS，QPS指标值的shell脚本 。
脚本内容如下所示#!/bin/bashmysqladmin -uroot -proot --socket=/u02/run/3308/mysql.sock 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;}}}'在这里我用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.00SQL statistics:queries performed:read:76664write:21901other:10951total:109516transactions: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.4493stotal number of events:5475Latency (ms):min:97.28avg:942.69max:15577.3995th percentile:1533.66sum:5161211.71Threads fairness:events (avg/stddev):42.7734/2.45execution time (avg/stddev):40.3220/0.11
运维小工具：Mysql库TPS，QPS实时监控脚本

文章插图
 
用途TPS，QPS监控脚本监控，看一下监控内容是否和sysbench的结果有差异
[mysql@localhost ~]$ ./mysql_tps.sh ------------------------------------------------------- QPSCommit RollbackTPSThreads_con Threads_run ------------------------------------------------------- 3090186018613040 2661131013113089 2603129012913045 255792092130123 2066126012613011 2638123012313025 27701740174130127 3006172017213039 2797117011713043 2247103010313080 27421570157130119 ------------------------------------------------------- QPSCommit RollbackTPSThreads_con Threads_run ------------------------------------------------------- 2974159015913022 2864141014113023 27541300130130122 2685149014913040 2809126012613021 2631140014013021 2594126012613023 2868148014813028 2696130013013063 2920148014813049 2569127012713037 从sysbench的结果可以看到，TPS为135.35 per sec，QPS为2707.35 per sec
这个结果和我们的脚本监控基本是一致的 。
在这里如果想要看insert,update,delete,select语句的执行情况，可以将脚本进行升级，其内容如下 所示
运维小工具：Mysql库TPS，QPS实时监控脚本

文章插图
 
改进型TPS，QPS监控脚本#!/bin/bashmysqladmin -uroot -proot --socket=/u02/run/3308/mysql.sock extended-status -i1|awk 'BEGIN{local_switch=0;print "QPSCommit RollbackTPSdeleteinsert selectupdateThreads_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 "------------------------------------------------------- nQPSCommit RollbackTPSdeleteinsert selectupdateThreads_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;}}}'


推荐阅读


上一篇：喝茶什么茶能减肥吗,什么茶减肥

下一篇：喝完咖啡可以喝茶吗,喝茶可以改变体内酸性体质吗