MySQL timeout knows how much

MySQL timeout knows how much

                         ——————by石锅拌饭

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 | Ten|| delayed_insert_timeout |  -|| innodb_flush_log_at_timeout | 1|| innodb_lock_wait_timeout |  -|| innodb_rollback_on_timeout | OFF|| interactive_timeout | 28800|| lock_wait_timeout | 31536000|| net_read_timeout |  -|| net_write_timeout |  -|| 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:

256 | unauthenticated user | localhost:60595NULLNULLfromNULL

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  ofSeconds theServer Waits forActivity onA noninteractive connectionbeforeClosingit. On thread startup, theSession Wait_timeout Value isInitialized from  the GlobalWait_timeout valueor  from  the GlobalInteractive_timeout value, depending on  theType ofClient ( asDefined by  theClient_interactive Connect option toMysql_real_connect ()).

The test is as follows:

setglobal interactive_timeout=3##设置交互超时为3秒

When you re-enter MySQL, you can see:

Mysql> Show variables like'%timeout% '; ##wait_timeouthas been set to3seconds +-----------------------------+----------+|variable_name|Value|+-----------------------------+----------+| connect_timeout | Ten|| delayed_insert_timeout |  -|| innodb_flush_log_at_timeout | 1|| innodb_lock_wait_timeout |  -|| innodb_rollback_on_timeout | OFF|| interactive_timeout | 3|| lock_wait_timeout | 31536000|| net_read_timeout |  -|| 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):MySQLServer has gone away # #超时重连NoConnection.TryingTo reconnect ...Connection ID:     - Current Database:***NONE***+-----------------------------+----------+|variable_name|Value|+-----------------------------+----------+| connect_timeout | Ten|| delayed_insert_timeout |  -|| innodb_flush_log_at_timeout | 1|| innodb_lock_wait_timeout |  -|| innodb_rollback_on_timeout | OFF|| interactive_timeout | 3|| lock_wait_timeout | 31536000|| net_read_timeout |  -|| 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.

Thelength  of  Time inchSeconds an InnoDBTransactionWaits forA row lockbeforeGiving up. The default value is  -Seconds. ATransaction  thatTries toAccess a row that  isLocked byAnother InnoDBTransactionWaits atMost this many seconds for WriteAccess to  theRowbeforeIssuing theFollowingError: ERROR1205(HY000): Lock waitTimeoutexceeded;TryRestartingTransaction

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

`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 inchSeconds forAttempts toAcquire metadata locks. The permissible values range from 1  to 31536000(1  Year). The default is 31536000.ThisTimeoutApplies toAll statements thatUse metadata locks. These include DML andDDL operations ontables, views, stored procedures, andStored functions, asWell asLock TABLES, FLUSH TABLES with READ LOCK, andHANDLER statements

Test examples:
We test with a MyISAM engine's table myisam_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, get metadata lockmysql> Show CREATE table myisam_test;-----------------------------------------------------------+| Table |                                                                                                                                Create Table |+-----------------------------------------------------------| MyISAM_test | CREATE TABLE ' Myisam_Test ' (' i ' int (one) is not NULL,' j ' int (one) 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, delete table hint timeout 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:

......... 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  ofSeconds toWait forMore data fromA connectionbeforeAborting the Read. When theServer isReading from  theClient, Net_read_timeout is  the TimeoutValue Controlling when toAbort. When theServer isWriting to  theClient, Net_write_timeout is  the TimeoutValue Controlling when toAbort

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

-uroot-h127.0.0.1-P3306--local-infile=1

Import process Settings iptables disable Port 3306.

-A-p--3306-j-A-p--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