记一次批量删除导致MySQL只读实例同步延迟高达1288秒

背景

由于线上的操作日志记录表日志太大，需要进行定期删除处理。

DELETE FROM resty_log WHERE create_time < NOW() - INTERVAL 30 DAY;

以上SQL语句中，NOW() 函数返回当前的日期和时间（包括时、分、秒）。INTERVAL 30 DAY 表示一个时间间隔，即30天。整个WHERE子句筛选出所有create_time字段值小于当前时间减去30天的记录，并通过DELETE语句将这些记录从表中删除。

异常报警

云数据库RDS版发生告警

• 实例名称：xxxx-生产正式-只读库slave01
• 监控指标: 只读实例延迟
• 报警条件: 连续满足3次 （1109.5s>=10s）,
• 当前值: 1109.5s
• 持续时间: 2分钟

云服务器ECS发生告警

• 监控指标: (Agent)memory.used.utilization
• 报警条件: 连续满足3次 （80.51%>=80%）,
• 当前值: 80.51%
• 持续时间: 3分钟

只读实例延迟

连接拓扑管理

访问模式结构图

MySQL只读实例同步延迟原因与处理

问题描述

由于阿里云云数据库RDS只读实例采用MySQL原生的基于日志复制技术（异步复制或半异步复制），必然会有同步延迟。延迟会导致只读实例与主实例的数据出现不一致，从而导致业务出现问题。另外，延迟也有可能引起日志堆积，导致只读实例空间被迅速消耗。

若主实例正产生大量的日志，有可能会使只读实例被锁定。相关错误提示

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

以上错误信息是在执行删除脚本的异常错误信息。以上错误通常意味着在执行这个删除操作时，有其他事务正在访问或锁定resty_log表中的行，并且这些行恰好是你要删除的行。

原因引起

大事务写入。主实例执行一个涉及数据量非常大的update、delete、insert…select、replace…select等事务操作时，会生成大量的日志数据并同步到只读实例。只读实例需要花费与主实例相同的时间来完成该事务，因此会导致只读实例同步延迟。例如，在主实例上执行一个持续80秒的删除操作，只读实例进行相同操作时也需要花费很长时间，于是会出现延迟情况。虽然目前支持多表并发事务，但对于单表事务，只能单线程来完成复制，因此也会比较慢。

在大事务同步到只读实例导致延迟出现时，登录数据库，执行show slave status \GSQL语句，确认 Seconds_Behind_Master 不断变化，而 Exec_Master_Log_Pos 却保持不变，说明只读实例的SQL线程在执行一个大事务或者DDL操作，系统显示类似如下结果：

最后通过show processlist;语句定位具体的线程。只读实例执行show slave status \G命令，确定是否存在元数据锁。如果binlog是row模式，大事务会导致binlog文件比较大。可以通过命令show binary logs;查看File_size的数值，如果大于max_binlog_size参数的大小，则一定是产生了大事务。

解决方案

使用批量删除。在SQL中，DELETE 语句本身就可以用于删除多行数据，只要你的 WHERE 子句能够匹配到多行。但是，如果你需要处理的数据量非常大，直接执行一个删除大量行的 DELETE 语句可能会导致性能问题或锁表时间过长。

1、使用 WHERE 子句限制删除的行数

虽然这不是真正的“批量”处理，但你可以通过 WHERE 子句中的条件来限制每次删除的行数。例如，你可以根据某个时间戳或ID范围来删除数据。

DELETE FROM resty_log WHERE create_time < '2023-01-01' AND id <= 10000;

然后，你可以逐步增加ID的范围，直到删除所有需要的行。

2、使用 LIMIT 子句

不是所有的数据库系统都支持在 DELETE 语句中使用 LIMIT 子句，但MySQL和PostgreSQL等系统支持。这允许你限制每次删除操作影响的行数。

DELETE FROM resty_log WHERE create_time < '2023-01-01' LIMIT 1000;