背景介绍

本文根据MySQL主备延迟问题解决、分析的过程，描述数据库主备延迟可能的原因和实际的处理方法。业务差异造就了数据库的场景不同、配置不同、参数不同，但是技术上都有共通的原理，希望各位同学能从本文中得到借鉴。附件中总结了主备同步的基础知识，以供参考。

1 问题描述

DB日常巡检过程中，发现阿里云RDS For MySQL实例主备两节点出现较长时间的延迟。通过排查不同的时间段，发现主备延迟一直存在，每天成规律的上下波动（图1），从昨天开始主备延迟呈持续上升状态（图2）。

高可用版RDS数据库主备出现严重的延迟，高可用特性就会丢失。如遇主库突发故障，就会发生主备之间无法正常切换，导致业务的中断。实际生产环境中高可用是业务连续性运行的保障，主备库之间不能出现长时间的延迟情况，否则将造成不可评估的影响。

注：阿里云RDS高可用版主备延迟现阶段不支持报警监控（只读实例有主备延迟监控），备库节点访问权限未开放，只能人工去检查实例监控指标。

2 环境介绍

a、数据库版本：阿里云MySQL8.0 rds20200430。
b、配置信息：常规实例-高可用版32C 256G，最大IOPS：72000。
c、数据量：2.29/3T(对MYSQL单实例来说是个大实例)。
d、架构：高可用版RDS For MySQL主备两节点提供高可用架构。参考下图3：

3 实例信息

首先明确数据库负载情况，根据监控查看发现主库IO、网络、CPU、内存等指标都没有瓶颈问题。和研发人员交流获取相关信息，对实例实际承载业务情况和运行的负载情况进行梳理记录。

3.1 主库有大量insert语句

7*24小时“单条”insert写入（业务架构原因，DB前端没有削峰系统，insert不能批量插入），低峰每秒4000次插入，高峰每秒8000次插入。并且随着业务量的变化，每秒的SQL插入指标还在递增过程中。

3.2 主库有批量delete语句

a、业务在结构设计初期做了分库、分表。
b、一个实例24个库，每个库256张表，全实例6144张表。
c、对于所有表，每天定时删除七天前的数据，删除方式为根据表名、时间戳获取删除数据的最大id，单表、单条delete进行一次性删除。

3.3 主库有少量update语句

每秒大约70次和业务相关的update操作，update为单行更新。极少数情况会更新多行数据，多行更新的行数均为个位数（该点在问题排查的过程中也引起过一定的怀疑，最后通过binlog日志的解析进行了明确）。

3.4 主库表分析

实例因为表的delete删除操作导致每张表都存在较大的碎片率，全库前一天做了大量Alter Table Table_Name Engine=Innodb操作，数据库负载有了一定上升，但主机的相关资源没有瓶颈（MySQL表分析是重操作，一定结合实际业务情况决定操作区间，建议在业务低峰期进行。）。

3.5 主机负载

a、CPU平均使用率30%，无过大波动。
b、内存初始化值80%，无波动。
c、IOPS峰值1.6W，平均1W，最大IOPS：72000。
d、网络流量平均50MB/s，无过大波动。

3.6 备份情况

设置阿里云RDS For MySQL 默认备份每天一次全备，备份动作实际在备库进行，每日全备开始时间为4点，完成时间大约17点，备份时间较长。

4 问题分析

4.1 全库表分析排查

4.1.1 场景分析

数据库延迟从昨天开始持续性的增加，并未像往常一样每日阶段性的主备延迟为零。该实例昨天唯一做的变更就是进行了表分析操作。根据以往经验数据库百分之90%以上都是因为变更引发的，先对表分析操作进行排查。

前面我们也提到了，因为业务模型决定所有业务表每天需要定时删除七天前的数据。MySQL使用delete进行数据删除之后，占用的空间不会进行释放（Truncate Table会释放空间）。尽管占用的空间一定程度上会被重新使用，但是表的碎片率会随着删除次数的增多，越来越高。会造成空间资源严重浪费、统计信息不准确，从而造成SQL走了错误的执行计划，造成数据库出现大量慢查询。因此MySQL需要定期进行表分析来降低表的碎片率。

