How to choose a good value for MySQL relay-log-space-limit?

I just read this blog post about relay-log-space-limit on MySQL. I am convinced that the 'unlimited' default is indeed not very good. But how do I choose a good value for it?

We are using master-master circular replication if that would matter.

That would depend on four(4) factors

• How much diskspace you are willing to have for relay logs

• If you have expire_logs_days set to a nonzero value on the Master

• How quickly you react to any monitoring of Relay_Log_Space from SHOW SLAVE STATUS\G (See my Feb 09, 2012 post Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR? on relay_log_space_limit)

• How quickly you react to broken replication

• If Slave_IO_Running is No

• If Slave_SQL_Running is No

With Relay_Log_Space reaching relay_log_space_limit no log rotation occurs until every SQL statement in the oldest relay is processed.

What if the SQL thread breaks due to some SQL error? You need to resolve the error with the number of days set by expire_logs_days. If this is missed, when replication is fixed, the SQL that was downloaded to relay logs but rotated away from the Master will not make it the Slave. If not fixed, relay logs just grow because the IO thread would still be up and collect new SQL from the Master.

What if the IO thread breaks due to some communication error? This could be more critical if the last relay log gets logically corrupted (a possibility). Once Communication between Master and Slave is known to be OK, you must get

• Relay_Master_Log_File (like mysql-bin.000128)

• Exec_Master_Log_Pos (like 82323232)

from SHOW SLAVE STATUS\G. Use these commands to cleanup

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000128',
MASTER_LOG_POS=82323232;
START SLAVE;

This will erase all relay logs and start over with a fresh set.

EXAMPLE

I have a DB Hosting client that generates a full 1GB binary log every 18 minutes (Yes, 80GB a day). Since client has expire_logs_days = 3, client has 240 GB a day.

Picture the above scenarios and trying to deal with them without have the right alerts in place or enough diskspace of relay logs. This gets more complicated since with Circular Replication because binary logs and relay logs share the same space by default. Perhaps stored binary logs on a separate disk can help mitigate some diskspace issues.

RECOMMENDATION

If you can get to fix replication errors quickly or if diskspace is at a premium, you could set relay_log_space_limit low (something like 10G)

You may want to determine how much space you crank for binary logs in one hour and set relay_log_space_limit to something like 12 hours worth.

StackExchange Database Administrators Q#35118, answer score: 6