mysql监控调优

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

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

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

7 MyISAM Key Buffer命中率和缓冲区使用率

mysql> show global status like 'key_%'; +------------------------+-----------+ | Variable_name | Value | +------------------------+-----------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 106662 | | Key_blocks_used | 107171 |

| Key_read_requests | 883825678 | | Key_reads | 133294 |

| Key_write_requests | 217310758 | | Key_writes | 2061054 | +------------------------+-----------+ 7 rows in set (0.00 sec)

mysql> show variables like '%key_cache_block_size%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+

| key_cache_block_size | 1024 | +----------------------+-------+ 1 row in set (0.00 sec)

mysql> show variables like '%key_buffer_size%'; +-----------------+-----------+

| Variable_name | Value | +-----------------+-----------+

| key_buffer_size | 134217728 | +-----------------+-----------+ 1 row in set (0.00 sec)

缓冲区的使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.6%

读命中率=1-Key_reads /Key_read_requests=99.98%

写命中率=1-Key_writes / Key_write_requests =99.05%

可看到缓冲区的使用率并不高，如果很长一段时间后还没有使用完所有的键缓冲，可以考虑把缓冲区调小一点。

键缓存命中率可能意义不大，因为它和应用相关，有些应用在95%的命中率下就工作良好，有些则需要99.99%，所以从经验上看，每秒的缓存未命中次数更重要，假设一个独立磁盘每秒能做100个随机读，那么每秒有5个缓冲未命中可能不会导致I/O繁忙，但每秒80个就可能出现问题。

每秒缓存未命中=Key_reads/uptime=0.33

8 临时表使用情况

mysql> show global status like 'Created_tmp%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+

| Created_tmp_disk_tables | 19226325 | | Created_tmp_files | 117 |

| Created_tmp_tables | 56265812 | +-------------------------+----------+ 3 rows in set (0.00 sec)

mysql> show variables like '%tmp_table_size%'; +----------------+----------+ | Variable_name | Value | +----------------+----------+

| tmp_table_size | 67108864 | +----------------+----------+ 1 row in set (0.00 sec)

可看到总共创建了56265812 张临时表，其中有19226325 张涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp_table_size的值，我设的是64M。该比值应该控制在0.2以内。

9 binlog cache使用情况