内容为：MySQL_复制延迟过高(实例:XXXX），当前值:1032.00，阈值:1000 - 100000000 ，发生时间:12/30/2021 02:27:16。
分析过程一般情况下，如果出现复制延迟，首先要看库的复制状态和活动会话。第二天早上分析发现没有延迟。幸运的是，我们的监控和操作维护脚本相对完善。监控脚本每分钟执行一次。如果发现延迟，将记录上述两个信息。因此，从监控保留的日志可以看出，最早是2021-12-30 02:02:48已经延迟了9秒。
检查复制状态从复制状态可以看出，IO线程和SQL线程运行状态正常Yes，复制延迟Seconds_Behind_Master为9秒，SQL线程状态为Waiting for dependent transaction to commit，也就是说，等待事务提交。
[kingly@rhel7]$ mysql > show slave status\\G Relay_Master_Log_File: master-bin.039806 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 927883985 Relay_Log_Space: 929519379 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 9Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: 157bcec0-fc94-11e9-a77d-48df372eb155 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Waiting for dependent transaction to commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 157bcec0-fc94-11e9-a77d-48df372eb155:10146560435-10186081960 Executed_Gtid_Set: 157bcec0-fc94-11e9-a77d-48df372eb155:1-10186081526,68eb64a3-fc94-11e9-b9c7-48df372eaef5:1-228645 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:检查活动会话情况
从活动会话来看，几条DELETE语句均处于System lock状态，出现System lock 原因通常是：1）大量小事，如update大表的一行数据；2)表上没有主键；3)类似InnoDB lock，slave同时修改数据sql_thread修改相同的数据；4)IO压力过大。而且其他SQL回放线程处于等待事务提交的状态。
[kingly@rhel7]$ mysql >show processlist; 2021-12-30 02:02:48 NULL NULL 114314 Waiting for master to send event NULL2021-12-30 02:02:48 NULL NULL 0 Waiting for dependent transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 System lock DELETE FROM offlinenews_48 WHERE id=457025642021-12-30 02:02:48 NULL NULL 48 System lock DELETE FROM offlinenews_75 WHERE id=474902222021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 System lock DELETE FROM offlinenews_84 WHERE id=460764622021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 System lock DELETE FROM offlinenews_95 WHERE id=467059152021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction 电脑 to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 48 Waiting for preceding transaction to commit NULL2021-12-30 02:02:48 NULL NULL 49 Waiting for an event from Coordinator NULL2021-12-30 02:02:48 NULL NULL 49 Waiting for an event from Coordinator NULL2021-12-30 02:02:48 NULL NULL 49 Waiting for an event from Coordinator NULL2021-12-30 02:02:48 NULL NULL 55 Waiting for an event from Coordinator NULL2021-12-30 02:02:48 NULL NULL 55 Waiting for an event from Coordinator NULL2021-12-30 02:02:48 NULL NULL 55 Waiting for an event from Coordinator NULL
因此需要进一步分析，目前，是否没有提交从库执行的事务，导致堵塞SQL线程回放动作和这些DELETE操作。2021-12-300 2点左右的binlog，发 现并