Explorer le délai d'attente dans MySQL

1. Que savez-vous de la variable timeout ?

Ouvrez mysql et utilisez la commande show variables like '%timeout%' pour y jeter un œil. Vous ne le saurez pas tant que vous ne l'aurez pas regardé. quand vous le regardez, le résultat est comme indiqué ci-dessous. Il y a tellement de variables liées au délai d'attente. J'avais tellement peur que j'ai fait pipi. . Il s'avère que ma compréhension de MySQL est si insuffisante. Eh bien, que signifient ces délais d'attente ? J'ai passé un après-midi à étudier et fait quelques petites expériences, et j'ai finalement compris une chose ou deux. S'il y a des erreurs, n'hésitez pas à le faire. éclaire-moi ah.

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_ti、eout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

2. Analyse

Trouvons quelques-uns des plus couramment utilisés dans timeout et analysons-les un par un.

2.1 connect_timeout

connect_timeout fait référence au délai d'expiration de la négociation pendant le processus de connexion. La valeur par défaut est de 10 secondes après la version 5.0.52, et la valeur par défaut est de 5 secondes dans les versions précédentes. Le document officiel dit ceci :

connect_timeout: The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.0.52 and 5 seconds before that

Le principe de base de mysql devrait être d'avoir un fil d'écoute boucle pour recevoir les requêtes Lorsqu'une requête arrive, créez un fil (ou depuis le. pool de threads) pour gérer cette demande. Étant donné que la connexion MySQL utilise le protocole TCP, une négociation TCP à trois voies doit avoir été effectuée auparavant. Une fois la négociation à trois TCP réussie, le client entrera en blocage et attendra les messages du serveur. À ce stade, le serveur créera un thread (ou prendra un thread du pool de threads) pour traiter la demande. La partie de vérification principale comprend la vérification de l'hôte, du nom d'utilisateur et du mot de passe. Nous sommes familiers avec la vérification de l'hôte, car l'hôte est spécifié lors de l'utilisation de la commande grant pour autoriser les utilisateurs. Pour l'authentification du nom d'utilisateur et du mot de passe, le serveur génère d'abord un nombre aléatoire et l'envoie au client. Le client utilise le numéro aléatoire et le mot de passe pour effectuer plusieurs cryptages sha1, puis l'envoie au serveur pour vérification. S’il est réussi, l’ensemble du processus d’établissement de liaison de connexion est terminé. (Le processus spécifique de prise de contact sera trouvé et analysé plus tard)

On peut voir qu'il peut y avoir diverses erreurs possibles dans l'ensemble de la prise de contact de connexion. Ainsi, la valeur connect_timeout fait référence au délai d'expiration. Vous pouvez simplement le tester en exécutant la commande telnet suivante et vous constaterez que le client expirera et reviendra après 10 secondes.

telnet localhost 3306

L'état de la connexion dans mysql avant l'expiration du délai est le suivant :

256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL

2.2 interactive_timeout & wait_timeout

Lisons la documentation officielle Tout d'abord, dans la documentation, wait_timeout et interactive_timeout font tous deux référence au délai d'expiration de la connexion inactive. Lorsque le thread de connexion démarre, wait_timeout sera défini sur l'une de ces deux valeurs selon qu'il s'agit d'un mode interactif ou d'un mode non interactif. Si nous exécutons la commande mysql -uroot -p pour nous connecter à MySQL, wait_timeout sera défini sur la valeur de interactive_timeout. Si nous n'effectuons aucune opération dans le délai wait_timeout, un délai d'attente sera demandé lors de la nouvelle opération, ce qui signifie que le client MySQL se reconnectera.

The number of seconds the server waits for activity on a noninteractive connection before closing it.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).

Le test est le suivant :

mysql> set global interactive_timeout=3; ##设置交互超时为3秒

Ressaisissez mysql, et vous pouvez voir :

mysql> show variables like '%timeout%'; ##wait_timeout已经被设置为3秒
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

Vous pouvez voir que wait_timeout est défini à la valeur de interactive_timeout , de cette façon, si nous exécutons d'autres commandes après 3 secondes, l'invite sera la suivante :

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away  ##超时重连
No connection. Trying to reconnect...
Connection id:    50
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout

