Opened 17 years ago

Closed 14 years ago

#126 closed task (fixed)

mysql binary logs filling disk

Reported by: warmerdam Owned by: warmerdam
Priority: major Milestone:
Component: SysAdmin Keywords:
Cc: sbarnes, wolf

Description

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.

Change History (11)

comment:1 by sbarnes, 17 years ago

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.

comment:2 by warmerdam, 17 years ago

Shawn,

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

comment:3 by sbarnes, 17 years ago

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.

comment:4 by warmerdam, 17 years ago

Cc: sbarnes added
Owner: changed from sac@… to warmerdam

Shawn,

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

comment:5 by warmerdam, 17 years ago

Resolution: fixed
Status: newclosed

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.

comment:6 by tmitchell, 17 years ago

Resolution: fixed
Status: closedreopened

comment:7 by tmitchell, 17 years ago

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

comment:8 by tmitchell, 17 years ago

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.

in reply to:  7 comment:9 by wolf, 17 years ago

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

comment:10 by wolf, 17 years ago

Cc: wolf added

Adding myself to Cc

comment:11 by crschmidt, 14 years ago

Resolution: fixed
Status: reopenedclosed

I think we've moved on from this.

Note: See TracTickets for help on using tickets.