彻底终结MySQL同步延迟问题

矫情吗；* 2022-04-08 17:53 186阅读 0赞

作为一名DBA，在工作中会经常遇到一些MySQL主从同步延迟的问题，这些同步慢的问题，其实原因非常多，可能是因为主从的网络问题导致，可能是因为网络带宽问题导致，可能是因为大事务导致，也可能是因为单线程复制导致的延迟。最近遇到一个很典型的同步延迟问题，将分析过程写出来，希望对广大DBA在排查同步延迟问题有比较系统的方法论。

首先交代一下背景（不交代背景和场景的问题分析都是耍流氓）

最近有一组DB出现比较大的延迟，这组DB是专门用来存储监控数据，每分钟会使用load data的方式导入大量的数据。为了节省空间，将原来使用压缩表的innodb引擎转换成了TokuDB引擎，使用的版本和引擎如下：

> MySQL Version： 5.7
> 
> Storage Engine： TokuDB

转换后，发现主从延迟逐渐增大，基本每天落后主机大概50个binlog左右，大概延迟7.5个小时左右的数据，主机每天大概产生160个binlog，binlog列表如下图所示：

![Image 1][]

由于对该业务非常熟悉，因此很快就定位到造成主从同步延迟的原因，并很快就解决了延迟的问题。这里不直接说解决办法，而是想描述一套完整的解决主从延迟问题的思考方式，和大家一起来系统的做一些思考。带着问题去思考延迟的根本原因和解决办法。我想，这也许会更有意义。授人以鱼，不如授人以渔。接下来我们就一起开脑洞。

首先，既然产生了主从延迟，就说明在从机上的消费速度赶不上主机binlog产生的速度。我们先来思考一下可能的原因，并根据现场的蛛丝马迹去验证猜想的正确性。其实所谓的问题排查，就是提出可能问题猜想，然后不断去证明的过程。不同的是，每个人的经验不同，排查的质量也不尽头相同，仅此而已。那就来从各个可能的方面开脑洞吧。

--------------------

网络

网络可能导致主从延迟的问题，比如主机或者从机的带宽打满、主从之间网络延迟很大，有可能会导致主上的binlog没有全量传输到从机，造成延迟。

我的那组DB的IO线程已经将对应的binlog近乎实时的拉取到了从机DB上，基本排除网络导致的延迟。还可以结合网络质量相关监控来进一步确认是网络的问题。

--------------------

机器性能

从机使用了烂机器？之前有遇到过有的业务从机使用了很烂的机器，导致的主从延迟。比如主机使用SSD而从机还是使用的SATA。从机用烂机器的观念需要改改，随着DB自动切换的需求越来越高，尤其是我所在的金融行业，从机至少不要比主机配置差。

从机高负载？有很多业务会在从机上做统计，把从机服务器搞成高负载，从而造成从机延迟很大的情况，这种使用top命令即可快速发现。

从机磁盘有问题？磁盘、raid卡、调度策略有问题的情况下，有的时候会出现单个IO延迟很高的情况，比如raid卡电池充放电的时候，在没有设置强行write back的情况下得会将write back模式修改为write through。使用iostat命令查看DB数据盘的IO情况，是否是单个IO的执行时间很长，块大小和磁盘队列情况等，可以比较一下DB盘的IO调度规则以及块大小的设置等。使用iostat查看IO运行情况：

![Image 1][]

从IO情况看也没什么问题，单个IO延迟很小，iops很低，写带宽也不大。调度规则（cat /sys/block/fioa/queue/scheduler）和块大小等和主机设置是一样的，排除磁盘的问题。

从运行指标看，机器负载很低，机器性能也可以排除。

--------------------

大事务

是否是经常会有大事务？这个可能广大DBA们会遇到比较多，比如在RBR模式下，执行带有大量的delete操作，或者在MBR模式下删除的时候添加了不确定语句（类似limit），又或者一个表的alter操作等，都会导致延迟情况的发生。这种通过查看processlist相关信息以及使用mysqlbinlog查看binlog中的SQL就能快速进行确认。这个设想也被排除。

