江苏龙网

  1. 主页 > 生活百科 > >

Mysql库TPS，QPS实时监控脚本

Mysql库

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

Mysql库TPS，QPS实时监控脚本

文章插图
【Mysql库TPS，QPS实时监控脚本】 
在生产上部署MySQL时 ， 都会对同一配置的mysql数据库做QPS和TPS压测 ， 获取QPS和TPS的容量数据 ， 一旦上生产之后 ， 应用的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用途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语句的执行情况 ， 可以将脚本进行升级 ， 其内容如下 所示
 
改进型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;}}}'


推荐阅读


上一篇：简单、快速解决系统分区C盘空间不足的3种方法

下一篇：头条号满月复盘｜自媒体新手的进阶之路，有你想要的干货