【Mysql】從庫大量select堵塞，Waiting for table flush 狀態

小亮520cl發表於2017-09-12
MySqlAI

背景：

  1. mycat讀寫分離，應用大量select超時


1.檢查

  1. 透過檢查發現大量select處於Waiting for table flush 狀態，仔細看了一下processlist以及時間段，可以斷定是備份加select慢查詢引起的！


2.重現環境

  1. session1
    1. 終端一執行一個慢查詢
    2. mysql> select *,sleep(1000) from operation_log limit 100
    session2
    1. 終端二執行xtracebackup備份
    2. 。。。。。
    3. 。。。。。
    4. >> log scanned up to (768745274)
    5. 。。。。。備份堵塞
    檢視此時的processlist狀態
    1. mysql> show full processlist;
    2. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
    3. | Id | User | Host | db | Command | Time | State | Info |
    4. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
    5. | 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527333 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
    6. | 9140 | root | localhost | devops | Query | 564 | User sleep | select *,sleep(1000) from operation_log limit 100 |
    7. | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
    8. | 9143 | root | localhost:56880 | NULL | Query | 509 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |

  2. session3
    1. 終端3對慢查詢涉及到的表進行查詢操作
    2. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -p***** -e "select * from operation_log limit 10" devops
    3. Warning: Using a password on the command line interface can be insecure.
    4. ...堵塞狀態


  3. 此時的processlist狀態
    1. mysql> show full processlist;
    2. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
    3. | Id | User | Host | db | Command | Time | State | Info |
    4. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
    5. | 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527460 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
    6. | 9140 | root | localhost | devops | Query | 691 | User sleep | select *,sleep(1000) from operation_log limit 100 |
    7. | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
    8. | 9143 | root | localhost:56880 | NULL | Query | 636 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
    9. | 9150 | root | localhost | devops | Query | 454 | Waiting for table flush | select * from operation_log limit 10 | --查詢被堵塞
    10. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
    11. 步驟1阻塞了步驟二,步驟二導致步驟三需要等待步驟一。

  4. session4
    1. 終端四對其它非慢查詢中的表進行查詢（不堵塞）
    2. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from role limit 10" devops
    3. Warning: Using a password on the command line interface can be insecure.
    4. +----+-----------------+--------------------------------+--------+
    5. | id | role_name | description | status |
    6. +----+-----------------+--------------------------------+--------+
    7. | 1 | 超級管理員 | 所有許可權 | 1 |
    8. | 3 | 開發工程師 | 開發工程師開發工程師 | 1 |
    9. | 4 | 運維工程師 | 運帷工程師運帷工程師 | 1 |
    10. +----+-----------------+--------------------------------+--------+
    11. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from module limit 10" devops
    12. Warning: Using a password on the command line interface can be insecure.
    13. +-----+--------------+--------+------------+
    14. | id | module_name | status | list_order |
    15. +-----+--------------+--------+------------+
    16. | 100 | 系統管理 | 1 | 2 |
    17. | 600 | 環境管理 | 1 | 3 |
    18. +-----+--------------+--------+------------+

  5. 解決辦法：
  6. 殺掉原始慢查詢sql即可！
  7. xtrace版本2.2可加引數 --lock-wait-query-type=all
  8. xtrace版本2.4可加引數 --ftwrl-wait-query-type
  9. 該選項表示獲得全域性鎖之前允許那種查詢完成，預設是ALL，可選update。

原因：
在flush tables with read lock成功獲得鎖之前，必須等待所有語句執行完成（包括SELECT）。所以如果有個慢查詢在執行，或者一個開啟的事務，或者其他程式拿著表鎖，flush tables

with read lock就會被阻塞，直到所有的鎖被釋放。

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



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2144836/，如需轉載，請註明出處，否則將追究法律責任。

相關文章