mysql binary log makes disk full

Question

Suddenly, I got my website down (drupal 6.19) and could not start mysql (too many tables got crashed). by checking df -h, I found that the MySQL partition is full due to very large binary log files (I keep binary logs for one day only, expire_logs_days = 1); by checking mysqlbinlog for one of the bin logs I found out that most of the entries are for cache tables (cache_form, boost_cache, boost_cache_relationships, cache_content, etc) and for sessions table; some of the data is repeated many times.
by the way, i have watch ls -lh and the bin-log files got larger exponentially (every one or two minutes i got new binlog file with 100M file size!!)

Do you have any idea of what causes this? How can I fix this problem?

Answer 1

I guess one obvious improvement would be to use a different cache backend.

If you have a single server and enough memory, you could use an APC backend (D6: http://drupal.org/project/cacherouter, D7: http://drupal.org/project/apc) to have various of the often used caches directly in shared memory. That is probably the fastest cache solution you can get.

Another alternative is Memcached, which is especially interesting if you have multiples servers that should share a cache.

Note that you can't move the cache_form because that contains information about currently displayed forms and is not really a cache since it contains unique information, not just aggregated things like the others, which can always be cleared. Also, some other caches, especially cache_update, you might want to keep in the database, because that shouldn't be cleared like the rest as it contains information about update information of your used modules, something that doesn't change that often and can be very slow to re-fetch if you have many of them.

You can also move the sessions information out of the database, for example can you place it in MongoDB, and there are probably others too.

Answer 2

Have you checked the date of your bin logs ? expire_logs_days setting is not "auto-magic". It only takes effect when restarting mysql or when executing the command flush_logs or when max_binlog_size is reached.

Then mysql should rotate the current log file (creating a new one) and deleting the files older than 1 day (in your case)

Try to set up this last setting in your my.cnf and see if it works combined with expire_log_days.