Hard disk filling up? Check the MySQL binlog.

Last night our monitoring system started throwing errors about a hard drive filling up on one of our servers. Nothing out of the ordinary was going on, but I was doing some maintenance on one of the sites and removing some old spam comments from the system. We use wordpress, and it provides a handy empty spam option, so I figured I’d use that rather than using good old, reliable SQL. That was my first mistake.

When emptying the spam comments, wordpress issues one DELETE statement for each comment. So, not only does it take forever it also had a side effect I was not expecting. It quickly increases the size of the binary logs.

You see, MySQL has a feature called binary logging. It’s an essential feature that serves at least two purposes. One is data recovery. If you have a binary log, you can restore a database from an backup and then tell MySQL to run through the log and bring your db back up to date. The other is replication. The binlogs are used to create a master/slave relationship between two databases. The slave db pulls the binary logs from the master and then can be an exact copy to provide performance benefits and high availability.

Of course, when the alarms started going off, I didn’t suspect that they were in any way related to any of this. Deleting things is supposed to make a database smaller, right? What I eventually discovered was that the binlogs on the server had grown to 46GB. Considering we only had about 2 gigs worth of real data in the system, I had a configuration issue to track down.

It turned out to be simple. Along the way, someone had commented out a line in the my.cnf file.

#expire_logs_days = 14

So, our binlogs had been slowly piling up since that line was commented out… Sometime in early 2010 according to the date on the oldest log. Ouch. Uncommenting it, and restarting MySQL brought the size down considerably. Now we have room to spare again.

Another mystery solved. Now to seek out the next!