MySQL 5.6初始配置调优

原文日期: 2013年09月17日

翻译日期: 2014年06月01日

InnoDB设置

如果将该值设置得更大可能会存在风险,比如没有足够的空闲内存留给操作系统和依赖文件系统缓存的某些MySQL子系统(subsystem),包括二进制日志(binary logs),InnoDB事务日志(transaction logs)等.

有一个很好的类比示例:  假如某次航班一张票也没有卖出去 —— 那么让稍后航班的一些人乘坐该次航班,有可能是很好的策略,以防后面遇到恶劣的天气. 即有机会就将后台操作顺便处理了,以减少同稍后可能的实时操作产生竞争.

有一个很简单的计算:  如果每个磁盘每秒读写(IOPS)可以达到 200次, 则拥有10个磁盘的 RAID10 磁盘阵列IOPS理论上 =(10/2)* 200 = 1000. 我说它“很简单”,是因为RAID控制器通常能够提供额外的合并,并有效提高IOPS能力. 对于SSD磁盘,IOPS可以轻松达到好几千.

将这两个值设置得太大可能会存在某些风险,你肯定不希望后台操作妨碍了前台任务IO操作的性能. 过去的经验表明,将这两个值设置的太高,InnoDB持有的内部锁会导致性能降低(按我了解到的信息,在MySQL5.6中这得到了很大的改进).

复制(Replication)

假如服务器要支持主从复制,或按时间点恢复,在这种情况下,我们需要:

其他配置(Misc)

NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,

NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

防火墙是更合适的解决方案,通常我将3306端口屏蔽,不管是公网的还是内网的端口,只有特定的应用程序可以访问和连接到MySQL. 

我通常会设置 max_connect_errors=100000, 这样我可以避免任何“双重配置”,保证它不会碍事.

通常不可避免地这个值会被设置得更大,但让我有点紧张的是, 16核的机器在IO阻塞的情况下也只有大约 2x~10x 的连接执行能力. 

你可能希望,许多打开的连接都是空闲并休眠的. 但如果他们都处于活跃状态的话,可能会创建大量新的线程(thread-thrash).

如果条件允许,可以为应用程序配置优化数据库连接池(connection-pools)来解决这个问题,而不是打开并保持大量连接; 

当然那些不使用连接池(non-pooled ), 迅速打开,执行任务后又尽可能快地关闭连接的应用也是可行的. 

总结(Conclusion)

假设MySQL服务器的配置为:

64GB物理内存

硬件RAID控制器(假设每秒IO可达 2000 IOPS)

需要主从复制(Replication)

新的应用(eg. 非遗留系统)

有防火墙保护

不需要基于域名(hostnames,主机名)的授权

全球化应用,并不想固定在某一时区.

则配置可能如下所示:

希望本文说清了主要的问题. 如果有其他建议,请联系原作者.