MYSQL教程MySQL几点重要的性能指标计算和优化方法总结

《MYSQL教程MySQL几点重要的性能指标计算和优化方法总结》要点：
本文介绍了MYSQL教程MySQL几点重要的性能指标计算和优化方法总结，希望对您有用。如果有疑问，可以联系我们。

MYSQL数据库1 QPS计算(每秒查询数)

MYSQL数据库针对MyISAM引擎为主的DB

MYSQL数据库
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)

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

MYSQL数据库针对InnnoDB引擎为主的DB

MYSQL数据库
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)

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

MYSQL数据库2 TPS计算(每秒事务数)

MYSQL数据库
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

MYSQL数据库3 线程连接数和命中率

MYSQL数据库
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)

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

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

MYSQL数据库4 表缓存

MYSQL数据库
mysql> show global status like 'open_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 2228 |
+---------------+-------+
1 row in set (0.00 sec)

MYSQL数据库我们设置的打开表的缓存和表定义缓存

MYSQL数据库
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)

MYSQL数据库针对MyISAM：

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

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

MYSQL数据库table_definition_cache 通常简单设置为服务器中存在的表的数量,除非有上万张表.

MYSQL数据库针对InnoDB：

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

MYSQL数据库故InnoDB只会用到table_definiton_cache,不会使用table_open_cache；

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

MYSQL数据库5 最大连接数

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

MYSQL数据库我们设置的max_connections大小

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

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

MYSQL数据库6 Innodb 缓存命中率

MYSQL数据库
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   |     //表示从物理磁盘读取的页数
+---------------------------------------+--------------+
5 rows in set (0.00 sec)

MYSQL数据库缓冲池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%

MYSQL数据库如果该值小于99.9%,建议就应该增大innodb_buffer_pool_size的值了,该值一般设置为内存总大小的75%-85%,或者计算出操作系统所需缓存+mysql每个连接所需的内存(例如排序缓冲和临时表)+MyISAM键缓存,剩下的内存都给innodb_buffer_pool_size,不过也不宜设置太大,会造成内存的频繁交换,预热和关闭时间长等问题.