UCloud高可用数据库UDB主从复制延时的解决

MySQL主从复(fu)制的用数(shu)延时一直是业界困扰已久的问题。延时的据库解决出现(xian)会降低主从读写分离(li)的价值，不利于数(shu)据(ju)实时性较高的主制延业务使用MySQL。

UDB是从复UCloud推出的(de)云数据(ju)库服务，上线已达六年，用数运营了数以万计的(de)据库解决(jue)UDB MySQL实例。除了(le)提供高可用、主制延高性能、从(cong)复便捷易用的用数产品特(te)性，团队还平(ping)均每天帮助用户解决2-3起(qi)MySQL实例主从复制延时的据库解决问题。从大量实践中我们总结了主从复制延时的主制延各(ge)种成因和解决方法，现分享于此。从复

延时问题的用数重要性(xing)

如果主从复制(zhi)之间出现延时，就会影响主从数据的一致性。

在高可(ke)用复制场景下(xia)，我们在UDB高可用(yong)容灾设计上考虑到，若出现主备数据(ju)不一致的(de)场景，默认是不允许进行高可用容灾切换的。因为在主备数据不一致的情况下，此时(shi)发生容灾切(qie)换，且在新的主库写入了数据，那么从业务角度上，会(hui)产生意想不到的严重后果。

复制延时问题，不仅在UDB高可用中会带来不良后果，在只读从库的场景下，若从库产生复制延时，也可能会对业务造成一定影响，比如在(zai)业(ye)务上表现为读写不一致——新增/修改数据查不到等现象。

由此可(ke)见(jian)，主从复制的(de)延时问题在数据库运营中需要特别关注。一般来说，DBA在(zai)库上执行(xing)’SHOW SLAVE STATUS’，并且观察

‘Seconds_Behind_Master’的值，就能够了解当前某个数据库和它的(de)主库之间的数据复制延时。这个值是如此的重要，因此在UDB的监控界面上，我们将这个值单独抽取来，设计了“从库同步延时(shi)”监控项，以便于运维人员能够直(zhi)接在控制台上观察。

生(sheng)产环境中延时问题的分析及解决

我们将最常见的主从复制延时案例总结为(wei)几类，以下是相关案例的现象描述、原因分(fen)析和解决方法汇总。

◆ 案例一：主库DML请求频繁

某些用户在业务高峰期间，特别是对于数据库主库有大量的写请求操作，即大量insert、delete、update等并发操作的情况下，会出现主从复制延时问(wen)题。

现象描述

我们通过观察主库的写操作的QPS的值(zhi)，会看到主库的写操作的QPS值突然升高，伴随主从复制延时的上升，可以判断是(shi)由于主库DML请求频繁原(yuan)因造成(cheng)的(de)。

如上图，可以看(kan)出，在(zai)17:58分左右QPS突增，查看控制(zhi)台上(shang)的写相关QPS，也有(you)相应提升。而QPS突增的时间，对应的延时(shi)也在逐步上升，如下图所示。

原因分析

经过分析，我(wo)们认为这是由(you)于(yu)主库大量的写请求操作，在短时间产生(sheng)了大量的binlog。这些操作需要全(quan)部同步(bu)到从库，并且执行，因此产生(sheng)了主从的数据复制延时。

从深层次分析原(yuan)因，是因为在业务高峰期间的主库写入数据是并发写入的，而从库SQL Thread为单线程回放binlog日志，很容易造成(cheng)relaylog堆积，产生延时。

解决思路

如果是MySQL 5.7以下的版本，可以做分片(sharding)，通(tong)过水平扩展(scale out)的方(fang)法打散写请求，提升写请求写入binlog的并行度。

如果是MySQL 5.7以上的版本，在MySQL 5.7，使用了基于逻辑时钟(Group Commit)的并行复制。而在MySQL 8.0，使用了基于Write Set的(de)并行复制。这两种方案都能够提升回放binlog的性(xing)能，减少延时。

◆ 案例二：主库执行大事务

￭使用了大量速度很慢的导(dao)入(ru)数据语句，比如：INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE等(deng)；

￭使用了UPDATE、DELETE语句，对于一个(ge)很大(da)的表进(jin)行(xing)全表的UPDATE和DELETE等。

