Xtrabackup's trampled pit.

Xtrabackup believes that the current use of a very extensive, backup InnoDB table of the preferred tool, but there is a little pit, although the probability of a small occurrence, but I still stepped on the pit. For more information on Xtrabackup, please refer to the official documentation http://www.percona.com/doc/percona-xtrabackup/2.2/intro.html

Suddenly received an alarm, said from the library delay, log on to the server to perform show full processlist view, found the following status (self-simulation, and then the same as the online situation):

You can see that the INSERT statement here is stuck, waiting for a global lock, so the delay from the library is caused. So here's the question. Why is there a global lock, FLUSH TABLES with READ lock. It was later discovered that shortly after a backup script was added from the library, the Backup tool was using the Percona xtrabackup tool. This tool needs to execute the flush TABLES with READ LOCK after copying the files. Such as:

Because of the need to execute the flush TABLES with READ Lock (hereinafter replaced by the FTWRL abbreviation) to obtain the MDL lock for global globals, you can see information such as "Wait for global READ lock". If the inventory is in a large query, or if the replication thread is executing a lengthy DDL, and the FTWRL is blocked, then the subsequent query will be blocked, causing the business unavailability to cause a failure.

Just in the Percona blog to see an article, which is probably said Ftwrl.

What's the problem with FTWRL anyway?

A lot have been written FLUSH TABLES WITH READ LOCK on what really does. Here's yet another walk-through in a bit more detail than described elsewhere:

1. It first invalidates the Query Cache.
2. It then waits-in-flight updates to complete and at the same time it blocks all incoming updates. This was one problem for busy servers.
3. It then closes all open tables, and FLUSH expels them from the table cache. This is the also when have FTWRL -to-wait for all SELECT queries. Another, even bigger problem for busy servers, because-wait happens to occur with all updates blocked. What's even worse, the server at this stage is essentially offline, because even incoming queries would SELECT get blocked.
4. Finally, it blocks commits.

1. It first invalidates the query cache.
2. Then, wait for all updates to complete, and at the same time, it blocks all updates. This is a problem for busy servers.
3. It then closes all open tables (flushing tables) and brushes them out from the cache in the table. This is also when FTWRL must wait for all the select queries to complete. This is another bigger problem for busy servers because waiting just happens this will clog up all the updates. Worse, the server is essentially offline at this stage, because even the select query that is entered will be blocked.
4. Finally, it prevents the transaction from committing.

Summarize:

This time the replication delay fault due to the execution of large queries from the library, and inefficient, just backup script execution, due to xtrabackup execution flush TABLES with READ lock applied Global lock, resulting in lock waiting. At this point, the insert operation is performed from the library, resulting in the inability to insert, waiting for the global lock, resulting in replication delay. This situation Percona and Facebook have a corresponding solution. Finally xtrabackup from the 2.1 version, there are parameters to prevent the occurrence of similar things, interested students themselves query Xtrabackup official documents.

Resources

http://m.csdn.net/article/2015-01-20/2823634

http://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/

http://www.percona.com/blog/2010/04/24/how-fast-is-flush-tables-with-read-lock/

Http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/improved_ftwrl.html

Xtrabackup's trampled pit.