Wait issues caused by MySQL flush

Wait problems caused by--mysql flush

-------------------------------2014/07/13

Preface

In the actual production environment, a large number of SQL statements are sometimes found to be in waiting for table. State, sometimes it may seem confusing, this article explains the causes of this state and how to solve them.

Body

This article uses the lock table to simulate the actual situation, first introduces a lock tables need to note, if you use the lock tables in a session, you will only be able to access the table lock in this session. Official explanation:

If theLOCK TABLES statement must wait dueTo locks heldby other sessionsOnAnyOf the tables, it blocks untilAll locks can acquired. ASession that requires locks must acquireAll the locks it needsIn a singleLOCK TABLES statement. While the locks thus obtainedis held, theSession can accessOnly the locked tables.For example,In the following sequenceof statements, an error occursFor the attemptTo access T2 because it isNot lockedIn theLOCK TABLES statement:mysql>LOCK TABLES T1Read;mysql>select count (*) from T1;+----------+| COUNT (*) |+----------+|        3 |+ ----------+mysql> select count (*) from t2; ERROR 1100 (HY000): Table ' T2 ' is not locked with lock tablestables in the information_schema database are an  exception. They can accessed without being locked explicitly even while a session holds table Locks obtained with lock TABLES.     

For this reason, flush table with read lock is often selected at backup time, and is a good choice if successful, it locks all tables.

Take a look back at the official document explaining the flush table

in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

simulates the waiting tables wait problem caused by the flush operation.

A) Execution of lock table in Terminal 1 locks a table in the JACKJHU library

lock table t1 read;Query OK, 0 rows affected (0.00 sec)

b) Execute flush tables in Terminal 2

mysql> flush tables;       --无返回，被阻塞..

c) At this point Terminal 3 to connect to the JACKJHU library, if the MySQL connection is not using-a, there will be blocking.

for completion of table and column namesYou can turn off this feature to get a quicker startup with -A  --无返回，被阻塞，后面会解释原因…

Re-use the-a option to connect Terminal 3 to execute the Select Jackjhu Library in table T1, blocking.

select * from t1; --无返回，被阻塞..

d) New Terminal 4, use the-a option to connect MySQL to the JACKJHU library to query other tables, normal.

select * from t2;+----+---------+| id | name    |+----+---------+|  5 | mysql   ||  6 | test    |+----+---------+2 rows in set (0.01 sec)

To exit the terminal, re-use the no-a option to connect to MySQL and select Jackjhu.

for completion of table and column namesYou can turn off this feature to get a quicker startup with -A  --无返回，被阻塞..

Look at the current thread situation and analyze the cause of the blocking.

Mysql>ShowFull processlist;+----+------+-----------+---------+------------+------+-------------------------+-----------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+---------+------------+------+-------------------------+-----------------------+| 2 | Root | localhost | Jackjhu |    Query | 0 | init |ShowFull Processlist | |3 | Root | localhost | Jackjhu | Query |33 | WaitingForTable Flush | Flush tables          | |  7 | root | localhost | jackjhu | query      |   20 | Waiting for table flush | select * from t1      | | 12 | root | localhost | jackjhu | Field List |   10 | Waiting for table flush |                        |+----+------+------ -----+---------+------------+------+-------------------------+-----------------------+4 span class= "keyword" >rows in set (0.00 sec)   

for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, orOPTIMIZE TABLE.

Analysis

ID 3: Terminal 2, because T1 is locked by Terminal 1 (lock table T1 read), flush operation needs to refresh the table T1 buffer and close the table, blocked, the table locked cannot be closed.

ID 7: Terminal 3, querying the contents of the T1 table, the flush operation emits a signal that the table needs to be reopened due to a flush operation before the query. Terminal 3 queries need to reopen the T1 table, apparently also blocked, by the experiments above we know that open other tables are not locked can be, they have finished flush and can be opened.

ID 12: Do not use-A to connect to MySQL, is blocked. Thread Status: Field List. Explain:

is retrieving information for table columns. 

This is also blocked because the table T1 needs to be reopen blocked.

From the appearance of show full processlist, it seems that the problem is caused by the flush tables, in fact, the root of the problem is that the operation is blocked, that is, the table T1 is locked, or cannot be released to be closed.

Similarly ALTER TABLE, RENAME table, REPAIR table, ANALYZE table, OPTIMIZE table and other operations will also appear such problems, this is the experimental results.

Condition 1:lock Table T1 Read

Repair table, ALTER TABLE (CREATE INDEX ...), rename table,optimize table

The above operation and the following select appear waiting for table metadata lock

Analyze table T1 works fine, but the following select appears waiting for table metadata lock

--No field list lock

Condition 2:lock Table T1 Write

Just say something different from condition 1.

Analyze table

The above operation and the following select appear waiting for table metadata lock

--No field list lock

Note that the lock table is used here. Read simply simulates the fact that most of the production environment is due to long queries, resulting in flush table waiting for the table to fail to close.

bug with MySQL flush Operation

Phenomenon: If the waiting for table flush is found in the production environment as previously simulated, a large amount of SQL is blocked. In order to solve the problem, kill the flush tables This operation, this does not solve the problem, the need to reopen the tag will still exist without being cleared, the subsequent select still can not be continued.

MySQL bug:http://bugs.mysql.com/bug.php?id=44884

Waiting for table appears: Workaround

Find the source of blocking flush TABLES, ALTER table, RENAME table, REPAIR table, ANALYZE table, OPTIMIZE table, perhaps a lock table operation and a table lock, perhaps a long Query (can be found from the time column of Proceelist), in short, we can easily locate which table caused the problem, and then to locate the SQL statement, from this direction to think, it can be easier to solve such a problem.

finally look at the behavior of flush tables with read lock, first look at the official explanation:

and locks all tables for all databases with a global read lock.

Close the Open table.

Mysql> Flush table with ReadLock Query OK, 0 rows Affected (0.00 sec) mysql>ShowGlobal statusLike'%open% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| com_ha_open              | 0     | | com_show_open_tables     | 0     | | open_files               | 7     | | open_streams             | 0     | | open_table_definitions   | 0     | --Table structure file is closed | open_tables              | 0     | --table is closed | opened_files             | 561   | | opened_table_definitions | 101   | | opened_tables            | 101   | | slave_open_temp_tables   | 0     |+--------------------------+-------+10 rows in  set (0.00 sec)

Give MySQL a global read lock.

Mysql> Flush table with ReadLock Query OK, 0 rows Affected (0.00 sec) mysql>ShowGlobal statusLike'%open% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| com_ha_open              | 0     | | com_show_open_tables     | 0     | | open_files               | 7     | | open_streams             | 0     | | open_table_definitions   | 0     | | open_tables              | 0     | | opened_files             | 561   | | opened_table_definitions | 101   | | opened_tables            | 101   | | slave_open_temp_tables   | 0     |+--------------------------+-------+10 rows in Set (0.00 sec)  

For write operations close to blocking, write operations are locked waiting

   mysql> insert into t3 values (3, ' Uncommit '); span class= "comment" >--no return, wait, processlist in the state as follows: | query   |    37 | Waiting for global read lock                                                   | insert into t3 values (3,< Span class= "string" > ' Uncommit ') |             

If the transaction already has data written before flush with read lock but has not yet been committed, the commit will be blocked at this time.

commit lock                                                       | commit