当这(zhe)个(ge)事务在(zai)从库执行回(hui)放执行操作时，就有可能会产生主从复制(zhi)延时(shi)。

现象描述

我们从SHOW SLAVE STATUS的结果进行分析，会发现 Exec_Master_Log_Pos 字段一直未变，且second_behinds_master持续增加(jia)，而 Slave_SQL_Running_State 字段的值为”Reading event from the relay log”；同时，分析主库binlog，看主库当前执行的(de)事务，会发现有一些大事务，这样基本可以判定(ding)是执行大事务的原因导(dao)致的(de)主从复制延时。

原因分析

当大事务记录入binlog并同步(bu)到从(cong)库之后，从库执行这个(ge)事务的操作耗时也非常长，这段时间，就会(hui)产生主从复制延时。

举个例子，假如主库花费200s更新了一张大表，在主从(cong)库配置相近的情况下，从库也(ye)需(xu)要花几乎同样的时间更新这(zhe)张大表，此时从库延时开始堆积(ji)，后续的events无法更(geng)新。

解决(jue)思路

对(dui)于这种情况引起的主从复制延时，我们的改进方法是：拆分大事务语句到若干(gan)小事务中，这样能够进行(xing)及(ji)时提(ti)交，减小(xiao)主从(cong)复(fu)制延时。

◆ 案例三：主库对大表执行DDL语句

DDL全(quan)称为 Data Definition Language ，指一些对表结构进行修改操作的语句，比如，对表加一个字段或者加一个(ge)索引等等。当DDL对主库大表执行DDL语句的情况下，可能会产生主从复制延时(shi)。

现(xian)象描述

从现象上，如果从库执行SHOW SLAVE STATUS的输出中，检查Exec_Master_Log_Pos一直未动，在(zai)排除主库执行大事务的情(qing)况下，那(na)么就有可能是在执行大表的 DDL。这一点结合分(fen)析主库binlog，看主库当前执行的事务就可以进行确认。

DDL语句的执行情况，可以进一步细分现象来更好地判断：

1. DDL未开始，被阻塞，这时SHOW SLAVE STATUS的结果能检查到Slave_SQL_Running_State为waiting for table metadata lock，且Exec_Master_Log_Pos不变；

2. DDL正(zheng)在执行，SQL Thread单线程应(ying)用导致延时增加。这种情况下观察SHOW SLAVE STATU的结(jie)果能发现Slave_SQL_Running_State为altering table，而Exec_Master_Log_Pos不变。

如果有上述的现象，那么很有可能主库对大表执行DDL语句，同步(bu)到从库并在(zai)从库回放时，就产生了主从复制延时。

原因分析

DDL导(dao)致的主从复(fu)制延时的原因(yin)和大事(shi)务类似，也是因为从库执行DDL的binlog较(jiao)慢而产生了主从复(fu)制延时。

解决思(si)路

遇到这种情况，我们主要(yao)通过SHOW PROCESSLIST或对information_schema.innodb_trx做查询，来找到阻塞DDL语句，并KILL掉相关查询，让DDL正(zheng)常在从库执行。

DDL本身造成的延时难以(yi)避免，建议考虑：

￭避免业务高峰，尽量安排在业务低峰期执行 ；

￭set sql_log_bin=0后，分别在主从库上手动(dong)执行DDL（此操作对于某些DDL操作会(hui)造成数据不一致，请务(wu)必严格测试），这一条如果用户使用云数据库UDB，可以联系UCloud UDB运维团队进行协助操作。

◆ 案例四(si)：主库与从库配置不一致

如果主库和从库使用了(le)不同的(de)计算资源和存储资源，或者使用了不同的内核调教参数，可能会造(zao)成(cheng)主从不一致。

现象描述

我们会详细比对主库和(he)从(cong)库的性能监控数据，如果发现监控数据差异巨大，结合查看主从的各(ge)个配置情(qing)况，即可作出明确判断。

原因分析

各种硬件或者资源的配置差异都(dou)有可能导致主从的(de)性能差异，从而导致主从复制延时发生：

