叶金荣:MySQL通用优化技巧

时间:2023-05-27 17:18:19

转自:http://mp.weixin.qq.com/s?__biz=MjM5NDE0MjI4MA==&mid=208777870&idx=1&sn=6efddd6283e4deb3fe55a141b0db965c

本文根据 DevOps华南运维圈@UCloud微信群的「运维在线」栏目的嘉宾分享整理而成。「运维在线」将邀请业界运维前线技术专家作为分享嘉宾,分享技术趋势和技术实战,为运维朋友提供各种踩坑、躲坑、绕坑新技能。

嘉宾介绍

叶金荣
Oracle MySQL ACE,国内最早的MySQL推广者。2006年创办国内首个MySQL专业技术网站 MySQL 中文网。资深MySQL专家,10余年MySQL经验,擅长MysQL性能优化、架构设计、故障排查。

内容提纲

  1. MySQL的特点;

  2. 硬件、系统优化;

  3. MySQL 配置优化;

  4. SCHEMA设计优化;

  5. SQL 优化;

  6. 其他优化。

MySQL 的特点

首先,需要明确的是。想要做好MySQL优化,需要先了解MySQL都有哪些特点:

叶金荣:MySQL通用优化技巧

简言之,MySQL一般用于互联网业务的数据持久化存储,并且用于保证数据的一致性、可靠性,而不是用于:

  • 复杂查询;

  • 复杂运算;

  • 大二进制存储。

等奇葩用途。

CPU的利用特点

看看MySQL不同版本对CPU多核的支持、利用情况:

叶金荣:MySQL通用优化技巧

建议:

  • 采用最新MySQL版本,以提升其CPU利用率;

  • 每个SQL足够简单,不要太过复杂;

  • 每个连接足够快速完成,不要“恋战”。

内存利用特点

内存利用、管理方面有什么特点呢?

叶金荣:MySQL通用优化技巧

建议:

  • 关闭query cache;

  • 采用InnoDB;

  • 采用Percona\MariaDB分支版本;

  • 简单KV数据用NOSQL存储,不使用MySQL。

磁盘的利用特点

最后看下磁盘I/O方面的特点:

叶金荣:MySQL通用优化技巧

建议:

  • 使用多盘提升整体I/O性能;

  • 多使用高速I/O设备;

  • 尽量加大内存,缓解I/O负载。

MySQL 优化

了解完MySQL各方面的特点后,我们可以开始进行优化工作了。

在开始之前,我们需要先明确几点:

  1. 为何而优化?领导指派\用户投诉\监控预警\没事找事?当前跑得好好的话,就没必要折腾神马优化没事找抽,即便想练手,也要悠着点,防止误操作;

  2. 优化的目标是什么,说白了,就是要解决什么瓶颈,切忌在过程中偏离初心;

  3. 计算投入产出比,比如为了让性能提升1%而投入1人月,基本上是非常不划算了,还不如去干点别的;

  4. 优化前做好现场信息采集,优化后再次采集做对比,确认优化成果(用来邀功啊,让老板看到你的成绩,年底加加薪什么的,最起码也能锻炼总结归纳文档能力吧)。

通常,我们进行MySQL优化工作的套路是这样的:

叶金荣:MySQL通用优化技巧

确认需求,先明确当前的运行状态,是否真的需要进行优化,别没事找事;

常见瓶颈:

  • 绝大多数瓶颈在于I/O子系统;

  • 若CPU很高,90%以上是因为索引不当;

  • 发生swap时,可能因为内存分配太小或过大;

  • iowait太高时,想办法从索引角度入手优化,以及提高I/O设备性能,增加内存,减少排序,减少SELECT一次性读取数据量。

常用优化策略

  1. 瞬间并发很高,采用thread pool;

  2. 频繁order by\group by,索引入手;

  3. 适当调整内存,不要太大或太小。一般ibp设置为50% ~ 70%为宜;

  4. iowait高,加内存,提高iops,减少数据读写。

