How can I troubleshoot high replica lag with Amazon RDS for MySQL?
Last updated: 2020-06-18
Amazon RDS for MySQL uses asynchronous replication and sometimes the replica isn't able to keep up with the primary DB instance. This can cause replication lag.
When using Amazon RDS for MySQL read replica with binary log file position based replication, you can monitor replication lag in Amazon CloudWatch by viewing the Amazon RDS ReplicaLag metric. The ReplicaLag metric reports the value of the Seconds_Behind_Master field of the SHOW SLAVE STATUS command.
The Seconds_Behind_Master shows the difference between the current timestamp on the replica DB instance and the original timestamp logged on the primary DB instance for the event that is being processed on the replica DB instance.
MySQL replication works with three threads: the Binlog Dump thread, the IO_THREAD, and the SQL_THREAD. For more information about how these threads function, see the MySQL Documentation for Replication implementation details. If there is a delay in replication, first identify whether the lag is caused by the replica IO_THREAD or the replica SQL_THREAD. Then, you can identify the root cause of the lag.
To identify which replication thread is lagging, see the following examples:
1. Run the MASTER STATUS command on the primary DB instance, and review the output:
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)
Note: In the example output, the master DB instance is writing the binary logs to the file mysql-bin.066552.
2. Run the SHOW SLAVE STATUS command on the replica DB instance and review the output:
Example output A:
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
In example output A, the Master_Log_File: mysql-bin.066548 indicates that the replica IO_THREAD is reading from the binary log file mysql-bin.066548. This is because the primary DB instance is writing the binary logs to the file mysql-bin.066552. This output shows that the replica IO_THREAD is behind by 4 binlogs. However, the Relay_Master_Log_File is mysql-bin.066548, which indicates that the replica SQL_THREAD is reading from same file as the IO_THREAD. This means that the replica SQL_THREAD is keeping up, but the replica IO_THREAD is lagging behind.
Example output B:
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
Example output B shows that the Master log File is mysql-bin-changelog.066552. This is also the file parameter from the master status, which implies that IO_THREAD is keeping up with the primary DB instance. In the replica output, the SQL thread is performing Relay_Master_Log_File: mysql-bin-changelog.066530. This means that SQL_THREAD is lagging behind by 12 binary logs.
Normally, IO_THREAD doesn't cause large replication delays, because the IO_THREAD only reads the binary logs from the master. However, network connectivity and network latency can affect the speed of the reads between the servers. The replica IO_THREAD could be slow due to high bandwidth usage.
If the replica SQL_THREAD is the source of replication delays, those delays could be due to the following reasons:
- Long-running queries on the primary DB instance
- Insufficient DB instance class size or storage
- Parallel queries executed on the primary DB instance
- Binary logs synced to the disk on the replica DB instance
- Binlog_format on the replica is set to ROW
- Replica creation lag
Long-running queries on the primary instance
Long-running queries on the primary DB instance that take an equal amount of time to run on the replica DB instance can increase the seconds_behind_master. For example, if you executed a change on the primary DB instance and it takes one hour to execute, by the time that change starts running on the replica, the lag is one hour. Because the change might also take one hour to complete on the replica, by the time the change is complete, the total lag is approximately two hours. This delay is expected, but you can minimize this lag by monitoring the slow query log on the master. You can also identify long-running statements to reduce lag. Then, break long-running statements into smaller statements or transactions. For more information, see Accessing the MySQL slow query and general logs.
Insufficient DB instance class size or storage
If the replica DB instance class or storage configuration is smaller than the primary, then the replica is throttled and unable to keep up with the changes made on the primary. Be sure that the DB instance type of the replica is the same or higher than the primary DB instance. For replication to operate effectively, each read replica requires the same amount of compute and storage resources as the source DB instance. For more information, see DB instance classes.
Parallel queries executed on the primary DB instance
If you execute queries in parallel on the primary, they are committed in a serial order on the replica. This is because the MySQL replication is single threaded (SQL_THREAD), by default. If a high volume of writes to the source DB instance occur in parallel, the writes to the read replica are serialized using a single SQL_THREAD. This can cause a lag between the source DB instance and read replica.
Multi-threaded (parallel) replication is available for MySQL 5.6, MySQL 5.7, and higher versions. For more information about multi-threaded replication, see the MySQL Documentation for Binary logging options and variables.
Multi-threaded replication can cause gaps in replication. For example, multi-threaded replication isn't a best practice when skipping the replication errors, because it's difficult to identify which transactions are skipped. This can lead to gaps in data consistency between the primary and replica DB instances.
Binary logs synced to the disk on the replica DB instance
If you enable automatic backups on the replica, then additional effort might be required to sync the binary logs to the disk on the replica. The default value of the parameter sync_binlog is set to 1. If you change this value to 0, the synchronization of the binary log to the disk by the MySQL server is disabled. Instead of logging to the disk, the operating system (OS) occasionally flushes the binary logs to disk.
If you disable the binary log synchronization, this can reduce the performance overhead required to sync the binary logs to disk on every commit. However, if there is a power failure or the OS crashes, some of the commits might not be synchronized to the binary logs. This can affect point in time restore (PITR) capabilities. For more information, see the MySQL Documentation for sync_binlog.
Binlog_format on the replica is set to ROW
If you set the binlog_format on the replica to ROW, and the table on which the updates are made is missing the primary key, then, by default, slave-rows-search-algorithms=TABLE_SCAN,INDEX_SCAN executes on every row modified on the primary. This parameter also performs a full table scan on the replica. In this case, a short-term solution is to change the search algorithm to INDEX_SCAN,HASH_SCAN to reduce the overhead of full table scan. For a permanent solution, add an explicit primary key to each table.
For more information about the slave-rows-search-algorithms parameter, see the MySQL Documentation for slave-rows-search-algorithms.
Replica creation lag
Amazon RDS creates a read replica of a MySQL master by taking a DB snapshot of the master. Then, Amazon RDS restores the snapshot to create a new DB instance (replica) and establishes replication between the two.
New read replicas take time to be created. When the replication is established, there is a lag for the duration of the time that it takes to create a backup of the primary. To minimize this lag, create a manual backup before calling for the replica creation. Then, the snapshot taken by the replica creation process is an incremental backup, which is faster.
When a read replica is restored from a snapshot, the replica won't wait for all the data to be transferred from Amazon Simple Storage Service (Amazon S3) to the Amazon Elastic Block Store (Amazon EBS) volume that's associated with the replica DB instance. The replica DB instance is available to perform the DB operations, and the new volume created from existing Amazon EBS snapshot loads in the background (lazy loading).
For Amazon RDS for MySQL replicas (EBS-based volumes), the replica lag can increase initially, because the loading effect can influence the replication performance. For more information, see Initializing Amazon EBS volumes.
Consider enabling the InnoDB cache warming feature, which can provide performance gains by saving the current state of the buffer pool of the master DB instance, and then reloading the buffer pool on restored read replica. For additional information on InnoDB Cache Warming, see MySQL on Amazon RDS.