当我们被人雇来监测 MySQL 效能时，人们希望我们能够检视一下 MySQL 配置然后给出一些提高建议。许多人在事后都非常惊讶，因为我们建议他们仅仅改动几个设定，即使是这里有好几百个配置项。这篇文章的目的在于给你一份非常重要的配置项清单。
我们曾在几年前在博客网站里给出了这样的建议，但是 MySQL 的世界变化实在太快了！
写在开始前…
即使是经验老道的人也会犯错，会引起很多麻烦。所以在盲目的运用这些推荐之前，请记住下面的内容：
一次只改变一个设定！这是测试改变是否有益的唯一方法。
大多数配置能在执行时使用 SET GLOBAL 改变。这是非常便捷的方法它能使你在出问题后快速撤销变更。但是，要永久生效你需要在配置档案里做出改动。
一个变更即使重启了 MySQL 也没起作用？请确定你使用了正确的配置档案。请确定你把配置放在了正确的区域内 (所有这篇文章提到的配置都属于 [mysqld])
站群服务器在改动一个配置后启不来了：请确定你使用了正确的单位。例如，innodb_buffer_pool_size 的单位是 MB 而 max_connection 是没有单位的。
不要在一个配置档案里出现重复的配置项。如果你想追踪改动，请使用版本控制。
不要用天真的计算方法，例如” 现在我的站群服务器的内存是之前的 2 倍，所以我得把所有数值都改成之前的 2 倍 “。
基本配置
你需要经常察看以下 3 个配置项。不然，可能很快就会出问题。
innodb_buffer_pool_size: 这是你安装完 InnoDB 后第一个应该设定的选项。缓冲池是资料和索引 WordPress 加速缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬碟。典型的值是 5-6GB(8GB 内存)，20-25GB(32GB 内存)，100-120GB(128GB 内存) 。
innodb_log_file_size：这 是 redo 日志的大小。 redo 日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到 MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高效能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从 MySQL 5.5 之后，崩溃恢复的效能的到了很大提升，这样你就可以同时拥有较高的写入效能和崩溃恢复效能了。一直到 MySQL 5.5，redo 日志的总尺寸被限定在 4GB(预设可以有 2 个 log 档案) 。这在 MySQL 5.6 里被提高。
一开始就把 innodb_log_file_size 设定成 512M(这样有 1GB 的 redo 日志) 会使你有充裕的写操作空间。如果你知道你的应用程式需要频繁的写入资料并且你使用的时 MySQL 5.6，你可以一开始就把它这是成 4G 。
max_connections: 如果你经常看到 ‘Too many connections’ 错误，是因为 max_connections 的值太低了。这非常常见因为应用程式没有正确的关闭资料库连线，你需要比预设的 151 连线数更大的值。 max_connection 值被设高了 (例如 1000 或更高) 之后一个主要缺陷是当站群服务器执行 1000 个或更高的活动事务时会变 的没有响应。在应用程式里使用连线池或者在 MySQL 里使用程序池有助于解决这一问题。
InnoDB 配置
从 MySQL 5.5 版本开始，InnoDB 就是预设的储存引擎并且它比任何其他储存引擎的使用都要多得多。那也是为什么它需要小心配置的原因。
innodb_file_per_table： 这项设定告知 InnoDB 是否需要将所有表的资料和索引存放在共享表空间里（innodb_file_per_table = OFF）或者为每张表的资料单独放在一个.ibd 档案（innodb_file_per_table = ON）。每张表一个档案允许你在 drop 、 truncate 或者 rebuild 表时回收磁碟空间。这对于一些高阶特性也是有必要的，比如资料压缩。但是它 不会带来任何效能收益。你不想让每张表一个档案的主要场景是：有非常多的表（比如 10k+）。
MySQL 5.6 中，这个属性预设值是 ON，因此大部分情况下你什么都不需要做。对于之前的版本你必需在载入资料之前将这个属性设定为 ON，因为它只对新建立的表有影响。
innodb_flush_log_at_trx_commit： 预设值为 1，表示 InnoDB 完全支援 ACID 特性。当你的主要关注点是资料安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁碟（读写）速度 较慢的系统，它会带来很巨大的开销，因为每次将改变 flush 到 redo 日志都需要额外的 fsyncs 。将它的值设定为 2 会导致不太可靠（reliable）因为提交的事务仅仅每秒才 flush 一次到 redo 日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受 的。如果值为 0 速度就更快了，但在系统崩溃时可能丢失一些资料：只适用于备份节点。
innodb_flush_method: 这项配置决定了资料和日志写入硬碟的方式。一般来说，如果你有硬体 RAID 控制器，并且其独立 WordPress 加速缓存采用 write-back 机制，并有著电池断电保护，那 么应该设定配置为 O_DIRECT；否则，大多数情况下应将其设为 fdatasync（预设值）。 sysbench 是一个可以帮助你决定这个选项的好工 具。
innodb_log_buffer_size: 这项配置决定了为尚未执行的事务分配的 WordPress 加速缓存。其预设值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进位制大物件或者大文字栏位的话，这点 WordPress 加速缓存 很快就会被填满并触发额外的 I/O 操作。看看 Innodb_log_waits 状态变数，如果它不是 0，增加 innodb_log_buffer_size 。
其他设定
query_cache_size: query cache（查询 WordPress 加速缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设定 query_cache_size = 0（现在 MySQL 5.6 的预设值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的 WordPress 加速缓存（比如 memcache 或 redis）。如果你已经为你的应用启 用了 query cache 并且还没有发现任何问题，query cache 可能对你有用。这是如果你想停用它，那就得小心了。
log_bin：如 果你想让资料库站群服务器充当主节点的备份节点，那么开启二进位制日志是必须的。如果这么做了之后，还别忘了设定 server_id 为一个唯一的值。就算只有一 个站群服务器，如果你想做基于时间点的资料恢复，这（开启二进位制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进位制日志中的修改（增量备 份）。二进位制日志一旦建立就将永久储存。所以如果你不想让磁碟空间耗尽，你可以用 PURGE BINARY LOGS
来清除旧档案，或者设定 expire_logs_days 来指定过多少天日志将被自动清除。
记录二进位制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。
skip_name_resolve：当客户端连线资料库站群服务器时，站群服务器会进行 WordPress 主机名解析，并且当 DNS 很慢时，建立连线也会很慢。因此建议在启动站群服务器时关闭 skip_name_resolve 选项而不进行 DNS 查询。唯一的局限是之后 GRANT 语句中只能使用 IP 地址了，因此在新增这项设定到一个已有系统中必须格外小心。
总结
当然还有其他的设定可以起作用，取决于你的负载或硬体：在慢内存和快磁碟、高并发和写密集型负载情况下，你将需要特殊的调整。然而这里的目标是使得你可 以快速地获得一个稳健的 MySQL 配置，而不用花费太多时间在调整一些无关紧要的 MySQL 设定或读文件找出哪些设定对你来说很重要上。