MySQL timeout knows how much

MySQL timeout know how many------by stone pot mix rice
How much does 1.timeout variable know?

Open MySQL, with the show variables like ‘%timeout%‘ command to see, do not see do not know, a look startled, the results as shown below, so many timeout related variables, a little scare urine. Originally the understanding of MySQL is so not enough, good, all these timeout what each meaning, spent an afternoon to learn, did a few small experiments, finally understand one or two, if there are errors, please enlighten me.

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. Analysis

Below from the timeout inside find some more commonly used out of the analysis.

2.1 Connect_timeout

Connect_timeout refers to the time-out of the handshake during the connection, the default is 10 seconds after 5.0.52, and the previous version defaults to 5 seconds. This is what the official document says:

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 listener thread that loops through the request, and when a request comes in, the thread is created (or taken from the thread pool) to handle the request. Since the MySQL connection uses the TCP protocol, the TCP three handshake is required before. After the TCP three handshake succeeds, the client enters the block and waits for a message from the server. The server will create a thread at this time (or take a thread from the thread pool) to process the request, and the primary validation section includes host and username password validation. Host authentication is familiar to us because you have specified host when authorizing a user with the grant command. The user name password authentication is the server terminal to a random number sent to the client, the client with the random number and password after multiple SHA1 encryption sent to the server authentication. If passed, the entire connection handshake process is complete. (The concrete handshake process to find data re-analysis)

This shows that the entire connection handshake may have various possible errors. So this connect_timeout value means this time-out. Under a simple test, running the following Telnet command will find that the client will time out after 10 seconds.

telnet localhost 3306

The connection status in MySQL before the timeout is as follows:

2.2 Interactive_timeout & Wait_timeout

Or look at the official documents, from the document to see Wait_timeout and interactive_timeout refers to the inactive connection time-out, when the connection thread starts Wait_timeout is set to one of these two values according to whether it is interactive or non-interactive mode. If we run the mysql -uroot -p command login to mysql,wait_timeout it will be set to the value of interactive_timeout. If we do not do anything within the wait_timeout time, we will be prompted to time out again, this is 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秒

When you 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 the wait_timeout is set to the value of interactive_timeout so that we can execute the other commands after 3 seconds, we will prompt the following:

mysql> show variables like ‘%timeout%‘;ERROR 2006 (HY000): MySQL server has gone away  ##超时重连No connection. Trying to reconnect...Connection id:    50Current 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

Or the first official document, from the document, this value is for the InnoDB engine, is the InnoDB row lock waiting time-out, 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 is rolled back, or only the transaction waits for the row lock is 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

Similarly to test (create a InnoDB engine's table test, with only one column, column named a):

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

Insert three test data first

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

Current 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 transactionmysql> select * from test;+---+| a |+---+| 2 || 3 |+---+2 rows in set (0.00 sec)mysql> begin; ##这里我们直接开启另外的事务，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。

So if Innodb_rollback_on_timeout=on, the same transaction 2 will time out, but this time if we begin to open a new transaction, then the entire transaction of the request lock timeout will be rolled back instead of just the one that rolled back the timeout as before.

2.4 Lock_wait_timeout

The document describes the following, simply said Lock_wait_timeout is the metadata lock wait time-out, Arbitrary lock metadata Statements will use this timeout parameter, the default is one year. Metadata lock can participate in MySQL metadata lock, in order to ensure that the transaction serializable, whether it is a MyISAM or InnoDB engine table, as long as the start of a transaction, will get the operation table metadata lock, this time if another transaction to the table metadata modification, will block until the timeout is exceeded.

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's table Myisam_test to test. There is one record, and now we start a transaction and then execute a SELECT statement. Also open a session, and then perform metadata operations on the table, such as deleting the table, you will notice that the operation is blocked until lock_wait_timeout seconds after the prompt timeout.

##第一个session，获取metadata lockmysql> 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=latin1mysql> 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 meta-data manipulation directives in which the table structure is changed are as follows:

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

Of course, more to say, for the MyISAM table locking and concurrent insertion, and so on, this blog MyISAM table lock is very detailed, interested can see.

2.5 Net_read_timeout & Net_write_timeout

It is described in the documentation that these two parameters work in the case of poor network conditions. For example, I use load data infile in the client to import a large file into the database, and then midway through the iptables disable MySQL 3306 port, this time the server side of the connection state is reading from net, waiting for Net_read_ Closes the connection after a timeout. Similarly, when querying a large table in a program, the same disables the port during the query process, making the network out of line, so that the connection state is writing to net, and then closes the connection after net_write_timeout. Slave_net_timeout similar.

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

Test: I created a 120M data file data.txt. Then log in to MySQL.

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

Import process Settings iptables disable Port 3306.

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

You can see that the connection status is reading from net and then close after net_read_timeout seconds.

3. Summary

After several experiments, connect_timeout in the Handshake Authentication phase (authenticate), Interactive_timeout and wait_timeout in the connection idle phase (sleep), and Net_read _timeout and Net_write_timeout work when the connection is busy (query) or the network is having problems.

4. References

• MySQL Timeout research and examples
• MySQL Timeout parsing
• MySQL System variables

MySQL timeout knows how much