The meaning and calculation method of QPS and TPS of MySQL database

When doing DB benchmark testing, qps and tps are key indicators for measuring database performance. This article compares two calculation methods available online. Let’s first understand the relevant concepts.

Concept introduction:

• QPS: Queries Per Second is the number of queries a server can respond to per second. It is a measure of the amount of queries a specific query server handles within a specified time.
• TPS: Transactions Per Second is the number of transactions per second, which is the number of transactions processed by a database server in a unit of time.

The terms QPS and TPS are often mentioned in database performance monitoring. The following is a brief introduction to the meaning and calculation methods of QPS and TPS in MySQL databases.

1 QPS: Query per second. Here, QPS refers to the total number of queries executed by MySQL Server per second. The calculation method is as follows:

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

2 TPS: Transactions per second. The TPS value requested by the client application is obtained in the following way. The calculation method is 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), which is the number of read and write (I/O) operations per second. It is mostly used in databases and other occasions to measure the performance of random access.

The IOPS performance of the storage side is different from the IO on the host side. IOPS refers to how many times the storage can receive access from the host per second. One IO from the host requires multiple accesses to the storage to complete. For example, when the host writes a minimum data block, it also has to go through three steps: "sending a write request, writing data, and receiving a write confirmation", which means three storage end accesses.

The main IOPS testing benchmark tools include Iometer, IoZone, FIO, etc., which can be used comprehensively to test the IOPS of the disk under different situations. For application systems, you need to first determine the load characteristics of the data, then select reasonable IOPS indicators for measurement and comparative analysis, and then choose the appropriate storage media and software system based on this.

Questions records all select and dml queries since mysqld was started, including the number of show command queries. This is somewhat inaccurate. For example, many databases have monitoring systems running, which perform a show query on the database every 5 seconds to obtain the current database status. These queries are recorded in the QPS and TPS statistics, causing a certain amount of "data pollution".

If there are many MyISAM tables in the database, it is more appropriate to use questions for calculation.

If there are many InnoDB tables in the database, it is more appropriate to use the com_* data source for calculation.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links