MySQL复制延迟优化的方法论

数元技术 2020-11-15

1071

一、MySQL为什么会延迟

数据延迟：是指master执行了N个事务，slave却只执行了N-M个事务，说明master和slave之间产生了延迟。

延迟原因：延迟的原因很多种，大部分情况下是 slave的处理能力跟不上master导致。

接下来，我们从各种角度分析下延迟的原因。

1.1 MySQL复制的架构

通过架构图，可以直观的看到数据延迟的点有哪些，当然也就可以知道如何优化了。

1.2 大事务导致的延迟

大家都知道，binlog的写入时机是在commit的时候，redo的写入时机是在事务执行阶段就开始。

Oracle是通过物理复制，我们姑且认为是redo的复制，因为redo是事务执行阶段就开始写入的，所以，oracle的复制几乎没有延迟。

MySQL是基于binlog复制的，如果有一个非常大的事务，如果需要1个小时，那么master在1小时候后才会生成binlog，而此时，slave就比master慢了至少1个小时，还不算是binlog传输时间。

这是第一种延迟原因，破解方法后面说。

PS: DDL虽然不是事务，但是特性跟大事务一样，都是在master上执行了一个巨大无比的操作才写的binlog。

1.3 IO线程导致的延迟

根据复制的架构，Master写完binlog后，需要通过网络传输给slave（这部分我们需要网络的支持）。
然后呢，IO thread会将binlog写到slave的relay log中，这部分工作由IO thread完成。

好了，这里我们分析下瓶颈：

io thread 是单线程的
io thread 写入 relay log的速度

经过分析以及大量的实战，IO thread并不是我们的瓶颈，因为relay log是顺序的写入，非常快，几乎碰不到瓶颈。

1.4 SQL线程导致的延迟

master 上面的事务是可以进行并发的，然后binlog传输到slave后，slave是却以单线程的模式读取和执行relay log。
这是典型的消费能力不足

1.5 网络问题导致的延迟

网络问题不用多说了吧，如果要复制良好，一个稳定的网路环境是在所难免的。

1.6 硬件问题导致的延迟

如果master是SSD，但是slave还是机械硬盘，这样的架构存在延迟也不足为奇。

二、延迟场景的解决方案

2.1 DDL

2.1.1 ddl的最佳实践

通过pt-osc 或者 gh-ost 来让ddl拆分成一个个小事务，并且还有流控功能
在slave上先ddl，然后master-slave切换，然后再old master上进行ddl，从而完美的解决了这个问题。

2.2 大事务

2.2.1 大事务拆小事务

如果说大事务对于binlog的产生有极大的影响，那么我们认为定义小事务，大事务不允许执行。

有大事务的监控，可以基于时间，可以基于数据量，监控到不符合规范的trx自动kill。

2.3 大量并发事务

2.3.1 调整安全参数

sync_binlog = 0 && innodb_flush_trx_commit = 0 可以极大的提高事务处理的吞吐量，因为IO fsync的次数变少了，可以非常有效的降低数据延迟。

风险：如果slave挂了，需要重做slave。

2.3.2 MTS(enhanced multi-threaded slave)

之前有深入讨论过MTS的文章，它主要的功能就是让slave拥有比master更快的并行能力，从而有效的让延迟缩短，甚至无延迟。

终极大招

半同步：半同步可以让延迟为0，但是半同步有自动切换为异步复制的可能。

全同步：MySQL的group replication 就是这类的代表，这个话题以后再聊。

最后，以上就是关于MySQL延迟优化的方法，几乎涵盖了90%的方案，如果大家还有更好的方案，不妨拿出来大家一起探讨。

数元技术致力于数据库和大数据技术、架构技术、云计算、人工智能等领域的技术干货分享。关注我们，定时获得干货信息。数据社区技术专栏同步上线，点击阅读原文进入社区网站。

mysql

文章转载自数元技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。