制定方案时,重点解决发生频率高的问题(量变更容易引起质变);回顾反馈,整理文档,回顾总结,从零散资料中总结出规律,预防风险再次出现。

一般采用下面几个瓶颈分析工具:

叶金荣:MySQL通用优化技巧

绝大多数情况下,有经验的工程师靠sysstat工具集中的就足够了,很多问题一看现象大概就能知道瓶颈何在。

在MySQL层面,有哪些确认瓶颈的手段呢?

叶金荣:MySQL通用优化技巧

硬件、系统优化

我们继续MySQL优化之旅。先来看看从硬件以及OS层面,都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整,其次是RAID方面的优化。

再来看看几个参考配置图:

1、CPU选择最大性能模式,避免节能模式导致性能不足。

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

2、关闭NUMA,降低swap概率。

叶金荣:MySQL通用优化技巧
叶金荣:MySQL通用优化技巧

3、采用RAID-10,并且选择FORCE WB。

叶金荣:MySQL通用优化技巧

在OS层面,可以有几个优化手段:

  • 调整IO Scheduler

  • 使用XFS

  • 调整其他内核选项备

叶金荣:MySQL通用优化技巧

备注:

  1. vm.swappiness,降低发生swap的几率;

  2. vm.dirty_background_ratio & vm.dirty_ratio,避免瞬间大量I/O请求导致系统卡死。

从这个压测结果可以看到noop/deadline有明显优势。

叶金荣:MySQL通用优化技巧

这个io scheduler还可以在线修改的哦,还等神马?

echo deadline > /sys/block/sdc/queue/scheduler

在用PCIe SSD设备做测试时,XFS的IOPS能跑到ext4的4倍,表现非常好。

叶金荣:MySQL通用优化技巧

还有什么理由不用XFS呢?

xfs挂载参数:

/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

格式化参数不用特别指定,默认的即可。

MySQL配置优化

前面讲到,给MySQL分配的内存不要太大或太小,那么多少合适呢。

首先,要搞清楚MySQL的内存都由哪些部分组成:

  1. global buffers和oracle的SGA一个意思,就是全局一次分配,多个线程间共享。

  2. thread buffers和oracle的PGA一个意思,每个线程单独分配,线程间不能相互共享,因此不要分配过大,避免内存不够使用,发生OOM。

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

原则: 对这些选项调整时,不要照猫画虎随便调整,要先做到心里有数,了解其具体作用才动手。

看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如:

叶金荣:MySQL通用优化技巧

如果再启用binlog后的对比:

叶金荣:MySQL通用优化技巧

最后,再加上sync_binlog选项不同设置的对比:

叶金荣:MySQL通用优化技巧

备注: 这3个测试结果图均来自Percona。

结论&建议:

  1. 想要保证数据安全,就设置 trx_commit =1 & sync_binlog = 1

  2. 在slave上或非关键场景,可以都改成0

SCHEMA设计优化

接下来看看MySQL的模式(SCHEMA)设计优化要点:

叶金荣:MySQL通用优化技巧

要点:

  1. 默认地,使用InnoDB引擎,别上MyISAM给自己找事;

  2. InnoDB必须要有自增(或类似自增)属性的主键;

  3. 不使用或少使用TEXT/BLOB列;

  4. NOT NULL主要是为了优化索引效率;

  5. 若无特殊需求,均可使用latin1字符集,否则用utf8\utf8mb4等大字符集保证通用性。

其他要点:

叶金荣:MySQL通用优化技巧

SQL优化

SQL优化层面有几个要点:

叶金荣:MySQL通用优化技巧

以及 COUNT(*)、大分页 的优化要点:

叶金荣:MySQL通用优化技巧

接下来,我们来看看EXPLAIN的结果中,有哪些关键信息要注意的。首先看下EXPLAIN结果的type列,都可以给我们什么信息:

叶金荣:MySQL通用优化技巧

再看看Extra列有哪些状态要引起重视:

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

MySQL的慢日志可用下面的工具来进行解析和管理:

叶金荣:MySQL通用优化技巧

