如何计算MYSQL中的TPS和QPS指标

1.指标介绍

•QPS： Queries Per Second
查询量/秒，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理查询量多少的衡量标准。

•TPS : Transactions Per Second 事务数/秒，是一台数据库服务器在单位时间内处理的事务的个数。

2.QPS 指标

先来认识一下以下几个有关查询的指标：

1.Questions： MySQL从上一次启动到当前客户端发送给MySQL的查询数量，不包括存储过程内部的查询。它不统计COM_PING,COM_STATISTICS,COM_STMT_PREPARE,COM_STMT_CLOSE,COM_STMT_RESET数量，但它会把show命令计算到指标当中。

2.Queries： MySQL从上一次启动到当前客户端发送给MySQL的查询数量，包括存储过程内部的查询，它不统计COM_PING和COM_STATISTICS两个命令。

3.Com_select： MySQL从上一次启动到当前所执行的查询语句总数量。

由上可见，在数据库中执行show命令会使questions值加1，而com_select则不记录，而且对于数据库的监控，经常会用到show命令，所以在用questions方式计算时，数据其实是被污染的。而且questions的值在设置环境变量的时候，也是一直在增长的，而com_select的值在此过程中，并不增长。所以在使用question方式进行计算时，人为拉高了qps的结果，相对来说，使用com_select此种方式来计算qps，相对比较帖近真实情况一些，也就是说，在同等条件下，拉高了qps的值。

以下将介绍通过Questions方式以及Com_select方式计算QPS

2.1 Questions方式计算QPS

1.Questions方式计算QPS公式

questions = show global status where variable_name='Questions';
uptime = show global status like 'Uptime';
qps=questions/uptime

如上，拿当前Questions值除去Uptime=QPS，这个QPS的意义为从MySQL上一次启动到当前并且包含show命令平均每秒的QPS值，假如某个时间段的查询数量特别高，但是通过除Uptime时间，也会被拉下来。并且此questions值包含了show命令及环境变量所造成的数据污染。

2.Questions方式计算QPS指标SQL

注：以下SQL在MySQL8.0中进行测试，5.6或5.7中的稍有差异。

select round(sum(if(variable_name='Questions',variable_value,0))/sum(if(variable_name='Uptime',variable_value,0)),1) as 'QPS' from performance_schema.global_status where variable_name in ('Questions','Uptime');

2.2 Com_select方式计算QPS

通过Com_select来计算QPS，可以连续获取两次Com_select指标，拿新指标减去老指标后再除于间隔时长的出间隔时间内的每秒平均值。这种方式更贴近真实一些。

1.Com_Select方式计算QPS脚本

Com_Select = show global status where Variable_name='Com_select';
#!/usr/bin/env bash
OLD_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep $1
NEW_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
echo "($NEW_QPS-$OLD_QPS) / $1" | bc

3.TPS 指标

1.获取TPS指标的方式也有两种：

1.基于com_commit、com_rollback计算tps

2.基于com_insert、com_delete、com_update的status,变量计算tps

2.基于com_commit、com_rollback计算tps

相关指标介绍：
•Com_commit： MySQL从上一次启动到当前所执行的提交语句总数量

•Com_rollback： MySQL从上一次启动到当前所执行的回退语句总数量

1.基于com_commit和com_rollback方式计算TPS公式

这样计算出来的TPS也是MySQL从上次启动到当前平均每秒的TPS指标

com_commit = show global status where variable_name='com_commit';

com_rollback = show global status where variable_name='com_rollback';

uptime = show global status where Variable_name='Uptime';

tps=(com_commit + com_rollback)/uptime

2.计算脚本

#!/usr/bin/env bash
COM_COMMIT=`echo "show global status where Variable_name='Com_commit';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
COM_ROLLBACK=`echo "show global status where Variable_name='Com_rollback';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
UPTIME=`echo "show global status where Variable_name='Uptime';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`echo "($COM_COMMIT + $COM_ROLLBACK) / $UPTIME" | bc

3.基于com_insert、com_delete、com_update的status计算tps

1.相关指标介绍：

•Com_update： MySQL从上一次启动到当前所执行的更新语句总数量

•Com_delete： MySQL从上一次启动到当前所执行的删除语句总数量

•Com_insert： MySQL从上一次启动到当前所执行的插入语句总数量

2.计算脚本

指定间隔时间内取值两次，然后新指标减去老指标后三个指标相加再除以间隔时间得出间隔时间内每秒平均TPS

#/usr/bin/env bash
OLD_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep $1
NEW_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`echo "(($NEW_COM_INSERT - $OLD_COM_INSERT) + ($NEW_COM_UPDATE - $OLD_COM_UPDATE) + ($NEW_COM_DELETE - $OLD_COM_DELETE)) / $1" | bc