[ES三周年]如何计算数据库的TPS和QPS

发布于

介绍

我们在做数据库性能压力测试、做监控和告警项，或者想要真实地了解业务数据库负载的时候，常常需要使用两个数字化的衡量指标。他们是什么？相信很多数据库从业的读者已经呼之欲出了，那就是 QPS 和 TPS。

我们经常使用到这两个指标，那我们是否清楚他们是什么，在 MySQL 中应该如何计算获得呢？今天这里就是刨根问底栏目组...

QPS的定义和计算方法

首先我们来确认一下什么是 QPS。

根据百度百科，QPS 即 Queries-per-second，是每秒查询率的意思。这个定义是非常明确的。

下面我们探讨一下，他在 MySQL 里是如何计算的。

我们先去官网查询下是否有官方的说明~

很遗憾，MySQL 官网并没有对 QPS 做出明确的解释，那么就由我来带大家一起探讨一下这个 QPS 应该怎么计算吧。经过我的研究，网上普遍存在三种 QPS 的计算方法。

  • QPS = DQL(select)-per-second
  • QPS = Queries-per-second
  • QPS = Questions-per-second

大家看到这三个方法可能已经懵逼了，请大家稍安勿躁，我来详细讲解一下。

方法一、QPS = DQL(select)-per-second

使用这个计算方法的人，普遍认为 QPS 的 Query，中文意思是查询的意思，所以对应的就是所有 DQL 语句，即 select 打头的语句。这种计算方式算出来的 QPS 意味着是数据库服务器的只读压力，如果数据库没有读只有写，那么他的 QPS 即为 0，这显然是不合理的，相信只有极少数人采用了这种计算方法。

方法二、Queries-per-second

方法一的计算方法是有问题的，原因在于把 QPS 中的 Q ，即 Query 理解为"查询"，并偏执地理解为 DQL了，理解为 select only了，这是一种对 Q 的狭义的理解。而实际上这里的 Q 是广义的，大家想想什么是 SQL？SQL = DQL + DML + DDL + DCL，所以 QPS 中的 Q 应该和 SQL 中的 Q 一样，都是广义上的 Query，也就是所有的 SQL 语句。 那么我们如何获取 MySQL 数据库服务器上所有的 SQL 语句总数?

我们知道，从show global status like 'Queries'能获取 Queries 数，官方对这个 status 值的解释是:

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.

中文的意思是，Queries 计数表示服务器执行的语句数。与 Questions 计数不同，此变量包括了存储过程中执行的语句。它不计数COM_PING或COM_STATISTICS命令。

所以方法二的计算方法就是，从show global status like 'Queries'能获取 Queries 数，然后经过 t 秒，再从show global status like 'Queries'能获取 Queries 数，前后 Queries 数的差值，除以 t 秒，即算出 Queries-per-second。

看起来这种计算方式是正确的。

先别那么快下定论，咱们再看看第三种计算方法。

方法三、Questions-per-second

方法三的计算方法和方法二类似，只是把show global status like 'Queries'修改为从show global status like 'Questions'。

那么 Questions 代表什么呢？以下是官方文档的解释:

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, orCOM_STMT_RESETcommands

这里我们可以看出，Queries 计数和 Questions 计数的区别在

Queries 计数

Questions 计数

备注

因为 Queries 计数统计的更多，所以理论上 Queries 计数总是大于等于 Questions 计数。

采用 Queries 计数还是 Questions 计数，基本是见人见智了。而我们这边由于几乎没有业务使用到存储过程和预准备语句，所以用哪一种方式都一样。

有趣的现象是，官方用的是第二种方法"Queries-per-second"。纳尼？不是说官方文档没定义和说明吗？ emem，这是我的发现，见下图。

截图是登录 mysql 客户端后输入\s（status）的结果，经过我的验证，这里的 Queris per second avg，等于 Questions/Uptime 而不是 Queries/Uptime。

当然这里显示的 Queris per second avg 参考意义不大，因为分母是 Uptime，也就是 mysqld 服务启动的时间。不能真实的反馈真实的、瞬时的 QPS 指标。还是老老实实用方法二提到的计算思路，获取 t 秒 Questions 的变化值，然后除以 t 秒这种方法来计算吧。

稍等，好像有点问题！ 你不是说官方采用的是 Queries-per-second 方法吗，怎么用 Questions/Uptime 而不是 Queries/Uptime？请看下图~

其实官方的\s（status）的 Questions 值的结果是来源于 show global status like 'Queries' 而不是 show global status like 'Questions' 。所以这里的 Questions 实际上是 Queries，所以我归类为第二种方法"Queries-per-second"，没有毛病。

不清楚是否算是一个文字显示上的 BUG ，也许官方自己都在纠结中吧。

TPS的定义和计算方法

相对于 QPS 的定义，TPS 的定义我们不用查看百度百科了，因为在官方文档就可以找到对于 TPS 的说明：

TPS Acronym for “transactions per second”, a unit of measurement sometimes used in benchmarks. Its value depends on the workload represented by a particular benchmark test, combined with factors that you control such as the hardware capacity and database configuration.

TPS 是 "Transactions Per Second" （每秒事务数）的缩写，是一种用于基准测试的测量单位，是一台数据库服务器在单位时间内处理的事务的个数。它的值取决于一个特定的基准测试所代表的工作负荷，以及其他的因素，如硬件容量和数据库配置。

明确了 TPS 的含义为每秒的事务数，还需要知道在 MySQL 数据库中只有使用了 Innodb 数据库引擎的数据库或表才支持事务，在 MySQL 中现在最常用的存储引擎就是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

