MySQL几点重要的性能指标计算和优化

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_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 2228  |
+---------------+-------+
1 row in set (0.00 sec)

我们设置的打开表的缓存和表定义缓存

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

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