MySQL relay-log-space-limit vs. your page cache

Leif (who works for Spinn3r) published a great post on the mySQL relay-log-space-limit variable and using the page cache efficiently:

When the SQL thread is not able to keep up, logs will accumulate. You can set the location of your relay logs with the relay-log option; in the default configuration (and many production configurations), the relay logs are stored on the same disk as the rest of MySQL’s data. If the total size of the relay logs ever exceeds relay-log-space-limit, the IO thread will pause until the SQL thread has moved on to its next file and deleted its previous one.

Unfortunately, relay-log-space-limit defaults to zero (unlimited). This means that when the SQL thread can’t keep up, the slave’s relay logs will continue to accumulate indefinitely (until its disk fills up). When the total size of those logs approaches the amount of vfs page cache available, the SQL thread will begin to read them from disk (instead of getting cache hits), which will exacerbate the IO shortage that caused the problem in the first place.

%d bloggers like this: