关于MySQL性能调优你必须了解的15个重要变量(小结)(2)-木庄网络博客

下面介绍如何更改这个变量，在mysql命令行提示符执行：

SET global innodb_thread_concurrency=X;

对于大多数工作负载和服务器，设置为8是一个好开端，然后你可以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加。可以通过show engine innodb status\G来查看目前查询处理情况，查找类似如下行：

22 queries inside InnoDB, 104 queries in queue

9.SKIP_NAME_RESOLVE

这一项不得不提及，因为仍然有很多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。

大多数情况下你更改这个会没有什么感觉，因为大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时，它会出现在你服务器上出现“unauthenticated connections” ，而就是为什么所有的请求都突然开始慢下来了。

所以不要等到这种事情发生才更改。现在添加这个变量并且避免基于主机名的授权。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用来当刷新脏数据时，控制MySQL每秒执行的写IO量。

* innodb_io_capacity_max: 在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量

首先，这与读取无关 – SELECT查询执行的操作。对于读操作，MySQL会尽最大可能处理并返回结果。至于写操作，MySQL在后台会循环刷新，在每一个循环会检查有多少数据需要刷新，并且不会用超过innodb_io_capacity指定的数来做刷新操作。这也包括更改缓冲区合并（在它们刷新到磁盘之前，更改缓冲区是辅助脏页存储的关键）。

第二，我需要解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来。然后，MySQL会用到innodb_io_capacity_max。

那么，应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢？

最好的方法是测量你的存储设置的随机写吞吐量，然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS。innodb_io_capacity就设置为它的50-75%，特别是你的系统主要是写操作时。

通常你可以预测你的系统的IOPS是多少。例如由8 15k硬盘组成的RAID10能做大约每秒1000随机写操作，所以你可以设置innodb_io_capacity=600和innodb_io_capacity_max=1000。许多廉价企业SSD可以做4,000-10,000 IOPS等。

这个值设置得不完美问题不大。但是，要注意默认的200和400会限制你的写吞吐量，因此你可能偶尔会捕捉到刷新进程。如果出现这种情况，可能是已经达到你硬盘的写IO吞吐量，或者这个值设置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默认值，因为它已经设置正确了。

不过在MySQL 5.5或5.1，强烈建议关闭这个变量 – 如果是开启，像命令show table status会立即查询INFORMATION_SCHEMA而不是等几秒再执行，这会使用到额外的IO操作。

从5.1.32版本开始，这个是动态变量，意味着你不需要重启MySQL服务器来关闭它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关，不过如果你偶尔重启mysql服务器(如生效配置)，那么就有关。当两个都激活时，MySQL缓冲池的内容(更具体地说，是缓存页)在停止MySQL时存储到一个文件。当你下次启动MySQL时，它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍。

两件事：

第一，它实际上没有在关闭时复制缓冲池内容到文件，仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了。然后它就能以大量的顺序读非常快速的加载那些页面，而不是需要成千上万的小随机读。

第二，启动时是在后台加载内容，因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响)。

从MySQL 5.7.7开始，默认只有25%的缓冲池页面在mysql关闭时存储到文件，但是你可以控制这个值 – 使用innodb_buffer_pool_dump_pct，建议75-100。

这个特性从MySQL 5.6才开始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化)，那么自适应哈希索引将下你的下一个瓶颈。自适应哈希索引是InnoDB内部维护的动态索引，可以提高最常用的查询模式的性能。这个特性可以重启服务器关闭，不过默认下在mysql的所有版本开启。

这个技术非常复杂，在大多数情况下它会对大多数类型的查询直到加速的作用。不过，当你有太多的查询往数据库，在某一个点上它会花过多的时间等待AHI锁和闩锁。

如果你的是MySQL 5.7，没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割为8个分区，因为不存在全局互斥。

不过在mysql 5.7前的版本，没有AHI分区数量的控制。换句话说，有一个全局互斥锁来保护AHI，可能导致你的select查询经常撞墙。

所以如果你运行的是5.1或5.6，并且有大量的select查询，最简单的方案就是切换成同一版本的Percona Server来激活AHI分区。

14.QUERY_CACHE_TYPE