MySQL Backup Issues – Mysqldump –single-transaction and Update Queries

backupinnodbMySQLmysql-5.6mysqldump

If I use mysqldump –single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting.

However, I have caught the following situation last night:

excerpt from show full processlist:

hundreds of those…

   Command: Query
   Time: 291
   State: Waiting for table flush
   Info: insert into db_external_notification.....

then this:

Command: Query
Time: 1204
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`

and the rest of the threads are in Sleep

does anyone have any idea what are these inserts waiting for? I don't see any FLUSH tables or DDL or anything mentioned in the manual that can cause the queries to wait.

full mysqldump command

mysqldump --quick --add-drop-table --single-transaction --master-data=2 -uxx -pxx dbname

I guess –quick is redundant here, probably a leftover from earlier times, this script is very old, but shouldn't hurt anyting

Best Answer

The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK;. It causes mysqldump to setup a repeatable read transaction for all tables being dumped.

From your question, you stated that the mysqldump's SELECT for the db_external_notification table is holding up hundreds of INSERT command to that same table. Why is this happening ?

The most likely thing a lock on the gen_clust_index (better known as the Clustered Index). This paradigm causes data and index pages for a table to coexist. Those index pages are based on either the PRIMARY KEY or and auto-generated RowID index (in the event there is no PRIMARY KEY).

You should be able to spot this by running SHOW ENGINE INNODB STATUS\G and look for any page from the gen_clust_index that has an exclusive lock. Doing INSERTs into a table with a Clustered Index requires an exclusive lock for handling the PRIMARY KEY's BTREE, as well the serialization of the auto_increment.

I have discussed this phenomenon before

UPDATE 2014-07-21 15:03 EDT

Please look at lines 614-617 of your PastBin

mysql tables in use 1, locked 0
MySQL thread id 6155315, OS thread handle 0x85f11b70, query id 367774810 localhost root Sending data
SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
Trx read view will not see trx with id >= 1252538405, sees < 1252538391

Note that line 617 says

Trx read view will not see trx with id >= 1252538405, sees < 1252538391

What does this tell me? You have some PRIMARY KEY with an auto_increment on id.

Your max id for the table db_external_notification was less than 1252538391 when the mysqldump was launched. When you subtract 1252538391 from 1252538405, this means that 14 or more INSERT commands have been attempted. Internally, this would need to move the auto_increment of this table at least 14 times. Yet, nothing can be committed or even pushed into the Log Buffer because of managing this id gap.

Now, look at the processlist from your PasteBin. Unless I miscounted, I saw 38 DB Connections doing an INSERT (19 Before the mysqldump process (process id 6155315), 19 After). I am sure 14 or more of those connections are frozen because of managing the auto_increment gap.

Related Question