Exploring timeout in mysql

1. How much do you know about timeout variables?

Open mysql and use the show variables like '%timeout%' command to take a look. You won’t know if you don’t look at it. You will be shocked when you look at it. The results are as follows As shown above, there are so many timeout related variables, which can scare you to death. . It turns out that my understanding of mysql is so insufficient. Well, what do these timeouts mean? I spent an afternoon studying and did a few small experiments, and finally understood a thing or two. If there are any mistakes, please feel free to enlighten me. 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. Analysis

Let’s find some of the more commonly used ones in timeout and analyze them one by one.

2.1 connect_timeout

connect_timeout refers to the handshake timeout during the connection process. The default is 10 seconds after 5.0.52, and the default is 5 seconds in previous versions. The official document says this:

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

The basic principle of mysql should be that there is a listening thread Loop to receive requests. When a request comes, create a thread (or take it from the thread pool) to handle this request. Since the mysql connection uses the TCP protocol, a TCP three-way handshake must have been performed before. After the TCP three-way handshake is successful, the client will enter blocking and wait for messages from the server. At this time, the server will create a thread (or take a thread from the thread pool) to process the request. The main verification part includes host and username and password verification. We are familiar with host verification, because the host is specified when using the grant command to authorize users. For username and password authentication, the server first generates a random number and sends it to the client. The client uses the random number and password to perform sha1 encryption multiple times and then sends it to the server for verification. If passed, the entire connection handshake process is completed. (The specific handshake process will be found and analyzed later)

It can be seen that there may be various possible errors in the entire connection handshake. So the connect_timeout value refers to the timeout time. You can simply test it by running the following telnet command and you will find that the client will time out and return after 10 seconds.

telnet localhost 3306

The connection status in mysql before timeout is as follows:

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

2.2 interactive_timeout & wait_timeout

Let’s look at the official documentation first, from the documentation Both wait_timeout and interactive_timeout refer to the inactive connection timeout. When the connection thread starts, wait_timeout will be set to one of these two values ​​depending on whether it is interactive mode or non-interactive mode. If we run the mysql -uroot -p command to log in to mysql, wait_timeout will be set to the value of interactive_timeout. If we do not perform any operation within the wait_timeout time, a timeout will be prompted when we operate again, which means the mysql client will reconnect.

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()).

The test is as follows:

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

Re-enter mysql, you can see:

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

You can see that wait_timeout is set to the value of interactive_timeout, so, we have 3 seconds Then execute other commands, the following prompt will appear:

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

It is better to refer to the official document first. From the document, this value is for the innodb engine and is innodb. The wait timeout for the lock, the default is 50 seconds. If it times out, the current statement is rolled back. If innodb_rollback_on_timeout is set, the entire transaction will be rolled back. Otherwise, only the statement in which the transaction is waiting for the row lock will be rolled back.

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

Let’s test it in the same way (first create a table test in the innodb engine, with only one column, and the column name is a):

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

First insert three pieces of test data

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

Currently innodb_rollback_on_timeout=OFF, set innodb_lock_wait_timeout=1, we open two 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不变。

Then if innodb_rollback_on_timeout=ON, the same transaction 2 will time out, but at this time if we begin to open a new transaction, the request lock will be rolled back The entire transaction times out, instead of just rolling back the timed out statement like before.

2.4 lock_wait_timeout

The document describes it as follows. Simply speaking, lock_wait_timeout is the metadata lock waiting timeout. This timeout parameter will be used in any statement that locks metadata. The default is one year. Metadata locks can participate in mysql metadata lock. In order to ensure that transactions can be serialized, whether it is a table in myisam or innodb engine, as long as a transaction is opened in a session, the metadata lock of the operation table will be obtained. At this time, if If another session wants to modify the metadata of the table, it will block until timeout.

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

Test example:
We use a myisam engine table myisam_test to test. There is a record (1,1). Now we open a session and then execute a select statement. In addition, open a session and then perform table metadata operations, such as Delete table. You will find that the operation is blocked until a timeout occurs after lock_wait_timeout seconds.

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

The metadata operation instructions for changing the table structure are as follows:

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

Of course, one more thing, for myisam table locking and concurrent insertion, this blog myisam table lock It's very detailed, you can check it out if you're interested.

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

The above is the detailed content of Exploring timeout in mysql. For more information, please follow other related articles on the PHP Chinese website!