A new MySQL backups temperature scale, with showers

We’re used to Cold, Warm and Hot backups. This scale of three temperatures does not quite reflect the impact of backups on your MySQL database.

In this post I offer a new backup temperature scale, and (somewhat seriously) compare it with showers. Call it the backup shower scale.

A database backup is like a shower: the colder it is, the less time you want to spend doing it.

Cold

A cold backup requires taking your database down (i.e. stop the service).

Example: file system copy

This can work well for replicating slaves, which may not be required for normal operation. You take the slave down, turn off the service, make your backup, turn everything on again, and let the backup catch up. Just make sure its master has all the necessary binary logs.

A friend was staying at my place and was lecturing me on the benefits of cold showers; how it was good for your health. A couple hours later, preparing for bed, I hear him exclaiming from the bathroom: “Whaaaaa! There’s no hot water!”

Warm

A backup which requires locking down your database as read-only.

Examples: MyDumper, mysqlhotcopy (MyISAM only), mysqldump (non-transactional)

Again, this may do well on slaves. As for working out a warm backup on the master: I don’t know. Is a mildly-warm shower really any better than a cold one?

Not quite hot

A backup which is supposedly non-interruptive to normal operation. You are allowed to keep writes to the database. But, things are not quite as they seem.

Example: mysqldump –single-transaction

mysqldump can make a backup in an open transaction. With InnoDB this means a consistent snapshot of the data. But open transactions lead to locks. These accumulate. MVCC makes for changes unable to fall back into the baseline, waiting for the backup’s transaction to complete. Eventually, there are so many locks that your database is as good as dead. mysqldump doesn’t work well for very large databases under heavy load.

[UPDATE: As per Domas’ clarification, MyDumper falls under this category, as well]

In terms of shower, you have reasonably hot water for some time, but they eventually run cold.

Hot

A hot backup is such that does not impose any constraints on the database itself. But it may impose load on other components, such as the disks, memory, CPU. This leads to an implicit impact on MySQL’s performance.

An LVM snapshot works this way. With LVM snapshots, writes to the database cause for blocks copy-on-write. The disk is busier. MySQL has to compete for disk use. An LVM backup works just well, but has a noticeable impact on the database’s ability to keep up as before.

You may have enough hot water for your shower, but there’s not enough pressure. The minute the dishwasher starts working, your shower turns to a sad dripping.

Searing hot

A searing hot backup is one that does not interfere with database operation, explicitly or implicitly, to a reasonable extent.

Xtrabackup with throttling is such a backup. The impact can be made low such that it is unnoticeable. You are willing to have the backup take longer time to complete.