Website performance issues

If you recently visited my website (let's say in the last two or three days), you might have noticed some performance issues. It seems I didn't pay that much attention to the MySQL binary logging configuration, when I enabled this feature so that I could do point-in-time recovery in case something goes wrong.

In /etc/mysql/my.cnf, I had these options related to binlogging:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Given that my /var/log partition is only 2GB in size, I might want to lower the expire_log_days value. Today I noticed that /var/log had 0% free disk space, and that 1.8GB of it was used by /var/log/mysql. Did an ls in that directory, and saw about 30 binlog files, some of them as big as the max_binlog_size value.

Now, since I am doing a daily database backup, there is no need to keep files older than one day (they'll end up in the daily filesystem backup anyway). So I could have MySQL automatically "expire" logs older than 1 day, but I configured it to 2 days just to be on the safe side.

Good that I have /var and /var/log on seperate partitions, I might have lost some more important data otherwise.

Update: And today (16/11) the same problem occurred. MySQL will no longer automatically remove the expired binlogs if you manually removed some of them without updating the index file. Which is exactly what I did to get some free disk space in /var/log.

Tags: 

Topics: