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、如果是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;