--------------------

锁

锁冲突问题也可能导致从机的SQL线程执行慢，比如从机上有一些select  ....  for update的SQL，或者使用了MyISAM引擎等。此类问题，可以通过抓去processlist以及查看information\_schema下面和锁以及事务相关的表来查看。

经过排查也并未发现锁的问题。

--------------------

参数

参数部分使用如果是innodb引擎，可以根据自己的使用环境调整innodb\_flush\_log\_at\_trx\_commit、sync\_binlog参数来提升复制速度，那组DB使用的TokuDB，则可以优化tokudb\_commit\_sync、tokudb\_fsync\_log\_period、sync\_binlog等参数来做调整。这些参数调整后，复制的延迟情况会有一些作用。

备注：这种调整可能会影响数据的安全性，需要结合业务来考虑。

--------------------

多线程

多线程问题可能是DBA们遇到最多的问题，之前在5.1和5.5版本，mysql的单线程复制瓶颈就广受诟病。从5.6开始mysql正式支持多线程复制。

很容易想到，如果是单线程同步的话，单个线程存在写入瓶颈，导致主从延迟。那就先调整为多线程试试效果。

可以通过show processlist查看是否有多个同步线程，也可以查看参数的方式查看是否使用多线程（show variables like '%slave\_parallel%'）

![Image 1][]

当你看到是上图这种结果的时候，恭喜你，你使用的是单线程。使用下面那行命令改造成多线程复制：

> STOP SLAVE SQL\_THREAD;SET GLOBAL slave\_parallel\_type='LOGICAL\_CLOCK';SET GLOBAL slave\_parallel\_workers=8;START SLAVE SQL\_THREAD;

改造后如下图所示：

![Image 1][]

我的环境如上图所示，本来就已经是多线程复制了，因此问题的根源也不在是否开启多线程复制上。但是当我使用show processlist查看复制状态的时候，大多数情况下发现只有1个SQL线程在执行，如下图所示：

![Image 1][]

通过上面的图可以发现，基本都是一个线程在执行，那么可以初步判定是多线程的威力没有得到很好的发挥，为了更有力地说明问题，想办法统计出来每个同步线程使用的比率。统计方法如下:

1、将线上从机相关统计打开（出于性能考虑默认是关闭的），打开方法可以如下如下SQL：

> UPDATE performance\_schema.setup\_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events\_transactions%';
> 
> UPDATE performance\_schema.setup\_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'transaction';

2、创建一个查看各个同步线程使用量的视图，代码如下：

> USE test;
> 
> CREATE VIEW rep\_thread\_count AS SELECT a.THREAD\_ID AS THREAD\_ID,a.COUNT\_STAR AS COUNT\_STAR FROM performance\_schema.events\_transactions\_summary\_by\_thread\_by\_event\_name a WHERE a.THREAD\_ID in (SELECT b.THREAD\_ID FROM performance\_schema.replication\_applier\_status\_by\_worker b);

3、一段时间后，统计各个同步线程的使用比率，SQL如下:

> SELECT SUM(COUNT\_STAR) FROMrep\_thread\_count INTO @total;
> 
> SELECT 100\*(COUNT\_STAR/@total) AS thread\_usage FROMrep\_thread\_count;

结果如下：

![Image 1][]

从上面的结果我们可以看出，绝大多数情况下，都是一个线程在跑，在监控这种存在大量数据导入的场景，肯定容易出现瓶颈。如果能提高各个线程并发执行的能力，可能很好地改善同步延迟的情况，那该如何来解决呢？

--------------------

组提交

