MySQL需要关注的参数及状态变量解读

目录

• MySQL需要关注的参数及状态变量
• 总结

MySQL需要关注的参数及状态变量

• open_files_limit

操作系统允许mysqld打开的文件数量。

这个值可以设置得比较大，比如50000，最好在系统初始化安装时就设置了一个较大的值。

可修改文件/etc/security/limits.conf来实现，

命令如下：

vi /etc/security/limits.conf
* -nofile 50000

• max_connect_errors

此值应设置得比较大，如大于5000，以避免因为连接出错而超过出错阈值，导致MySQL阻止该主机连接，如被阻塞，则须手动执行flush-hosts进行复位。

• max_connections

允许并行的客户端连接数目。默认值为100太小，一般会不够用。

生产环境中建议设置为2000~5000.注意，对于32位的MySQL由于有内存限制，连接数不能过大(建议小于800)，否则可能会由于连接过多，造成MySQL实例崩溃。

• max_used_connections

MySQL Server启动后曾经到达的最大连接数。

如果该值达到max_connections，那么某个时刻存在突然的高峰连接时，可能会有性能问题。

• threads_connected

当前打开的连接数量。这个值不能超过设置的max_connections*80%。需要注意及时调整max_connections的值。一旦连接数超过了max_connections，就会出现客户端连接不上的错误。

• aborted_connects

试图连接到MySQL服务器而失败的连接数。正常情况下，该值不会持续增加，出现连接失败的原因主要有如下几点：

• 1) 客户端程序在退出之前未调用mysql_close()。
• 2) 客户端的空闲时间超过了wait_timeout或interactive_timeout秒，未向服务器发出任何请求。
• 3) 客户端在数据传输中途突然结束。
• Aborted_clients

由于客户端没有正确关闭连接导致客户端终止而中断的连接数。

出现下述情况时，服务器将增加”Aborted_clients“(放弃客户端)的状态变量。

• 1) 客户端不具有连接至数据库的权限。
• 2) 客户端采用了不正确的密码。
• 3) 连接信息包含不正确的信息。
• 4) 获取连接信息包的时间超过了connect_timeout秒。

我们可以使用如下命令发现异常：

mysqladmin -uroot -p -S /path/to/tmp//3306/mysql.sock ext | grep Abort

也可以使用tcpdump来判断是什么原因导致了异常：

tcpdump -s 1500 -w tcp.out port 3306
strings tcpdump.out

• thread_cache_size

服务器应缓存多少线程以便重新使用？

当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程将被放入缓存。

如果有新连接请求分配线程则可以从缓存中重新利用线程，只有当缓存空了时才会创建新线程。如果新连接很多，则可以增加该变量以提高性能。

如果是大量并发的短连接，则可能会因为thread_cache_size不够而导致性能问题。生产环境中一般将其设置为100~200。

由于线程可以缓存，所以线程持有的内存不会被轻易释放。

• Threads_created

创建用来处理连接的线程数。应该监视Thread_created的增量，如果较多，则需要增加thread_cache_size的值。

以上对thread_cache_size的设置在高并发的时候会很有效。高并发时大量并发短连接对CPU的冲击不容忽视。

• treads_running

指同时运行的线程数目。这个值一般不会大于逻辑CPU的个数，如果经常有过多的线程同时运行，那么可能就意味着有性能的问题。

这个指标很重要往往表明一个系统繁忙程度，它在系统爆发性性能问题之前，会有一个上升的趋势，此时收集的性能信息，将有助于我们诊断复杂的性能问题。

• slow_launch_chreads

如果这个值比较大，则意味着创建线程太慢了，可能是系统出现了性能问题，存在资源瓶颈，从而导致操作系统没有安排足够的CPU时间给新创建的线程。

• query_cache_size

为了缓存查询结果分配的内存大小。一般设置为256MB。注意不要设置得太大。

可监控查询缓存命中率：Qcache_hits / (Qcache_hits+Com_select)。

更改这个值，会清空所有的缓存结果集，对于非常繁忙的系统，可能会很耗时，导致服务停顿，因为MySQL在删除所有的缓存查询时是逐个进行的。

• Qchache_lowmem_prunes

该变量记录了由于查询缓存出现内存不足，而需要从缓存中删除的查询数量，可通过监控Qcache_lowmem_prunes的增量，来衡量是否需要增大query_cache_size。

Qcache_lowmem_prunes状态变量提供的信息能够帮助你调整查询缓存的大小。

它可计算为了缓存新的查询而从查询缓存区移出到自由内存中的查询数目。

查询缓存区使用最近最少使用(LRU)策略来确定哪些查询需要从缓存区中移出。

• InnoDB_buffer_pool_wait_free

一般情况下，是通过后台向InnoDB缓冲池中写入数据的。

但是，如果需要读或创建页，并且没有 干净的页可用，那么它还需要先等待页面清空。

如果已经适当设置了缓冲池的大小，那么该值应该会很小。

• Slow_queries

查询时间超过long_query_time秒的查询个数。应该监控此变量的增量变化，一般1秒内不要超过5~10个，否则可能是有性能问题。

• Select_full_join

没有 使用索引的连接数量。如果该值较大，则应该仔细检查一下表的索引。

• Created_tmp_tables

创建内存临时表的数量，如果Created_tmp_disk_tables比较大，则应该考虑增加tmp_table_size的大小。

注：应该将tmp_table_size和max_heap_table_size简单调整到大小一样。32MB一般足够了。对这两个参数的控制通常基于内存引擎的临时表可以增长的阈值，若超过了这个阈值，就会转化成 On-disk MyISAM表。

• Created_tmp_disk_tables

服务器执行语句时在硬盘上自动创建的临时表的数量。

• Bytes_received 和 Bytes_sent

可用来监控MySQL的流量。

• key_buffer_size

MyISAM索引缓冲，实际用到多少就分配多少。不一定需要分配很大的空间，可参考实际观察到的值，不要大于实际值。

如下命令可用于评估索引空间的大小。

select sum(index_length) from information_schema.tables where engine="MYISAM";

• Open_tables

当前打开的表的数量。

• Opened_tables

已经打开的表的数量。

查看Open_tables及Opened_tables的增量时，如果后者的增量比较大，那么可能table_open_cache（或者table_cache）不够用了。

如果Open_tables对比table_cache_size并不大，但Opened_tables还在持续增长，那么也可能是显式临时表被不断打开而导致的。

• table_open_cache（table_cache 5.1.3之前的参数名）

默认的设置太小了，生产环境中应该将其设置得足够大，数千到一万是比较合理的值。

检查Opened_tables status变量，如果该值比较大，而我们不经常运行 FLUSH TABLES命令，那么应该增加table_open_cache的变量值。

• table_definition_cache

一般可以将其设置为足够高的值来缓存表定义，比如4096，这并不会耗费什么资源。默认的256太小了。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。