Mysql主从延时-Multi-threaded slave statistics for channel【转】

今天Mysql从库日志抛以下note信息，起初是主库上这几天有很大的并发事务，导致从库延时非常大，但是从库并没有什么压力，我们来分析下这个问题。

2021-01-10T16:08:39.947611+08:00 85441 [Note] Multi-threaded slave statistics for channel '';seconds elapsed = 120;events assigned = 4005889;worker queues filled over
overrun level = 0;waited due a Worker queue full = 0;waited due the total size = 0;waited at clock conflicts = 6918018179200 waited (count) when Workers occupied = 0
waited when Workers occupied = 0

1、日志分析如下：

Multi-threaded slave statistics for channel ”:
seconds elapsed = 120; 每隔120s输出
eventsassigned = 4005889; 总共有多少个event被分配执行
queues filled over overrun level = 0; 多线程同步中，worker 的私有队列长度超长的次数
waited due aWorker queue full = 0; 因为worker的队列超长而产生等待的次数
waited due the total size = 0; 超过最大size的次数
waited at clock conflicts= 6918018179200;因为逻辑时间产生冲突的等待时间，单位是纳秒
waited (count) when Workers occupied = 0 因为workder被占用而出现等待的次数。（总计值）
waited when Workers occupied = 0 因为workder被占用而出现等待的总时间，总计值，单位是纳秒

 

2、建议修改方案

1、调大slave_pending_jobs_size_max
如果slave_pending_jobs_size_max的大小小于当前需要执行事件所需的内存大小
2、临时调整sync_binlog大小
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
3、slave_parallel_workers
调整并行复制的参数，建议修改为cpu的核数
4、innodb_io_capacity
sas硬盘建议200，如果是nvme固态盘，建议调整为1500

 

转自

Mysql主从延时-Multi-threaded slave statistics for channel_multi-threaded slave statistics for channel '': se-CSDN博客
https://blog.csdn.net/sendqmail/article/details/122413407