How to calculate the values of MySQL QPS and TPS
QPS: number of queries per second
TPS: the amount of things per second
The calculation method of QPS
Questions = SHOW GLOBAL STATUS LIKE 'Questions'
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'
QPS=Questions/Uptime
For example:
Mysql > show global status like 'questions'
+-+ +
| | Variable_name | Value |
+-+ +
| | Questions | 449106827 | |
+-+ +
1 row in set (0.00 sec)
Mysql > show global status like 'uptime'
+-+ +
| | Variable_name | Value |
+-+ +
| | Uptime | 238722 | |
+-+ +
1 row in set (0.00 sec)
Mysql > select 449106827 as 238722 as' QPS VALUE' from dual
+-+
| | QPS VALUE |
+-+
| | 1881.2963 |
+-+
1 row in set (0.00 sec)
_ _ _
The calculation method of TPS
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
For example:
Mysql > SHOW GLOBAL STATUS LIKE 'Com_commit'
+-+ +
| | Variable_name | Value |
+-+ +
| | Com_commit | 71050554 | |
+-+ +
1 row in set (0.00 sec)
Mysql > SHOW GLOBAL STATUS LIKE 'Com_rollback'
+-+ +
| | Variable_name | Value |
+-+ +
| | Com_rollback | 537161 | |
+-+ +
1 row in set (0.00 sec)
Mysql > SHOW GLOBAL STATUS LIKE 'Uptime'
+-+ +
| | Variable_name | Value |
+-+ +
| | Uptime | 238827 | |
+-+ +
1 row in set (0.00 sec)
Mysql > select (71050554 / 537161) / 238827 as' TPS VALUE' from dual
+-+
| | TPS VALUE |
+-+
| | 299.7472 |
+-+
1 row in set (0.00 sec)
