Mysql timeout and mysqltimeout

Mysql timeout and mysqltimeout
Mysql timeout

------ By bibimbap

1. How much does the timeout variable know?

Open mysql and useshow variables like '%timeout%'The command looks like I don't know, but I was shocked. The result is shown below. With so many timeout variables, I'm scared .. The original understanding of mysql was not enough. Well, what exactly do these timeouts mean? It took me one afternoon to study, and I did a few small experiments and finally got to know something, in case of any errors, please do not hesitate to inform us.

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

Next we will find some commonly used timeout methods to analyze them one by one.

2.1 connect_timeout

Connect_timeout refers to the handshake timeout during the connection process. The default value is 10 seconds after 5.0.52, and the default value is 5 seconds in earlier 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 is that there is a listening thread that receives requests cyclically. When there is a request, create a thread (or obtain it from the thread pool) to process the request. Because the mysql connection uses the TCP protocol, TCP three-way handshake is required before. After the TCP three-way handshake is successful, the client is blocked and waits for messages from the server. At this time, the server creates a thread (or obtains a thread from the thread pool) to process requests. The main verification part includes host and user name and password verification. We are familiar with host verification because the specified host is used to authorize users by using the grant command. User name and password authentication means that the server sends a random number to the client. The client uses the random number and password for multiple sha1 encryption and then sends it to the server for verification. If the connection passes, the entire handshake process is completed. (Find the materials for further analysis in the handshake process)

It can be seen that there may be various possible errors in the whole handshake. Therefore, the connect_timeout value indicates the timeout time. In a simple test, run the following telnet command and you will find that the client will return timeout after 10 seconds.

telnet localhost 3306

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

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

2.2 interactive_timeout & wait_timeout

Let's take a look at the official documentation. From the documentation, wait_timeout and interactive_timeout both indicate inactive connection timeout times, when the connection line starts, wait_timeout is set to one of the two values based on the interaction mode or non-interaction mode. If we runmysql -uroot -pAfter logging on to mysql, wait_timeout is set to the value of interactive_timeout. If we do not perform any operation within the wait_timeout time, the system will prompt timeout when the operation is performed again. This is because 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; # set interaction timeout to 3 seconds

Enter mysql again. At this time, you can see:

Mysql> show variables like '% timeout % '; # wait_timeout has been set to 3 seconds + hour + ---------- + | Variable_name | Value | + hour + ---------- + | connect_timeout | 10 | delayed_insert_timeout | 300 | hour | 1 | latency | 50 | latency | OFF | interactive_timeout | 3 | lock_wait_timeout | 31536000 | net_read_timeout | 30 | net_write_timeout | 3 | latency | 31536000 | slave_net_timeout | 3600 | wait_timeout | 3 | + --------------------------- + ---------- +

We can see that wait_timeout is set to the value of interactive_timeout. In this way, we will execute other commands three seconds later, and the prompt is as follows:

Mysql> show variables like '% timeout %'; ERROR 2006 (HY000): MySQL server has gone away # reconnect to No connection upon timeout. trying to reconnect... connection id: 50 Current database: * ** NONE ** + bytes + ---------- + | Variable_name | Value | + bytes + ---------- + | connect_timeout | 10 | delayed_insert_timeout | 300 | bytes | 1 | innodb_lock_wait_timeout | 50 | bytes | OFF | interactive_timeout | 3 | lock_wait_timeout | 31536000 | net_read_timeout | 30 | net_write_timeout | 3 | bytes | 31536000 | slave_net_timeout | 3600 | wait_timeout | 3 | + ----------------------------- + ---------- +

2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout

In this document, the value is for the innodb engine and is the waiting time-out period of the innodb row lock. The default value is 50 seconds. If it times out, the current statement will be rolled back. If innodb_rollback_on_timeout is set, the entire transaction is rolled back. Otherwise, only the Statement of the transaction waiting 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, for testing (create an innodb Engine table test with only one column and column name ):

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

First, insert three test data items.

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

Currently, innodb_rollback_on_timeout = OFF and innodb_lock_wait_timeout = 1 is set. We start two transactions.

# 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)

# Transaction 2, request row lock 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; # request row Lock timeout 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; # Here we start another transaction directly, then the original transaction will only roll back the second statement, and the final result is that only 2 and 3 are left in the test table. if the ro shown here Llback, the entire transaction will be rolled back and will remain unchanged for 1, 2, 3.

If innodb_rollback_on_timeout = ON, transaction 2 times out, but if we start a new transaction in begin, the entire transaction with the request lock timeout will be rolled back, instead of rolling back the statement that times out as before.

2.4 lock_wait_timeout

The description in this document is as follows. To put it simply, lock_wait_timeout is the metadata lock wait timeout. This timeout parameter is used in any metadata lock statement. The default value is one year. Metadata locks can be used in mysql metadata locks. To ensure that transactions are serializable, No matter myisam or innodb Engine tables, metadata locks of Operation tables are obtained as long as a transaction is started, at this time, if another transaction needs to modify the table metadata, it will block until the 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 the myisam_test table of the myisam engine for testing. There is a record (). Now we start a transaction and then execute a select statement. Open another session and perform metadata operations on the table. For example, if you delete the table, you will find that the operation is blocked until lock_wait_timeout seconds and the system prompts timeout.

# First session, obtain metadata lockmysql> show create table myisam_test; tables + | Table | Create Table | + tables | 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) # Another session, table deletion prompts timeout mysql> drop table myisam_test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The metadata operation commands for changing the table structure include the following:

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

Of course, let's say that the myisam Table locks and concurrent inserts are very detailed in this blog. If you are interested, please take a look.

2.5 net_read_timeout & net_write_timeout

The description in this document is as follows, that is, these two parameters work when the network conditions are poor. For example, I use the load data infile method on the client to import a large file to the database, and then use iptables to disable port 3306 of mysql, at this time, the connection status on the server is reading from net, and the connection is closed after net_read_timeout. Similarly, when you query a large table in a program, the port is also disabled during the query process to make the network unavailable, so that the connection status is writing to net, disable the connection after net_write_timeout. Slave_net_timeout is 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 120mdata file data.txt. Then log on to mysql.

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

During the import process, set iptables to 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 the connection is closed after net_read_timeout seconds.

3. Summary

After several experiments, we can find that connect_timeout works in the handshake authentication phase (authenticate) and interactive_timeout works in the idle connection phase (sleep, net_read_timeout and net_write_timeout work when the connection is busy (query) or the network is faulty.

4. References

• Mysql timeout survey and example
• Mysql timeout Parsing
• Mysql system variables