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

Short description

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

In Aurora MySQL version 2, the relay_log_space_limit value is set to 1000000000 (953.6 MiB) and can't be modified.

In Aurora MySQL version 3, the parameter name is changed to relay-log-space-limit, and can be modified. Set this parameter to 0, and then the relay log can be stored with no limit.

Resolution

If there are successful writes in your replica, then you have enough relay log space. You can monitor this activity with the Write Throughput metric in Enhanced Monitoring.

To troubleshoot the replica's performance, see How can I troubleshoot high replica lag with Amazon Relational Database Service (Amazon RDS) for MySQL and Why did my Amazon Aurora read replica fall behind and restart?

The following conditions can lead to 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 set to ROW.

The following conditions can also affect 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.

Related information

relay-log-space-limit on the MySQL website