Passons à l'officiel document en premier. D'après le document, cette valeur est Pour le moteur innodb, il s'agit du délai d'attente pour les verrous de ligne dans innodb. La valeur par défaut est de 50 secondes. Si le délai expire, l'instruction actuelle est annulée. Si innodb_rollback_on_timeout est défini, la transaction entière sera annulée. Sinon, seule l'instruction dans laquelle la transaction attend le verrou de ligne sera annulée.

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Testons-le de la même manière (créons d'abord un test de table dans le moteur innodb, avec une seule colonne et le nom de la colonne est un) :

mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;

Première insertion trois données de test

mysql> select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |

Actuellement innodb_rollback_on_timeout=OFF, définissez innodb_lock_wait_timeout=1, nous ouvrons deux transactions

##事务1 加行锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where a=2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.01 sec)

##事务2，请求行锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where a=1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test where a=2; ##请求行锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test;
+---+
| a |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)

mysql> begin; ##
这里我们直接开启另外的事务(或者直接commit当前事务)，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。

Puis si innodb_rollback_on_timeout=ON, la même transaction 2 expirera, mais à ce moment-là, si nous commençons Lorsqu'une nouvelle transaction est démarrée, la transaction entière qui a demandé le délai d'expiration du verrouillage sera annulée, au lieu de simplement annuler l'instruction qui a expiré comme auparavant.

2.4 lock_wait_timeout

Le document le décrit comme suit, lock_wait_timeout est le délai d'attente du verrouillage des métadonnées. Ce paramètre de délai d'attente sera utilisé dans toute instruction qui verrouille les métadonnées. . Les verrous de métadonnées peuvent participer au verrouillage des métadonnées mysql. Afin de garantir que les transactions peuvent être sérialisées, qu'il s'agisse d'une table dans le moteur myisam ou innodb, tant qu'une transaction est ouverte dans une session, le verrou des métadonnées de la table d'opération sera. obtenu. À ce moment, si Si une autre session souhaite modifier les métadonnées de la table, elle se bloquera jusqu'à l'expiration du délai.

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements

Exemple de test :
Nous utilisons une table de moteur myisam myisam_test pour tester. Il y a un enregistrement (1,1). Maintenant, nous ouvrons une session puis exécutons une instruction select. Ouvrez également une session, puis effectuez des opérations de métadonnées sur la table, telles que supprimant la table . Vous constaterez que l'opération est bloquée jusqu'à ce qu'un délai d'attente se produise après lock_wait_timeout secondes.

##第一个session，获取metadata lock
mysql> show create table myisam_test;
-----------------------------------------------------------+
| Table       | Create Table|
+-----------------------------------------------------------
| myisam_test | CREATE TABLE `myisam_test` (
  `i` int(11) NOT NULL,
  `j` int(11) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_test;
+---+------+
| i | j    |
+---+------+
| 2 |    1 |
+---+------+
1 row in set (0.00 sec)

##另一个session，删除表提示超时
mysql> drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Les instructions d'opération des métadonnées pour changer la structure de la table sont les suivantes :

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE；

Bien sûr, encore une chose, pour le verrouillage et l'insertion simultanée de la table myisam, ceci article Le blog myisam table lock est très détaillé, si vous êtes intéressé, vous pouvez y jeter un oeil.

2.5 net_read_timeout & net_write_timeout

文档中描述如下，就是说这两个参数在网络条件不好的情况下起作用。比如我在客户端用load data infile的方式导入很大的一个文件到数据库中，然后中途用iptables禁用掉mysql的3306端口，这个时候服务器端该连接状态是reading from net，在等待net_read_timeout后关闭该连接。同理，在程序里面查询一个很大的表时，在查询过程中同样禁用掉端口，制造网络不通的情况，这样该连接状态是writing to net，然后在net_write_timeout后关闭该连接。slave_net_timeout类似。

The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort

测试：
我创建一个120M的数据文件data.txt。然后登陆到mysql。

mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1

导入过程设置iptables禁用3306端口。

iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

可以看到连接状态为reading from net，然后经过net_read_timeout秒后关闭。

3.总结

经过几个实验可以发现，connect_timeout在握手认证阶段（authenticate）起作用，interactive_timeout 和wait_timeout在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）或者网络出现问题时起作用。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

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!