Thursday, September 29, 2011

MySQL relay log purging

From time to time I get interesting MySQL questions from people who attended the trainings I conducted. Yesterday I got this message,

"We have setup master-slave mysql. Now it seems that the slave is running out of disk space ( yesterday we got 100%), the replication died. And I found that file slave-relay.000xxx, is taking up disk space.Just wondering if I can delete the old slave-relay.000xxx dated in August, just to save disk space. Is this a normal master-slave situation,  Do you have any other recommendation?"

Unlike other questions this was a bit tricky since I was not sure. I didn't want to take the route of deleting the relay logs, since as long as I remembered it was supposed to be automatically purged as soon as the SQL thread has executed the statement.

After a bit of Googling I was left with two options,

1. Might be a version bug where the relay logs are not getting purged
2. To control the relay log explicitly by adding following to the my.cnf,
 relay-log-purge=1
 relay-log-space-limit=size

by setting the relay log space limit it makes sure that when the limit is reached, the IO thread stops until the SQL thread clears the back log.

He had tried the option 2 and was able to get his problem under control.

No comments:

Post a Comment