How can I troubleshoot the "Waiting for the slave SQL thread to free enough relay log space" error in Amazon Aurora MySQL?

Short description

When Aurora MySQL is a replica of binary log replication, it runs the I/O thread and the SQL thread in the same way as MySQL. The I/O thread reads binary logs from the primary, and then saves them as relay logs in the replica DB instance. The SQL thread processes the events in the relay logs, and then deletes the relay logs when the events in the relay logs are processed.

If the SQL thread doesn't process events fast enough to catch up with the speed that the relay logs are being generated at, the amount of relay logs increase.

When the global variable relay_log_space_limit is set to larger than 0 and the total size of all relay logs reach the limit, new relay logs aren't saved. Until the relay log space becomes available again, the output of SHOW SLAVE STATUS shows the message "Waiting for the slave SQL thread to free enough relay log space" in the Slave_IO_State field.

In Aurora MySQL, the relay_log_space_limit is set to 1000000000 (953.6 MiB) and can't be modified. This prevents the cluster volume from becoming unnecessary large. When the total size of all relay logs reaches 1000000000 bytes (953.6 MiB), the I/O thread stops saving relay logs. It waits for the SQL thread to process events and delete the existing logs. Slave_IO_State then shows the message "Waiting for the slave SQL thread to free enough relay log space". If the SQL thread isn't stopped, the relay logs are eventually deleted, and the I/O thread resumes saving new relay logs.

This also means that replication lag exists because the SQL isn't fast enough to catch up with the generation of relay logs by the I/O thread. Even if relay_log_space_limit is modified to a larger value, the relay logs still further accumulate, and the issue isn't resolved until the SQL thread catches up.

You can view the current relay log space, the status of the I/O thread, and the status of the SQL thread in the output of the SHOW SLAVE STATUS command.

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: mysql-bin-changelog.237029
Read_Master_Log_Pos: 55356151
Relay_Master_Log_File: mysql-bin-changelog.237023
Exec_Master_Log_Pos: 120
Relay_Log_Space: 1000002403

Master_Log_File and Read_Master_Log_Pos show the binary log file name and the position where the I/O thread completed reading and saving. Relay_Master_Log_File and Exec_Master_Log_Pos show the binary log file name and the position where the SQL thread is processing. Although what the SQL thread actually reads are relay logs, the corresponding binary log file name in the primary DB instance and the position is displayed.

When Master_Log_File is different from Relay_Master_Log_File, the SQL thread isn't fast enough. If Master_Log_File and Relay_Master_Log_File are the same, the I/O thread might be contributing to the lag.

The following factors can cause insufficient performance of the SQL thread:

• Long-running queries on the primary DB instance
• Insufficient DB instance class size or storage
• Parallel queries performed on the primary DB instance
• Binary logs synced to the disk on the replica DB instance
• Binlog_format on the replica DB instance is set to ROW

For more information on resolving these issues, see How can I troubleshoot high replica lag with Amazon RDS for MySQL

Additionally, the following factors can also impact the performance of the SQL thread:

• A very large Transaction History List Length (HLL) on the replica DB instance
• Less-than-efficient I/O operations on the replica DB instance
• Tables with lots of secondary indexes on the replica DB instance

Resolution

As long as there are writes happening in your replica, you don't need to worry about relay log space. You can monitor this using the Write Throughput metric in Enhanced Monitoring.

Instead, focus on troubleshooting the replica's performance. For more details, see How can I troubleshoot high replica lag with Amazon RDS for MySQL and Why did my Amazon Aurora read replica fall behind and restart?

Related information

MySQL documentation for Replica server options and variables