Analyse des MySQL-Threads in Waiting for Table Flush

Ich bin kürzlich auf einen Fall und viele Anfragen gestoßen Aufgrund der Blockierung werden keine Ergebnisse zurückgegeben. Überprüfen Sie mit show Processlist, ob sich viele MySQL-Threads im Status „Warten auf Tabellenlöschung“ befinden. Die Abfrageanweisung wurde blockiert und kann nur durch Beenden des Prozesses gelöst werden. Werfen wir also zunächst einen Blick auf die offizielle Erklärung von Waiting for Table Flush: https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

Warten auf Tischspülung

Der Thread führt FLUSH TABLES aus und wartet darauf, dass alle Threads geschlossen werden ihre Tabellen, oder der Thread hat eine Benachrichtigung erhalten, dass sich die zugrunde liegende Struktur einer Tabelle geändert hat und er die Tabelle erneut öffnen muss, um die neue Struktur zu erhalten. Um die Tabelle erneut zu öffnen, muss er jedoch warten, bis alle anderen Threads die Tabelle geschlossen haben Frage.

Diese Benachrichtigung erfolgt, wenn ein anderer Thread FLUSH TABLES verwendet hat oder eine der folgenden Aussagen zur betreffenden Tabelle: FLUSH TABLES tbl_name, ALTER TABLE, TABELLE UMBENENNEN, TABELLE REPARATUR, TABELLE ANALYSIEREN, oder OPTIMIEREN TABELLE.

Dann simulieren wir, dass sich der Thread in der Tabelle „Warten auf Tabelle“ befindet Der Spülstatus ist wie folgt:

In der ersten Sitzungsverbindung (Verbindungs-ID=13) verwenden wir Sperrtabelle, um den Tabellentest zu sperren.

mysql> use MyDB;

Database changed

mysql> select connection_id();

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

| connection_id() |

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

|              13 |

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

1 row in set (0.00 sec)

 <br>

mysql> lock table test read;

Query OK, 0 rows affected (0.00 sec)

 <br>

mysql>

In der zweiten Sitzungsverbindung (Verbindungs-ID=17) können wir eine Flush-Tabelle oder einen Flush-Table-Test ausführen. An diesem Punkt werden Sie feststellen, dass die Flush-Tabelle blockiert ist.

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;

Wenn Sie in der dritten Sitzung/Verbindung zu MyDB wechseln, werden Sie aufgefordert "Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten" , das sich derzeit im Sperrzustand befindet. Zu diesem Zeitpunkt verlassen Sie die Sitzung und melden sich mit Parameter -A bei der Datenbank an. Wenn Sie die Testtabelle abfragen, wird diese blockiert ( Natürlich wird die Abfrage anderer Tabellen nicht blockiert ). Wie unten gezeigt:

mysql> myDB verwenden;

Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen lesen

Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten

mysql> use MyDB;

Datenbank geändert

mysql> select * from test;

In der vierten Sitzung/Verbindung verwenden wir show Processlist, um den Status aller Verbindungsthreads in der aktuellen Datenbank anzuzeigen. Sie werden sehen, dass sich 17 und 18 im Status „Warten auf Tabellenlöschung“ befinden. Wie im Screenshot unten gezeigt:

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>

| 🎜> Sie müssen Thread 13 beenden. Das Beenden von Thread 17 wird das Problem nicht lösen.

Produktion In vielen Umgebungen ist es möglicherweise nicht die durch das Lesen der Sperrtabelle verursachte Blockierung, sondern die langsame Abfrage, die dazu führt, dass die Flush-Tabelle die Tabelle nicht schließen kann und sich im folgenden Testfall immer im Wartezustand befindet Ich verwende dieselbe große Tabelle, um das kartesische Produkt zu simulieren. Eine langsame Abfrage, andere Vorgänge sind die gleichen, wie unten gezeigt, Sie werden sehen, dass auch Warten auf Tabellenspülung

generiert wird

mysql> WÄHLEN Sie T.* AUS TEST1 T, TEST1 L;

Außerdem gibt es einen Fall im Internet, wenn mysqldump gesichert wird, wenn die Parameter nicht verwendet werden –Einzeltransaktion oder aufgrund der gleichzeitigen Verwendung von Flush-Logs und –Die beiden Parameter einer Einzeltransaktion können ebenfalls ein solches Warteszenario verursachen. Wenn diese beiden Parameter zusammengefügt werden, wird eine FLUSH TABLES-Operation ausgeführt, bevor mit dem Dump von Daten begonnen wird.

Lösung:

Wenn „Warten auf Tabellenlöschung“ angezeigt wird, müssen wir im Allgemeinen die Tabellen finden, die gesperrt sind, oder die langsamen Abfragen, die dazu führen, dass die Tabellenspülung wartet und die Tabelle nicht geschlossen werden kann. Beenden Sie dann einfach den entsprechenden Thread, aber es ist eine Herausforderung, ihn genau zu lokalisieren, insbesondere in einer Produktionsumgebung. Wenn Sie show Processlist verwenden, werden Sie eine große Anzahl von Threads sehen. Da wird einem schwindelig, wie kann man das Problem sofort lokalisieren?

Für langsam Situation, in der sich andere Threads aufgrund einer Abfrage im Zustand „Warten auf Tabellenlöschung“ befinden:

Sie können die Threads mit einem großen Zeitwert in der Prozessliste anzeigen. Anschließend nach Überprüfung und Bestätigung beenden. Wie im obigen Screenshot gezeigt, ist Sitzungsverbindung 14 die Quell-SQL, die die Blockierung verursacht. Es gibt eine Regel, dass der Zeitspaltenwert dieses Threads höher sein muss als der des blockierten Threads. Dadurch können viele Datensätze gefiltert werden.

Für Sperre Die Situation, in der sich andere Threads im Status „Warten auf Tabellenlöschung“ befinden, wird durch die Tabelle verursacht:

Für den Fall, dass das Lesen der Sperrtabelle im Experiment verwendet wird, befindet sich diese Sitzung möglicherweise im Ruhezustand und wird nicht in der Ausgabe des Befehls show engine innodb status G angezeigt. Auch wenn show open tables where in_use >=1; herausfinden kann, welche Tabelle gesperrt ist, kann der spezifische Thread (die Verbindung) nicht gefunden werden. Aber inntop kann zum Auffinden verwendet werden. Wie unten gezeigt, hat Thread 17 den Tabellentest gesperrt und Thread 17 kann in innotop gefunden werden. Wie das Sprichwort sagt: Wenn Sie Ihre Arbeit gut machen wollen, müssen Sie zuerst Ihre Werkzeuge schärfen!

Außerdem in der offiziellen Dokumentation ALTER TABLE, TABELLE UMBENENNEN, TABELLE REPARIEREN, ANALYSE TABLE oder OPTIMIZE TABLE können dies alles verursachen Art des Wartens. , einige einfache Tests wurden auch unten durchgeführt, wie unten gezeigt:

Ein weiteres Szenario des Wartens auf die Tischreinigung

会话连接（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>

Das obige ist der detaillierte Inhalt vonAnalyse des MySQL-Threads in Waiting for Table Flush. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!