// shall I write some keywords here to boost search engine ranking?

Saturday, August 30, 2008

MySQL: Limit Relay Log Size To Prevent Hard Disk Full

In MySQL replication, you may notice the slave host generate some file with filename like 'hostname-relay-bin.000001' in data directory.

These files are the relay log generated by MySQL slave to store the replication data from master host before update into slave DB.

So when replication was lag behind master, or replication was stopped for long time and resume, large relay log will be generated by MySQL slave.

And this may cause your hard disk full, and result in MySQL stop working.

To prevent this, you can set the 'relay-log-space-limit' in your my.ini file (i.e. 5G):

relay-log-space-limit=5G
Regarding the appropriate size, here is the suggestion from MySQL documentation:
You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore --relay-log-space-limit.
In case this long description make you blur, just set the 'relay-log-space-limit' to 3 times the value of 'max-binlog-size' or 'max-relay-log-size'.

1 comment:

Anonymous said...

It is often better to not comment at all if you can't comment clearly in the language you are choosing to use.