MySQL Flush導致的等待問題

weixin_33843409發表於2015-08-20
MySql

前言

在實際生產環境中有時會發現大量的sql語句處於waiting for table.. 狀態中，有時候看起來會讓人覺得困惑，本文將講解此狀態產生的原因及解決辦法。

正文

本文中用到了lock table來模擬實際情況， 首先介紹一個lock tables需要注意的事項，如果在一個session中使用了lock tables，那麼在此session中將只能訪問獲取到lock的表。官方解釋：

If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

mysql> LOCK TABLES t1 READ;

mysql> SELECT COUNT(*) FROM t1;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

mysql> SELECT COUNT(*) FROM t2;

ERROR 1100 (HY000): Table ‘t2‘ was not locked with LOCK TABLES

Tables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES.

 基於這樣的原因，往往在備份時選擇flush table with read lock;是個不錯的選擇，如果成功，它會鎖住所有表。

 回顧一下官方文件關於flush table的解釋

Closes all open tables, forces all tables 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.

 模擬Flush操作導致的waiting tables等待問題。

 

a) 在終端1中執行lock table鎖住jackjhu庫中的一張表

mysql> lock table t1 read;

Query OK, 0 rows affected (0.00 sec)

 b) 在終端2中執行flush tables

mysql> flush tables;

       --無返回，被阻塞..

c) 此時終端3欲連線到jackjhu庫，如果mysql連線時沒有使用-A，將出現阻塞。

mysql> use jackjhu

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

      --無返回，被阻塞，後面會解釋原因…

重新使用-A選項連線終端3執行select jackjhu庫中表t1，阻塞。

mysql> select * from t1;

      --無返回，被阻塞..

d) 新建終端4，使用-A選項連線mysql，到jackjhu庫中查詢其他表，正常。

mysql> select * from t2;

+----+---------+
| id | name    |
+----+---------+
|  5 | mysql   |
|  6 | test    |
+----+---------+
2 rows in set (0.01 sec)

 退出終端，重新使用不帶-A選項連線mysql，選中jackjhu。

mysql> use jackjhu

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

       --無返回，被阻塞..

檢視現在的執行緒情況，並分析阻塞的原因。

mysql> show full processlist;

+----+------+-----------+---------+------------+------+-------------------------+-----------------------+

| Id | User | Host      | db      | Command    | Time | State                   | Info                  |

+----+------+-----------+---------+------------+------+-------------------------+-----------------------+

|  2 | root | localhost | jackjhu | Query      |    0 | init                    | show full processlist |

|  3 | root | localhost | jackjhu | Query      |   33 | Waiting for table 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 rows in set (0.00 sec) 

首先檢視waiting for table ..的官方解釋：

The thread got a notification that the underlying structure 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.

分析

ID 3：終端2，由於t1被終端1鎖住（lock table t1 read），flush操作中需要重新整理表t1緩衝並關閉表，被阻塞，表被鎖無法關閉。

ID 7：終端3，查詢t1表的內容，由於查詢前有flush操作，flush操作會發出表需要被重新開啟的訊號。終端3的查詢需要重新開啟t1表，顯然也被阻塞，由我們上面的實驗知道，開啟其他沒有被鎖定的表是可以的，他們已經完成flush並可以被開啟。

ID 12：不使用-A連線mysql，被阻塞。執行緒狀態：Field List。解釋：

The thread is retrieving information for table columns.

 由於表t1需要被reopen阻塞，所以這裡也被阻塞。

從show full processlist這個表象上看，好像是flush tables引起的問題，其實問題的根源在於這個操作被阻塞，也就是表t1被鎖住，或無法釋放被關閉。

 同樣ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,  OPTIMIZE TABLE等操作也會出現這樣的問題，這面是實驗結果。

條件1：lock table t1 read      

repair table, alter table(create index…),rename table,optimize table

以上操作和之後的select出現 Waiting for table metadata lock

analyze table t1操作正常，但是後面的select出現 Waiting for table metadata lock

--無field List鎖

條件2：lock table t1 write

僅僅說一下和條件1中情況不同的，其他一樣。

analyze table

以上操作和之後的select出現 Waiting for table metadata lock

--無field List鎖

注意這裡使用了lock table .. read簡單的進行了模擬，實際上生產環境中大多數是由於長查詢，導致flush table一直等待無法關閉該表導致。

 

MySQL Flush操作的bug

 

現象：如上文模擬的情況，如果在生產環境中發現waiting for table flush，阻塞了大量的sql。這時候為了解決問題，kill掉flush tables這個操作，這並不能解決問題， 需要reopen的標記依然會存在而沒有被清除，後面的select依然沒能被繼續。

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

 

出現waiting for table.. 的解決辦法

 

找到阻塞FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE等操作的源頭，或許是lock table操作和有表鎖，或許是一個長長的查詢(可以從proceelist的time列找到)，總之，我們可以很容易定位到哪張表引起的問題，再去定位sql語句，從這個方向去思考，可以比較容易的解決這樣的問題。

 

最後看看flush tables with read lock的行為，先看看官方解釋： 

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

關閉開啟的表。

mysql> flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show global status like ‘%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)

給mysql全域性的讀鎖。

mysql> flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show global status like ‘%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)

對於寫入操作近些阻塞，寫入操作被鎖等待

mysql> insert into t3 values(3,‘uncommit‘);

--無返回，等待，processlist中狀態如下：

|Query   |    37 | Waiting for global read lock                                                  | insert into t3 values(3,‘uncommit‘) |

如果事務在flush with read lock前已經有資料寫入，但是還沒有提交，此時提交的話也會被阻塞。

Query   |     3 | Waiting for commit lock                                                       | commit

 

相關文章