Be careful with FLUSH TABLES WITH READ LOCK

Be careful with FLUSH TABLES WITH READ LOCK: "Be careful when using FLUSH TABLES WITH READ LOCK (aka FTWRL). I have written about potential problems that may occur when using FTWRL. Anyone who runs ibbackup or xtrabackup on a server that writes a binlog needs FTWRL to run as fast as possible with as few problems as possible, but that is not always the case. In its current form, you must monitor FTWRL and either kill it or long-running queries when FTWRL takes too long.

MySQL does three things when processing FTWRL. First it sets the global read lock. Then it closes open tables. Finally it sets a flag to block commits. You will have problems in production when FTWRL doesn't return quickly. It doesn't return quickly when there are long running queries as it waits for the current queries to finish. The problem is that insert, update, delete and replace statements are blocked after the first step. When FTWRL lingers in the second step (close open tables), then your server will stop accepting writes. An additional problem is that for deployments with many open tables, it is a lot of work to close and then re-open them. I need to confirm whether re-open is done serially because a mutex is held and whether InnoDB re-samples all indexes on all reopened tables to get optimizer statistics.

I blame MyISAM for the current problems. As I am not a MyISAM expert, this is an educated guess and I welcome your feedback. The problem with FTWRL is FT (flush tables) and MyISAM is the reason that tables must be flushed. The --delay-key-write option and possibly other features in MyISAM allow open tables to buffer committed changes. The buffered changes are written to MyISAM data files when the open table is closed.

INSERT DELAYED might also cause problems, but anybody who needs a hot backup shouldn't be using that option.

I think we can make this better and my solution is DFTBGRL (don't flush tables but get read lock). Maybe it needs a better name. DFTBGRL skips the second step of FTWRL -- it sets the global read lock and then it sets a flag to block commits. This should be much safer to use in production.

PlanetMySQL Voting:
Vote UP /
Vote DOWN"