Analyse du thread MySQL dans En attente du vidage de la table

J'ai récemment rencontré un cas et j'en ai reçu de nombreux requêtes Aucun résultat n'est renvoyé en raison du blocage. Utilisez show processlist pour vérifier et trouver que de nombreux threads MySQL sont dans l'état En attente de vidage de la table. L'instruction de requête a été bloquée et ne peut être résolue qu'en interrompant le processus. Jetons donc d'abord un coup d'œil à l'explication officielle de Waiting for table flush : https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

En attente de chasse d'eau à la table

Le thread est en cours d'exécution FLUSH TABLES et attend la fermeture de tous les threads leurs tables, ou le thread a reçu une notification indiquant que la structure sous-jacente d'une table a changé et qu'il doit rouvrir la table pour obtenir la nouvelle structure. Cependant, pour rouvrir la table, il doit attendre que tous les autres threads aient fermé la table. question

Cette notification a lieu si un autre fil de discussion a utilisé FLUSH TABLES ou une des mentions suivantes sur la table en question : FLUSH TABLES tbl_name, ALTER TABLE, RENOMMER LA TABLE, TABLE DE RÉPARATION, ANALYSER LA TABLE, ou OPTIMISER TABLE.

Ensuite, simulons que le fil est dans la table En attente L'état du rinçage est le suivant :

Lors de la première connexion de session (identifiant de connexion=13), nous utilisons lock table pour verrouiller la table de test.

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>

Dans la deuxième connexion de session (identifiant de connexion = 17), nous pouvons exécuter un test de table de vidage ou un test de table de vidage. À ce stade, vous constaterez que la table de vidage est bloquée.

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;

 

 

 

 

Lors de la troisième session/connexion, lorsque vous passez à MyDB, vous serez invité "Vous pouvez désactiver cette fonctionnalité pour obtenir un démarrage plus rapide avec -A" , qui est actuellement en état de blocage. À ce moment, vous quittez la session et utilisez le paramètre -A pour vous connecter à la base de données. Si vous interrogez la table de test, elle sera bloquée ( Bien entendu, l'interrogation d'autres tables ne sera pas bloquée ). Comme indiqué ci-dessous :

mysql> utiliser MyDB ;

Lire les informations de la table pour compléter les noms des tables et des colonnes

Vous pouvez désactiver cette fonctionnalité pour obtenir un démarrage plus rapide avec -A

mysql> utiliser MyDB ;

Base de données modifiée

mysql> >

Dans la quatrième session/connexion, nous utilisons show processlist pour afficher l'état de tous les threads de connexion dans la base de données actuelle. Vous verrez que 17 et 18 sont dans l'état En attente de vidage de la table. Comme le montre la capture d'écran ci-dessous :

 

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>

Vous devez tuer le fil 13. Tuer le fil 17 ne résoudra pas le problème.

Production Dans de nombreux environnements, il se peut que ce ne soit pas le blocage provoqué par la lecture de la table de verrouillage, mais la requête lente, qui empêche la table de vidage de fermer la table et soit toujours dans un état d'attente. Par exemple, dans le scénario de test ci-dessous. , J'utilise la même grande table pour simuler le produit cartésien. Une requête lente, les autres opérations sont les mêmes, comme indiqué ci-dessous, vous verrez que l'attente du vidage de la table est également générée.

 

mysql> SELECT T.* FROM TEST1 T, TEST1 L;

 

 

 

De plus, il existe un cas sur Internet Lorsque mysqldump est sauvegardé, si les paramètres ne sont pas utilisés . —transaction unique ou en raison de l'utilisation simultanée de flush-logs et de —Les deux paramètres de transaction unique peuvent également provoquer un tel scénario d'attente. Lorsque ces deux paramètres sont réunis, une opération FLUSH TABLES sera effectuée avant de commencer à vider les données.

Solution :

Lorsque Waiting for table flush apparaît, nous devons généralement trouver les tables qui sont verrouillées ou les requêtes lentes qui font que la table de vidage est en attente et incapable de fermer la table. Ensuite, tuez simplement le thread correspondant, mais comment le localiser avec précision est un défi, en particulier dans un environnement de production, si vous utilisez show processlist, vous verrez un grand nombre de threads. Cela donne le vertige, comment localiser le problème tout de suite ?

Pour le lent Situation où d'autres threads sont dans l'état En attente de vidage de la table provoqué par une requête :

Vous pouvez afficher les fils de discussion avec une grande valeur Time dans la liste des processus d'affichage. Puis tuez après contrôle et confirmation. Comme le montre la capture d'écran ci-dessus, la connexion de session 14 est le SQL source qui provoque le blocage. Il existe une règle selon laquelle la valeur de la colonne Heure de ce thread doit être supérieure à celle du thread bloqué. Cela peut filtrer de nombreux enregistrements.

Pour le verrouillage La situation dans laquelle d'autres threads sont dans l'état En attente de vidage de la table provoquée par la lecture de la table :

Dans le cas où la lecture de la table de verrouillage est utilisée dans l'expérience, cette session peut être en état de veille et elle n'apparaîtra pas dans la sortie de la commande show engine innodb status G. Même si show open tables which in_use >=1; peut découvrir quelle table est verrouillée, il ne peut pas localiser le thread spécifique (connexion). En fait, c'est un casse-tête. Mais inntop peut être utilisé pour le localiser. Comme indiqué ci-dessous, le thread 17 a verrouillé le test de la table et le thread 17 peut être localisé dans innotop. Comme le dit le proverbe, si vous voulez bien faire votre travail, vous devez d’abord affûter vos outils !

De plus, dans la documentation officielle, ALTER TABLE, RENOMMER LA TABLE, TABLE DE RÉPARATION, ANALYZE TABLE, ou OPTIMIZE TABLE peuvent tous provoquer cela sorte d'attente. , quelques tests simples ont également été effectués ci-dessous, comme indiqué ci-dessous :

Un autre scénario d'Attente de chasse d'eau de la 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>

 

 

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!