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

简短描述

由于 Amazon RDS for MySQL 使用异步复制，有时副本无法与主数据库实例同步，从而导致复制延迟。

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

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

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

MySQL 复制使用二进制日志转储、复制 I/O 接收器和复制 SQL 应用器线程。有关线程如何运作的详细信息，请参阅 MySQL 网站上的复制线程。如果复制出现延迟，请确定是 IO_THREAD 副本还是 SQL_THREAD 副本导致延迟。然后，您可以确定延迟的根本原因。

解决方法

找出滞后的复制线程

在主数据库实例上运行 SHOW MASTER STATUS 命令：

mysql> SHOW MASTER STATUS;

输出示例：

+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin.066552           |      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

在副本数据库实例上运行 SHOW SLAVE STATUS 命令：

mysql> SHOW SLAVE STATUS\G;

输出示例 1：

*************************** 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

**注意：**在前面的输出示例中，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 ：

*************************** 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

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

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

如果 SQL_THREAD 副本导致复制延迟，请按照以下故障排除步骤来解决您的问题。

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

在主数据库实例上长时间运行且在副本数据库实例上运行的时长相等的写入查询可能会增加 seconds_behind_master。例如，如果对主实例的更改需要 1 小时才能运行，则延迟为 1 小时。如果在副本上完成更改也需要 1 小时，则总延迟约为 2 小时。

为了最大限度地减少延迟，您可以监控主实例上的慢速查询日志。您还可以将长时间运行的语句简化为较小的语句或事务。

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

如果副本数据库实例类或存储配置低于主实例，副本可能会因为资源不足而受到限制。副本无法保持主实例上的更改次数。

要解决此问题，请确保副本的数据库实例类型等于或高于主数据库实例。为了使复制有效运行，每个读取副本需要与源数据库实例相同数量的计算和存储资源。有关详细信息，请参阅数据库实例类。

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

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

多线程（并行）复制可用于 MySQL 5.6 及更高版本。有关多线程复制的详细信息，请参阅 MySQL 网站上的二进制日志记录选项和变量。

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

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

当您在副本上启用自动备份时，将二进制日志同步到副本上的磁盘会产生开销。sync_binlog 参数的默认值设置为 1。如果将该值更改为 0，则还会关闭 MySQL 服务器将二进制日志同步到磁盘的功能。而操作系统偶尔会将二进制日志刷新到磁盘。

要减少每次提交时将二进制日志同步到磁盘所需的性能开销，请关闭二进制日志同步。但是，如果出现电力故障或操作系统崩溃，某些提交可能不会同步到二进制日志。异步可能会影响时间点恢复 (PITR) 功能。有关详细信息，请参阅 MySQL 网站上的 sync_binlog。

Binlog_format 设置为 ROW

在以下场景中，SQL 线程在复制时会执行全表扫描：

• 主数据库实例上的 binlog_format 设置为 ROW。
• 源表没有主键。

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

要临时解决此问题，请将搜索算法更改为 INDEX_SCAN,HASH_SCAN，以减少全表扫描的开销。要获得更永久的解决方案，最佳做法是向每个表添加显式主键。

有关 slave-rows-search-algorithms 参数的详细信息，请参阅 MySQL 网站上的 slave_rows_search_algorithms。

副本创建延迟

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

建立复制后，当 Amazon RDS 创建主数据库实例的备份时，会出现延迟。为了最大限度地减少这一延迟，请在调用副本创建之前创建手动备份。然后，数据库快照将成为增量备份。

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

**注意：**对于 Amazon RDS for MySQL 副本（由 Amazon EBS 支持的卷），副本延迟最初可能会增加，因为延迟加载会影响复制性能。

为了减少延迟加载对新读取副本的表的影响，您可以执行使用全表扫描的操作。例如，对特定表或数据库的读取副本运行 mysqldump，这样 Amazon RDS 就会优先考虑来自 Amazon Simple Storage Service (Amazon S3) 的所有备份表数据。

您也可以使用按需 InnoDB 缓存预热功能。InnoDB 缓存预热功能将缓冲池状态保存在磁盘上，存储在 InnoDB 数据目录中名为 ib_buffer_pool 的文件中。性能得到提升，因为 Amazon RDS 会在您创建读取副本之前转储主数据库实例缓冲池的当前状态。然后，您可以在创建读取副本后重新加载缓冲池。

相关信息

在 Amazon RDS 中使用 MySQL 复制

使用 MySQL 读取副本