テーブルフラッシュ待機中のMySQLスレッドの分析

最近、多くのクエリがブロックされ、結果が返されないというケースに遭遇しました。show processlist を使用して確認したところ、多くの MySQL スレッドがテーブル フラッシュ待ち状態であることがわかりました。クエリ ステートメントは常にブロックされており、Kill プロセスによってのみ解決できます。それでは、まずテーブル フラッシュの待機に関する公式の説明を見てみましょう: https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

スレッドは

FLUSH TABLES を実行しており、すべてのスレッドがテーブルを閉じるのを待っているか、スレッドはテーブルの基礎となる構造が変更され、テーブルを再度開く必要があるという通知を受け取りました。ただし、テーブルを再度開くには、他のすべてのスレッドが問題のテーブルを閉じるまで待つ必要があります。

この通知は、別のスレッドがFLUSH TABLES、または問題のテーブルで次のステートメントのいずれかを使用した場合に発生します: FLUSH TABLES tbl_name、 テーブルを変更する、テーブルの名前変更、テーブルの修復、テーブルの分析 、または テーブルの最適化。

次に、次に示すように、スレッドがテーブルフラッシュ待ち状態にあるときの状況をシミュレートしましょう。 :

最初のセッション接続 (接続 ID=13) では、ロックテーブルを使用してテーブルテストをロックします。

❤️

rreeerreee

mysql> use MyDB;

Database changed

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

 <br>

Database changed

mysql> select connection_id();

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

| connection_id() |

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

|              17 |

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

1 row in set (0.00 sec)

 <br>

mysql> flush table test;

 

 

 

 

3 番目のセッション/接続で、MyDB に切り替えると、プロンプトが表示されます 「 -A を使用してこの機能をオフにすると、より速く起動できます 」 」、現在ブロックされています。このとき、セッションを終了し、パラメーター -A を使用してデータベースにログインすると、テスト テーブルをクエリするとブロックされます (もちろん、他のテーブルのクエリはブロックされません)。以下に示すように:

mysql> use MyDB;

テーブル名と列名の説明

-A

を使用して、この機能をオフにして起動を速くすることができます

mysql> select * from test;

、show processlist を使用してステータスを表示します。現在のデータベース内のすべての接続スレッドが 17 と 18 の両方でテーブルのフラッシュを待機している状態であることがわかります。以下のスクリーンショットに示すように:

 

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)

 <br>

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)

 <br>

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)

 <br>

mysql> kill 17;

Query OK, 0 rows affected (0.00 sec)

 <br>

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)

 <br>

mysql> kill 13;

Query OK, 0 rows affected (0.00 sec)

 <br>

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)

 <br>

mysql>

|

注: スレッドを強制終了する必要があります 13. スレッドを強制終了する17では問題は解決しません。

実稼働環境では、多くの場合、ブロックはロックテーブルの読み取りによって引き起こされるわけではありませんたとえば、以下のテスト ケースでは、同じ大きなテーブルを使用してデカルト積を実行し、低速クエリをシミュレートしました。他の操作は同じです。以下に示すように、テーブルのフラッシュを待っています

も生成されます。

mysql> TEST1 T、TEST1 L から T.* を選択します;

个 また、インターネット上では、MYSQLDUMP がバックアップの場合、パラメーターが使用されていない場合、

SINGLE-TRANSACTION または Flush-Logs と — を使用するケースがあります。 シングルトランザクション これら 2 つのパラメータを一緒にすると、データのダンプを開始する前に FLUSH TABLES 操作が実行され、このような待機シナリオが発生する可能性があります。

解決策:

「テーブルのフラッシュを待機しています」が表示される場合は、通常、ロックされているテーブル、またはフラッシュ テーブルが待機中でテーブルを閉じることができない原因となっている遅いクエリを見つける必要があります。次に、対応するスレッドを強制終了するだけですが、特に本番環境で show processlist を使用すると、大量のスレッドが表示されることになります。めまいがしてしまいますが、問題をすぐに特定するにはどうすればよいでしょうか?

遅いクエリによって他のスレッドがテーブルフラッシュ待ち状態になっている状況の場合:

を参照できます。 processlist の Time 値が大きいスレッドを表示します。上のスクリーンショットに示されているように、セッション接続 14 がブロックの原因となるソース SQL です。このスレッドの Time 列の値は、ブロックされたスレッドの値より大きくなければならないというルールがあります。これにより、多くのレコードをフィルタリングできます。

他のスレッドがロックテーブルによって引き起こされるテーブルフラッシュ待ち状態にある状況については、以下を参照してください:

実験でロックテーブルリードを使用した場合、このセッションはスリープ状態になる可能性があり、show Engine innodb status Gコマンドの出力情報には表示されません。 show open tables where in_use >=1; がロックされているテーブルを見つけることはできても、実際には、これは頭痛の種です。ただし、inntop を使用してそれを見つけることができます。以下に示すように、スレッド 17 はテーブル テストをロックしており、スレッド 17 を innotop で見つけることができます。ことわざにあるように、仕事をうまくやり遂げたいなら、まず自分の道具を磨かなければなりません。

さらに、公式ドキュメントでは ALTER TABLE, RENAME TABLE,REPAIR TABLE, 分析テーブル、または OPTIMIZE TABLE では、この種の待機が発生する可能性があります。以下に示すように、いくつかの簡単なテストを示します。

テーブルフラッシュ待ちの別のシーン

 

会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL

 

mysql> select connection_id();

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

| connection_id() |

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

|              18 |

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

1 row in set (0.00 sec)

 <br>

mysql> select name, sleep(64) from test;

 

会话连接（connection id=6)执行下面SQL语句，分析表test

 

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)

 <br>

mysql>

 

会话连接（connection id=8)执行下面SQL语句

 

mysql> select connection_id();

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

| connection_id() |

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

|               8 |

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

1 row in set (0.00 sec)

 <br>

mysql> select * from test;

 

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table flush状态。 因为当对表做了ANALYZE TABLE后，后台针对该表的查询需要等待，因为MySQL已经检测到该表内部变化，需要使用FLUSH TABLE关闭然后重新打开该表，所以当你查询该表时，就会处于 Waiting for table flush

 

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)

 <br>

mysql>

 

 

 

 

Waiting for table metadata lock

 

 

会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL

 

 

mysql> select connection_id();

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

| connection_id() |

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

|              17 |

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

1 row in set (0.00 sec)

 <br>

mysql> select name, sleep(100) from test;

 

 

会话连接（connection id=6）执行下面SQL语句， 修改表结构操作

 

mysql> select connection_id();

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

| connection_id() |

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

|               6 |

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

1 row in set (0.00 sec)

 <br>

mysql> alter table test add tname varchar(10); // rename table test to kkk 同样会引起Waiting for table metadata lock

 

 

会话连接（connection id=8）执行下面SQL语句，查询表test

 

mysql> select connection_id();

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

| connection_id() |

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

|               8 |

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

1 row in set (0.00 sec)

 <br>

mysql> select * from test;

 

 

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table metadata lock状态。

 

 

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)

 <br>

mysql>

 

以上がテーブルフラッシュ待機中のMySQLスレッドの分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。