MySQL 之 IOPS、QPS、TPS
IOPS
IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数)，是衡量磁盘性能的主要指标之一
QPS
QPS（Queries Per Second）就是每秒的查询数，对数据库而言就是数据库每秒执行的 SQL 数（含 insert、select、update、delete 等）
计算方式
- Questions = SHOW GLOBAL STATUS LIKE 'Questions';
- Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
- QPS=Questions/Uptime
MySQL [devops]> show global status like 'Questions';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 5391300412 |
+---------------+------------+
1 row in set (0.01 sec)
MySQL [devops]> show global status like 'Uptime';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 46230664 |
+---------------+----------+
1 row in set (0.00 sec)
MySQL [devops]> select 5391300412/46230664;
+---------------------+
| 5391300412/46230664 |
+---------------------+
| 116.6174 |
+---------------------+
1 row in set (0.00 sec)
TPS
适用 innodb Transactions Per Second（每秒传输的事物处理个数），即服务器每秒处理的事务数
计算方式
- 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
MySQL [devops]> show global status like 'Com_commit';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_commit | 44406593 |
+---------------+----------+
1 row in set (0.00 sec)
MySQL [devops]> show global status like 'Com_rollback';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback | 10316 |
+---------------+-------+
1 row in set (0.01 sec)
MySQL [devops]> show global status like 'Uptime';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 46230327 |
+---------------+----------+
1 row in set (0.01 sec)
MySQL [devops]> select (44406593+10316)/46230327;
+---------------------------+
| (44406593+10316)/46230327 |
+---------------------------+
| 0.9608 |
+---------------------------+
1 row in set (0.01 sec)