Shulou(Shulou.com)06/01 Report--

Editor to share with you the example analysis of mysql timeout variables, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. How much do you know about timeout variables

Open mysql, use the show variables like'% timeout%' command to take a look, do not see do not know, a look startled, the result is shown below, so many timeout-related variables, a pee. It turns out that the understanding of mysql is not enough, well, what exactly do these timeout mean? I spent the whole afternoon studying and did a few small experiments. I finally understood one or two. If there are any mistakes, please do not hesitate to comment.

Mysql > show variables like'% timeout%' +-- +-+ | Variable_name | Value | +-+-+ | connect_timeout | 10 | | delayed_insert_timeout | | | 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 | +-+-+ |

two。 Analysis.

Let's find some more commonly used ones from timeout and analyze them one by one.

2.1 connect_timeout

Connect_timeout refers to the timeout of a handshake during a connection, which defaults to 10 seconds after 5.0.52, compared with 5 seconds in previous versions. 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 a listener thread loops to receive the request, and when a request comes, a thread is created (or fetched from the thread pool) to process the request. Because the mysql connection uses the TCP protocol, it is necessary to carry out the TCP three-way handshake before. After the TCP three-way handshake is successful, the client enters the block and waits for a message from the server. At this time, the server will create a thread (or take a thread from the thread pool) to process the request, and the main authentication part includes host and username password authentication. Host authentication is familiar to us because host is specified when authorizing users with the grant command. The authentication of user name and password is that the server sends it to the client as a random number, and the client uses the random number and password to encrypt sha1 many times and then sends it to the server for verification. If passed, the entire connection handshake process is completed. (after finding the data in the specific handshake process, we will analyze it)

Thus it can be seen that there may be various possible errors in the whole connection handshake. So the connect_ timeout value refers to the timeout. You can simply test that running the following telnet command will find that the client will return after 10 seconds of timeout.

Telnet localhost 3306

The connection status in mysql before timeout is as follows:

| 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 first. from the documentation, wait_timeout and interactive_timeout both refer to the inactive connection timeout. When the connection thread starts, wait_timeout will be set to one of these two values according to whether the connection thread is in interactive mode or non-interactive mode. If we run the mysql-uroot-p command to log in to mysql,wait_timeout, it will be set to the value of interactive_timeout. If we don't do anything within the wait_timeout time, we will be prompted to time out when we do it again, which means that 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 tests are as follows:

Mysql > set global interactive_timeout=3; # # set the interaction timeout to 3 seconds

Re-enter mysql, and you can see:

Mysql > show variables like'% timeout%' # # wait_timeout has been set to 3 seconds +-+-+ | 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 that when we execute other commands 3 seconds later, we will be prompted as follows:

Mysql > show variables like'% timeout%';ERROR 2006 (HY000): MySQL server has gone away # # timeout reconnect 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 first present the official document, from the document, this value is for the innodb engine, is the innodb row lock wait timeout, 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; otherwise, only the statement in which the transaction waits for a 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

Let's test it again (first create an innodb engine table test with only one column named 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 |

The current innodb_rollback_on_timeout=OFF, set innodb_lock_wait_timeout=1, and we start two transactions

# # transaction 1 add row lock mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from test where astat2 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 query OK, 1 row affected (0.00 sec) mysql > delete from test where query 2; # # request row lock timeout ERROR 1205 (HY000): Try restarting transactionmysql > select * from test;+---+ | a | +-- + | 2 | | 3 | +-+ 2 rows in set (0.00 sec) mysql > begin; # # here we directly open another transaction (or directly commit the current transaction), then the original transaction will only roll back the second statement, resulting in only 2 and 3 left in the test table. If the rollback we show here, the entire transaction will be rolled back, leaving 1, 2 and 3 unchanged.

So if innodb_rollback_on_timeout=ON, the same transaction 2 will time out, but at this time if we begin starts a new transaction, then the entire transaction that requested the lock timeout will be rolled back instead of just the statement that timed out as before.

2.4 lock_wait_timeout

The following is described in the document. To put it simply, lock_wait_timeout is a metadata lock wait timeout. Any statement that locks metadata will use this timeout parameter. The default is one year. Metadata locks can participate in mysql metadata lock. In order to ensure that transactions can be serialized, as long as a transaction is opened in a session, the metadata lock of the operation table will be acquired. If another session wants to modify the metadata of the table, 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 examples:

We tested it with a myisam engine table myisam_test. There is a record (1 session 1). Now let's start a select and then execute a statement. Open another session, and then perform metadata operations on the table, such as deleting the table, and you will find that the operation is blocked until the lock_wait_timeout second prompts for a timeout.

# # obtain metadata lockmysql > show create table myisam_test for the first session -+ | 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) # # another session, delete table prompt timeout 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 tinto alter TABLE t...; DROP TABLE nt;ALTER TABLE nt...; LOCK TABLE t. WRITE

Of course, one more sentence, for myisam table locking and concurrent insertion and so on, this blog myisam table lock is very detailed, interested can take a look.

2.5 net_read_timeout & net_write_timeout

The following is described in the document, that is, these two parameters work in the case of poor network conditions. For example, on the client side, I import a large file into the database by load data infile, and then disable port 3306 of mysql with iptables. At this time, the connection status on the server side is reading from net, and the connection is closed after waiting for net_read_timeout. Similarly, when querying a large table in the program, the port is also disabled during the query, creating a network failure, so that the connection status is writing to net, and then close 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 120m data file data.txt. Then log in to mysql.

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

The import process sets iptables to disable port 3306.

Iptables-An INPUT-p tcp-- dport 3306-j DROPiptables-An OUTPUT-p tcp-- sport 3306-j DROP

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

After several experiments, it can be found that connect_timeout works in the handshake authentication phase (authenticate), interactive_timeout and wait_timeout play a role in the connection idle phase (sleep), and net_read_timeout and net_write_timeout play a role in the connection busy phase (query) or when there is a network problem.

The above is all the content of the article "sample Analysis of mysql timeout variables". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.