Explorer la variable de délai d'attente MySQL

Tutoriel vidéo mysqlLa colonne présente la variable timeout

Recommandé (gratuit) : Tutoriel vidéo MySQL

1. Que savez-vous de la variable timeout

Ouvrez MySQL avec

Lorsque vous regardez la commande, vous ne savez pas ce que c'est et vous êtes choqué quand vous la regardez. Les résultats sont les suivants. Il y a tellement de variables liées au délai d'attente, et vous. sont morts de peur. . Il s'avère que ma compréhension de MySQL n'est pas suffisante. 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. show variables like '%timeout%'

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_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

2. Analyse

Trouvons quelques-uns couramment utilisés à partir du délai d'attente 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 qu'il existe un thread d'écoute qui reçoit les requêtes en boucle. Lorsqu'une requête arrive, un thread est créé (ou extrait du pool de threads). pour traiter la 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 par nom d'utilisateur et 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 les 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

Regardons d'abord la documentation officielle, de 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

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. mysql -uroot -p

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秒

Entrez à nouveau dans 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 sur la valeur de interactive_timeout. De cette façon, nous avons 3 secondes. Après avoir exécuté d'autres commandes, l'invite suivante apparaîtra :

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

Il est préférable de se référer au fonctionnaire document en premier. À partir du document, cette valeur est pour innodb Le moteur est le 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;

Insérez d'abord trois éléments de test data

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)

rrree Puis si innodb_rollback_on_timeout=ON, la même transaction 2 expirera, mais à ce moment-là si nous commençons pour ouvrir une nouvelle transaction, le verrou de la demande sera annulé. La transaction entière expire, au lieu de simplement annuler l'instruction expirée comme avant.

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. . Par défaut pendant un an. 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.

##事务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不变。

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. De plus, ouvrez une session, puis effectuez des opérations de métadonnées sur la table, telles que la suppression de 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

其中更改表结构的元数据操作指令有如下这些：

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

当然，多说一句，对于myisam表的加锁以及并发插入等，这篇博客myisam表锁非常详细，有兴趣的可以看看。

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）或者网络出现问题时起作用。

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!