MySQL主从延迟案例分析

同步延迟告警

【*****************】主从同步延迟过高,告警阈值300,当前为1973】,请及时处理!,产生时间2022-09-30 06:51:27

ERRO日志

2022-09-30T06:50:16.110234+08:00 32196300
[Note] Multi-threaded slave statistics for channel '': seconds elapsed = 121; events assigned = 454740993; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 19460575157300 waited (count) when Workers occupied = 152376 waited when Workers occupied = 1026653718600

日志解析

2022-09-30T06:50:16.110234+08:00 32196300
[Note] Multi-threaded slave statistics for channel '':
seconds elapsed = 121; 每隔121s输出
events assigned = 454740993; 总共有多少event被分配执行;
worker queues filled over overrun level = 0;多线程同步中,worker 的私有队列长度超长的次数
waited due a Worker queue full = 0; 因为worker的队列超长而产生等待的次数
waited due the total size = 0; 超过最大size的次数
waited at clock conflicts = 19460575157300 在事务之间存在依赖的情况下,该参数显示等待时间相当于冲突检测和解决方案的逻辑时间。
waited (count) when Workers occupied = 152376 因为workder被占用而出现等待的次数。(总计值)
waited when Workers occupied = 1026653718600 因为workder被占用而出现等待的总时间,总计值,单位是纳秒

解决方式

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,如果是固态盘,建议调整为1500
发表评论

相关文章