LVM uses copy-on-write to implement snapshots. Whenever you’re writing data to some page, LVM copies the original page (the way it looked like when the snapshot was taken) to the snapshot volume. The snapshot volume must be large enough to accommodate all pages written to for the duration of the snapshot’s lifetime. In other words, you must be able to copy the data somewhere outside (tape, NFS, rsync, etc.) in less time than it would take for the snapshot to fill up.
While LVM allows for hot backups of MySQL, it still poses an impact on the disks. An LVM snapshot backup may not go unnoticed by the MySQL users.
Some general guidelines for making life easier with LVM backups follow.
Lighter, longer snapshots
If you’re confident that you have enough space on your snapshot volume, you may take the opportunity to make for a longer backup time. Why? Because you would then be able to reduce the stress from the file system. Use ionice when copying your data from the snapshot volume:
ionice -c 2 cp -R /mnt/mysql_snapshot /mnt/backup/daily/20100719/
[Update: this is only on the cfq I/O scheduler; thanks, Vojtech]
Are you running out of space?
Monitor snapshot’s allocated size: if there’s just one snapshot, do it like this:
lvdisplay | grep Allocated Mon Jul 19 09:51:29 2010 Allocated to snapshot 3.63%
Don’t let it reach 100%.
Avoid running out of space
To make sure you don’t run out of snapshot allocated size, stop all administrative scripts.
- Are you running your weekly purging of old data? You will be writing a lot of pages, and all will have to fit in the snapshot.
- Building your reports? You may be creating large temporary tables; make sure these are not on the snapshot volume.
- Rebuilding your Sphinx fulltext index? Make sure it is not on the snapshot volume, or postpone till after backup.
You will gain not only snapshot space, but also faster backups.
Someone did the job before you
Use mylvmbackup: the MySQL LVM backup script by Lenz Grimmer. Or do it manually: follow this old-yet-relevant post by Peter Zaitsev.
Some important notes:
– ionice works only with CFQ IO scheduler. However, CFQ is usually not recommended for MySQL
– LVM snapshots are costly, if I was looking for a new binary backup solution, I would choose XtraBackup instead of LVM
@Vojtech,
With regard to XtraBackup: I use it frequently. I’ve had some occasions where, due to problems with innodb tablespace, it was unable to perform the backup (failed either while mid-taking the backup or at recovery time). Failure actually looks more like a bug.
The advantage of LVM snapshot is that it is DB-unaware.
In many cases, this seems like this technique may lead to priority inversion by increasing the time the snapshot is open and increasing the time for suffering under snapshot write overhead.
@Andrew – more time to suffer, but the pain is lighter. This is my experience.
Hi Shlomi! Good post, thanks for providing this background information and the plug for mylvmbackup. I’ve added a link to your post to the “Documentation and Links” section on the project page at http://lenzg.net/mylvmbackup/#Docs
Cheers!
LenZ