然而Mysql主备同步是基于binlog 进行的。我们知道传统的半同步复制在2PC的commit stage阶段后会将sql传输到从库，该模式下主库宕机，可能会造成主备数据差异。无损的半同步复制在2PC的sync stage阶段会将SQL传输到从库（阿里云RDS默认无损复制）。当主库在进行表分析没有完成的情况下，从库是无法获取执行表分析的SQL语句，这就造成了主备之间存在延迟，当实例主库进行大批量的进行表分析操作，主备延迟时间确实会增高，但正常应该很快会追平。

我们发现主备存在延迟，且延迟没有像往常一样呈现周期性波动，而是延迟的时间越来越长（如下图4所示），第一想到的就是昨天对数据库的变更操作“表分析”。查看数据，发现延迟数据上升的时间段和主库进行表分析的时间段重合（表分析操作还在持续运行中）。猜想是因为表分析造成的，因为表分析的语句需要执行完之后，才能传输到备库，所以会造成主备延迟（正常主库表分析完成、备库再执行，应该很快会追平）。

根据现有情况判断主备之间的延迟可能是因为主库在进行大批量的表分析造成的。根据前面查看主备延迟监控，该实例的主备延迟是周期性的波浪线，那么正常状况下主备延迟会归零。考虑到我们人工进行了表分析，所以等待表分析被备库执行完成之后，再进行问题分析。

4.1.2 实际结果

等待期间在测试环境中进行相关测试。我们在主库进行大批量表分析，发现表分析确实会造成主备延迟的增加，但是正常情况表分析完成之后，主备库的延迟会逐步追平。

针对生产环境，我们联系阿里云后台人员，在备库查看正在运行的sql、解析备库最新的Binlog日志，发现表分析已经完成。等待一段时间后，主备延迟并没有像期望那样追平归零，甚至增大的趋势都没有降低，主备的差距越来越大。

4.2 备库备份分析

4.2.1 场景分析

表分析确实会造成主备延迟，现象是明确的，正常情况下延迟应该会追平，但现阶段延迟持续增加，就应该是其他因素影响的了。

重新进行检查，因为无法直接观察获取备库DB运行状况（无权限），尝试对延迟的数据信息进行统计。检查发现备库做全备的时候，主备延迟增加的速率会明显增加，备份完成之后，延迟增加速率会相对减缓。考虑该实例的数据量较大，备份的时间相对较长，且DB在备份的过程会获取相关表的锁资源，一定程度会影响备库的SQL重做的效率。

两点猜测：一是因为备份文件太多，太大，导致的恢复无法追平。二是备库在备份的时候主机资源不足，影响SQL重做的效率。备份影响主备延迟的猜想测试分析过程如下表：

时间	20210531	20210601	20210602	20210603	20210604	20210605	20210606	20210607	20210608	20210609
备份计划时间	4-5点	4-5点	4-5点	4-5点	4-5点	无备份	无备份	7-8点	4-5	4-5
备份开始时间			4:56-17:01点	4:46-15:34点	4:46-16:02点			7:05-17:02点	4:22-15:05点
延迟转折区间			3:00-19:20点	4:55-15:05点	4:25-15:35点	5:00-11:20点	4:00-10:20点	2:20-08:55点	4:30-12:15点
延迟区间差值			16小时20分	11小时40分	10小时45分	6小时20分	6小时20分	6小时25分	7小时45分
对应延迟时间			180000- 197550	183381- 193204	179892- 194244	184333- 198219	180007- 194582	174021- 188587	181222- 194482
172800s 两天	3600 1小时
Delete脚本执行			2点半执行的	2:00-4:16点	2:00-4:20点	2点开始	2:00-4:51点	2:00-5:07点	2:00-4:36点

4.2.2 实际结果

通过调整备份的开始时间、修改备份的频率，完成上表，对比发现备份过程确实会造成延迟问题。但是不进行备份的情况下，延迟并没有降低，只是在无备份操作的情况下，主备延迟增加速率有一定减缓。备份操作，只是主备延迟持续增加速度的一个影响因素。主备延迟增加问题还是无法改善、修复。

对于备库主机资源的不足的猜想，根据阿里云后台提供的数据，主机资源没有瓶颈、DB资源也没有瓶颈。如下图5所示：

4.3 大事务delete影响分析

4.3.1 场景分析

查看DB监控和从开发了解到，该实例的特点是7*24小时大量单行的insert、每天的定时delete、少量的update。现阶段我们明确知道，表分析会造成主备延迟，备份影响延迟增长的速率。根据以往经验大事务造成主备延迟的情况非常的多。

