Mysql和Oracle如何计算QPS和TPS

0、参考资料

MySQL 5.7 Reference Manual -> 5.1.9 Server Status Variables
https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

Oracle Database Reference
https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3093.htm#REFRN30272

1、概念

QPS和TPS是衡量数据库性能的重要指标，反应了数据库的处理能力。

QPS：Queries Per Second

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

TPS : Transactions Per Second

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

2、MySQL中QPS和TPS计算方式

2.1、指标相关的一些状态参数值解释

show global status like 'uptime';

说明：The number of seconds that the server has been up.

MySQL server运行的总计时间，单位秒。在计算QPS和TPS的时候，需要查询前后两次查询值以及对应时间，数值差除以时间差就获得到了平均每秒指标的值。

show global status like 'Queries';

说明：The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.

The discussion at the beginning of this section indicates how to relate this statement-counting status variable to other such variables.

在mysql中执行的语句数量，包括DDL、DML语句、存储过程、show命令、set命令等，但是不包含PING和STATISTICS相关命令。

show global status like 'Questions';

说明：The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

通过对比Queries就知道，少了存储过程、COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET这些命令的执行量。

show global status like 'Com_commit';

show global status like 'Com_rollback';

show global status like 'Com_insert';

show global status like 'Com_delete';

show global status like 'Com_update';

show global status like 'Com_select';

说明：The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

顾名思义，就是sql增、删、改、查、提交、回退的次数记录,记录的范围更加具体。这些统计值指的发出某种类型命令的次数。例如，设置了自动提交事务，你在执行完update命令自动提交后，即使更新0行，Com_update计数增加1，Com_commit计数不增加。因为你没有敲commit命令。同样的，如果你没有任何事务做，单纯的敲commit命令，也会让Com_commit增加。

show global status like 'Handler_commit';

说明：The number of internal COMMIT statements.

相比于Com_commit，设置为自动提交后，dml语句执行结束后Handler_commit计数增加1。设置为手动提交后，dml语句执行结束后Handler_commit计数加1，commit提交后，计数再加1。

show global status like 'Handler_rollback';

说明：The number of requests for a storage engine to perform a rollback operation.

存储引擎执行rollback的次数；类似Handler_commit的现象。如果你闲的没事儿敲rollback命令，是不会计数的。

show global status like 'Handler_write';
show global status like 'Handler_delete';
show global status like 'Handler_update';

说明：The number of requests to insert/delete/update a row in a table.

插入表中的行数的计数，不是insert/delete/update语句条数。例如你insert into tab select xxx,插入了N条数据，那么Handler_write计数增加N。

更多TPS&PQS相关参数值解释参考官方文档

Threads_running                               
Uptime_since_flush_status 
Com_execute_sql 
Com_delete_multi                                
Com_insert_select          
Com_install_plugin         
Com_revoke                 
Com_revoke_all                          
Com_rollback_to_savepoint  
Com_savepoint              
Com_truncate               
Com_uninstall_plugin       
Com_unlock_tables                         
Com_update_multi           
Com_xa_commit              
Com_xa_rollback            
Com_xa_start                                
Handler_read_first         
Handler_read_key           
Handler_read_last          
Handler_read_next          
Handler_read_prev          
Handler_read_rnd           
Handler_read_rnd_next             
Handler_savepoint          
Handler_savepoint_rollback 

2.2、计算QPS

对于QPS来说，不仅要统计sql中查询的量，还要统计增删改以及存储过程等执行的量，这样数据才比较好看些。简单概括为下面的公式：

qps=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s

计算平均每秒执行次数方法：
mystat1：

show global status where variable_name in('com_select','com_insert','com_delete','com_update');

获取间隔1s 的 com_*的值，并作差值运算
mystat2: 1秒后再次执行上面的命令

最后做计算：

del_diff = (int(mystat2['com_delete']) - int(mystat1['com_delete']) ) / diff
ins_diff = (int(mystat2['com_insert']) - int(mystat1['com_insert']) ) / diff
sel_diff = (int(mystat2['com_select']) - int(mystat1['com_select']) ) / diff
upd_diff = (int(mystat2['com_update']) - int(mystat1['com_update']) ) / diff

最终做汇总：
qps = del_diff + ins_diff + sel_diff + upd_diff

上面的计算方法有几个问题：

1）、没有统计到存储过程的执行次数

2）、多个值相加，比较麻烦

上面不是有Queries和Questions可以直接查出来嘛。虽然多了些show、set等这样的管理命令，但毕竟对QPS污染比较少。所以，实际生产过程中，监控QPS选取的是Queries的值。

总结： MySQL中反映QPS一般用show global status like 'Questions’计算出来的结果。

2.3、计算TPS

对于TPS，我们需要统计的是增、删、改、回退的量，简单概括为以下公式：

tps= Com_insert/s + Com_update/s + Com_delete/s + Com_rollback/s

对上面公式的思考：

1）、对于事物，要么提交要么回退，可以只统计commit和rollback。

2）、Com_commit和Com_rollback并不能真实的反映事物，如果有应用闲的没事儿循环提交1亿次呢？

3）、多个指标汇总，也是比较麻烦的。

根据上面Handler_commit和Handler_rollback的解释说明，这两个指标可以比较准确的反映TPS。所以，生产实践中也一般是用这俩指标。

总结: MySQL中 TPS = Handler_commit/s + Handler_rollback/s

3、Oracle中QPS和TPS计算方式

3.1、Oracle和TPS&QPS相关性能视图

V$SYSMETRIC

V$SYSMETRIC displays the system metric values captured for the most current time interval for both the long duration (60-second) and short duration (15-second) system metrics.

该视图记录了数据库系统指标，反映了数据库当前的实时状态，记录了最近一分钟的值以及最近15秒的值。

V$SYSMETRIC_HISTORY

V$SYSMETRIC_HISTORY displays all system metric values available in the database. Both long duration (60-second with 1 hour history) and short duration (15-second with one-interval only) metrics are displayed by this view.

该视图是对V$SYSMETRIC的历史记录，但历史数据只记录了最近1小时的数据。

V$SYSMETRIC_SUMMARY

V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item.

视图是对V$SYSMETRIC_HISTORY中的历史记录一个汇总，计算了最近1小时内各个指标的最大值、最小值、平均值。

V$SYSSTAT

V$SYSSTAT displays system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.

该视图记录了数据库系统中指标自实例启动以来的累加值，和MySQL中show status中看到的一样，所以需要前后两次查询该视图计算差值然后除以时间差，获取指标每秒执行。

3.2、查询出QPS和TPS

Oracle作为RDBMS的一哥，很多细节设计的比较完善。在v$SYSMETRIC*这些视图中，已经帮你计算好了TPS和QPS。

方法1：查询V$SYSMETRIC或V$SYSMETRIC_HISTORY

QPS：

最近1分钟QPS
select  value from v$sysmetric where metric_name in ('Executions Per Sec') where group_id=2；
最近15秒QPS
select  value from v$sysmetric where metric_name in ('Executions Per Sec') where group_id=3

TPS：

select (select value from v$sysmetric where metric_name in ('User Commits Per Sec') and group_id=2) + 
(select value from v$sysmetric where metric_name in ('User Rollbacks Per Sec') and group_id=2) as TPS from dual;

方法2：自己查询V$SYSSTAT,类似mysql的方法，计算出QPS和TPS

QPS:前后两次差值/时间差

select  value from v$sysstat where name = 'execute count';

TPS：前后两次差值/时间差

select (select value from V$SYSSTAT where name in ('user commits')) + 
(select value from V$SYSSTAT where name in ('user rollbacks')) as TPS from dual;