内容提纲
MySQL 的特点首先,需要明确的是。想要做好MySQL优化,需要先了解MySQL都有哪些特点:
简言之,MySQL一般用于互联网业务的数据持久化存储,并且用于保证数据的一致性、可靠性,而不是用于:
等奇葩用途。 CPU的利用特点看看MySQL不同版本对CPU多核的支持、利用情况:
建议:
内存利用特点内存利用、管理方面有什么特点呢?
建议:
磁盘的利用特点最后看下磁盘I/O方面的特点:
建议:
MySQL 优化了解完MySQL各方面的特点后,我们可以开始进行优化工作了。 在开始之前,我们需要先明确几点:
通常,我们进行MySQL优化工作的套路是这样的:
确认需求,先明确当前的运行状态,是否真的需要进行优化,别没事找事; 常见瓶颈:
常用优化策略
制定方案时,重点解决发生频率高的问题(量变更容易引起质变);回顾反馈,整理文档,回顾总结,从零散资料中总结出规律,预防风险再次出现。 一般采用下面几个瓶颈分析工具:
绝大多数情况下,有经验的工程师靠sysstat工具集中的就足够了,很多问题一看现象大概就能知道瓶颈何在。 在MySQL层面,有哪些确认瓶颈的手段呢?
硬件、系统优化我们继续MySQL优化之旅。先来看看从硬件以及OS层面,都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整,其次是RAID方面的优化。 再来看看几个参考配置图: 1、CPU选择最大性能模式,避免节能模式导致性能不足。
2、关闭NUMA,降低swap概率。
3、采用RAID-10,并且选择FORCE WB。
在OS层面,可以有几个优化手段:
备注:
从这个压测结果可以看到noop/deadline有明显优势。
这个io scheduler还可以在线修改的哦,还等神马? echo deadline > /sys/block/sdc/queue/scheduler 在用PCIe SSD设备做测试时,XFS的IOPS能跑到ext4的4倍,表现非常好。
还有什么理由不用XFS呢? xfs挂载参数: /dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0 格式化参数不用特别指定,默认的即可。 MySQL配置优化前面讲到,给MySQL分配的内存不要太大或太小,那么多少合适呢。 首先,要搞清楚MySQL的内存都由哪些部分组成:
原则: 对这些选项调整时,不要照猫画虎随便调整,要先做到心里有数,了解其具体作用才动手。 看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如:
如果再启用binlog后的对比:
最后,再加上sync_binlog选项不同设置的对比:
备注: 这3个测试结果图均来自Percona。 结论&建议:
SCHEMA设计优化接下来看看MySQL的模式(SCHEMA)设计优化要点:
要点:
其他要点:
SQL优化SQL优化层面有几个要点:
以及 COUNT(*)、大分页 的优化要点:
接下来,我们来看看EXPLAIN的结果中,有哪些关键信息要注意的。首先看下EXPLAIN结果的type列,都可以给我们什么信息:
再看看Extra列有哪些状态要引起重视:
MySQL的慢日志可用下面的工具来进行解析和管理:
pt-query-digest + Box Anemometer的案例,可以对slow log进行便捷管理。
关于JOIN优化有下面的几个关键点:
接下来看看哪些情况下,无法有效使用索引的:
再看看几个杀手级SQL的案例及其优化建议:
在平时,我们登入MySQL服务器后,如果觉得有问题,可以重点关注下面的一些线程状态:
其他优化
关于DBA的利器,常用percona-toolkit工具简介:
附:关于MariaDB及Percona分支版本的简介
Q&AQ1: 多实例,进程会不会抢占?每个事例都是单独起的。
Q2: SSD建议单盘还是Raid?
Q3: 能介绍一下哪些业务场景适合哪种RAID吗?
Q4: nnodb_buffer_pool_instances应该如何设置?
Q5: No text,or in compressed是指如果使用text的话,建议压缩吗?在压缩数据方面,叶老师有什么经验吗?
Q6: MariaDB和MySQL的优缺点,以及大神怎么看Maria有否取代MySQL的趋势?
Q7: 新的业务系统,是建议继续用MySQL5.5或以上,还是用mariaDB?
Q8: 你们的数据库备份是用Percona的工具进行吗?每周一全备,每天一增量?用这些工具备份,会不会出现恢复不了的情况?这个有没有办法验证备份是否“正常” ?
Q9:恢复性测试怎么做 有流程方案指导一下吗?
Q10: 有没有什么效率较高的验证备份有效性的工具或者方法?还是只好把库恢复出来核对?
转载请保留固定链接: https://linuxeye.com/database/2842.html |