1 QPS计算(每秒查询数)

针对MyISAM引擎为主的DB

mysql> show GLOBAL status like 'questions';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 2009191409 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388402 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=questions/uptime=5172，mysql自启动以来的平均QPS，如果要计算某一时间段内的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=(q2-q1)/(t2-t1)

针对InnnoDB引擎为主的DB

mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';

+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一时间段内的QPS查询方法同上。

2 TPS计算(每秒事务数)

mysql> show global status like 'com_commit';

+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_commit | 7424815 |
+---------------+---------+
1 row in set (0.00 sec)


mysql> show global status like 'com_rollback';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 389467 |
+---------------+--------+
1 row in set (0.00 sec)

TPS=(com_commit+com_rollback)/uptime=22

3 线程连接数和命中率

mysql> show global status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 480 | //代表当前此时此刻线程缓存中有多少空闲线程
| Threads_connected | 153 | //代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数
| Threads_created | 20344 | //代表从最近一次服务启动，已创建线程的数量
| Threads_running | 2 | //代表当前激活的（非睡眠状态）线程数
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> show global status like 'Connections';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Connections | 381487397 |
+---------------+-----------+
1 row in set (0.00 sec)

线程缓存命中率=1-Threads_created/Connections = 99.994%

我们设置的线程缓存个数

mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 500 |
+-------------------+-------+
1 row in set (0.00 sec)

根据Threads_connected可预估thread_cache_size值应该设置多大，一般来说250是一个不错的上限值，如果内存足够大，也可以设置成thread_cache_size值和threaads_connected值相同；

或者通过观察threads_created值，如果该值很大或一直在增长，可以适当增加thread_cache_size的值；在休眠状态下每个线程大概占用256KB左右的内存，所以当内存足够时，设置太小也不会节约太多内存，除非该值已经超过几千。

4 表缓存

mysql> show global status like 'open_ta