I currently have a server running mysql v.4.0.25 on Debian, Which is replicating from another server.
The slave is replicating fine, but the SQL-Thread is not cleaning up after itself, Its not deleting the relay logs once its done with them - Now the limit is 512mb to the relay logs but Im having to keep an eye on the disk space because of it.
I have searched dev.mysql, google, bugs.mysql, Ask, This forum - But to no avail!
Does anyone have an idea as to what could be causing this? and how it could be solved?
If I havent supplied enough info - Just let me know.
Thanks and best regards
The slave SQL thread processes the contents of the relay log files. When it starts, it reads events from the relay logs and executes them. As it finishes processing each relay log file it deletes it if the I/O thread is writing to a newer relay log. If the SQL thread is reading the same relay log that the I/O thread is writing, the SQL thread pauses until more events are available in the file.
It sounds like your SQL thread is constantly updating records and isn't able to remove the log file because the I/O thread is still writing to it.
A slave server creates a new relay log file under the following conditions:
* Each time the I/O thread starts.
* When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs.
* When the size of the current relay log file becomes too large. The meaning of “too large” is determined as follows:
o If the value of max_relay_log_size is greater than 0, that is the maximum relay log file size.
o If the value of max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size.
Thanks for the reply,
Firstly, I have read through the dev.mysql stuff - And for this situation, Its of no use - But thanks anyway.
The second thing is that the files are still progressing, So the sql_thread is creating the new files, and advancing to them.
The SHOW SLAVE STATUS shows that the slave is using the new files which are being created.
So the replication is working OK, and the new logs are being created OK when the old one hits the max size, Its just that the sql_thread isn't deleting them after its created a new file.
Now mysql is quite happy for me to manually delete them (Obviously with caution) and no problems occur from that.
I just have a messy sql_thread that wont tidy up after itself!
Thanks again for the reply and best regards
Their is the binary for it, But just the sheer size of the data which is on this server puts a hurdle their firstly.
Secondly the code which runs on this db is a tangled web, and would be a major project to change some things, The site is mission critical you see - And we cant afford to have the site down for too long. Upgrading would need some serious planning etc.
So although I would love to, Its simply out of the question at the moment