我如何解决使用 Amazon RDS for MySQL 时出现的副本延迟高问题？

2 分钟阅读

我想找到使用 Amazon Relational Database Service（Amazon RDS）for MySQL 时副本延迟的原因。

简短描述

Amazon RDS for MySQL 使用异步复制。这意味着该副本有时无法与主数据库实例保持同步。因此，可能会出现复制延迟。

要监控复制延迟，使用具有基于二进制日志文件位置的复制的 Amazon RDS for MySQL 只读副本。

在 Amazon CloudWatch 中，检查 Amazon RDS 的 ReplicaLag 指标。ReplicaLag 指标报告 SHOW SLAVE STATUS 命令的 Seconds_Behind_Master 字段的值。

Seconds_Behind_Master 字段显示副本数据库实例上当前时间戳之间的差异。另外还显示在主数据库实例上记录的原始时间戳，用于在副本数据库实例上处理事件。

MySQL 复制使用三个线程：二进制日志转储线程、IO_THREAD 和 SQL_THREAD。有关这些线程具体工作方式的更多信息，请参阅 MySQL 文档中的复制线程部分。如果复制出现延迟，应确定是副本 IO_THREAD 还是副本 SQL_THREAD 导致延迟。然后，您可以确定延迟的根本原因。

解决方法

要确定哪个复制线程延迟，请参见以下示例：

1. 在主数据库实例上运行 SHOW MASTER STATUS 命令，然后查看输出。输出类似于：

mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.066552|      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意： 在此示例输出中，源数据库实例或主数据库实例将二进制日志写入文件 mysql-bin.066552。

2. 在副本数据库实例上运行 SHOW SLAVE STATUS 命令，然后查看输出。输出类似于以下示例：

示例 1：

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在示例 1 中，Master_Log_File: mysql-bin.066548 表示副本 IO_THREAD 从二进制日志文件 mysql-bin.066548 读取。主数据库实例将二进制日志写入 mysql-bin.066552 文件。此输出显示副本 IO_THREAD 滞后了四个二进制日志。但是，Relay_Master_Log_File 是 mysql-bin.066548，这表明副本 SQL_THREAD 与 IO_THREAD 从同一个文件读取。这意味着副本 SQL_THREAD 一直在运行，但副本 IO_THREAD 出现延迟。

示例 2：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

示例 2 显示主实例的日志文件是 mysql-bin-changelog.066552。输出显示 IO_THREAD 与主数据库实例保持同步。在副本输出中，SQL 线程执行 Relay_Master_Log_File: mysql-bin-changelog.066530。因此，SQL_THREAD 滞后 22 个二进制日志。

通常，IO_THREAD 不会导致很大程度的复制延迟，因为 IO_THREAD 仅从主实例或源实例读取二进制日志。但是，网络连接和网络延迟会影响在服务器之间的读取速度。IO_THREAD 副本可能会由于带宽使用率高而变慢。

如果副本 SQL_THREAD 是复制延迟的根源，以下情况可能会导致延迟：

查询在主数据库实例上长时间运行
数据库实例类大小或存储不足
并行查询在主数据库实例上运行
二进制日志同步到副本数据库实例上的磁盘
副本上的 Binlog_format 设置为 ROW
副本创建延迟

查询在主实例上长时间运行

在主数据库实例上长时间运行且在副本数据库实例上运行的时长相等的查询可能会增加 seconds_behind_master。例如，如果您在主实例上启动更改，更改过程需要运行一个小时，延迟为一小时。如果更改在副本上也需要一小时完成，完成时的总延迟约为两个小时。这是预期的延迟时间，您可以监控主实例上速度较慢的查询日志，来最大限度地减少此延迟。您还可以找出长时间运行的语句以减少延迟。然后，将长时间运行的语句分解为较小的语句或事务。

数据库实例类大小或存储不足

如果副本数据库实例类或存储配置低于主实例，副本可能会因为资源不足而受到限制。这是因为副本无法与主实例上所做的更改同步。确保副本的数据库实例类型与主数据库实例相同或更高。为了使复制有效运行，每个读取副本需要与源数据库实例相同数量的计算和存储资源。有关更多信息，请参阅数据库实例类。

