mysql binary logs filling disk

[warmerdam]

On www.osgeo.org the mysql binary logs in /var/lib/mysql have grown to 175GB, filling the disks.

It seems these logs are described at:

​http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

I have purged some older logs by issuing the following mysql command:

purge master logs before '2007-06-01';

But I think we need to figure out why the logs are so huge, whether we even need them, and how to purge them more appropriately. According to notes in the purge command docs you can set an automatic purge limit in your mysql.cnf file using the expire_log_days keyword.

[sbarnes]

The original reason for using binary logs is for doing incremental backups via mysql Zamanda Recovery tool. Binary logs are a requirment for the tool.

​http://mysqlbackup.zmanda.com/index.php/Quick_start_example

I'm not married to this tool if anyone has a better suggestion.

[warmerdam]

Shawn,

Is Zamanda backup in place for mysql? How long do the binary logs need to be kept around?

[sbarnes]

Frank,

Yes, Zamanda is running. The binary logs have to be kept as long as we keep the backup, we can set the number of days to whatever we want.

Right now zamanda keeps logs for 1 week. So we can purge the binary logs older than one week.

[warmerdam]

Shawn,

Great, I'll change the configuration to use an 8 day expiration for the binary logs.

[warmerdam]

I have finally (after another crisis!) added the "expire_logs_days" (set to 10 I think) in /etc/my.cnf and rebooted the server to ensure it gets put in place.

[tmitchell]

Opening this back up, since the bin log issue is back. Starting two days ago, the logs starting stacking up again for some reason - sometimes twice an hour, at 1GB+ each.

That was also the day the Drupal upgrade was done on osgeo1. For what it's worth, we don't have the same problem on osgeo2. Any ideas on this Wolf?

--Tyler

[tmitchell]

I have DISABLED binary logging completely until we can figure this out. I don't like having this turned off without discussing it more broadly - but at the rate it was going it would fill the drive again in 72 hours. Where is our backup plan for mysql?

I commented out the log-bin and expire_logs_days options in /etc/my.cnf and restarted the service.

[wolf]

Replying to tmitchell:

That was also the day the Drupal upgrade was done on osgeo1. For what it's worth, we don't have the same problem on osgeo2. Any ideas on this Wolf?

Hmm yeah looking at the contents of the logfiles with:

mysqlbinlog mysql-bin.00NUMBER

I see that most of the queries are 'UPDATE cache ... ' and 'INSERT INTO cache' which might be because I had to purge the cache to fix the issue with the pearwiki_filter module. But OTOH every page load will still touch the cache table. Maybe we should disable the cache on Drupal (which would result in a performance loss)?

Thoughts? Ideas?

--Wolf

[wolf]

Adding myself to Cc

[crschmidt]

I think we've moved on from this.