MySQL备库复制延迟的原因及解决办法.docx

lianR

116

4页

4次

2024-03-08

免费下载

背景

今天有同事问我主从复制延迟会影响高可用切换的 RTO 怎么办，这个不需要做实验，我可以直

接回答，所以有了以下赶鸭子的文章，都是一线运维经验之谈，建议四连：点赞、收藏、转发、

在看。

一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io

thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。

所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。

复制延迟的原因及解决办法

一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io

thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。

所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。

1. 主从服务器的配置或者压力不一，从实例的 IO 能力太弱。

这个需要技术管理手段去规避，务必要保证主备库无论从硬件配置、操作系统配置、mysqld 配

置都要一致，不会出现主库比备库性能好很多的情况。不建议备库开读写分离，有财力的公司

读写分离的读可以放在另外的只读从库上。

2. 主库的 TPS 太高

在 MySQL5.5 及之前版本，从库是不支持并行复制功能的，主库上会有并发事务，利用多核

CPU ，多个连接同时写入数据，但从库 sql thread 线程应用 relay log 时不支持并行回放，只能

单线程回放，那么主库只要并发高的时候，从库永远是复制延迟的。

MySQL5.6 版本优化了这个问题，从库只支持基于 database 的并行复制，也就是如果主库上多

个并发事务必须在不同 database 上跑，在从库上才能并行复制，这就非常鸡肋，大多数业务的

并发 SQL 都在同一个业务库的，所以这并不能并行复制，这个问题是 MySQL5.6 复制延迟的常

见原因，请务必升级到 MySQL5.7 以支持基于逻辑时钟的并行复制。

MySQL5.7 版本的基于逻辑时钟的并行复制，是基于组提交的原理来实现的，首先在主库满足

能组提交的事务，都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明

事务之间没有任何冲突（否则就不可能提交）。但他没有完全模拟主库上的并发执行，所以在

从库上的回放力度依然没有主库高，但比 MySQL5.6 鸡肋的基于 database 的并行复制强多了，

这个时候已经能消灭大多数复制延迟了。MySQL5.7.22 开始官方推出了基于 writeset 的并行复

制，他能把并行复制推到了全新的高度，怎么说呢？就是主库原本不是并行的 SQL，只要不冲

突在从库上都能并行，极限情况下，从库回放速度甚至比主库还高！这基本上是消灭了这个原

因下的复制延迟。

并行复制是有相关参数推荐的，请视情况调整，如有疑问可以与我联系。

skip_slave_start =0

relay_log =/database/mysql/log/relaylog/330

7/relay-bin

relay_log_recovery =1

master_info_repository =table

relay_log_info_repository =table

slave_parallel_type =logical_clock

slave_parallel_workers =4

loose-rpl_semi_sync_master_enabled =1

loose-rpl_semi_sync_slave_enabled =1

loose-rpl_semi_sync_master_timeout =1000

slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'

binlog_group_commit_sync_delay =500

binlog_group_commit_sync_no_delay_count = 13

binlog_transaction_dependency_tracking = WRITESET

transaction_write_set_extraction = XXHASH64

3. 备份导致写入阻塞

我们有一部分的 MySQL 备份计划是采用备库上每天凌晨执行物理全备的方法做备份。物理全备

采用的是开源工具 xtrabackup ，实际上无论使用 xtrabackup 或者 mysqldump，备份工具都需要

执行 FLUSH TABLES WITH READ LOCK (FTWRL)命令，这个命令需要获取一些锁。

1. 如果此时在备库上有大查询，就堵塞 FTWRL 操作，备库 show processlist “显示 Waiting for

table flush” ，这个 FTWRL 被阻塞会从而导致这张表上的事务无法提交，也就是影响扩大了，影响到

表的写入了。这个一般不常见，因为如果这个从库定位为备库一般就规范使用会不作为读写分离的

只读库使用，上面不应该有大查询。

2. 如果库里 MyISAM 表比较多，为了保证备份数据的一致性，FTWRL 操作持续时间会较长，直到所

有 MyISAM 表拷贝完成，期间任何写入都会被阻塞，卡在 "Waiting for global read lock"，解决这个

问题的办法就是永远不要使用 MyISAM 存储引擎的表，请将他们修改为 InnoDB 存储引擎的表。

#设置以下参数保证没有人能使用 MyISAM 表，保平安。

[mysqld]

...

default_storage_engine=innodb

# MySQL5.7 以后官方

默认值

default_tmp_storage_engine=innodb

# MySQL5.7 以后官方

默认值

disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,M

ERGE,NDB,MyISAM

因为 MySQL5.7 中 MySQL 元数据还有 MyISAM 表，所以以上设置会影响 MySQL5.7 的小版本升

级，需要一些小技巧解决，MySQL8.0 的话就直接干吧。

