MySQL Binary Logs of Death
I'm running a high traffic Drupal site and having trouble with large MySQL binary logs being created (up to 1Gb every ~30mins at peak times). MySQL server disk space gets filled up before logs can rotate so the site falls over.
The site is multi-lingual, with normal-mode page caching enabled. I wondered whether any other High Performance Drupal ninjas had any experience with such problems. Any help or insight much appreciated.
I've implemented a belt-and-braces cron job to purge the binary logs older than X time, which will keep us going for the time being. If necessary I will turn off the MySQL binary logs completely, but I'm reluctant to do that since we'll have to put in place some other procedures to protect us against db failure.
The problems seem to be similar to those outlined on the Agaric Design blog - http://agaricdesign.com/note/mysql-binary-logs-death - but there is no definite solution on offer there, other than turning off the log-bin.
Site set-up:
- Ubuntu 7.10 Gutsy Gibbon
- Drupal 5.7
- Apache/2.2.4 (Ubuntu)
- PHP/5.2.3-1ubuntu6.3
An idea, use different MySQL for cache tables
Maybe you could setup a different MySQL server to host the cache tables. On that server, you could disable the binlog. The cache tables are accessed from cache.inc, which can be replaced from settings.php, so you could create a version of it that uses a different DB server to do its job.
Depending on what the site is doing, it might be updating variables, or other big records for each page, so every change in the DB has to be logged into the binlog. You could use the devel module to see which SQL statements are executed for each page, so this could give you an idea of what's going on.
Or... if you don't have a procedure to performs point in time recoveries, which is the main benefit of using the binlog, then you could disable the binlog completely. I think it depends on whether it can be assumed data loss since last full backup.
Different DB Same Server
You could follow what Markus said, but also do it on the same db server. Just add a new database, then do a binlog-ignore-db=cache_db. The other option, if you have the memory, is to use something like cache_router then set up memory based caching. If your using APC for op-code caching, I would suggest using that for Drupal caching, as APC seems faster then memcached, but it's only good if your running on a single Apache server. Otherwise you need to go with a memory caching type server that can share the data.
Exclude watchdog, etc.
I would inventory your tables and see which one do not require replication.
You may loose some administrative functionality which you can correct by have an instance of Drupal pointed to your master for both reads and writes.
Check for example how often you are writing to watchdog -- I would exclude that.
You may have other high update tables such as accesslog.
If you want to trade more notes ping me.
Or just nuke them
You don't have to keep those logs around to save you from failure if you're replicating somewhere, which makes backing up easier anyway. So you know, you're not the only person who chose to simply delete logs on a regular basis; we simply delete all but the last 3 at any time.
Check also that this issue
Isn't causing your cache to be constantly refreshed.
http://pajamadesign.com/2008/06/29/the-t-that-took-down-a-webserver/
benjamin, Agaric Design Collective
Binlogs don't allow table exclusion
"Exclude watchdog, etc."
I am in the same situation as joe-b, and we thought of limiting the tables to be included, but from what I understand the binlogs are by database only and cannot select only some of the tables.
162 Unlimited - Drupal Development and Web Design