pt-query-digest + Box Anemometer的案例,可以对slow log进行便捷管理。

叶金荣:MySQL通用优化技巧

关于JOIN优化有下面的几个关键点:

叶金荣:MySQL通用优化技巧

接下来看看哪些情况下,无法有效使用索引的:

叶金荣:MySQL通用优化技巧

再看看几个杀手级SQL的案例及其优化建议:

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

在平时,我们登入MySQL服务器后,如果觉得有问题,可以重点关注下面的一些线程状态:

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

其他优化

叶金荣:MySQL通用优化技巧

关于DBA的利器,常用percona-toolkit工具简介:

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

附:关于MariaDB及Percona分支版本的简介

叶金荣:MySQL通用优化技巧

叶金荣:MySQL通用优化技巧

Q&A

Q1: 多实例,进程会不会抢占?每个事例都是单独起的。

A:除了OS层面的资源会相互影响外,其他的不会。比如某个实例消耗特别多cpu资源的话,那么其他实例也会跟着受影响,这是必然的,除非用虚拟化等方式做隔离。

Q2: SSD建议单盘还是Raid?

A:如果不担心丢数据,单盘呗。如果怕丢的话,那显然不能单盘了。随机io很高的话,Raid5就不合适了。不过除非采用SSD,用Raid5也不怕了。事实上,Raid卡反而会影响(降低)SSD性能的发挥,但为了数据可靠性,没办法,还好影响不算特别大。

Q3: 能介绍一下哪些业务场景适合哪种RAID吗?

A:1、高随机IO,用Raid10;2、需要大容量,用Raid5。基本就这两种方案,事实上,因为SSD的IOPS性能已经很不错了,很多企业会选择直接用3块盘构建Raid5。毋庸置疑,上了PCIE SSD,可以避免很多问题,或者DBA可以少干很多活,至少可以缓解。

Q4: nnodb_buffer_pool_instances应该如何设置?

A:ibp的instance一般不超过8为宜,超过8的话,可能有反作用,不过多个instance的前提是,平均到每个instance的ibp不能小于2G,否则也没啥意义。

Q5: No text,or in compressed是指如果使用text的话,建议压缩吗?在压缩数据方面,叶老师有什么经验吗?

A:对的,建议不要在InnoDB中存储大量文本。需要的话,事先压缩好再存进去。不需要检索的文本,可以统统压缩后存进去,不是用InnoDB的压缩格式哦,是事先外部压缩后存储,文本内容在存储进去前先压缩好,不是用InnoDB的compressed这种row format,那会被坑惨的,性能损失9层,只有一半压缩比,还不如用TokuDB算了。

Q6: MariaDB和MySQL的优缺点,以及大神怎么看Maria有否取代MySQL的趋势?

A:想要取代还早呢,没那么容易,而且也没必要取代,作为补充就ok。除非哪天MySQL官方版本闭源了,或者支持很差。

Q7: 新的业务系统,是建议继续用MySQL5.5或以上,还是用mariaDB?

A:建议优先Percona 5.6,其次是MySQL 5.6,最末才是MariaDB。

Q8: 你们的数据库备份是用Percona的工具进行吗?每周一全备,每天一增量?用这些工具备份,会不会出现恢复不了的情况?这个有没有办法验证备份是否“正常” ?

A:工具则以xtrabackup为主,mysqldump为辅,数量不是巨大的话,每天一全备,大多有slave做热备,所以就没定期增备了。Mydumper也有些不太爽的,也比较小众就是,备份文件一定要做恢复性测试,千万别只备份不恢复测试,关键时刻会死人的。

Q9:恢复性测试怎么做 有流程方案指导一下吗?

A:简单的:数据恢复,简单查询验证数量,关键数据什么的;复杂的:搭测试环境呗。

Q10: 有没有什么效率较高的验证备份有效性的工具或者方法?还是只好把库恢复出来核对?

A:mysqldump或mydumper备份的文件,可以用grep简单快速验证;xtrabackup的话,只能看文件大小,或者做全量恢复了。