Be careful rotating MySQL logs
If you enable logging of all queries as “slow queries” using the patch or MySQL 5.1 you can get log file to grow huge. Same may happen with general log file. In some cases we’ve got log file sizes of 100G or more which may need to be cleaned up.
Here is some danger waiting for you at least on typical Linux systems - If you follow most simple process - delete log file you do not need and run “FLUSH LOGS” so file is recreated and space reclaimed you can get into serious trouble. File close operation will when perform deletion which can be quite a long process, depending on filesystem and log file size - we’ve seen it to take 10 minutes or more in some cases. When log file is reopened MySQL will be practically unavailable causing unanticipated downtime.
The workaround for this problem is very simple - instead of deleting MySQL log file - rename it, call “FLUSH LOGS” which will be instant as it will not involve complex delete operation and when you can delete the log file you no more need.
It can be also good idea to hook up log rotate to take care of MySQL logs so you would not need to cleanup them manually. In some cases it is already setup if you use MySQL distribution supplied by OS vendor.
del.icio.us
digg5 Comments
I assume purging the logfile like ‘> logfile.log’ is ok.
Comment :: December 9, 2007 @ 1:50 pm
The other point to be made is to make sure you use the logs. Why are you logging, if not to use the logs? If you need or want them for historical purposes, or possible forensics, then they should be archived to another server. Certainly log rotation should be considered when logs are enabled — and not just for MySQL, but for any log.
Comment :: December 10, 2007 @ 12:30 am
Martin,
For large log files “echo > logfile.log” seems to stall writes to the log file until echo command is completed. This at least applies to some filesystems on Linux so I’d be careful.
This is however good way for small log files
Comment :: December 10, 2007 @ 6:09 am
Sheeri,
Good point. However you do not always have to use the logs to keep them enabled. Sometimes you have them in case you will need to use them - ie you want to check what happened during some point in time in the past.
It is also worth to note in MySQL 5.0 you can’t enable/disable general query log without restarts so if you may need it sometimes and you can’t afford server restarts you just need to have it enabled.
Nice workaround I’ve seen though is having symlink from general.log -> /dev/null which allow you to disable writes when you do not need log file without server restarts. Though of course some of logging overhead remains in this case.
Comment :: December 10, 2007 @ 6:12 am
[...] from：http://www.mysqlperformanceblog.com/2007/12/09/be-careful-rotating-mysql-logs/ [...]
Pingback :: December 16, 2007 @ 9:25 pm