MySQL Flush導致的等待問題
前言
在實際生產環境中有時會發現大量的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的行為，先看看官方解釋：
關閉開啟的表。
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
相關文章
- MySQL:Analyze table導致'waiting for table flush'MySqlAI
- MySQL8.0 view導致的效能問題MySqlView
- ANALYZE導致的阻塞問題分析
- MySQL Case-時間問題導致MySQL例項批次當機MySql
- 新手FTP問題，等待高手指導！！！(轉)FTP
- MySQL：一次timestamp時區轉換導致的問題MySql
- MySQL Sending data導致查詢很慢的問題詳細分析MySql
- 一個簡單的MySQL引數導致的連線問題解惑MySql
- 15、MySQL Case-時間問題導致MySQL例項批次當機MySql
- 克隆ORACLE軟體的導致的問題Oracle
- oracle bug 6825287導致DX鎖等待Oracle
- GoldenGate導致的Streams miscellaneous event等待事件Go事件
- CAS導致的ABA問題及解決
- golang slice使用不慎導致的問題Golang
- 分散式鎖導致的超賣問題分散式
- MySQL鎖等待與死鎖問題分析MySql
- MYSQL 5.7 升級 8.0 後的 由於字符集導致的大問題 ？MySql
- latch 相關效能問題診斷： latch: row cache objects等待事件導致CPU負載高Object事件負載
- 關於 Laravel mix 導致 Bootstrap 失效的問題Laravelboot
- 建立index 使用Online導致的問題Index
- ORACLE資料檔名導致的奇怪問題Oracle
- SCHEDULER呼叫XDB程式導致效能問題
- mysql下的flush操作MySql
- 執行計劃的偏差導致的效能問題
- 記錄一次因 mysql 欄位取名不規範導致的問題MySql
- Latch導致MySQL CrashMySql
- vue的scope導致樣式修改不了問題Vue
- EfCore3的OwnedType會導致Sql效率問題SQL
- javascript小數乘法運算導致的精度問題JavaScript
- 完美的執行計劃導致的效能問題
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- 一次oracle行級鎖導致的問題Oracle
- Oracle監聽日誌過大導致的問題Oracle
- Fastclick 導致click事件觸發兩次的問題AST事件
- memlock過低導致的資料庫效能問題資料庫
- oracle 序列值導致的主鍵衝突問題Oracle
- 【爬坑】.Net編譯環境導致的問題編譯
- redis AOF落地策略rewrite導致阻塞問題Redis