Technology Sharing | How to calculate MySQL QPS/TPS

Author: Chen Juncong

Member of the database team of China Mobile Information Infrastructure Department, mainly responsible for the maintenance of open source databases such as MySQL, TiDB, Redis, and clickhouse.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

When we are doing database performance stress testing, monitoring and warning items, or wanting to truly understand the business database load, we often need to use two digital measurement indicators. What are they? I believe that many readers in the database industry are already ready to come out, that is QPS and TPS.

We often use these two indicators, so do we know what they are and how to calculate them in MySQL? Today, here is the bottom question column group...

Definition and calculation method of QPS

First, let's confirm what QPS is.

According to Baidu Encyclopedia, QPS is Queries-per-second, which means query rate per second. This definition is very clear.

Let's discuss how it is calculated in MySQL.

Let's first go to the official website to check if there is an official description~

Unfortunately, the MySQL official website does not give a clear explanation of QPS, so let me take you to discuss how this QPS should be calculated. After my research, there are generally three QPS calculation methods on the Internet.

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

As you see these three methods may have been stunned, please stay calm and let me explain them in detail.

Method 1: QPS = DQL(select)-per-second

People who use this calculation method generally believe that QPS's Query means query in Chinese, so it corresponds to all DQL statements, that is, the statements beginning with select. The QPS calculated by this calculation means the read-only pressure of the database server. If the database does not read but write, then its QPS is 0, which is obviously unreasonable. I believe that only a few people use this calculation. method.

Method two, Queries-per-second

The calculation method of method one is problematic. The reason is that Q in QPS, that is, Query, is understood as "query", and paranoidly understood as DQL, understood as select only, which is a narrow understanding of Q . In fact, Q here is in a broad sense. Think about it, what is SQL? SQL = DQL + DML + DDL + DCL, so the Q in QPS should be the same as the Q in SQL, which is a generalized Query, that is, all SQL statements.
So how do we get the total number of all SQL statements on the MySQL database server?

We know that the show global status like 'Queries' . The official interpretation of the status value is:

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.

Chinese means that the Queries count indicates the number of statements executed by the server. Unlike the Questions count, this variable includes the statements executed in the stored procedure. It does not count COM_PING or COM_STATISTICS commands.

So the calculation method of the second method is show global status like 'Queries' , and then after t seconds, show global status like 'Queries' , and divide the difference between the previous and next Queries number by t seconds to calculate the Queries-per-second.

It seems that this calculation method is correct.

Don't draw conclusions so quickly, let's look at the third calculation method.

Method three, Questions-per-second

The calculation method of Method 3 is similar to Method 2, except that show global status like 'Queries' modified from show global status like 'Questions' .

So what does Questions stand for? The following is the explanation of the official document:

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

Here we can see that the difference between Queries count and Questions count is

	Queries count	Questions count	Remark
Stored procedure	Include	Does not contain	Internal storage statement, non-text SQL interaction
COM_STMT_PREPARE	Include	Does not contain	Prepared statements, non-text SQL interaction
COM_STMT_CLOSE	Include	Does not contain	Prepared statements, non-text SQL interaction
COM_STMT_RESET	Include	Does not contain	Prepared statements, non-text SQL interaction

Because Queries counts more statistics, in theory the Queries count is always greater than or equal to the Questions count.

Whether to use Queries counting or Questions counting, it's basically a matter of understanding. Since almost no business uses stored procedures and prepared statements here, we use either method in the same way.

The interesting phenomenon is that the official second method "Queries-per-second" is used. Nani? Doesn't it mean that the official document does not define and explain?
emem, this is my discovery, see the picture below.

\s (status) after logging in to the mysql client. After my verification, the Queris per second avg here is equal to Questions/Uptime instead of Queries/Uptime.

Of course, the Queris per second avg shown here is of little reference because the denominator is Uptime, which is the time when the mysqld service was started. Can not truly feedback the real and instantaneous QPS indicators. Or the calculation idea mentioned in the honest and practical method 2, get the change value of Questions in t seconds, and then divide by t seconds to calculate it.

Wait, there seems to be a problem! Didn't you say that the official method is Queries-per-second? How to use Questions/Uptime instead of Queries/Uptime? Please see the picture below~

In fact, the official \s (status) is derived from show global status like 'Queries' instead of show global status like 'Questions' . So the Questions here are actually Queries, so I classify them as the second method "Queries-per-second", there is nothing wrong with it.

It is not clear whether it is a BUG on the text display, maybe the official itself is entangled.

Definition and calculation method of TPS

Compared with the definition of QPS, we don't need to check Baidu Encyclopedia for the definition of TPS, because the description of TPS can be found in the official document:

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 is the abbreviation of "Transactions Per Second" (transactions per second), is a measurement unit used for benchmarking, is the number of transactions processed by a database server in a unit of time. Its value depends on the workload represented by a particular benchmark, as well as other factors such as hardware capacity and database configuration.

It is clear that the meaning of TPS is the number of transactions per second. It is also necessary to know that only databases or tables that use the Innodb database engine in the MySQL database support transactions. The most commonly used storage engine in MySQL is InnoDB, which is from MySQL 5.5. Version 5 has become the default storage engine.

Subtext: Don't be silly to discuss TPS under the MyISAM storage engine.

