Significance and Calculation Method of Mysql database QPS and TPS

Significance and Calculation Method of Mysql database QPS and TPS

The terms QPS and TPS are often mentioned in the monitoring of database performance. Next we will briefly share the meanings and calculation methods of QPS and TPS in the MySQL database.

1 TPS: the transaction volume per second. You can obtain the TPS value requested by the client application in the following way:

Questions = show global status like 'questions ';
Uptime = show global status like 'uptime ';
QPS = Questions/Uptime

2 QPS: queries per second. The QPS here refers to the total number of queries executed by the MySQL Server per second. 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), that is, the number of read/write (I/O) Operations Per Second. It is mostly used for databases and other occasions to measure the performance of random access.

The storage IOPS performance is different from the host I/O. IOPS refers to the number of accesses allowed by the host per second. Only one IO of the host can be completed after multiple accesses to the storage. For example, when a host writes a minimum data block, it must go through three steps, namely, sending write requests, writing data, and receiving write confirmation.

IOPS testing benchmark tools include Iometer, IoZone, and FIO, which can be used to test disk IOPS in different situations. For an application system, you must first determine the load characteristics of the data, then select appropriate IOPS indicators for measurement and comparative analysis, and then select the appropriate storage medium and software system.