Significance and calculation method of QPS and TPS in Mysql database

When doing db benchmark test, qps and tps are the key indicators to measure database performance. This paper compares two calculation methods on the Internet. First, let's understand the related concepts.

Concept introduction:

QPS: Queries Per Second query volume/second is the corresponding query number per second of a server, which is a measure of the query volume handled by a specific query server within a specified time. TPS: Transactions Per Second is the number of transactions per second, the number of transactions processed per unit time by a database server.

The two terms QPS and TPS are often mentioned in the performance monitoring of the database. The following briefly shares the meanings and calculation methods of QPS and TPS in the MySQL database.

1 QPS: The amount of Query per second, where QPS refers to the total amount of Query executed by MySQL Server per second, calculated as follows:

Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime

2 TPS: Transactions per second, to get the TPS value requested by the client application, calculated as follows:

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
TPS=(Com_commit + Com_rollback)/Uptime

IOPS: (Input/Output Operations Per Second), that is, the number of read and write operations per second (I/O), which is mostly used in databases and other occasions to measure the performance of random access.

The performance of IOPS on the storage side is different from IO on the host side. IOPS refers to how many accesses from the host can be accepted by the storage per second, and one IO on the host needs multiple accesses to the storage before it can be completed. For example, when a host writes a smallest data block, it also goes through three steps, such as "sending a write request, writing data, and receiving a write confirmation", that is, three storage accesses.

IOPS testing benchmark tools mainly include Iometer, IoZone, FIO, etc., which can be comprehensively used to test IOPS of disks in different situations. For the application system, it is necessary to determine the load characteristics of data first, and then select a reasonable IOPS index for measurement and comparative analysis, and then select the appropriate storage medium and software system.

Questions is a record of all select, dml times since mysqld was started, including the number of queries for show commands. This is somewhat inaccurate. For example, many databases have monitoring systems running, and show queries are carried out on the databases every 5 seconds to obtain the current database status, and these queries are recorded in QPS and TPS statistics, resulting in a certain "data pollution".

If there are more myisam tables in the database, it is appropriate to calculate questions.

If there are more innodb tables in the database, the com_* data source is more appropriate for calculation.

Summarize