Regarding the calculation method of TPS, there are also different opinions on the Internet. Let's continue to discuss the real TPS calculation method.

• Method 1: Calculate the sum of additions, deletions, modifications, and checks
• Method 2: Calculate the sum of commit and rollback
• Method 3: Calculate Gtid growth value

Method 1: Calculate the sum of additions, deletions, modifications, and investigations

In the previous QPS calculation, we learned show global status , which can be used to calculate QPS, and TPS can be the same.
We can get com_insert、com_delete , com_update , com_select to calculate TPS.

The explanation of the official document is as follows:

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.

Chinese means that this Com_xxx statement counter variable indicates the number of times each variable. The xxx statement has been executed. Each type of statement has a state variable. For example, Com_delete and Com_update represent the number of DELETE and UPDATE statements, respectively. Com_delete_multi Com_update_multi , but it is suitable for DELETE and UPDATE statements that use multi-table syntax.

It can be found here that if multiple tables are deleted or multiple tables are updated, the count variables that need to be used are Com_delete_multi and Com_update_multi .

That is, the calculation formula of method one is:
TPS = within unit time t
( com_insert + com_delete + com_update + com_select + Com_delete_multi + Com_update_multi ) increase value / unit time t

Here, we do not delve into whether the counter is used correctly, because we want to calculate the number of transactions per second. In view of the fact that multiple SQLs can be run in one transaction, this calculation formula obviously violates the definition and is wrong.

Method 2: Calculate the sum of commit and rollback

For transactions, begin and commit/rollback statements are required, right. So calculate the sum of commit and rollback, that is, calculate com_commit + com_rollback , and you can also calculate TPS, right?

Wrong, wrong, wrong! MySQL is different from Oracle. In Oracle, transactions need to be displayed and committed, and commit must be executed to commit the transaction. And MySQL is set to automatically commit by default (parameter autocommit=1). Therefore, it is also possible for MySQL not to commit. As long as begin and commit/rollback are not explicitly wrapped, then a piece of SQL will be automatically submitted after it is issued, which is a transaction. Most MySQL developers now use automatic submission extensively.

Therefore, there will be a very embarrassing phenomenon: In a MySQL database cluster with one master and one slave, because the master database does not actively execute commit, com_commit is 0, so the TPS calculated by method 2 is 0, and the binlog It will automatically fill in the commit statement. When copying to the slave library, the SQL playback from the library will bring a commit, then the slave library will have com_commit, and the TPS of the slave library is real.

This statistical method is obviously unacceptable to us.

Then I think that the second method can be modified according to the following ideas:

Handler_commit+Handler_rollback+Com_commit+Com_rollback+Com_rollback_to_savepoint+Handler_savepoint_rollback

The Handler_commit with Handler_rollback and 061387b9e24f46 are implicitly submitted counters. I only provide ideas, and do not guarantee the correctness of the data. For specific calculation methods, readers can try to reform according to this idea.

Method 3: Calculate TPS with GTID

Those who are familiar with MySQL must know that the GTID for opening the database is a rigid indicator, so what is the GTID?

GTID (Global Transaction Identifier) global transaction identifier, which guarantees that a unique ID can be generated in the replication cluster for each transaction submitted by the master. A GITD consists of two parts, source_id and transaction_id, the structure is GTID=source_id:transaction_id, where source_id is the server-uuid value of the main database that executes the transaction, and the server-uuid value is generated when the mysql service is started for the first time. Save In the data directory of the database, there is an auto.conf file in the data directory, which saves the server-uuid value (unique). The transaction_id is a self-increasing sequence starting from 1, indicating that this transaction is the first transaction executed on the main database, and MySQL will ensure that this transaction has a one-to-one relationship with GTID.

Since a transaction will only generate a unique GTID, and the transaction_id part is still an increasing sequence, then calculating TPS based on this value should be the most accurate way.

MySQL 5.6 version starts to support GTID function.

knows that the calculation of TPS based on GTID is the most accurate, then how to calculate it?
On MySQL, you can use the show master status command to view Executed_Gtid_Set , which represents the GTID set that has been executed on this instance.

If the library is performed show slave status the corresponding output column Executed_Gtid_Set , the meaning is the same.

For example, in the following case, TPS can be calculated directly based on the quotient of the GTID value difference between the two output results in a unit time and the unit time.

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)

The boss of this blog gave a custom function to calculate TPS based on GTID, you can refer to it.
https://lefred.be/content/mysql-how-many-transactions-where-committed-during-an-interval-of-time/

Some people here may say that I am not right, because the GTID I use here can indeed guarantee that all counts are transactions, but it does not include select type transactions.

Let me give two explanations:

1. As mentioned earlier, GTID (Global Transaction Identifier) represents a global transaction identifier. GTID does not give a GTID number to select only transactions, that is, the official does not intend to consider such queries as transactions, so GTID itself is indeed a narrow sense The concept of transaction, so the TPS we calculate here is also a narrow TPS, but the problem is that this is the TPS we really need!
2. If you are concerned about business reading, you can look at QPS. If you are concerned about business and business writing, then look at TPS. My definition is more conducive to the realization of this separation of read and write dimensions.

in conclusion

This article discusses various calculation methods of QPS and TPS, and gives the best calculation method we think.

If there are errors or inconsistent opinions in the above content, please point out and make comments.

Reference documents:

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/