mysql大事务导致主从延时处理

show slave status\G

Slave_SQL_Running_State: Waiting for dependent transaction to commit

或

Slave_SQL_Running_State: Waiting for Slave Worker queue

或

Slave_SQL_Running_State: Waiting for Slave Workers to free pending events

1、执行sql检查是否有长事务没有提交？

select * from information_schema.INNODB_TRX i, information_schema.processlist p where i.trx_mysql_thread_id=p.id and p.time > 60;

2、查看是否开启gtid

show variables like '%gtid%';

3、stop slave;如果停止不了，可以kill大事务线程再执行stop slave

4、如果开启了gtid，那么执行：

stop slave;
set global slave_pending_jobs_size_max=16777216*32;
set global slave_parallel_workers=16;
set global max_allowed_packet=67108864*2;
start slave;

或跳过这个大事务

stop slave;
SET @@SESSION.GTID_NEXT= '7a240e0a-b937-11ea-990c-00e0ed87fc72:848358386';
begin;commit;
set session gtid_next='AUTOMATIC';
start slave;
show slave status\G

5、如果没有开启gtid，可以执行跳过这个大事务：

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
show slave status\G
 

6、在从节点执行show open tables where in_use>0;查看是否有表持续处于使用状态？然后查看这表是否有主键？如果没有很有可能是因为主节点有大量的写入、更新或删除操作，导致从节点在回放的时候产生全表扫描从而阻塞写，并形成大量的主从延时。再查看select * from performance_schema.metadata_locks查看表对应的库表是否长期占用SHARED_WRITE锁？

处理方法：对这张表创建主键，如果创建主键还不能解决，那建议执行下一步

7、如果是5.7及以上可以跳过这对应大事务操作的表，这个是有风险的，可能会造成数据不一致，等主从延时恢复后，需要比较一下主从数据是否一致

stop slave;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('ddjct0.ods_zsj_terminal_scan');
start slave;

执行上面步骤后需马上去除过滤操作，尽可能地减少主从不一致的情况发生

stop slave;

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();

start slave;