sqlserver⾼可⽤⽅案_⾼可⽤数据库主从复制延时的解决⽅案MySQL主从复制的延时⼀直是业界困扰已久的问题。延时的出现会降低主从读写分离的价值，不利于数据实时性较⾼的业务使⽤MySQL。
UDB是UCloud推出的云数据库服务，上线已达六年，运营了数以万计的UDB MySQL实例。除了提供⾼可⽤、⾼性能、便捷易⽤的产品特性，团队还平均每天帮助⽤户解决2-3起MySQL实例主从复制延时的问题。从⼤量实践中我们总结了主从复制延时的各种成因和解决⽅法，现分享于此。
延时问题的重要性
主从复制机制⼴泛应⽤在UDB的内部实现中：UDB创建的从库和主库就采⽤了“主从复制”的数据复制；另外，UDB的主打产品“UDB MySQL⾼可⽤实例”，也是采⽤2个数据库互为主从的“双主模式”来进⾏数据复制，⽽双主模式的核⼼就是主从复制机制。
如果主从复制之间出现延时，就会影响主从数据的⼀致性。
在⾼可⽤复制场景下，我们在UDB⾼可⽤容灾设计上考虑到，若出现主备数据不⼀致的场景，默认是不允许进⾏⾼可⽤容灾切换的。因为在主备数据不⼀致的情况下，此时发⽣容灾切换，且在新的主库写⼊了数据，那么从业务⾓度上，会产⽣意想不到的严重后果。
复制延时问题，不仅在UDB⾼可⽤中会带来不良后果，在只读从库的场景下，若从库产⽣复制延时，也
可能会对业务造成⼀定影响，⽐如在业务上表现为读写不⼀致——新增/修改数据查不到等现象。
由此可见，主从复制的延时问题在数据库运营中需要特别关注。⼀般来说，DBA在库上执⾏'SHOW SLAVE STATUS'，并且观察
'Seconds_Behind_Master’的值，就能够了解当前某个数据库和它的主库之间的数据复制延时。这个值是如此的重要，因此在UDB的监控界⾯上，我们将这个值单独抽取来，设计了“从库同步延时”监控项，以便于运维⼈员能够直接在控制台上观察。
⽣产环境中延时问题的分析及解决
我们将最常见的主从复制延时案例总结为⼏类，以下是相关案例的现象描述、原因分析和解决⽅法汇
总。
◆ 案例⼀：主库DML请求频繁
某些⽤户在业务⾼峰期间，特别是对于数据库主库有⼤量的写请求操作，即⼤量insert、delete、update等并发操作的情况下，会出现主从复制延时问题。
现象描述
我们通过观察主库的写操作的QPS的值，会看到主库的写操作的QPS值突然升⾼，伴随主从复制延时的上升，可以判断是由于主库DML请求频繁原因造成的。
如上图，可以看出，在17:58分左右QPS突增，查看控制台上的写相关QPS，也有相应提升。⽽QPS突增的时间，对应的延时也在逐步上升，如下图所⽰。
原因分析
经过分析，我们认为这是由于主库⼤量的写请求操作，在短时间产⽣了⼤量的binlog。这些操作需要全部同步到从库，并且执⾏，因此产⽣了主从的数据复制延时。
从深层次分析原因，是因为在业务⾼峰期间的主库写⼊数据是并发写⼊的，⽽从库SQL Thread为单线程回放binlog⽇志，很容易造成relaylog堆积，产⽣延时。
解决思路
如果是MySQL 5.7以下的版本，可以做分⽚(sharding)，通过⽔平扩展(scale out)的⽅法打散写请求，提升写请求写⼊binlog的并⾏度。
如果是MySQL 5.7以上的版本，在MySQL 5.7，使⽤了基于逻辑时钟(Group Commit)的并⾏复制。⽽在MySQL 8.0，使⽤了基于Write Set的并⾏复制。这两种⽅案都能够提升回放binlog的性能，减少延时。
◆ 案例⼆：主库执⾏⼤事务
⼤事务指⼀个事务的执⾏，耗时⾮常长。常见产⽣⼤事务的语句有：
使⽤了⼤量速度很慢的导⼊数据语句，⽐如：INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE等；
使⽤了UPDATE、DELETE语句，对于⼀个很⼤的表进⾏全表的UPDATE和DELETE等。
当这个事务在从库执⾏回放执⾏操作时，就有可能会产⽣主从复制延时。
现象描述
我们从SHOW SLAVE STATUS的结果进⾏分析，会发现 Exec_Master_Log_Pos 字段⼀直未变，且second_behinds_master持续增加，⽽ Slave_SQL_Running_State 字段的值为"Reading event from the relay log"；同时，分析主库binlog，看主库当前执⾏的事务，会发现有⼀些⼤事务，这样基本可以判定是执⾏⼤事务的原因导致的主从复制延时。
原因分析
当⼤事务记录⼊binlog并同步到从库之后，从库执⾏这个事务的操作耗时也⾮常长，这段时间，就会产⽣主从复制延时。
举个例⼦，假如主库花费200s更新了⼀张⼤表，在主从库配置相近的情况下，从库也需要花⼏乎同样
的时间更新这张⼤表，此时从库延时开始堆积，后续的events⽆法更新。
解决思路
对于这种情况引起的主从复制延时，我们的改进⽅法是：拆分⼤事务语句到若⼲⼩事务中，这样能够进⾏及时提交，减⼩主从复制延时。
◆ 案例三：主库对⼤表执⾏DDL语句
DDL全称为 Data Definition Language ，指⼀些对表结构进⾏修改操作的语句，⽐如，对表加⼀个字段或者加⼀个索引等等。当DDL对主库⼤表执⾏DDL语句的情况下，可能会产⽣主从复制延时。
现象描述
从现象上，如果从库执⾏SHOW SLAVE STATUS的输出中，检查Exec_Master_Log_Pos⼀直未动，在排除主库执⾏⼤事务的情况下，那么就有可能是在执⾏⼤表的 DDL。这⼀点结合分析主库binlog，看主库当前执⾏的事务就可以进⾏确认。
DDL语句的执⾏情况，可以进⼀步细分现象来更好地判断：
1.DDL未开始，被阻塞，这时SHOW SLAVE STATUS的结果能检查到Slave_SQL_Running_State为waiting for table metadata lock，且Exec_Master_Log_Pos不变；
2.DDL正在执⾏，SQL Thread单线程应⽤导致延时增加。这种情况下观察SHOW SLAVE STATU的结果能发现
Slave_SQL_Running_State为altering table，⽽Exec_Master_Log_Pos不变。
如果有上述的现象，那么很有可能主库对⼤表执⾏DDL语句，同步到从库并在从库回放时，就产⽣了主从复制延时。
原因分析
DDL导致的主从复制延时的原因和⼤事务类似，也是因为从库执⾏DDL的binlog较慢⽽产⽣了主从复制延时。
解决思路
遇到这种情况，我们主要通过SHOW PROCESSLIST或对information_schema.innodb_trx做查询，来到阻塞DDL语句，并KILL掉相关查询，让DDL正常在从库执⾏。
DDL本⾝造成的延时难以避免，建议考虑：
避免业务⾼峰，尽量安排在业务低峰期执⾏ ；
set sql_log_bin=0后，分别在主从库上⼿动执⾏DDL(此操作对于某些DDL操作会造成数据不⼀致，请务必严格测试)，这⼀条如果⽤户使⽤云数据库UDB，可以联系UCloud UDB运维团队进⾏协助操作。
◆ 案例四：主库与从库配置不⼀致
如果主库和从库使⽤了不同的计算资源和存储资源，或者使⽤了不同的内核调教参数，可能会造成主从不⼀致。
现象描述
我们会详细⽐对主库和从库的性能监控数据，如果发现监控数据差异巨⼤，结合查看主从的各个配置情况，即可作出明确判断。
原因分析
各种硬件或者资源的配置差异都有可能导致主从的性能差异，从⽽导致主从复制延时发⽣：
硬件上：⽐如，主库实例服务器使⽤SSD磁盘，⽽从库实例服务器使⽤普通SAS盘，那么主库产⽣的写⼊操作在从库上不能马上消化掉，就产⽣了主从复制延时；
配置上：⽐如，RAID卡写策略不⼀致、OS内核参数设置不⼀致、MySQL落盘策略不⼀致等，都是可能的原因。
解决思路
考虑尽量统⼀DB机器的配置(包括硬件及选项参数)。甚⾄对于某些OLAP业务，从库实例硬件配置需要略⾼于主库。
◆ 案例五：表缺乏主键或合适索引
sqlserver备份表语句如果数据库的表缺少主键或者合适索引，在主从复制的binlog_format设置为'row'的情况下，可能会产⽣主从复制延时。
现象描述
我们进⾏数据库检查时，会发现：
观察SHOW SLAVE STATUS的输出，发现Slave_SQL_Running_State为Reading event from the relay log；
SHOW OPEN TABLES WHERE in_use=1的表⼀直存在；
观察SHOW SLAVE STATUS的Exec_Master_Log_Pos字段不变；
mysqld进程的CPU接近100%(⽆读业务时)，IO压⼒不⼤。
这些现象出现的情况下，可以认为很可能有表缺乏主键或唯⼀索引。
原因分析
在主从复制的binlog_format设置为'row'的情况下，⽐如有这样的⼀个场景，主库更新⼀张500万表中的20万⾏数据。binlog在row格式下，记录到binlog的为20万次update操作，也就是每次操作更新1条记录。如果这条语句恰好有不好的执⾏计划，如发⽣全表扫描，那么每⼀条update语句需要全表扫描。此时SQL Thread重放将特别慢，造成严重的主从复制延时。
解决思路
这种情况下，我们会去检查表结构，保证每个表都有显式⾃增主键，并协助⽤户建⽴合适索引。
◆ 案例六：从库⾃⾝压⼒过⼤
有时候，从库性能压⼒很⼤的情况下，跟不上主库的更新速度，就产⽣了主从复制延时。
现象描述
观察数据库实例时，会发现CPU负载过⾼，IO利⽤率过⾼等现象，这些导致SQL Thread应⽤过慢。这样就可以判断是因为从库⾃⾝压⼒过⼤引起主从复制延时。
原因分析
部分UCloud⽤户对于数据库的主从会使⽤读写分离模式，读请求⼤部分在从库上执⾏。在业务有⼤量读请求的场景下，从库会产⽣⽐主库⼤得多的性能压⼒。有的⽤户甚⾄会在从库运⾏⼗分耗费计算资源的OLAP业务，这也对从库造成了更⾼的性能挑战，这些都会造成主从复制的延时。
解决思路
这种情况下，我们会建议⽤户建⽴更多从库，打散读请求，降低现有从库实例的压⼒。对于OLAP业务来说，可以专门建⽴⼀个从库来做OLAP业务，并对这个从库，允许适当的主从复制延时。
总结
在使⽤MySQL的主从复制模式进⾏数据复制时，主从复制延时是⼀个需要考量的关键因素。它会影响数据的⼀致性，进⽽影响数据库⾼可⽤的容灾切换。
在遇到数据库之间出现主从复制延时的情况下，我们团队基于过往经验，归纳出以下⽅法与流程来协助排查问题：
通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况。(顺便也可排除在从库备份时的类似原因)；
若Exec_Master_Log_Pos不变，考虑⼤事务、DDL、⽆主键，检查主库对应的binlog及position即可；
若Exec_Master_Log_Pos变化，延时逐步增加，考虑从库机器负载，如IO、CPU等，并考虑主库写操作与从库⾃⾝压⼒是否过⼤。