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.
I assume purging the logfile like ‘> logfile.log’ is ok.
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.
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 🙂
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.
Peter,
So moving (mv) the logfiles and then giving the mysqld process a HUP or USR1 is no good? I’m hoping an external signal will remove the need to FLUSH LOGS, or do the same thing.
– bish
Peter,
This reference alludes to another page which may suggest that a MV and HUP will rotate the logs safely, without incurring a huge delay or requiring the extra setup the issue of the FLUSH LOGS command may require. The referenced page isn’t reachable right now, unfortunately, but if it becomes available later it may help suggest there’s an alternative plan we can use as well.
http://blesseddlo.wordpress.com/2011/04/15/mysql-logs-filling-up-var/
( –> http://dev.gigigo.tw/index.php?option=com_content&view=article&id=341&Itemid=330)
– bish
Hello guys,
I have just made the mistake Peter talks about in this thread. I had this huge slow queries log (over 2 GB) and i truncated it with cat /dev/null > mysql-slow.log but the space released was less than 400MB. Now i wonder if there is possible to reclaim the remaining 1.6GB of space. Pls help!