phpmysqlqps技巧_技能分享 若何计算 MySQL 的 QPSTPS

中移信息根本平台部数据库团队成员，紧张卖力 MySQL、TiDB、Redis、clickhouse 等开源数据库的掩护事情。

本文来源：原创投稿

爱可生开源社区出品，原创内容未经授权不得随意利用，转载请联系

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

我们常常利用到这两个指标，那我们是否清楚他们是什么，在 MySQL 中该当如何打算得到呢？本日这里便是刨根问底栏目组...

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

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

下面我们磋商一下，他在 MySQL 里是如何打算的。

我们先辞官网查询下是否有官方的解释~

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

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

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

方法一的打算方法是有问题的，缘故原由在于把 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 计数 备注 存储过程 包含 不包含 内部存储语句，非文本SQL交互 COM_STMT_PREPARE 包含 不包含 预准备语句，非文本SQL交互 COM_STMT_CLOSE 包含 不包含 预准备语句，非文本SQL交互 COM_STMT_RESET 包含 不包含 预准备语句，非文本SQL交互

由于 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。

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-25161 row in set (0.00 sec)

这篇博客的大佬，给出了一种自定义函数基于 GTID 打算 TPS 的方法，可以参考。
https://lefred.be/content/mysql-how-many-transactions-where-committed-during-an-interval-of-time/

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

我给出两个阐明吧:

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

本文磋商了 QPS 和 TPS 的各种打算方法，并给出我们认为的最佳打算方法。

如上内容如存在缺点或见地不一致，欢迎指出并提出见地。

参考文档:

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

https://lefred.be/content/mysql-how-many-transactions-where-committed-during-an-interval-of-time/

标签：打算方法