Erkundung des Timeouts in MySQL

1. Wie viel wissen Sie über die Timeout-Variable?

Öffnen Sie MySQL und werfen Sie einen Blick darauf. Sie werden es nicht wissen Wenn Sie es sich ansehen, sind die Ergebnisse wie folgt: Es gibt so viele Timeout-Variablen, dass ich gepinkelt habe. . Es stellt sich heraus, dass mein Verständnis von MySQL nicht ausreicht. Nun, was bedeuten diese Auszeiten? Ich habe einen Nachmittag damit verbracht, ein paar kleine Experimente durchzuführen, und habe schließlich ein oder zwei Dinge verstanden erleuchte mich. 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_ti、eout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

2. Analyse

Lassen Sie uns einige der am häufigsten verwendeten im Timeout finden und sie einzeln analysieren.

2.1 connect_timeout

connect_timeout bezieht sich auf das Handshake-Timeout während des Verbindungsprozesses. Der Standardwert beträgt 10 Sekunden nach 5.0.52 und der Standardwert beträgt 5 Sekunden. Im offiziellen Dokument heißt es:

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

Das Grundprinzip von MySQL sollte darin bestehen, einen Listening-Thread

Schleife zu haben, um Anfragen zu empfangen Thread-Pool), um diese Anfrage zu bearbeiten. Da die MySQL-Verbindung das TCP-Protokoll verwendet, muss zuvor ein TCP-Drei-Wege-Handshake durchgeführt worden sein. Nachdem der TCP-Drei-Wege-Handshake erfolgreich war, wechselt der Client in die Blockierung und wartet auf Nachrichten vom Server. Zu diesem Zeitpunkt erstellt der Server einen Thread (oder nimmt einen Thread aus dem Thread-Pool), um die Anforderung zu verarbeiten. Der Hauptüberprüfungsteil umfasst die Überprüfung des Hosts, des Benutzernamens und des Kennworts. Wir sind mit der Host-Überprüfung vertraut, da der Host angegeben wird, wenn der Grant-Befehl zur Autorisierung von Benutzern verwendet wird. Für die Authentifizierung mit Benutzername und Passwort generiert der Server zunächst eine Zufallszahl und sendet sie an den Client. Der Client verwendet die Zufallszahl und das Passwort, um mehrere sha1-Verschlüsselungen durchzuführen und sendet sie dann zur Überprüfung an den Server. Bei Übergabe ist der gesamte Verbindungs-Handshake-Prozess abgeschlossen. (Der spezifische Handshake-Prozess wird später gefunden und analysiert)

Es ist ersichtlich, dass im gesamten Verbindungs-Handshake verschiedene mögliche Fehler auftreten können. Der Wert connect_timeout bezieht sich also auf die Timeout-Zeit. Sie können es einfach testen, indem Sie den folgenden Telnet-Befehl ausführen. Sie werden feststellen, dass der Client eine Zeitüberschreitung erfährt und nach 10 Sekunden zurückkehrt.

telnet localhost 3306

Der Verbindungsstatus

in MySQL vor dem Timeout lautet wie folgt:

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

2.2 Interactive_timeout & wait_timeout

Lesen wir die offizielle Dokumentation Erstens: Aus der Dokumentation geht hervor, dass „wait_timeout“ und „ Interactive_timeout“ beide auf das Zeitlimit für inaktive Verbindungen verweisen. Wenn der Verbindungsthread gestartet wird, wird „wait_timeout“ auf einen dieser beiden Werte gesetzt, je nachdem, ob es sich um einen interaktiven Modus oder einen nicht interaktiven Modus handelt. Wenn wir den Befehl

ausführen, um uns bei MySQL anzumelden, wird wait_timeout auf den Wert von Interactive_timeout gesetzt. Wenn wir innerhalb der Wait_Timeout-Zeit keine Operation ausführen, wird bei der erneuten Operation eine Zeitüberschreitung angezeigt, was bedeutet, dass der MySQL-Client erneut eine Verbindung herstellt. 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()).

Der Test ist wie folgt:

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

Geben Sie MySQL erneut ein und Sie können Folgendes sehen:

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        |
+-----------------------------+----------+

Sie können sehen, dass wait_timeout festgelegt ist Wenn wir auf diese Weise nach 3 Sekunden andere Befehle ausführen, lautet die Eingabeaufforderung wie folgt:

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

Gehen wir zum Beamten Aus dem Dokument geht hervor, dass dieser Wert für die Innodb-Engine die Wartezeit für Zeilensperren in Innodb ist. Bei einer Zeitüberschreitung wird die aktuelle Anweisung zurückgesetzt. Wenn innodb_rollback_on_timeout festgelegt ist, wird die gesamte Transaktion zurückgesetzt. Andernfalls wird nur die Anweisung zurückgesetzt, in der die Transaktion auf die Zeilensperre wartet.

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

Testen wir es auf die gleiche Weise (erstellen Sie zunächst einen Tabellentest in der Innodb-Engine mit nur einer Spalte und dem Spaltennamen a):

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

Erste Einfügung drei Testdaten

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

Derzeit ist innodb_rollback_on_timeout=OFF, innodb_lock_wait_timeout=1 gesetzt, wir öffnen zwei Transaktionen

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

Wenn dann innodb_rollback_on_timeout=ON, die gleiche Transaktion 2 Es kommt zu einer Zeitüberschreitung, aber wenn wir zu diesem Zeitpunkt beginnen, eine neue Transaktion zu starten, wird die gesamte Transaktion, die die Sperrzeitüberschreitung angefordert hat, zurückgesetzt, anstatt wie zuvor nur die Anweisung zurückzusetzen, bei der die Zeitüberschreitung abgelaufen ist.

2.4 lock_wait_timeout

Das Dokument beschreibt es einfach wie folgt: Dieser Timeout-Parameter wird in jeder Anweisung verwendet, die Metadaten sperrt . Metadatensperren können an der MySQL-Metadatensperre beteiligt sein. Um sicherzustellen, dass Transaktionen serialisiert werden können, unabhängig davon, ob es sich um eine Tabelle in Myisam oder in der Innodb-Engine handelt, wird die Metadatensperre der Operationstabelle aktiviert, solange eine Transaktion in einer Sitzung geöffnet wird Wenn zu diesem Zeitpunkt eine andere Sitzung die Metadaten der Tabelle ändern möchte, wird sie bis zum Timeout blockiert.

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

Testbeispiel:

Wir verwenden zum Testen eine Myisam-Engine-Tabelle myisam_test. Es gibt einen Datensatz (1,1). Jetzt öffnen wir eine Sitzung und führen dann eine Select-Anweisung aus. Öffnen Sie außerdem eine Sitzung und führen Sie dann Metadatenoperationen für die Tabelle durch, z. B. das Löschen der Tabelle
. Sie werden feststellen, dass der Vorgang blockiert ist, bis nach lock_wait_timeout Sekunden eine Zeitüberschreitung auftritt.

Die Metadaten-Operationsanweisungen zum Ändern der Tabellenstruktur lauten wie folgt:

##第一个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

Natürlich noch etwas zum Sperren und gleichzeitigen Einfügen der Myisam-Tabelle, dies Artikel Der Blog Myisam Table Lock ist sehr detailliert, wenn Sie interessiert sind, können Sie einen Blick darauf werfen.

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

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. 数据库设计那些事

Das obige ist der detaillierte Inhalt vonErkundung des Timeouts in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!