潜台词: 别傻傻的和我讨论 MyISAM 存储引擎下的 TPS 了。

关于 TPS 的计算方法，网上也是众说纷纭，我们继续探讨一下真正的 TPS 计算方法。

  • 方法一: 计算增删改查总和
  • 方法二: 计算 commit、rollback 总和
  • 方法三: 计算 Gtid 增长值

方法一、计算增删改查总和

在前面的 QPS 计算中，我们学会了从show global status里获取一些 SQL 语句计数统计值，用于计算 QPS，TPS 同样地可以。 我们可以获取 com_insert、com_delete、com_update、com_select来计算 TPS 。

官方文档的解释如下:

Com_xxx 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.

中文意思是，这个 Com_xxx 语句计数器变量指示每个变量的次数。xxx 语句已执行。每种类型的语句都有一个状态变量。例如,Com_delete和Com_update分别表示执行 DELETE 和 UPDATE 语句的次数。Com_delete_multi和Com_update_multi相似，但适用于 DELETE 和 UPDATE 使用多表语法的语句。

这里能发现如果涉及多表删除或者多表更新情况，需要使用的计数变量是Com_delete_multi和Com_update_multi。

也就是方法一的计算公式为: TPS = 单位时间 t 内 (com_insert + com_delete + com_update + com_select + Com_delete_multi + Com_update_multi)的增长值/ 单位时间 t

这里，我们不深究计数器使用得是否正确，由于我们是要计算每秒事务数，鉴于一个事务里可以跑多个 SQL，这种计算公式明显违反了定义，是错误的。

方法二: 计算 commit、rollback 总和

是事务就需要有 begin 和 commit/rollback 语句，对吧。所以计算 commit、rollback 的总和，即计算com_commit+com_rollback ，也可以计算出 TPS，对吧？

错错错! MySQL 区别于 Oracle，在 Oracle 里事务是需要显示提交的，必须执行 commit 提交事务。而 MySQL 默认是设置了自动提交的(参数 autocommit=1)。 所以 MySQL 不 commit 也是可以的，只要不显式地包裹了 begin 和 commit/rollback，那么一条 SQL 下发完，就会自动提交，就是一条事务。现在大多数 MySQL 的开发人员都是大量地使用自动提交。

所以会有一个很尬尴的现象，就是: 在一套一主一从的 MySQL 数据库集群里，主库因为不主动执行commit，com_commit为 0 ，所以采用方法二计算出来的 TPS 为 0，而 binlog 是会自动补 commit 语句的，复制到从库时，从库回放 SQL，会带 commit，那么从库会有com_commit，从库的 TPS 是真实的。

这种统计方法，显然是我们不能接受的。

那么我认为，对于方法二，可以按以下思路来改造：

Handler_commit+Handler_rollback+Com_commit+Com_rollback+Com_rollback_to_savepoint+Handler_savepoint_rollback

其中Handler_commit、Handler_rollback等打头的计数器是隐式提交的计数器。我只提供思路，不保证数据正确性，具体计算方法，读者可以尝试按这个思路改造。

方法三、用 GTID 计算 TPS

熟悉 MySQL 的同学肯定清楚开启数据库的 GTID 是一项硬性指标，那么 GTID 是什么？

GTID（ Global Transaction Identifier）全局事务标识，其保证为每一个在 master 提交的事务在复制集群中可以生成一个唯一的 ID。一个 GITD 由两部分组成的，分别是 source_id 和 transaction_id，结构为 GTID=source_id:transaction_id，其中 source_id 就是执行事务的主库的 server-uuid 值，server-uuid 值是在 mysql 服务首次启动生成的，保存在数据库的数据目录中，在数据目录中有一个 auto.conf 文件，这个文件保存了 server-uuid 值（唯一的）。而 transaction_id 则是从 1 开始自增的序列，表示这个事务是在主库上执行的第几个事务，MySQL 会保证这个事务和 GTID 是一比一的关系。

既然一个事务只会生成一个唯一的 GTID，而且 transaction_id 的部分还是顺序递增的序列，那么根据这个值来计算 TPS 是应该是最准确的一种方式了。

MySQL 5.6 版本开始支持 GTID 功能。

知道了基于GTID来计算TPS最准确，那如何计算呢？ 在 MySQL 上，可以使用 show master status 命令来查看 Executed_Gtid_Set 的值，这个值表示已经在这个实例上执行的 GTID集合。

如果是从库，执行show slave status中输出的对应列Executed_Gtid_Set，含义也相同。

比如下面这种情况，直接可以根据单位时间内两次输出结果 GTID 数值差值与单位时间之商计算得出 TPS。

代码语言：javascript
复制
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 926206
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 59bf2dea-e3b5-11eb-ae63-02000aba3f7b:1-2516
1 row in set (0.00 sec)

这里可能有人会说，我这没算对，因为我这里使用的 GTID 确实可以保证所有计数都是事务的，但并没有包含 select 类型的事务。

我给出两个解释吧:

  1. 前面提到了，GTID（ Global Transaction Identifier）表示全局事务标识，GTID 没有给 select only 的事务一个 GTID 编号，也就是官方根本没有打算把这类查询的事务认为是事务，所以 GTID 本身确实是一种狭义的事务的概念，所以我们这边计算的 TPS 也是一种狭义的 TPS，但问题是，这就是我们真正需要的 TPS！
  2. 如果您关注业务的读，大可以看 QPS，如果您关注事务，关注业务的写入，那就看 TPS，我的定义更利于实现这个读写维度分离的关注。

总结一下

本文探讨了 QPS 和 TPS 的各种计算方法，并给出我们认为的最佳计算方法。

#es#com#commit#mysql#status