本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我们
<强>
<强> 1.1,CPU相关
在服务器的BIOS设置中,可调整下面的几个配置,目的是发挥CPU最大性能,或者避免经典的NUMA问题:
1,选择每瓦特性能优化(DAPC)模式,发挥CPU最大性能,跑DB这种通常需要高运算量的服务就不要考虑节电了;
2,关闭C1E和C状态等选项,目的也是为了提升CPU效率;
3,内存频率(内存频率)选择最佳性能(最佳性能),
4,内存设置菜单中,启用节点交错,避免NUMA问题;
<强> 1.2,磁盘I/O相关
下面几个是按照IOPS性能提升的幅度排序,对于磁盘I/O可优化的一些措施:
1,使用SSD或者作为PCIe SSD设备,至少获得数百倍甚至万倍的IOPS提升;
2,购置阵列卡同时配备缓存及BBU模块,可明显提升IOPS(主要是指机械盘,SSD或作为PCIe SSD除外,同时需要定期检查缓存及BBU模块的健康状况,确保意外时不至于丢失数据);
3,有阵列卡时,设置阵列写策略为世行,甚至力WB(若有双电保护,或对数据安全性要求不是特别高的话),严禁使用WT策略,并且闭阵列预读策略,基本上是鸡肋,用处不大;
4,尽可能选用raid 10,而非raid - 5;
5,使用机械盘的话,尽可能选择高转速的,例如选用15 krpm,而不是7.2 krpm的盘,不差几个钱的;
<强>
<强> 2.1,文件系统层优化
在文件系统层、下面几个措施可明显提升IOPS性能:
1,使用期限/等待这两种I/O调度器,千万别用cfq(它不适合跑DB类服务);
2,使用xfs文件系统,千万别用ext3; ext4勉强可用,但业务量很大的话,则一定要用xfs;
3,文件系统山参数中增加:减小,nodiratime, nobarrier几个选项(nobarrier是xfs文件系统特有的),
<强> 2.2,其他内核参数优化
针对关键内核参数设定合适的值,目的是为了减少交换的倾向,并且让内存和磁盘I/O不会出现大幅波动,导致瞬间波峰负载:
1,将vm。swappiness设置为5 - 10左右即可,甚至设置为0 (RHEL 7以上则慎重设置为0,除非你允许伯父杀死发生),以降低使用交换的机会。
2,将vm.dirty_background_ratio设置为5 - 10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待(和MySQL中的innodb_max_dirty_pages_pct类似);
3,将net.ipv4.tcp_tw_recycle, net.ipv4.tcp_tw_reuse都设置为1,减少TIME_WAIT,提高TCP效率;
4,至于网传的read_ahead_kb, nr_requests这两个参数,我经过测试后,发现对读写混合为主的OLTP环境影响并不大(应该是对读敏感的场景更有效果),不过没准是我测试方法有问题,可自行斟酌是否调整;
<强> 3.1,关于版本选择
官方版本我们称为ORACLE MySQL,这个没什么好说的,相信绝大多数人会选择它。
我个人强烈建议选择Percona分支版本,它是一个相对比较成熟的,优秀的MySQL分支版本,在性能提升,可靠性,管理型方面做了不少改善。它和官方甲骨文MySQL版本基本完全兼容,并且性能大约有20%以上的提升,因此我优先推荐它,我自己也从2008年一直以它为主。
另一个重要的分支版本是MariaDB,说MariaDB是分支版本其实已经不太合适了,因为它的目标是取代ORACLE MySQL。它主要在原来的MySQL服务器层做了大量的源码级改进,也是一个非常可靠的,优秀的分支版本。但也由此产生了以GTID为代表的和官方版本无法兼容的新特性(MySQL 5.7开始,也支持GTID模式在线动态开启或关闭了),也考虑到绝大多数人还是会跟着官方版本走,因此没优先推荐MariaDB。
<强> 3.2,关于最重要的参数选项调整建议
建议调整下面几个关键参数以获得较好的性能(可使用本站提供生的my . cnf中所做成器生成配置文件模板):
1,选择Percona或MariaDB版本的话,强烈建议启用线程池特性,可使得在高并发的情况下,性能不会发生大幅下降。此外,还有extra_port功能,非常实用,关键时刻能救命的。还有另外一个重要特色是QUERY_RESPONSE_TIME功能,也能使我们对整体的SQL响应时间分布有直观感受;
MySQL性能全面优化方法参考,从CPU、文件系统选择到mysql.cnf参数优化