我们不妨从多线程同步的原理来思考，在5.7中，多线程复制的功能有很很大的改善，支持LOGICAL\_CLOCK的方式，在这种方式下，并发执行的多个事务只要能在同一时刻commit，就说明线程之间没有锁冲突，那么master就可以将这一组的事务标记并在slave机器上安全的进行并发执行。因此，可以尽可能地使所有线程能在同一时刻提交，这样就能很大程度上提升从机的执行的并行度，从而减少从机的延迟。

有了这个猜想后，很自然想到了人为控制尽可能多地使所有线程在同一时刻提交，其实官方已经给我们提供了类似的参数，参数如下：

> binlog\_group\_commit\_sync\_delay

\#参数说明见：[https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html\#sysvar\_binlog\_group\_commit\_sync\_delay][https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html_sysvar_binlog_group_commit_sync_delay]

**备注：这个参数会对延迟SQL的响应，对延迟非常敏感的环境需要特别注意，单位是微秒**

> binlog\_group\_commit\_sync\_no\_delay\_count

\#参数说明见：[https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html\#sysvar\_binlog\_group\_commit\_sync\_no\_delay\_count][https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html_sysvar_binlog_group_commit_sync_no_delay_count]

备注：这个参数取到了一定的保护作用，在达到binlog\_group\_commit\_sync\_no\_delay\_count设定的值的时候，不管是否达到了binlog\_group\_commit\_sync\_delay设置定的阀值，都立即进行提交。

由于是监控的DB，主要是load数据，然后展示，1秒左右的导入延迟对业务没什么影响，因此将两个参数调整为：

> SET GLOBAL binlog\_group\_commit\_sync\_delay = 1000000;
> 
> SET GLOBAL binlog\_group\_commit\_sync\_no\_delay\_count = 20;

\#备注，这两个参数请根据业务特性进行调整，以免造成线上故障。

为了防止导入SQL堆积，设置SET GLOBAL binlog\_group\_commit\_sync\_no\_delay\_count为20，在达到20个事务的时候不管是否达到了1秒都进行提交。减少对业务的影响。

设置完这两个参数后，发现并发复制瞬间提升了好多，很多时候8个线程都能跑满。于是将线程调整到16个。运行一段事件后，再次统计各个同步线程的使用比率，发现并发度提升了非常多，新的比率如下图所示：

![Image 1][]

通过show slave status查看，发现从机延迟越来越小，目前已经完全追上，并稳定运行了一周。

--------------------

回顾总结

最后，简单总结一下：

在遇到主从延迟的问题的时候，可以从如下几个地方开脑洞，寻找蛛丝马迹，找到问题的根源，对症下药，药到病除，排查范围包括但不限于如下几方面：

网络方面

性能方面

配置方面（参数优化）

大事务

锁

多线程复制

组提交

通过上面对整个问题排查的梳理，希望广大DBA遇到类似复制延迟的问题都能彻底终结。

参考资料：

[https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html][https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html]

[https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/][https_www.percona.com_blog_2016_02_10_estimating-potential-for-mysql-5-7-parallel-replication]

作者：飞鸿无痕  
链接：https://www.jianshu.com/p/ed19bb0e748a  
來源：简书  
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

[Image 1]: 
[https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html_sysvar_binlog_group_commit_sync_delay]: https://link.jianshu.com?t=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Freplication-options-binary-log.html%23sysvar_binlog_group_commit_sync_delay
[https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html_sysvar_binlog_group_commit_sync_no_delay_count]: https://link.jianshu.com?t=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Freplication-options-binary-log.html%23sysvar_binlog_group_commit_sync_no_delay_count
[https_dev.mysql.com_doc_refman_5.7_en_replication-options-binary-log.html]: https://link.jianshu.com?t=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Freplication-options-binary-log.html
[https_www.percona.com_blog_2016_02_10_estimating-potential-for-mysql-5-7-parallel-replication]: https://link.jianshu.com?t=https%3A%2F%2Fwww.percona.com%2Fblog%2F2016%2F02%2F10%2Festimating-potential-for-mysql-5-7-parallel-replication%2F