并行查询在主数据库实例上运行

如果您在主实例上并行运行查询，它们会按顺序在副本上提交。这是因为默认情况下，MySQL 复制是单线程的（SQL_THREAD）。如果对源数据库实例的大量写入是并行进行的，对读取副本的写入会序列化。对读取副本的写入使用单个 SQL_THREAD 进行序列化。这可能会导致源数据库实例和读取副本之间出现延迟。

多线程（并行）复制可用于 MySQL 5.6、MySQL 5.7 及更高版本。有关多线程复制的更多信息，请参阅二进制日志记录选项和变量的 MySQL 文档部分。

多线程复制可能会导致复制中出现差异。例如，跳过复制错误时，多线程复制并不是最佳做法，因为很难确定您跳过了哪些事务。这可能会导致主数据库实例和副本数据库实例之间的数据一致性出现差距。

二进制日志同步到副本数据库实例上的磁盘

在副本上启用自动备份可能会导致需要额外操作才能将二进制日志同步到副本上的磁盘。参数 sync_binlog 的默认值设置为 1。如果将此值更改为 0，同时还会关闭 MySQL 服务器将二进制日志同步到磁盘。操作系统（OS）有时会将二进制日志刷新到磁盘，而不是记录到磁盘。

关闭二进制日志同步可以减少每次提交时将二进制日志同步到磁盘所需的性能开销。但是，如果出现电力故障或操作系统崩溃，某些提交可能不会同步到二进制日志。这一异步情况可能会影响时间点恢复（PITR）功能。有关更多信息，请参阅 sync_binlog 的 MySQL 文档部分。

Binlog_format 设置为 ROW

当存在以下两个因素时，SQL 线程会对副本执行全表扫描：

主数据库实例上的 binlog_format 设置为 ROW。
源表缺少主键。

发生此情况，是因为参数 slave_rows_search_algorithms 的默认值为 TABLE_SCAN,INDEX_SCAN。

要在短期内解决此问题，将搜索算法更改为 INDEX_SCAN,HASH_SCAN，以减少全表扫描的开销。要从长远上解决，最佳做法是向每个表添加一个显式主键。

有关 slave-rows-search-algorithms 参数的更多信息，请参阅 slave-rows-search-algorithms 的 MySQL 文档部分。

副本创建延迟

Amazon RDS 通过拍摄数据库快照来创建 MySQL 主实例的读取副本。然后，Amazon RDS 会恢复快照来创建新的数据库实例（副本），并在两者之间建立复制关系。

Amazon RDS 需要时间来创建新的读取副本。建立复制后，创建主数据库实例备份所用的时间会有延迟。为了最大限度地减少这一延迟，应在调用副本创建之前创建手动备份。然后，数据库快照将成为增量备份。

当您从快照恢复读取副本时，该副本不会等待所有数据从源传输。副本数据库实例可用于执行数据库操作。现有的 Amazon Elastic Block Store（Amazon EBS）快照加载会在后台创建一个新卷。

注意： 对于 Amazon RDS for MySQL 副本（基于 EBS 的卷），副本延迟最初可能会增加。这是因为延迟加载效果会影响复制性能。

为了帮助缓解延迟加载对新创建的读取副本的表的影响，您可以执行包含全表扫描的操作。例如，对特定表或数据库的读取副本运行 mysqldump 操作。这样可以让 Amazon RDS 从 Amazon Simple Storage Service（Amazon S3）下载所有备份的表数据，并确定其优先顺序。

另外，可以考虑使用“按需”提供的 InnoDB 缓存预热功能。InnoDB 缓存预热功能将缓冲池状态保存在磁盘上，存储在 InnoDB 数据目录中名为 ib_buffer_pool 的文件中。这可以在创建读取副本之前转储主数据库实例缓冲池的当前状态，从而提高性能。然后，在您创建读取副本后重新加载缓冲池。