テーブルフラッシュの待機中のMySQLスレッドの分析
Analysis Mysql Thread Waiting
最近、多くのクエリがブロックされ、結果が返されない場合がありました。 show processlistを使用して確認したところ、多くのMySQLスレッドがWaiting for tableflush状態になっていることがわかりました。クエリステートメントはブロックされており、Killプロセスによってのみ解決できます。それでは、テーブルフラッシュの待機に関する公式の説明を見てみましょう：https：//dev.mysql.com/doc/refman/5.6/en/general-thread-states.html
テーブルフラッシュを待っています
スレッドが実行されていますフラッシュテーブルそして、すべてのスレッドがテーブルを閉じるのを待っているか、スレッドがテーブルの基になる構造が変更されたという通知を受け取り、新しい構造を取得するためにテーブルを再度開く必要があります。ただし、テーブルを再度開くには、他のすべてのスレッドが問題のテーブルを閉じるまで待機する必要があります。
この通知は、別のスレッドが使用した場合に発生しますフラッシュテーブルまたは、問題のテーブルに関する次のステートメントの1つ：FLUSH TABLES tbl_name 、他の机、テーブルの名前を変更、修理表、テーブルの分析、または最適化テーブル。
次に、次に示すように、スレッドがWaiting for tableflush状態にある状況をシミュレートします。
最初のセッション接続（接続ID = 13）では、ロックテーブルを使用してテーブルテストをロックします。
mysql> use MyDB Database changed mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 13 | +-----------------+ 1 row in set (0.00 sec) mysql> lock table test read Query OK, 0 rows affected (0.00 sec) mysql>
2番目のセッション接続（接続ID = 17）では、フラッシュテーブルまたはフラッシュテーブルテストのいずれかを実行できます。この時点で、フラッシュテーブルがブロックされていることがわかります。
mysql> use MyDB Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 17 | +-----------------+ 1 row in set (0.00 sec) mysql> flush table test
3番目のセッション/接続で、MyDBに切り替えると、「この機能をオフにして、現時点でブロック状態にある-Aを使用して起動を高速化できます」というプロンプトが表示されます。この時点で、セッションからログアウトし、パラメーター-Aを使用してデータベースにログインします。テストテーブルをクエリすると、ブロックされます（ もちろん、他のテーブルへのクエリはブロックされません ）。次のように：
mysql> MyDBを使用する
テーブル名と列名を完成させるためのテーブル情報の読み取り
この機能をオフにすると、-Aを使用してすばやく起動できます。
mysql> MyDBを使用する
データベースが変更されました
mysql> select * from test
4番目のセッション/接続では、show processlistを使用して、現在のデータベースのすべての接続スレッドのステータスを表示します。 17、18がWaiting for tableflushのステータスになっていることがわかります。以下のスクリーンショットに示すように：
mysql> show processlist +----+------+-----------+------+---------+------+-------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------------------+--------------------+ | 13 | root | localhost | MyDB | Sleep | 90 | | NULL | | 14 | root | localhost | NULL | Query | 0 | init | show processlist | | 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test | | 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test | +----+------+-----------+------+---------+------+-------------------------+--------------------+ 4 rows in set (0.00 sec) mysql>
mysql> show processlist +----+------+-----------+------+---------+------+-------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------------------+--------------------+ | 13 | root | localhost | MyDB | Sleep | 90 | | NULL | | 14 | root | localhost | NULL | Query | 0 | init | show processlist | | 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test | | 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test | +----+------+-----------+------+---------+------+-------------------------+--------------------+ 4 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> show open tables where in_use >=1 +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | MyDB | test | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec) mysql> kill 17 Query OK, 0 rows affected (0.00 sec) mysql> show processlist +----+------+-----------+------+---------+------+-------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------------------+--------------------+ | 13 | root | localhost | MyDB | Sleep | 442 | | NULL | | 14 | root | localhost | NULL | Query | 0 | init | show processlist | | 18 | root | localhost | MyDB | Query | 361 | Waiting for table flush | select * from test | +----+------+-----------+------+---------+------+-------------------------+--------------------+ 3 rows in set (0.00 sec) mysql> kill 13 Query OK, 0 rows affected (0.00 sec) mysql> show processlist +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 14 | root | localhost | NULL | Query | 0 | init | show processlist | | 18 | root | localhost | MyDB | Sleep | 427 | | NULL | +----+------+-----------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) mysql>
| 
注：スレッド13を強制終了する必要があります。スレッド17を強制終了しても問題は解決しません。
実稼働環境では、多くの場合、ロックテーブルの読み取りによるブロッキングではない可能性がありますが、クエリが遅いため、フラッシュテーブルはテーブルを閉じることができず、待機状態になっています。たとえば、次のテストケースでは、デカルト積に同じ大きなテーブルを使用します。遅いクエリをシミュレートします。他の操作は同じです。次に示すように、テーブルフラッシュの待機も生成されます。
mysql> SELECT T. * FROM TEST1 T、TEST1 L
また、インターネット上での事件もあります。 mysqldumpがバックアップされている場合、パラメーター-single-transactionが使用されていない場合、またはflush-logsと-single-transactionの2つのパラメーターが同時に使用されているため、この待機シナリオも発生する可能性があります。これらの2つのパラメーターはまとめられています。 、データのダンプを開始する前にFLUSHTABLES操作を実行します。
解決：
テーブルフラッシュの待機が表示されたら、通常、ロックされているテーブル、またはフラッシュテーブルがテーブルを閉じるのを常に待機する原因となる遅いクエリを見つける必要があります。次に、対応するスレッドを強制終了しますが、正確に特定する方法は、特に実稼働環境では、showprocesslistを使用して多数のスレッドが表示されるため、課題になります。あなたを眩惑させて、問題をすぐに見つける方法は？
テーブルのフラッシュを待機している状態でクエリが遅いために発生した他のスレッドの場合：
時間の値が大きいスレッドは、showprocesslistで表示できます。次に、スクリーニングと確認の後、キルはドロップされます。上のスクリーンショットに示されているように、セッション接続14はブロッキングの原因となったソースSQLです。このスレッドのTime列の値は、ブロックされたスレッドよりも大きくなければならないという規則があります。これにより、多くのレコードがフィルタリングされます。
ロックテーブルの読み取りによって引き起こされた他のスレッドがテーブルフラッシュ状態を待機している状況の場合：
実験で読み取ったロックテーブルを使用する場合、このセッションはスリープ状態である可能性があり、show engine innodb status Gコマンドの出力情報には表示されません。 in_use> = 1でopentablesを表示しても、テーブルがロックされていることはわかりますが、特定のスレッド（接続）を見つけることができない場合でも、実際にはこれは頭痛の種です。しかし、この武器の上部は見つけることができます。以下に示すように、スレッド17はテーブルテストをロックし、スレッド17はinnotopに配置できます。いわゆる労働者は、最善を尽くすために最初に工具を研ぐ必要があります！
さらに、公式ドキュメントでは、ALTER TABLE、RENAME TABLE、REPAIR TABLE、ANALYZE TABLE、またはOPTIMIZETABLEがこのタイプの待機を引き起こす可能性があります。以下に示すように、いくつかの簡単なテストを示します。
テーブルが別のシーンをフラッシュするのを待っています
セッション接続（接続ID = 18）は、次のSQLステートメントを実行して、低速のクエリSQLをシミュレートします。
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 18 | +-----------------+ 1 row in set (0.00 sec) mysql> select name, sleep(64) from test
セッション接続（接続ID = 6）は、次のSQLステートメントを実行してテーブルテストを分析します
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) mysql> analyze table test +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | MyDB.test | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.04 sec) mysql>
セッション接続（接続ID = 8）は、次のSQLステートメントを実行します
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec) mysql> select * from test
スレッドのステータスを確認すると、ブロックされたセッションがWaiting for tableflush状態になっていることがわかります。テーブルに対してANALYZETABLEを実行した後、テーブルのバックグラウンドクエリが実行されるためMySQLがテーブルの内部変更を検出したため、待機する必要があります。FLUSHTABLEを使用してテーブルを閉じてから再度開く必要があります。したがって、テーブルをクエリすると、テーブルのフラッシュを待機することになります。
mysql> show processlist +----+------+-----------+------+---------+------+-------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------------------+----------------------------------+ | 6 | root | localhost | MyDB | Sleep | 22 | | NULL | | 8 | root | localhost | MyDB | Query | 14 | Waiting for table flush | select * from test | | 15 | root | localhost | NULL | Sleep | 3 | | NULL | | 16 | root | localhost | NULL | Query | 0 | init | show processlist | | 18 | root | localhost | MyDB | Query | 46 | User sleep | select name, sleep(64) from test | +----+------+-----------+------+---------+------+-------------------------+----------------------------------+ 5 rows in set (0.00 sec) mysql>
テーブルメタデータロックを待機しています
セッション接続（接続ID = 17）は、次のSQLステートメントを実行して、低速のクエリSQLをシミュレートします。
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 17 | +-----------------+ 1 row in set (0.00 sec) mysql> select name, sleep(100) from test
セッション接続（接続ID = 6）は、次のSQLステートメントを実行し、テーブル構造操作を変更します
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) mysql> alter table test add tname varchar(10) // rename table test to kkk will also cause Waiting for table metadata lock
セッション接続（接続ID = 8）は、次のSQLステートメント、クエリテーブルテストを実行します
mysql> select connection_id() +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec) mysql> select * from test
スレッドのステータスを確認すると、ブロックされたセッションがWaiting for tablemetadataロック状態になっていることがわかります。
mysql> show processlist +----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+ | 6 | root | localhost | MyDB | Query | 19 | Waiting for table metadata lock | alter table test add tname varchar(10) | | 8 | root | localhost | MyDB | Query | 6 | Waiting for table metadata lock | select * from test | | 15 | root | localhost | NULL | Sleep | 8 | | NULL | | 16 | root | localhost | NULL | Query | 0 | init | show processlist | | 17 | root | localhost | MyDB | Query | 55 | User sleep | select name, sleep(100) from test | +----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+ 5 rows in set (0.00 sec) mysql>
参考資料：
https://www.percona.com/blog/2013/02/27/mysql-optimizer-analyze-table-and-waiting-for-table-flush/
http://www.cnblogs.com/jackhub/p/3841004.html
http://myrock.github.io/2014/11/20/mysql-waiting-for-table-flush/
http://mysql.taobao.org/monthly/2016/03/10/
元のソース：cnblogs-> http://www.cnblogs.com/kerrycode/p/7388968.html