￭硬件上：比如，主库实例服务器使(shi)用SSD磁盘，而从库实(shi)例服务器使(shi)用普通(tong)SAS盘，那么主库产生的写入操作在从库上不能马上(shang)消化掉，就产生了主从复制延时(shi)；

￭配置上：比如，RAID卡写策略不一致、OS内核参数设置不一致、MySQL落盘策略不一致等，都是可能的原因。

考虑尽量统一DB机器的配置（包括硬件及选项参数）。甚至(zhi)对于某些OLAP业务，从库实例硬件配置需要略高于(yu)主库。

◆ 案例五：表缺乏主键或合适索引

如果数据库的表缺少主键或者合适索引，在(zai)主从复制的binlog_format设置为’row’的情况下，可能会产生主从复制延时。

现象描述

我们进行数据库检查时，会发现：

￭观察SHOW SLAVE STATUS的输出，发现Slave_SQL_Running_State为(wei)Reading event from the relay log；

￭SHOW open='open' TABLES WHERE in_use=1的表一直存在(zai)；

￭观察SHOW SLAVE STATUS的Exec_Master_Log_Pos字段不变；

￭mysqld进程的CPU接近100%（无读业(ye)务时），IO压力不大。

这些现象(xiang)出现的情况下，可以认为很可能有(you)表(biao)缺乏主键或唯一索引。

原(yuan)因分析

在主从(cong)复(fu)制的binlog_format设置为’row’的情况下，比如有这样的一个场景，主库更新一张500万表中(zhong)的20万行数据。binlog在row格式下，记录到binlog的为20万次update操作，也就是每次操作(zuo)更新1条记录。如果这条语句恰好有不好的执行计划，如发生全表扫描，那么每一条update语句需要全表扫描。此时SQL Thread重放将特别慢，造成严重(zhong)的主从复制延时。

解决思路

这种情况下，我们会去检查表结构，保证每个表都(dou)有显式自增主键，并(bing)协助用(yong)户建立(li)合适索引。

◆ 案例六：从库自身(shen)压力(li)过大

有时候，从库性能压力很大(da)的情况下，跟不上主库的更新速度，就产生了主从复制延时。

现象(xiang)描述

观察数据库实(shi)例时(shi)，会发现CPU负载过(guo)高，IO利用率过高等现象，这些导致SQL Thread应用(yong)过慢。这样就可以判断是因为从库自身(shen)压(ya)力(li)过大引起(qi)主从复制延时。

原因(yin)分析

部分UCloud用户对于数据库的主从(cong)会使用读写分离(li)模式，读请求大部分在从(cong)库上执行(xing)。在业务有(you)大量读请求的场景下，从库会产生比主库大得多的性能压(ya)力。有的用(yong)户甚至会(hui)在从库运行十(shi)分(fen)耗费计算资源的OLAP业务，这也(ye)对从库造成了(le)更高的性能挑战，这些都会造(zao)成主从复制的延时(shi)。

解(jie)决思路

这种情况下，我们会建议用户建(jian)立更(geng)多从库，打散读请求，降低现有(you)从库实例的压力。对于OLAP业务来说，可以专门建立(li)一个从库来做OLAP业务，并对这个从库，允许适当的主从复制延时(shi)。

总结

在使用MySQL的主从复制模式进行数据复制时，主从复制延时是一个需要考量的(de)关(guan)键因素。它会影响数据的一致性，进而影响(xiang)数据库高可用的容灾切换。

在遇到数据库之(zhi)间(jian)出现主从复制延时的情况下(xia)，我们团队基于过往经验，归纳出以下方法与流程来协助排查问题：

￭通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现(xian)在从库的情况。（顺便也可排除在从库备份时的类似原因）；

￭若Exec_Master_Log_Pos不变，考虑大(da)事务(wu)、DDL、无主键，检查主库对应的binlog及position即可(ke)；

￭若Exec_Master_Log_Pos变化，延时(shi)逐步增加，考虑从库机器(qi)负载，如IO、CPU等，并考虑主库写操作与从库自身压力是否过大。

UDB的高可用(yong)、高性能、便捷易用，可(ke)以大量减轻使用者的运维负担。在使用过程中， UDB团(tuan)队也会利用多年累积的运营经验，帮助用户及时分析、排查问题原(yuan)因，并给出合理(li)的解决方法。