MySQL复制延迟很头疼？从AI诊断到内核优化，AliSQL为您保驾护航。

复制延迟问题对于MySQL数据库来说搭建主备架构增强可用性搭建只读实例增强扩展性都是刚需中的刚需这二者都依赖于 MySQL 基于 Binlog 的逻辑复制。在MySQL中主库会以事务维度将被修改的所有数据记录到 Binlog 中并传输到从库从库收到 Binlog 后将修改应用到自己的表上。正常情况下从主库提交事务到从库应用完成的时间差要很短以保证从库的数据的时效性但是在一些业务场景中这个时间差可能拉长到分钟甚至小时级严重影响从库数据时效这就是复制延迟问题。用 AliSQL解决“复制延迟”界的“顽疾”MySQL 复制延迟的成因很多其中有些场景可以通过堆资源改配置等方式扛过去但有些“顽疾”场景只能通过深度的内核优化来根除。在MySQL中最典型线上最常见的“顽疾”场景有以下四类大表 DDL大事务批量数据处理业务例如低峰期进行的数据整理导入删除等小事务高并发业务例如节假日或大促时的业务高峰期等我们在一个未开启复制延迟优化的AliSQL实例上验证这四种业务场景下的复制延迟情况。如上图所示依次执行一张大表的 optimize table一个 500 万行的 insert 大事务一个批量数据清理任务用4个并发每个sql 删除1万行数据一个小事务高并发的业务场景sysbench 的 write_only 脚本512并发9万TPS。可以看到这四段测试都触发了只读实例的复制延迟。用 RDS AI 助手来诊断这四段复制延迟如下图所示。AI 助手查看监控了解析binlog并结合实例进行分析最终诊断出了四段延迟的根本原因并给出了解决延迟问题的优化建议。我们在 AliSQL 实例上按AI助手的建议打开AliSQL的四项复制延迟优化DDL实时复制大事务实时复制中等事务并行复制优化小事务打包优化。再次测试这四个业务场景如上图所示优化开启后复制延迟消失。对比优化前后的CPU利用率图可以看到大表DDL和大事务在从库上提早开始执行避免了复制延迟批量数据处理和高并发业务场景的CPU利用率都有提升这是因为它们的复制并发度提高了从库吞吐显著提升避免了复制延迟。抽丝剥茧复制延迟的诊断与优化上章提到的四种复制延迟问题RDS AI 助手如何准确诊断出原因AliSQL 内核又如何进行了优化本章将说明四种延迟问题的成因诊断和优化方法。大事务和DDL延迟原因大事务和DDL是MySQL中最典型的复制延迟问题其原理如上图所示。在MySQL中大事务和DDL都是在提交时写binlog随后才会被传输到从库并应用。因此大事务和DDL在从库执行多久就会产生多久的复制延迟。诊断方法大事务和DDL在从库执行期间从库的复制延迟计算方法为now - 主库提交时间。大事务和DDL在主库提交时间是固定的因此其复制延迟每秒一定增加1复制延迟曲线一定是一条斜率为1的直线。当复制延迟曲线的斜率为1时延迟的原因大概率是大事务或DDL这时只需要解析延迟开始的时刻主库上的binlog如果找到大事务或DDL就能确定延迟的原因找到导致延迟的具体业务。AliSQL 实时复制优化为了解决大事务和DDL导致的复制延迟AliSQL 引入了实时复制优化。如上图所示将大事务和DDL的 binlog 内容在其执行期间就传输到从库并实时的在从库执行当大事务或DDL在主库提交时只需通知从库一起提交就可以做到零延迟如果大事务或DDL在主库回滚只需通知从库一起回滚不影响主从复制。通过这个优化AliSQL彻底解决了大事务和DDL的复制延迟问题小事务高并发业务延迟原因很多业务在高峰期会以较高的并发度更新数据库当主库业务的并发度高于从库应用的并发度时就可能导致从库的事务吞吐不如主库产生复制延迟。造成从库并发度不够的因素有哪些我们按顺序来讲解。Worker 线程数从库的 worker 线程数量如果很少并发度肯定上不去这是最基础的条件。worker线程的数量由 slave_parallel_workers 参数控制。事务可并发度当worker线程充足时两个事务在从库能否并发应用取决于这两个事务有没有修改同一行数据。在MySQL 中可以选择三种方法判断两个事务是否可以并发分别是是否修改同一个库是否在主库同时提交是否修改同一行writeset。后两种方法仅在MySQL 5.7及以上版本支持。其中效果最好的方法是writeset它引入了一个哈希表来计算事务是否修改同一行数据。大部分日常业务场景中事务间的数据冲突并不大开启writeset后可并发度都很高。Writeset 在一些coner case如无主键外键表等中也存在一定的局限性本文不展开。复制链路上的性能瓶颈当我们有了足够多的worker线程并且开启writeset功能获得了足够的可并发度我们就具备了提升从库并发度的前提条件但最后能否真的高并发度复制还要看从库IOSQL和Worker线程的吞吐。熟悉MySQL的朋友会知道多线程复制中有IOSQL和Worker三类线程IO线程负责从主库收取binlog eventSQL线程负责将收到的binlog event分发给Worker线程Worker线程有多个负责并行应用事务。其中IO线程和SQL线程SQL线程和Worker线程之间都存在由锁保护的等待和通知操作高并发时这些锁很容易成为性能瓶颈。在MySQL的binlog中一个dml事务会由多个event组成包括gtidquerytable maprow和xid event。以 sysbench write_only 这个常用的测试脚本为例该脚本一个事务中会更新2行插入1行删除1行体现在binlog中一个事务就有11个event。经AliSQL优化后write_only 脚本的峰值TPS为8.8万即从库每秒要应用97万个binlog event极端情况下每秒要加锁300万次。这样频率的加锁即便锁内处理很快也会带来巨大性能瓶颈。诊断方法MySQL 的SHOW SLAVE STATUS命令中的Slave_SQL_Running_State列会反馈出SQL线程的状态是我们诊断这类问题的利器。当woker线程数不够时SQL线程会经常处于等待空闲worker的状态此时执行SHOW SLAVE STATUS命令通常会看到Slave_SQL_Running_State: Waiting for replica workers to process their queues当事务可并发度不够时SQL线程会经常处于等待事务依赖关系状态此时执行SHOW SLAVE STATUS命令通常会看到Slave_SQL_Running_State: Waiting for dependent transaction to commit此时我们就要检查主库参数开启writeset如果writeset已经开启了那说明主库当前业务的可并发度确实很低例如批量数据处理业务下一节将详细讲解外键表的业务或者热点更新业务等。当Worker线程和可并发度都充足时IO和SQL线程的吞吐就成为复制的瓶颈。在延迟期间执行SHOW SLAVE STATUS命令如果是IO线程吞吐瓶颈通常会看到Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates很好理解如果有延迟的情况下没有新的relay log可读那一定是IO线程慢了或者中断了。如果是SQL线程吞吐瓶颈通常会看到Slave_SQL_Running_State: Reading event from the relay log这个状态包含了SQL线程大部分工作内容读relay log和大部分锁等待都包含在这个状态中。当看到上述两个状态时复制的瓶颈很可能在IO和SQL线程的吞吐上此时可以解析binlog查看每秒钟的事务数和binlog event数来辅助判断如果每秒有几万个事务并且有几十万甚至上百万的binlog event就可以确定是小事务高并发导致的复制延迟问题。AliSQL 高并发复制优化针对小事务高并发的延迟问题AliSQL从两方面进行了优化AliSQL针对性的优化了复制多个线程之间的加锁逻辑比MySQL原生复制逻辑减少了30%以上的加锁次数。AliSQL引入了小事务打包优化能够合并同一个事务内的多个event的加锁动作显著减少加锁次数。通过这两项优化高并发场景下AliSQL从库的吞吐可以高于主库小事务高并发场景的复制延迟问题彻底解决。批量数据处理延迟原因很多业务会在凌晨或其他低峰期进行数据删除整理或者导入等工作每天都要处理数百万甚至数千万行数据。通常情况下开发会用小批量多并发的方式进行处理这样处理速度快并且可以灵活调整批量和并发的大小控制对数据库的影响。然而MySQL的并行复制逻辑对这种业务非常不友好执行此类定时任务时经常会出现复制延迟严重的甚至到第二天定时任务开始前第一天任务产生的延迟都还没追齐。为什么跑批这么容易延迟呢在执行批量数据处理期间实例上主要有两种事务一种是批量数据处理的中等大小事务一般一个事务内处理几千行数据另一种是普通业务的小事务大部分实例上即便在低峰期还是会有一定量的普通业务只是业务压力较小。两种事务会交叉存在于binlog文件中当两个中等事务中间夹着多个小事务并且这些小事务修改了同行数据时两个中等事务就无法并发执行了。如上图例子中Trx2和Trx5是批量数据处理的中等事务Trx3和Trx4是普通业务的小事务并且修改了同一行数据。SQL线程在分发这些事务时Trx2 和 Trx3都可以正常分发Trx4则需等待Trx3及之前的所有事务都提交后才能分发这导致Trx5的分发是到了Trx2提交后才进行的进而导致Trx2和Trx5无法并行应用。因此在批量数据处理期间经常看到从库上并发度非常低甚至很多时刻只有一个worker在工作几乎退化成了单线程应用导致复制延迟问题。诊断方法清楚原理后诊断此问题就很容易了。第一步我们解析延迟期间的binlog统计一个binlog内修改千行以上的中等事务和修改百行以内的小事务的数量如果中等事务和小事务数量都不少就要进一步判断其并行度。第二步判断binlog中每两个相邻的中等事务是否能够并行执行中等事务之间是否有相互依赖的小事务如果有很多的相邻的中等事务无法并行执行复制就会退化为串行进而导致复制延迟问题。AliSQL 中等事务复制优化为了优化这个场景AliSQL 将原本在SQL线程中的阻塞后续事务分发的等待逻辑放到了worker线程中。这样一来如果有少量修改同行的小事务分布在中等事务之间这些小事务会在worker线程中等待依赖的事务提交而不是在SQL线程中等待阻塞其他事务的分发。这样一来这些没有冲突的中等事务也就能够并发起来了。附录RDS AI 助手诊断流程视频注发文章前需要剪辑一下视频视频只要从4:00开始的后半段并建议加速一下视频减少时长