MySQL几点重要的性能指标计算和优化的方法代码总结

下面小编就为大家带来一篇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)

针对MyISAM：

mysql每打开一个表，都会读入一些数据到table_open_cache 缓存 中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取，所以该值要设置得足够大以避免需要重新打开和重新解析表的定义，一般设置为max_connections的10倍，但最好保持在10000以内。

还有种依据就是根据状态open_tables的值进行设置，如果发现open_tables的值每秒变化很大，那么可能需要增大table_open_cache的值。

table_definition_cache 通常简单设置为服务器中存在的表的数量，除非有上万张表。

针对InnoDB：

与MyISAM不同，InnoDB的open table和open file并无直接联系，即打开frm表时其相应的ibd文件可能处于关闭状态；

故InnoDB只会用到table_definiton_cache，不会使用table_open_cache；

其frm文件保存于table_definition_cache中，而idb则由innodb_open_files决定(前提是开启了innodb_file_per_table)，最好将innodb_open_files设置得足够大，使得服务器可以保持所有的.ibd文件同时打开。

5 最大连接数

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

我们设置的max_connections大小

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

通常max_connections的大小应该设置为比Max_used_connections状态值大，Max_used_connections状态值反映服务器连接在某个时间段是否有尖峰，如果该值大于max_connections值，代表客户端至少被拒绝了一次，可以简单地设置为符合以下条件：Max_used_connections/max_connections=0.8

6 Innodb 缓存命中率

mysql> show global status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name             | Value    |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd   | 0      |
| Innodb_buffer_pool_read_ahead     | 268720    |   //预读的页数
| Innodb_buffer_pool_read_ahead_evicted | 0      |   
| Innodb_buffer_pool_read_requests   | 480291074970 | //从缓冲池中读取的次数
| Innodb_buffer_pool_reads       | 29912739   |     //表示从物理磁盘读取的页数
+---------------------------------------+-