How "MySQL" Calculates TPS,QPS

Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against coronavirus.

 

When doing db benchmarks, qps, tps are key metrics for measuring database performance. This article compares two methods of calculation on the Internet. Let's take a look at the related concepts.

Concept introduction:
QPS: Queries Per Second query volume per second, which is the number of queries per second that a server can respond to, and is a measure of how much query queries are processed by a particular query server within a specified time.
TPS : Transactions Per Second is the number of transactions per second, which is the number of transactions that a database server processes in a unit of time.
 
How to calculate:
Viewing from the Internet If you get qps for mysql, there are two ways to tps:

Method 1. Calculate qps based on questions, calculate tps based on com_commit com_rollback
Questions = show global status like ‘questions‘;
Uptime = show global status like ‘uptime‘;
Qps=questions/uptime
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
 
Method 2. Calculate tps, qps based on the com_* status variable
Use the following command:
Show global status where variable_name in(‘com_select‘, ‘com_insert‘, ‘com_delete‘, ‘com_update’);
Get the value of com_* at interval 1s and do the difference operation
Del_diff = (int(mystat2[‘com_delete‘]) - int(mystat1[‘com_delete‘]) ) / diff
Ins_diff = (int(mystat2[‘com_insert‘]) - int(mystat1[‘com_insert‘]) ) / diff
Sel_diff = (int(mystat2[‘com_select‘]) - int(mystat1[‘com_select‘]) ) / diff
Upd_diff = (int(mystat2[‘com_update‘]) - int(mystat1[‘com_update‘]) ) / diff
 
Is the value of the above calculation method accurate?

1 com_commit, com_rollback has nothing to do with displaying the specified transaction, only related to explicitly submitting the commit rollback.
2 com_* will increase by 1 regardless of whether the result of dml is successful.
 
b Test against the myisam table:
1 Perform a dml operation on the myisam table. Only the questions change the other values.
2 For the myisam storage engine to calculate its tps using com_*, qps is inaccurate, and it is more appropriate to use the value of the questions.
 
Use the script to get the qps of the database using different variables, tps comparison:
Qps_s is based on com_select
Qps_ques is based on questions ,
Tps_iud is based on the sum of com_insert, com_update, com_delete,
Tps_com_rol is based on the sum of com_commit com_rollback
The question based is larger than the value based on com_select, because the question itself is a collection of all db accesses.
 
to sum up:
Questions are the number of times the select, dml counts, including the show command, have been logged since the mysqld was started. This is somewhat inaccurate. For example, many databases have monitoring systems running, and a show query is performed on the database every 5 seconds to obtain the status of the current database. These queries are recorded in QPS, TPS statistics, resulting in certain "data." Pollution".
If there are more myisam tables in the database, the calculation is still appropriate.
If there are more innodb tables in the database, it is more appropriate to calculate the com_* data source.