主备发生延迟，正常优化的第一步也应该是将大事务的sql进行拆分。该实例因为业务原因，大事务delete操作SQL拆分、修改需要一定时间，排除其他问题现象后。同步进行的delete大事务的拆分、修改也已经通过测试。沟通阿里云后台人员提供了相关参考建议：创建删除依赖的索引，改成多线程并发删除。参考如下：

create table batch_delete(batch_id,pk_id) as select round(pk_id/100,0) batch_id,pk_id from src_tbl where data_to_be_deleted;

create index batch_delete_bid_idx on batch_delete(batch_id);
多个线程并行

delete from src_tbl where pk_id in (select pk_id from batch_delete where batch_id = xxx；commit；

考虑到该实例有6千多张表，如果创建索引，创建的对象太多，并且在不明确的情况下，不期望在生产环境主库上做较大的变动。换个思路我们尝试将delete每次删除的数据量降低、并将单次删除的并发提高。逐步进行测试。

（1）delete并发从6持续增加到20。
（2）delete从按照条件获取删除ID，每次删除1W、5K、1K、0.5K）。

4.3.2 实际结果

并发的提高，单个delete 的删除量的调整。delete 任务的完成时间随着调整测试在不断的发生变化。但是对于该实例的主备延迟的优化，效果甚微，主备延迟的还在持续缓慢增长。（怀疑人生，正常的主备问题通过该方案能有缓解，亦或解决，但是该实例问题顽固存在）。

4.4 真假大事务Update语句分析

4.4.1 场景分析

检查DB引擎监控发现，update语句从监控上看是大事务，update_ps 27，监控对应的innodb_rows_updated 8611，并不是研发最开始所说的单行更新。如下图6所示：

研发人员告知update每次都是单行更新，特殊场景下会更新两三行，不是大事务。但从监控指标来看，单个update的SQL确实对应更新几百行数据。

4.4.2 实际结果

原因不明确，监控指标含义不清。实际测试分析，根据监控指标的时间点，找到实际binlog日志。进行解析分析。证实Update都是单行更新。该监控指标并不是实际的更新行数。所以主备延迟和update语句无关（后续实验证明，insert指标和delete指标都是准确的。Update指标不对应sql语句实际更新行数，而是其他指标意义）。

4.5 PK与UK的排查

4.5.1 场景分析

怀疑是否是因为表对象没有PK或者UK造成的？因为在DML SQL发生时，主键或者唯一键会影响到SQL的性能。（问题排查过程，没有方向，先排除可能影响的因素）。

4.5.2 实际结果

捞取频率较高的SQL语句，检查发现Update与Delete在删除的时候都有合理的主键和唯一键，主库没有慢SQL。

4.6 中间过程与问题发现

4.6.1 中间过程：

经过现象分析，我们能确定的：

a、主备主机资源没有瓶颈。
b、网络资源不是瓶颈。
c、表分析不是主要原因。
d、备库备份不是主要原因。
e、delete语句经过优化后，实例无大的事务。
f、主键、唯一键都是最优创建，没有全表扫的情况。

按照经验主备延迟在上述问题排查完全之后，应该能够初步解决主备延迟的问题。但是实际并没有解决。回头再次查看mysql 关于主备同步的相关参数，回顾到了并行恢复优化的相关参数-WRITESET（很早就出来的参数，但是一般场景都使用不到，详情见附件1）。

4.6.2 问题发现

主备并行恢复基于组提交的方式，binlog_transaction_dependency_tracking=commit_order（阿里云RDS默认参数），binlog生成按照组提交的方式。从库slave_parallel_type=logical_clock；基于组提交的并行恢复，同一组提交的事务的恢复没有锁冲突。同一个Group内的事务将会在Slave上并行回放。

解析binlog日志发现发现：sequence_number不同，last_committed相同的情况很少。数量基本都是个数级别。从现象上分析从库的并行恢复正常进行，但是恢复的速率非常不好（前期问题排查，从库SQL运行正常，没有锁相关的事件，没有考虑到并行恢复的效果）。实例binlog解析内容,参考如下图7：

4.7 WRITESET并行恢复测试

4.7.1 场景分析

对该实例解析了binlog，发现binlog中last_commited的数值几乎都不同，猜想从库的并行恢复的效果不会太好。结合前期的测试和问题排查，数据库每时每刻都有大量的单条insert插入语句。现在证实组提交的效果不好，从库相当于是单线程的SQL恢复。询问阿里云工程师明确，writeset参数在阿里云RDS上还没有大规模的使用，且不是默认参数。主备延迟持续增加确实可能是因为并行恢复效果不好。RDS默认参数为commit_order，需要修改成writeset进行相关测试(writeset相关知识可参考附件1中的内容)。

主库环境修改：

（1） set global transaction_write_set_extraction=XXHASH64;

（2） set global binlog_transaction_dependency_tracking=WRITESET;

## 下三参数设置是为了防止主备切换后环境变化。

（3） set global slave_parallel_type='logical_clock';

（4） set global slave_parallel_workers=16;

（5） set global slave_preserve_commit_order=ON;

测试环境从库环境修改：

（1） stop slave;

##下二参数设置是为了防止主备切换后环境变化。

（2） set global transaction_write_set_extraction=XXHASH64;

（3） set global binlog_transaction_dependency_tracking=WRITESET;

（4） set global slave_parallel_type='logical_clock';

（5） set global slave_parallel_workers=16;

（6） set global slave_preserve_commit_order=ON;

（7） start slave;

（8） show processlist; 查看并行复制是否开启。

（9） show slave status\G 查看集群运行状况。

4.7.2 实际结果

通过测试修改binlog_transaction_dependency_tracking = WRITESET， binlog组提交的效果呈现单个last_committed 对应上千的sequence_number。组提交效果明显（图8所示）。

通过调整主库参数binlog_transaction_dependency_tracking 为WRITESET和和备库slave_parallel_workers的并行参数，主备的延迟情况得到了解决。主备之间能够实时同步，高可用架构可用状态，业务连续运行有了保障。主备实时同步如下图9：

5 问题揭晓

使用WRITESET的方式并通过调整并行恢复参数，主备之间的延迟问题得到了解决，如图9所示，主备延迟基本控制在1秒以内。综合分析，该实例主备延迟是因为单条insert插入数据量太大，大量插入导致主库组提交效果不明显，备库并行恢复效果不佳造成的。

回顾最开始发现的现象：

（1）主备延迟呈现周期性波动：
主备之间延迟的周期性波动主要的原因是，主库在业务高峰期insert插入太多导致备库无法及时重做，业务低峰期又能追平差异造成的现象。

（2）进行表分析后开始主备延迟持续性增加：
表分析确实造成了主备的延迟，但实际在本次分析过程中，只占很小的影响因素。最后经过统计业务的插入量，本月内缓慢增加了百分之三十左右。增加的这段插入量的过程，主备同步性能达到瓶颈，备库始终无法及时进行SQL的重做，就是导致主备延迟持续增加的罪魁祸首。
反向也说明了当前业务场景下MySQL数据库在commit_order的参数模式下，主备数据同步有一定的瓶颈。而writeset参数控制下，能有效的解决该业务场景下主备同步的问题。

6 问题总结

本次优化通过各个方面的细化分析，踩了很多的坑，走了很多的弯路，最终结合现有业务特征，选择了合适的复制模式，解决了该场景下主备复制的延迟问题。通过本次主备延迟的问题分析总结如下建议：

1. 熟知自己管理资源的情况，了解云环境或物理环境的主机负载。

2. 使用云环境需要明确云环境默认参数，明确主备复制的模式是什么。

3. 深入了解业务场景、SQL的类型。对比业务场景和主备复制的特点，选择合适复制技术（不明确的前提下，可在上线前进行压测）。

4. 业务允许的情况下对数据库大事务SQL进行提前拆分。

5. 架构设计前考虑前端添加削峰系统，避免大批量单行SQL 的操作。

6. 根据业务量的预估，提前进行分库分表规划（mysql 单表上千万就可考虑分库分表）。

7. 最最最重要的一点，解决问题，对于一切不明确现象，都要靠自己静下心去验证、来思考，不能只听他人诉说，时刻牢记实践出真知。

注：本文只是结合自有环境，实际问题解决过程进行了记录。主要从资源使用、业务场景、并行恢复等方面进行了分析、验证。主备同步还可根据其他方面进行分析、优化，如：MYSQL sync_binlog 与innodb_flush_log_at_trx_commit的双一设置对DB性能的影响。

7 附件1 主备同步基础知识总结

7.1 复制基础

首先关于mysql数据库主备延迟，我们需要先了解它的主备复制的方式。如下图10所示：

MySQL主备复制是基于Binlog的复制，简略流程如下：

主库Commit提交，生成Binlog日志；
从库IO Thread获取到binlog日志生成Relay Log；
从库SQL Thread 获取读取Relay Log 文件中的日志，解析成Sql语句；
从库进行重放操作。

7.2 复制方式

1.2.1 传统复制

传统复制模式，基于binlog文件和binlog位点进行复制，因为在树形复制的场景下，位点信息不具备全局唯一性，容易造成数据丢失。

1.2.2 GTID复制

GTID复制模式，基于binlog文件和GTID号（uuid：seqno）。GTID在复制集群中具有全局唯一性的特点，相比传统复制更能保证数据安全。使用GTID可以范围的向主库请求binlog，这样当主库宕机，程序就可以选择更加合理的备库，其他的从库也可以指向新主库，与新主库创建主备关系。

7.3 复制模式

1.3.1 同步模式

当主事务提交一个事务，在主向前端反馈commit成功结果“之前”，必须保证所有的从库已经提交了这个事务，所有的从库不仅接收，还必须保证应用了该事务。代表产品：MySQL Cluster。

1.3.2 异步模式

当主事务Commit后将事务写到binlog日志，不需要关心从库是否接收或者什么时候接收ack的消息。主库直接返回成果结果给客户端，不会与从库发生ack之类交互操作。

1.3.3 半同步模式

rpl_semi_sync_master_wait_point=after commit;

半同步复制（5.7.2版本之前默认唯一的一个参数值after_commit），主库上客户端发出commit指令，事务提交到存储引擎后，等待从库传递回来ack，在向前端返回数据操作成功的状态。与无损复制的区别就是，如果主库上的事务已经提交到了存储引擎，而正在等待从的ack过程中，这个时候发生crash，则主库上的这个事务已经认为commit了。而从库还没有commit，当这个时候切换到从后，就会发生回滚最后的这个事务，这个时候主备数据就会不一致。

1.3.4 增强半同步复制（无损复制）

rpl_semi_sync_master_wait_point=after_sync;
rpl_semi_sync_master_wait_for_slave_count=1；
rpl_semi_sync_master_wait_no_slave=on

主库上客户端发出提交指令，事务写入到binlog buffer，IO thread获取binlog数据传入到了从库，（事务写入到relay log,且flush to disk 中持久化到了磁盘上），然后从库给主库返回一个ack，master才会把事提交到存储引擎，且返回到client一个commit成功的指令。无损复制一定程度上避免了主备不一致的情况。

当同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000ms，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主备又重新恢复为半同步复制。

7.4 复制技术

7.4.1 单线程复制

主库进行数据变更，生成binlog之后，dump线程工作，I/O线程获取数据在从库生成relay log,从库sql线程从relay log 中读取接收到的主库binlog变更事务，然后将所有事件都进行一次“串行执行”。而binlog的写入时机是事务完成对数据的修改，且事务commit之后。也就是说，从库的事务具有限制性，主库提交之后，然后产生的binlog发送到从库执行。理论而言，一前一后肯定有复制延迟，特别是再包含大事务的时候。

7.4.2 多线程（并行）复制

7.4.2.1 多线程复制的背景：

MySQL的复制是基于binlog的。MySQL复制包括两部分，IO线程和 SQL线程。IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log。SQL线程主要负责解析relay log，并应用到slave中。不管怎么说，IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。

IO没必要多线程，因为IO线程并不是瓶颈（阿里云云盘请注意不同级别的最大带宽规格）。

SQL可以多线程，目前最新的5.6，5.7，8.0 都是在SQL线程上实现了多线程，来提升slave的并发度。

7.4.2.2 多线程复制的重点：

并行复制的重点，能不能进行并行复制，关键点还是在多个事务之间是否有锁冲突。

7.4.2.3 DATABASE并行复制

基于库的并行复制。不同库的事务没有锁冲突。

Master:
binlog_transaction_dependency_tracking = COMMIT_ORDER

Slave:
stop slave sql_thread;
set global slave_parallel_type='DATABASE';
set global slave_parallel_workers=4;
start slave sql_thread;

7.4.2.4 LOGICAL_CLOCK并行复制

基于组提交的并行复制方式（基于事务）。

slave-parallel-type=LOGICAL_CLOCK ：Commit-Parent-Based模式（旧）：同一组的事务last-commit相同，表示没有锁冲突. SQL可以并行重做。Lock-Based模式（新）：即便不是同一组的事务，只要事务之间没有锁冲突，就可以并发进行SQL重做。不在同一组，只要N个事务可以重叠，说明没有锁冲突。