MySQL 优化从库延迟的一些思路_mysql从库延迟优化-优快云博客

作者：孙绪宗，新浪微博 DBA 团队工程师，主要负责 MySQL、PostgreSQL 等关系型数据库运维。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1000 字，预计阅读需要 3 分钟。

引言

在数据库运维过程中，无论是迁移扩容还是生产投量，都必不可少的会遇到从库迁移追不上的问题。这些问题令人头疼。

以下列举几个我个人遇到过的原因：

buffer_pool 设置过大，导致 MySQL 使用 SWAP
备份导致 SQL_THREAD 回放等待 MDL
大事务
慢查询导致从库性能低下
并行复制导致从库延迟监控一直为 1s
网络问题

具体情况具体分析，这里不赘述。

如果你在常规排查之后，依然无法解决。接下来，我将根据自身的一些经验，提供一些参数调整思路，供大家参考。

思路一：sync 相关

我们在追延迟的情况，可以调整一下参数，增加日志落盘效率。后续上线从库可以再设置回来。

sync_binlog=0
sync_master_info=10000 #default
sync_relay_log=10000 #default
sync_relay_log_info=10000 #default

思路二：buffer 和并发等相关

可以考虑增加一下 buffer_pool，SQL_THREAD 回放执行的更快。

如果内存空间不足的话，可以适当调整 change buffer 的比例（前提是无读，正常情况下延迟库均为无业务连接）。

innodb_buffer_pool_size=24G #24*1024*1024*1024
innodb_change_buffer_max_size=50
innodb_thread_concurrency=0
innodb_adaptive_hash_index=0

增大 innodb_buffer_pool_size 风险点：

内存过度分配导致 SWAP 触发或 OOM，需预留足够内存给系统和 MySQL 其他组件，建议缓冲池不超过物理内存的 70%；
调整需分步进行，结合系统内存监控，避免一次性设置过大。

思路三：slave 相关

考虑开启并行复制

开启并行复制，8.0+ 版本考虑用 writeset。复制线程可以多观察一下，如果没够的话，可以考虑增加。但不建议超过 CPU 核心数或者 innodb_thread_concurrency 参数值。

slave_preserve_commit_order 会加一层锁，追延迟的时候建议关闭，后续上线从库可以再打开。

slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_preserve_commit_order=OFF

个人不建议修改以下参数，性能虽然会有所增长，但同时会导致主库 commit 等待。当然部分非实时类业务可以调整。

binlog 的组提交的两个有关参数：

binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync 刷盘；
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

考虑关闭 log_slave_updates

log_slave_updates 这个需要重启生效。但是有 gdb 经验的小伙伴可以 gdb 修改，不需要重启，只需要重启 slave 复制即可生效。无 gdb 经验可能会导致 crash 不建议。

同时注意需要了解架构，没有 binlog 备份或者级联库，且无业务连接，建议可以关闭。

思路四

MGR 架构可以考虑先改为异步复制，关闭 slave_preserve_commit_order，待延迟追完后再加入到集群。

思路五

其他性能参数按照模板理论上不会有太大问题，这套操作下来延迟大概率会有所下降，降为 0 只是时间问题。

附录

并行复制积压日志解析:

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
--------------------------------
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被占用而出现等待的总时间，总计值，单位是纳秒