Databases Reference
In-Depth Information
• Remember that LVM needs to copy each modified block to the snapshot only once.
When MySQL writes a block in the original volume, it copies the block to the
snapshot, then makes a note of the copied block in its exception table. Future writes
to this block will not cause any further copies to the snapshot.
• If you use only InnoDB, consider how InnoDB writes data. Because it writes all
data twice, at least half of InnoDB's write I/O goes to the doublewrite buffer, log
files, and other relatively small areas on disk. These reuse the same disk blocks over
and over, so they'll have an initial impact on the snapshot, but after that they'll
stop causing writes to the snapshot.
• Next, estimate how much of your I/O will be writing to blocks that haven't yet
been copied to the snapshot, as opposed to modifying the same data again and
again. Be generous with your estimate.
• Use
vmstat
or
iostat
to gather statistics on how many blocks your server writes per
second.
• Measure (or estimate) how long it will take to copy your backup to another loca-
tion: in other words, how long you need to keep the LVM snapshot open.
Let's suppose you've estimated that half of your writes will cause writes to the snap-
shot's copy-on-write space, and your server writes 10 MB per second. If it takes an hour
(3,600 seconds) to copy the snapshot to another server, you will need 1/2 × 10 MB ×
3,600, or 18 GB of space for the snapshot. Err on the side of caution, and add some
extra space as well.
Sometimes it's easy to calculate how much data will change while you keep the snapshot
open. Let's look at an example. The BoardReader forum search engine has about 1 TB
of InnoDB tables per storage node. However, we know the biggest cost is loading new
data. About 10 GB of new data is added per day, so 50 GB should be plenty of space
for the snapshot. This estimate doesn't always work, though. At one point, we had a
long-running
ALTER TABLE
that changed each shard one after the other, which modified
much more than 50 GB of data; while this was running, we weren't able to make the
backup. To avoid problems such as this, you can wait a while after creating the snap-
shot, because the added load is the highest right after creating the snapshot.
Backup Myth #2: “My Snapshot Is My Backup”
A snapshot, whether it's an LVM snapshot, a ZFS snapshot, or a SAN snapshot, isn't
a real backup because it doesn't contain a full copy of your data. Because snapshots are
copy-on-write, they contain only the differences between the live copy of the data and
the data at the point in time when the snapshot happened. If an unmodified block
becomes corrupt in the live copy of the data, there's no good copy of that block that
you can use for recovery, and every snapshot sees the same corrupted block that the
live volume does. Use snapshots to “freeze” your data while you take a backup, but
don't rely on the snapshot itself as a backup.
