After last time, I'd poked at the LB servers databases, so we were getting effectively lock free backups, on one of the servers.
In the intervening period, after it was seen to be stable, I did the same for the other server.
However, Mike noted that the disk space used for the logs was growing rapidly. (I blame those pesky LHC physicists. Running jobs on our systems - anyone would think there was data to analyse or something ...). Because we're running with LB servers on the same machines as the WMS, this means that the /var partition contains both the database files, and the users sandbox - hence the old binary logs take space away from users stuff. (That's something to think about for the reinstall - might be worth separating them).
Time to automate log triming. Firstly, the manual side: the statement
PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';
to the server trims out some of the older logs. You can also trim up to a given file.
Better than that, however, is to put
expire_logs_days=8
in the my.cnf. This tells mysql to retire logs older than 8 days at server start up, or when the logs are flushed.
So as long as we ensure that when we take a full backup we flush the logs, then logs are automatically trimmed to just over a weeks worth. Adding that parameter to the mysqldump script, and we're done.
The binary logs have value, independant of the backups - there's a tool to read them, and look at what was happening. Whether 8 days is the best level for us is something that we'll have to monitor - arguments for shorter time periods seem stronger than for longer.
Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts
Friday, November 27, 2009
Thursday, August 13, 2009
Database backups, and lock time
Running a service creates data. Running a service for a long time creates lots of data.
In this case, the WMS and LB servers - we're sitting with about 18GB on each LB. This is not a problem - they're well indexed against the usual queries (out of the box, no fiddling required), so the old data isn't really noticed.
Until you take a backup.
Then, in order to get a consistant backup, it's locked for however long it takes to dump all that data. Which is about 45 minutes.
That's too long - it means we have some time when it's not available, it's getting noticed. So, how can we take a backup, without locking the database for so long?
There's various options for that, but the best looking (read: simplest) one is to enable binary logging in MySQL. Because the tables used are all InnoDB, which is transactional, this means that the backup can mark a position in the log, and then use that to _not_ backup operations that came after it - which results in a consistant backup. (If your using any MyISM tables, which are not transactional, you can't do this. Hence the use of LVM snapshoting or other exotic techniques).
This it really simple: in the my.cnf for each service, put 'log-bin' (without the quotes) in the [mysqld] section, and restart.
Binary logging is now enabled.
Next, to take a lock free [0] dump, add the --single-transaction flag to mysqldump.
The time taken to actually dump the data to disk won't change, but the database won't be locked for that time.
I did this for one of our LB servers, and then, while the dump was running, submited a job through the WMS. The job was assigned to the LB I was dumping, proving it can be written to, and has now completed, while the dump hasn't yet finished.
I've modified our usual backup script, so that if it detects the presence of /var/lib/mysql/${hostname}-bin.index, which is the index for the binary log, it automatically uses --single-transaction. That way, we still have a single backup script, but it does it the best way possible.
There are a couple of downsides to binary logging: It means the DB has to write more data to disk, so is about 1% slower. As the services are not running at 99% of the cpu, that's ok for us. It also means that each new piece of data is stored twice - once in the DB, and once in the log. Therefore the data storage need grows twice as fast - faster, if there are deletes to the database. I'm looking at an 18GB database - so this won't be a problem. Also, you can purge old logs, so I don't feel that this is a problem any more than the risk of the database expanding over the partition size is.
One thing I'll be looking at is useing the binary logs to take an incremental backup. That'll still not lock the database, but will also be much smaller and faster to take. That's a bit more complicated to arrange, so it'll go into the pile of 'ideas that look nice, but we don't think we need it yet'
As an aside, I think this has to go down as one of the more anticlimatic updates - it was simple, quick and just worked. Unless disk space is very tight, I can't see why one wouldn't enable it.
[0] Technically, it takes a lock, waits for all pending transactions to complete, marks the log position, then releases it. If you have slow operations in flight, it locks it for the duration of that operation.
In this case, the WMS and LB servers - we're sitting with about 18GB on each LB. This is not a problem - they're well indexed against the usual queries (out of the box, no fiddling required), so the old data isn't really noticed.
Until you take a backup.
Then, in order to get a consistant backup, it's locked for however long it takes to dump all that data. Which is about 45 minutes.
That's too long - it means we have some time when it's not available, it's getting noticed. So, how can we take a backup, without locking the database for so long?
There's various options for that, but the best looking (read: simplest) one is to enable binary logging in MySQL. Because the tables used are all InnoDB, which is transactional, this means that the backup can mark a position in the log, and then use that to _not_ backup operations that came after it - which results in a consistant backup. (If your using any MyISM tables, which are not transactional, you can't do this. Hence the use of LVM snapshoting or other exotic techniques).
This it really simple: in the my.cnf for each service, put 'log-bin' (without the quotes) in the [mysqld] section, and restart.
Binary logging is now enabled.
Next, to take a lock free [0] dump, add the --single-transaction flag to mysqldump.
The time taken to actually dump the data to disk won't change, but the database won't be locked for that time.
I did this for one of our LB servers, and then, while the dump was running, submited a job through the WMS. The job was assigned to the LB I was dumping, proving it can be written to, and has now completed, while the dump hasn't yet finished.
I've modified our usual backup script, so that if it detects the presence of /var/lib/mysql/${hostname}-bin.index, which is the index for the binary log, it automatically uses --single-transaction. That way, we still have a single backup script, but it does it the best way possible.
There are a couple of downsides to binary logging: It means the DB has to write more data to disk, so is about 1% slower. As the services are not running at 99% of the cpu, that's ok for us. It also means that each new piece of data is stored twice - once in the DB, and once in the log. Therefore the data storage need grows twice as fast - faster, if there are deletes to the database. I'm looking at an 18GB database - so this won't be a problem. Also, you can purge old logs, so I don't feel that this is a problem any more than the risk of the database expanding over the partition size is.
One thing I'll be looking at is useing the binary logs to take an incremental backup. That'll still not lock the database, but will also be much smaller and faster to take. That's a bit more complicated to arrange, so it'll go into the pile of 'ideas that look nice, but we don't think we need it yet'
As an aside, I think this has to go down as one of the more anticlimatic updates - it was simple, quick and just worked. Unless disk space is very tight, I can't see why one wouldn't enable it.
[0] Technically, it takes a lock, waits for all pending transactions to complete, marks the log position, then releases it. If you have slow operations in flight, it locks it for the duration of that operation.
Subscribe to:
Posts (Atom)