Yes, if you are running backing on a large database that is also handling production traffic (not a very smart idea to begin with). This is especially important for backups created using snapshots based on copy-on-write algorithm.
Brian makes an important point in a comment to my post regarding backup. He points out "Backups are always onerous on IO" and that a better way to backup is to use slaves or a standby master (if using multi master replication).
If you *must* run backups on a production server, then ibbackup becomes very important as it doesn't affect performance as much as the evil snapshots created by snapshot tools like fssnap and LVM. I have found that in our case purchasing ibbackup licenses were worth every penny.
In our environment, running backups using copy-on-write snapshots was killing performance. Writes would start stalling several hours into the backup process. It didn't help that backups would take 27 hours to finish. I moved most systems to using ibbackup and for those systems running backups hasn't been an issue at all.
Of course, if you must backup production servers, take snapshots to backup everything except the databases. That way the snapshots will be held for a much smaller period and you can continue backing up databases using ibbackup.
What about mysqldump? I don't consider mysqldump an appropriate tool for periodic backups. I can see it working for small databases but running it on enterprise level databases for daily backups is just not going to be feasible.
I would love to discuss backups more at the conference. I also would like to evaluate some of the backup vendors exhibiting at the conference.
5 comments:
I think the problem with mysqldump is the TYPE of backup it does. In the interest of maintaining portability, MySQL has chosen to do a SQL dump as a backup. As you said, this is fine for smaller databases but absolutely blows for anything of substance. PGSQL has the same problem and even the "binary" backup is nothing more than a compressed SQL dump.
I really wish both teams would implement a binary backup format that performs better than a SQL dump.
Hi!
For Archive there is "archive_reader" which will generate a backup of a live Archive table for you. When I wrote it, I wanted to be able to point it at one of my NAS devices to not destroy disk IO.
From my own tests, and what others tell me, the IO penalty is not really noticeable.
Keep in mind though... Archive is a very special corner case :)
Cheers,
-Brian
Hi Brian, as always, thanks for the comment. I was not aware of archive_reader so I will check definitely it out.
"From my own tests, and what others tell me, the IO penalty is not really noticeable."
I agree! For most installations of MySQL, backup won't be a big pain especially if they have low volume of writes. However, in our case, backing up terabytes of data with high number of inserts was causing a lot of pain until we implemented a snapshot less backup strategy for databases.
Another strategy we are in the process of evaluating is snapshots at the storage array level as we think that may be less taxing than snapshots at OS level.
-cheers
Frank
Another strategy we are in the process of evaluating is snapshots at the storage array level as we think that may be less taxing than snapshots at OS level.
I actually forgot to mention this as an option in my comment.
Hardware snapshotting is probably the best option for such a large database. Obviously you should be aware that depending on your SAN implementation while the snapshot command can be almost instantaneous on the SAN, the actual copy can not be finished for several hours depending on the write activity on the source LUN.
Depending on your backup needs, a mix of backing up a slave and making use of maatkit's slave delay tool can be ideal! I've had a few encounters where a lagged slave has been invaluable (schema change gone wrong for instance). I've written about it here: http://northernmost.org/blog/mysql-backup-using-replication/
I've also had pleasant experiences with R1soft's CDP with the MySQL plugin (proprietary), but I have yet to have the guts to deploy it in a live high-traffic environment.
Post a Comment