背景

今天有同事问我主从复制延迟会影响高可用切换的 RTO 怎么办，这个不需要做实验，我可以直

接回答，所以有了以下赶鸭子的文章，都是一线运维经验之谈，建议四连：点赞、收藏、转发、

在看。

一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io

thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。

所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。

复制延迟的原因及解决办法

一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io

thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。

所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。

1. 主从服务器的配置或者压力不一，从实例的 IO 能力太弱。

这个需要技术管理手段去规避，务必要保证主备库无论从硬件配置、操作系统配置、mysqld 配

置都要一致，不会出现主库比备库性能好很多的情况。不建议备库开读写分离，有财力的公司

读写分离的读可以放在另外的只读从库上。

2. 主库的 TPS 太高

在 MySQL5.5 及之前版本，从库是不支持并行复制功能的，主库上会有并发事务，利用多核

CPU ，多个连接同时写入数据，但从库 sql thread 线程应用 relay log 时不支持并行回放，只能

单线程回放，那么主库只要并发高的时候，从库永远是复制延迟的。

MySQL5.6 版本优化了这个问题，从库只支持基于 database 的并行复制，也就是如果主库上多

个并发事务必须在不同 database 上跑，在从库上才能并行复制，这就非常鸡肋，大多数业务的

并发 SQL 都在同一个业务库的，所以这并不能并行复制，这个问题是 MySQL5.6 复制延迟的常

见原因，请务必升级到 MySQL5.7 以支持基于逻辑时钟的并行复制。

MySQL5.7 版本的基于逻辑时钟的并行复制，是基于组提交的原理来实现的，首先在主库满足

能组提交的事务，都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明

事务之间没有任何冲突（否则就不可能提交）。但他没有完全模拟主库上的并发执行，所以在

从库上的回放力度依然没有主库高，但比 MySQL5.6 鸡肋的基于 database 的并行复制强多了，

这个时候已经能消灭大多数复制延迟了。MySQL5.7.22 开始官方推出了基于 writeset 的并行复

制，他能把并行复制推到了全新的高度，怎么说呢？就是主库原本不是并行的 SQL，只要不冲

突在从库上都能并行，极限情况下，从库回放速度甚至比主库还高！这基本上是消灭了这个原

因下的复制延迟。

并行复制是有相关参数推荐的，请视情况调整，如有疑问可以与我联系。

skip_slave_start =0

relay_log =/database/mysql/log/relaylog/330

7/relay-bin

relay_log_recovery =1

master_info_repository =table

relay_log_info_repository =table

slave_parallel_type =logical_clock

slave_parallel_workers =4

loose-rpl_semi_sync_master_enabled =1

loose-rpl_semi_sync_slave_enabled =1

loose-rpl_semi_sync_master_timeout =1000

slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'

binlog_group_commit_sync_delay =500

binlog_group_commit_sync_no_delay_count = 13

binlog_transaction_dependency_tracking = WRITESET

transaction_write_set_extraction = XXHASH64

3. 备份导致写入阻塞

我们有一部分的 MySQL 备份计划是采用备库上每天凌晨执行物理全备的方法做备份。物理全备

采用的是开源工具 xtrabackup ，实际上无论使用 xtrabackup 或者 mysqldump，备份工具都需要

执行 FLUSH TABLES WITH READ LOCK (FTWRL)命令，这个命令需要获取一些锁。

1. 如果此时在备库上有大查询，就堵塞 FTWRL 操作，备库 show processlist “显示 Waiting for

table flush” ，这个 FTWRL 被阻塞会从而导致这张表上的事务无法提交，也就是影响扩大了，影响到

表的写入了。这个一般不常见，因为如果这个从库定位为备库一般就规范使用会不作为读写分离的

只读库使用，上面不应该有大查询。

2. 如果库里 MyISAM 表比较多，为了保证备份数据的一致性，FTWRL 操作持续时间会较长，直到所

有 MyISAM 表拷贝完成，期间任何写入都会被阻塞，卡在 "Waiting for global read lock"，解决这个

问题的办法就是永远不要使用 MyISAM 存储引擎的表，请将他们修改为 InnoDB 存储引擎的表。

#设置以下参数保证没有人能使用 MyISAM 表，保平安。

[mysqld]

...

default_storage_engine=innodb

# MySQL5.7 以后官方

默认值

default_tmp_storage_engine=innodb

# MySQL5.7 以后官方

默认值

disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,M

ERGE,NDB,MyISAM

因为 MySQL5.7 中 MySQL 元数据还有 MyISAM 表，所以以上设置会影响 MySQL5.7 的小版本升

级，需要一些小技巧解决，MySQL8.0 的话就直接干吧。

of 4

免费下载

msyql mysql数据库

关注

评论