Backup and Recovery - High Performance MySQL - page 626

One of the biggest problems with many backup methods is their use of FLUSH TABLES
WITH READ LOCK . This tells MySQL to close and lock all tables, flushes MyISAM's data
files to disk (but not InnoDB's!), and flushes the query cache. That can take a very long
time to complete. Exactly how long is unpredictable; it will be even longer if the global
read lock has to wait for a long-running statement to finish, or if you have many tables.
Until the lock is released, you can't change any data on the server, and everything will
block and queue. 2 FLUSH TABLES WITH READ LOCK is not as expensive as shutting down,
because most of your caches are still in memory and the server is still “warm,” but it's
very disruptive. Anyone who tells you it's fast probably is trying to sell you something
and has never worked on a real MySQL server in production.
The best way to avoid any use of FLUSH TABLES WITH READ LOCK is to use only InnoDB
tables. You can't avoid using MyISAM tables for privileges and other system informa-
tion, but if that data changes rarely (which is the norm) you can flush and lock only
those tables without causing trouble.
Here are some performance-related factors to consider when you're planning backups:
Lock time
How long do you need to hold locks, such as the global FLUSH TABLES WITH READ
LOCK , while backing up?
Backup time
How long does it take to copy the backup to the destination?
Backup load
How much does it impact the server's performance to copy the backup to the
destination?
Recovery time
How long does it take to copy your backup image from its storage location to the
MySQL server, replay binary logs, and so on?
The biggest trade-off is backup time versus backup load. You can often improve one
at the other's expense; for example, you can prioritize the backup at the expense of
causing more performance degradation on the server.
You can also design your backups to take advantage of load patterns. For instance, if
your server is only 50% loaded for 8 hours during the night, you can try to design your
backups to load the server less than 50% and still complete within 8 hours. You can
accomplish this in many ways: for example, you can use ionice and nice to prioritize
the copy or compression operations, use different compression levels, or compress the
data on the backup server instead of the MySQL server. You can also use lzo or pigz
for faster compression. You can use O_DIRECT or fadvise() to bypass the operating
system's cache for the copy operations, so they don't pollute the server's caches. Tools
2. Yes, even SELECT queries will get blocked, because there's bound to be a query that tries to modify some
data, and as soon as it starts waiting for a write lock on a table, all of the queries trying to get read locks
will have to wait, too.