MySQL延迟问题和数据刷盘策略流程分析

一、MySQL复制流程

官方文档流程如下：

MySQL延迟问题和数据刷盘策略

1、绝对的延时，相对的同步

2、纯写操作，线上标准配置下，从库压力大于主库，最起码从库有relaylog的写入。

二、MySQL延迟问题分析

1、主库DML请求频繁

原因：主库并发写入数据，而从库为单线程应用日志，很容易造成relaylog堆积，产生延迟。

解决思路：做sharding，打散写请求。考虑升级到MySQL5.7+，开启基于逻辑时钟的并行复制。

2、主库执行大事务

原因：类似主库花费很长时间更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的events无法更新。

解决思路：拆分大事务，及时提交。

3、主库对大表执行DDL语句

原因：DDL未开始执行，被阻塞，检查到位点不变；DDL正在执行，单线程应用导致延迟增加，位点不变。

解决思路：找到被阻塞DDL或是写操作的查询，干掉该查询，让DDL正常在从库上执行；业务低峰期执行，尽量使用支持OnlineDDL的高版本MySQL。

4、主从实例配置不一致

原因：硬件上：主库实例服务器使用SSD，而从库实例服务器使用普通SAS盘、cpu主频不一致等；配置上：如RAID卡写策略不一致，OS内核参数设置不一致，MySQL落盘策略(innodb_flush_log_at_trx_commit和sync_binlog等)不一致等

解决思路：尽量统一DB机器的配置（包括硬件及选项参数）；甚至对于某些OLAP业务，从库实例硬件配置高于主库等。

5、从库自身压力过大

原因：从库执行大量select请求，或业务大部分select请求被路由到从库实例上，甚至大量OLAP业务，或者从库正在备份等，此时可能造成cpu负载过高，io利用率过高等，导致SQLThread应用过慢。

解决思路：建立更多西安数据库培训从库，打散读请求，降低现有从库实例的压力。

也可以调整innodb_flush_log_at_trx_commit=0和sync_binlog=0刷盘参数来缓解IO压力来降低主从延迟。

三、大促期间CPU过高问题

现象：

高并发导致CPU负载过高，处理请求时间拉长，逐步积压，最终导致服务不可用；大量的慢SQL导致CPU负载过高。

解决思路：

基本上是禁止或是慎重考虑数据库主从切换，这个解决不了根本问题，需要研发配合根治SQL问题，也可以服务降级，容器的话可以动态扩容CPU；和业务协商启动pt-kill查杀只读慢SQL；查看是否可以通过增加一般索引或是联合索引来解决慢SQL问题，但此时要考虑DDL对数据库影响。

四、InnoDB刷盘策略

MySQL的innodb_flush_method这个参数控制着innodb数据文件及redolog的打开、刷写模式，对于这个参数，文档上是这样描述的：