MySQL主从复制延迟一直是业界长期存在的问题。延迟的出现会降低主从读写分离的价值，不利于数据实时性高的业务使用MySQL。UDB是UCloud推出的云数据库服务。上线六年，运营数万个UDBMySQL实例。除了提供高可用、高性能、便捷易用的产品特性外，团队还平均每天帮助用户解决2-3个MySQL实例主从复制延迟问题。我们从大量的实践中总结了主从复制延迟的各种原因和解决方法，在此分享。延迟问题的重要性主从复制机制在UDB内部实现中被广泛使用：UDB创建的从库和主库采用“主从复制”的数据复制；此外，UDB的旗舰产品“UDBMySQL高可用实例”，也采用了两个数据库的“双主模式”相互复制，而双主模式的核心就是主从复制机制。如果主从复制之间存在延迟，就会影响主从数据的一致性。在高可用复制场景下，我们在UDB高可用容灾设计中考虑，如果主备数据不一致，默认不允许高可用容灾切换。因为当主备数据不一致时，此时会发生容灾切换，将数据写入新的主库，所以从业务角度来看，会产生意想不到的严重后果。复制延迟问题不仅会给UDB高可用带来不良后果，在只读从库场景下，如果从库产生复制延迟，也可能对业务造成一定的影响。书写不一致——无法找到新的/修改的数据等现象。可见，主从复制的延迟问题在数据库操作中需要特别注意。一般来说，DBA通过对数据库执行'SHOWSLAVESTATUS'，观察'Seconds_Behind_Master'的值来了解当前数据库与其主数据库之间的数据复制延迟。这个值太重要了，所以在UDB监控界面上，我们把这个值单独提取出来，设计了“从库同步延时”监控项，让运维人员可以直接在控制台上观察。生产环境延迟问题分析及解决方案我们将最常见的主从复制延迟案例归纳为几类。以下是对相关案例的现象描述、原因分析及解决方案的总结。◆案例一：主库DML请求频繁。部分用户在业务高峰期会延迟主从复制，尤其是在主库数据库有大量写请求操作，即插入、删除、更新等大量并发操作时。时间问题。现象描述通过观察主库写操作的QPS值，我们可以看到主库写操作的QPS值突然增加，并且随着主从复制延迟的增加，可以判断是主库频繁请求DML造成的。的。如上图所示，可以看到在17:58左右QPS突然增加，控制台写相关的QPS也相应增加。至于QPS突然增加的时间，对应的延迟是逐渐增加的，如下图。原因分析经过分析，我们认为这是由于主库的大量写请求操作，短时间内产生了大量的binlog。这些操作都需要同步到从库执行，所以会出现主从数据复制延迟。深分析原因，是因为主库写入的数据是在业务高峰期并发写入的，而从库SQLThread是单线程回放binlog日志，容易造成relaylog堆积和延迟。解决方案：如果MySQL版本在5.7以下，可以做分片（sharding），采用横向扩展（scaleout）的方式分散写请求，提高写请求到binlog的并行度。如果是MySQL5.7以上的版本，在MySQL5.7中，采用了基于逻辑时钟（GroupCommit）的并行复制。在MySQL8.0中，使用了基于WriteSet的并行复制。这两种方案都可以提升binlog回放的性能，减少延迟。◆案例2：主库执行一个大事务。大交易是指交易的执行时间非常长。常见的产生大事务的语句包括：使用大量慢速导入数据语句，如：INSERTINTO$tb、SELECT*FROM$tb、LOADDATAINFILE等；使用UPDATE、DELETE语句，对一个大表进行全表的UPDATE和DELETE等。当这个事务执行从库回放执行操作时，可能会出现主从复制延迟。现象描述我们分析SHOWSLAVESTATUS的结果，会发现Exec_Master_Log_Pos字段没有变化，second_behinds_master继续增加，Slave_SQL_Running_State字段的值为“Readingeventfromtherelaylog”；同时分析主库binlog，看主库中的当前事务，会发现一些大事务，所以基本可以确定主从复制延迟是大事务执行造成的。原因分析当binlog中记录了一个大事务并同步到从库时，从库需要很长时间才能执行到这个事务的操作。这段时间会出现主从复制延迟。比如主库更新一张大表需要花费200s，主从库的配置差不多，从库也需要花费几乎相同的时间更新这张大表。无法更新事件。解决方案对于这种情况造成的主从复制延迟，我们的改进方法是：将大事务语句拆分成若干个小事务，可以及时提交，减少主从复制延迟。◆Case3：主库对大表执行DDL语句。DDL的全称是DataDefinitionLanguage，指的是一些修改表结构的语句，比如给表增加一个字段或者增加一个索引。当DDL对主库中的大表执行DDL语句时，可能会出现主从复制的延迟。现象描述从现象来看，如果从库执行SHOWSLAVESTATUS的输出，检查Exec_Master_Log_Pos没有被移动，如果排除主库执行大事务，那么可能是执行了一个DDL大桌子。这一点可以通过分析主库的binlog，查看主库当前执行的事务来确认。DDL语句的执行状态可以进一步细分，更好的判断：1、DDL还没有启动，被阻塞。此时SHOWSLAVESTATUS的结果可以查看到Slave_SQL_Running_State正在等待表元数据锁，Exec_Master_Log_Pos保持不变；2、.DDL正在执行，SQLThread单线程应用导致延迟增加。这种情况下，观察SHOWSLAVESTATU的结果，发现Slave_SQL_Running_State在alteringtable，而Exec_Master_Log_Pos没有变化。如果出现上述现象，很有可能是主库对大表执行DDL语句，同步到从库，从从库回放，造成主从复制延迟。原因分析DDL导致主从复制延迟的原因和大事务类似。也是因为从库执行DDL的binlog慢，导致主从复制延迟。解决方案遇到这种情况，我们主要是通过SHOWPROCESSLIST或者查询information_schema.innodb_trx来找到阻塞的DDL语句，kill掉相关的查询，这样DDL才能在从库中正常执行。DDL本身造成的延迟是无法避免的。建议考虑：避开业务高峰期，尽量安排在非高峰期执行；设置sql_log_bin=0后，分别在主从数据库上手动执行DDL（此操作会导致部分DDL操作数据不一致，请务必严格测试），如果用户使用云数据库UDB，可以联系UCloudUDB运维团队求助。◆案例4：主从配置不一致如果主从使用不同的计算资源和存储资源，或者使用不同的内核调优参数，都可能导致主从不一致。现象描述我们将详细对比主库和从库的性能监控数据。如果监控数据相差很大，我们可以通过查看master和slave的配置来做出明确的判断。原因分析各种硬件或资源的配置差异可能导致主从性能差异，导致主从复制延迟：硬件：例如主数据库实例服务器使用SSD盘，而从数据库实例服务器使用普通SAS盘，那么主库产生的写操作不能立即被从库消化，造成主从复制延迟；配置：比如RAID卡写策略不一致，OS内核参数设置不一致，MySQL存储策略不一致等都是可能的原因。解决办法是尽量统一DB机器的配置（包括硬件和可选参数）。即使是一些OLAP业务，从库实例的硬件配置也需要略高于主库。◆案例5：表缺少主键或合适的索引。如果数据库表缺少主键或合适的索引，当主从复制的binlog_format设置为'row'时，可能会出现主从复制延迟。现象描述当我们查看数据库时，会发现：观察SHOWSLAVESTATUS的输出，发现Slave_SQL_Running_State是Readingeventfromtherelaylog；SHOWOPENTABLESWHEREin_use=1该表始终存在；观察SHOWSLAVESTATUS的Exec_Master_Log_Pos字段保持不变；mysqld进程CPU接近100%（无读业务时），IO压力不高。当出现这些现象时，可以认为是有一张表缺少主键或索引。原因分析当主从复制的binlog_format设置为'row'时，例如有一个场景，主库更新500万表20万行数据。在binlog的行格式中，binlog中记录了20万条更新操作，即每条操作更新一条记录。如果这条语句正好有一个坏的执行计划，比如全表扫描，那么每条更新语句都需要全表扫描。此时SQLThreadreplay会极其缓慢，造成主从复制延迟严重。解决思路在这种情况下，我们会检查表结构，保证每张表都有一个显式的自增主键，并协助用户创建合适的索引。◆案例六：从库本身压力过大有时候，当从库的性能压力很大时，跟不上主库的更新速度，导致主从复制延迟。现象描述观察数据库实例，会发现CPU负载过高，IO利用率过高等，导致SQLThread应用速度过慢。这样就可以判断主从复制延迟是从库本身压力过大造成的。原因分析部分UCloud用户会对主从数据库采用读写分离模式，大部分读请求都在从数据库上执行。在业务有大量读请求的场景下，从库会产生比主库大很多的性能压力。一些用户甚至在从库中运行消耗大量计算资源的OLAP服务，这也对从库提出了更高的性能挑战，会造成主从复制的延迟。解决思路对于这种情况，我们建议用户创建更多的从库来分散读请求，减少现有从库实例的压力。对于OLAP业务，可以为OLAP业务专门建立一个从库，对于这个从库，允许适当的主从复制延迟。小结在使用MySQL的主从复制模式进行数据复制时，主从复制延迟是需要考虑的关键因素。会影响数据的一致性，进而影响数据库高可用的容灾切换。针对数据库间主从复制延迟的情况，我们团队根据以往的经验，总结了以下方法和流程来辅助排查：通过SHOWSLAVESTATUS和SHOWPROCESSLIST查看从库当前状态。（对了，从库备份的时候也可以排除类似的原因）；如果Exec_Master_Log_Pos不变，考虑大事务、DDL、无主键，查看主库对应的binlog和position；如果Exec_Master_Log_Pos发生变化，延迟会逐渐增加，考虑从库机器负载，如IO、CPU等，考虑主库写操作和从库本身是否压力过大。UDB的高可用、高性能、便捷易用的特点，可以大大减轻用户的运维负担。在使用过程中，UDB团队也会利用多年积累的操作经验，帮助用户及时分析、排查问题原因，提供合理的解决方案。
