relay-log-space-limit : IO_Thread and SQL_Thread

relay-log-space-limit

We don’t often see this option configured (default: unlimited) but it might be a good idea to set it. What it does is limit the amount of disk space the combined relay logs are allowed to take up.

A slave’s IO_Thread reads from the master and puts the events into the relay log;
the slave’s SQL_Thread reads from the relay log and executes the query.

If/when replication “breaks”, unless it’s connection related it tends to be during execution of a query. In that case the IO_Thread will keep running (receiving master events and storing in the relay log). Beyond some point, that doesn’t make sense.

The reason for having two separate replication threads (introduced in MySQL 4.0) is that long-running queries don’t delay receiving more data. That’s good. But receiving data is generally pretty fast, so as long as that basic issue is handled, it’s not necessary (for performance) to have the IO_Thread run ahead that far.
So you can set something like relay-log-space-limit=256M. This prevents slave disk space from getting gobbled up in some replication failure scenarios. The data will still be available in the logs on the master (provided of course the log expiration there isn’t too short – replication monitoring is still important!).

Conclusion: the relay log as a cache. Don’t leave it at “Unlimited”, that’s inefficient (and potentially problematic) use of resources. If you do run out of diskspace, the relay log can get corrupted – then you have to reposition, which will re-read the data from the master anyway.