Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the parameters wait_timeout and interactive_timeout in MySQL and the implementation method of idle timeout". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, the meaning of parameters

Here is a brief explanation of the meaning of the two parameters:

Interactive_timeout:The number of seconds the server waits for activity on an interactive connection before closing

It. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect ()

Wait_timeout: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 ())

They are all session/global-level, simply speaking, the former is used to describe the idle timeout of interactive clients, and the latter is used for idle timeouts of non-interactive clients, but it is also revealed here that if it is an interactive client connection session, then wait_timeout will be overwritten by interactive_timeout, in other words, if it is a non-interactive client connection session will not use interactive_timeout to overwrite wait_timeout That is to say, interactive_timeout has no effect.

Second, the internal representation of parameters

Interactive_timeout:

Static Sys_var_ulong Sys_interactive_timeout (vio_io_wait "interactive_timeout", "The number of seconds the server waits for activity on an interactive"connection before closing it", SESSION_VAR (net_interactive_timeout), CMD_LINE (REQUIRED_ARG), VALID_RANGE (1, LONG_TIMEOUT), DEFAULT (NET_WAIT_TIMEOUT), BLOCK_SIZE (1))

Wait_timeout:

Static Sys_var_ulong Sys_net_wait_timeout ("wait_timeout", "The number of seconds the server waits for activity on a"connection before closing it", SESSION_VAR (net_wait_timeout), CMD_LINE (REQUIRED_ARG), VALID_RANGE (1, IF_WIN (INT_MAX32/1000, LONG_TIMEOUT)), DEFAULT (NET_WAIT_TIMEOUT), BLOCK_SIZE (1)

We can see that internally, the parameter interactive_timeout is expressed as net_interactive_timeout,wait_timeout and expressed as net_wait_timeout.

3. Interactive_timeout covers wait_timeout

In fact, the function corresponds to server_mpvio_update_thd only when the user logs in, as shown below:

Server_mpvio_update_thd (THD * thd, MPVIO_EXT * mpvio) do_command {thd- > max_client_packet_length= mpvio- > max_client_packet_length; if (mpvio- > protocol- > has_client_capability (CLIENT_INTERACTIVE)) / / judge here thd- > variables.net_wait_timeout= thd- > variables.net_interactive_timeout;//

Here we can clearly see that there is an overwrite operation, and we can also see that the if condition here is that it will only be overwritten if it is a client connection of type CLIENT_INTERACTIVE.

The stack frames are as follows:

# 0 server_mpvio_update_thd (thd=0x7ffe7c012940, mpvio=0x7fffec0f6140) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/auth/sql_authentication.cc:2014#1 0x0000000000f01787 in acl_authenticate (thd=0x7ffe7c012940, command=COM_CONNECT, extra_port_connection=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/auth/sql_authentication.cc:2246#2 0x0000000001571149 in check_connection (thd=0x7ffe7c012940 Extra_port_connection=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1295#3 0x00000000015712dc in login_connection (thd=0x7ffe7c012940, extra_port_connection=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1352#4 0x0000000001571bfe in thd_prepare_connection (thd=0x7ffe7c012940 Extra_port_connection=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1516#5 0x000000000170e642 in handle_connection (arg=0x6781c30) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:306

Then we can draw a conclusion here that we will only determine whether the connection is interactive when logging in. If so, the parameter wait_timeout will be overwritten, but once connected, there will be no overwriting operation, even if we modify the value of interactive_timeout again, it will not be overwritten. Later, we will see that only wait_timeout is actually in effect.

Fourth, the realization of timeout

In fact, every time any command is executed, the wait_ timeout value is rechecked and assigned to the network read_ timeout value. In the function do_command, we can find my_net_set_read_timeout (net, thd- > get_wait_timeout ()); step, which is to assign our wait_timeout to the network read_ timeout value, which contains fragments

If (net- > read_timeout= = timeout) / / if read_timeout and wait_timeout are equal, DBUG_VOID_RETURN;// does not need to be assigned directly return net- > read_timeout= timeout;// otherwise. If (net- > vio) vio_timeout (net- > vio, 0, timeout); / / the assignment of net- > vio.read_timeout will be performed here

After executing this step, wait_timeout will take effect, and then the command will be executed. After executing the command, the whole thread will go back to the do_command function again, do the wait_timeout parameter in the my_net_set_read_timeout function, and block the acceptance of the command (you can see later that it is implemented by poll). At this time, wait_timeout will work. The whole stack frame is as follows:

# 0 vio_io_wait (vio=0x7ffe7c015520, event=VIO_IO_EVENT_READ, timeout=10000) at / root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:1119#1 0x0000000001e4d5f6 in vio_socket_io_wait (vio=0x7ffe7c015520, event=VIO_IO_EVENT_READ) at / root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:116#2 0x0000000001e4d6d2 in vio_read (vio=0x7ffe7c015520, buf=0x7ffe7c061c10 "\ 001" Size=4) at / root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:171#3 0x00000000014c6ceb in net_read_raw_loop (net=0x7ffe7c028440, count=4) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:672#4 0x00000000014c6ec2 in net_read_packet_header (net=0x7ffe7c028440) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:756#5 0x00000000014c6fcb in net_read_packet (net=0x7ffe7c028440 Complen=0x7fffec0c5c58) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:822#6 0x00000000014c715e in my_net_read (net=0x7ffe7c028440) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:899#7 0x00000000014de010 in Protocol_classic::read_packet (this=0x7ffe7c027bf8) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:808#8 0x00000000014de514 in Protocol_classic::get_command (this=0x7ffe7c027bf8 Com_data=0x7fffec0c5d70, cmd=0x7fffec0c5d98) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:965#9 0x00000000015c5699 in do_command (thd=0x7ffe7c0268e0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:960

The vio_io_wait function will eventually be called, and here are some of the snippets, and we can clearly see that the so-called idle timeout is actually implemented by our pool.

Switch ((ret= poll (& pfd, 1, timeout)) {case-1: / * On error,-1 is returned. * / break; case 0: / * Set errno to indicate a timeout error. (This is not compiled in on WIN32.) * / errno= SOCKET_ETIMEDOUT; break; default: / * Ensure that the requested I event has completed O event has completed. * / DBUG_ASSERT (pfd.revents & revents); break;}

So the whole step is

Loop

Check the wait_timeout parameters and assign values.

The blocking accept command is implemented by the poll function, and the idle wait timeout is also realized through the timeout parameter of the poll function. (it will be blocked here if you don't send the command.)

The order comes to exit the jam.

Do the wait_timeout parameter check again and assign a value.

Carry out the order.

Goto loop

5. Testing

I will use the mysql client and pymysql to test interactive and non-interactive connections here.

Interactive mysql client session interactive_timeout parameter overrides wait_timeout parameter

Mysql > show variables like 'wait_timeout%';+-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 28800 | +-+-+ 1 row in set (0.02 sec) mysql > show variables like' interactive_timeout' +-+-+ | Variable_name | Value | +-+-+ | interactive_timeout | 28800 | +-+-+ 1 row in set (0.01sec) mysql > set global interactive_timeout = 20 Query OK, 0 rows affected (0.00 sec) mysql > exitBye [root@gp1 log] # / mysqldata/mysql3340/bin/mysqlWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 6Server version: 5.7.22-22-debug-log Source distributionCopyright (c) 2009-2018 Percona LLC and/or its affiliatesCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show variables like 'interactive_timeout' +-+-+ | Variable_name | Value | +-+-+ | interactive_timeout | 20 | +-+-+ 1 row in set (0.01sec) mysql > show variables like 'wait_timeout' +-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 20 | +-+-+ 1 row in set (0.02 sec)

Modifying interactive_timeout does not take effect during interactive mysql client session login, but changing wait_timeout takes effect.

Mysql > show variables like 'interactive_timeout' +-+-+ | Variable_name | Value | +-+-+ | interactive_timeout | 28800 | +-+-+ 1 row in set (0.02sec) mysql > show variables like 'wait_timeout' +-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 28800 | +-+-+ 1 row in set (0.02 sec) mysql > set interactive_timeout=5;Query OK, 0 rows affected (0.00 sec) mysql > show variables like 'wait_timeout' +-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 28800 | +-+-+ 1 row in set (sec) mysql > show variables like 'interactive_timeout' +-+-+ | Variable_name | Value | +-+-+ | interactive_timeout | 5 | +-+-+ 1 row in set (0.02 sec) wait 5 seconds, but it does not take effect mysql > select sysdate () +-+ | sysdate () | +-+ | 0-02-28 17:24:29 | +-+ 1 row in set (sec) mysql > set wait_timeout=5 Query OK, 0 rows affected (0.00 sec) wait 5 seconds to find mysql > show variables like 'wait_timeout';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 10

Using python to connect non-interactive client interactive_timeout parameters does not override wait_timeout parameters

We can simply write a python script as follows:

Import socketimport pymysql.cursorsimport psutilimport subprocessmysql_con = {"host": "192.168.99.95", "port": 3340, "user": "pycon", "passwd": "gelc123", "db": "test"} def main (): sqlwait = "show variables like 'wait_timeout'" sqlinter = "show variables like' interactive_timeout'" sql_c_inter = "set global interactive_timeout=10" connect = pymysql.Connect (host=mysql_con ["host"], port=mysql_con ["port"] User=mysql_con ["user"], passwd=mysql_con ["passwd"] Db=mysql_con ["db"]) cursor = connect.cursor () # # View the initial value cursor.execute (sqlwait) ret_wait = cursor.fetchone () cursor.execute (sqlinter) ret_inter = cursor.fetchone () print ("before change: {}" .format (ret_wait+ret_inter)) # # change the value cursor.execute (sql_c_inter) connect.close () # # close the connection # # log in again and open the connection connect = pymysql.Connect (host=mysql_con ["host"] Port=mysql_con ["port"], user=mysql_con ["user"], passwd=mysql_con ["passwd"] Db=mysql_con ["db"]) cursor = connect.cursor () cursor.execute (sqlwait) ret_wait = cursor.fetchone () cursor.execute (sqlinter) ret_inter = cursor.fetchone () print ("after change: {}" .fo rmat (ret_wait+ret_inter)) # # restore value sql_c_inter = "set global interactive_timeout=28800" cursor.execute (sql_c_inter) connect.close () # Close connection # # Program start if _ _ name__ = ='_ _ main__': main ()

The test results are as follows:

Before change: ('wait_timeout',' 28800, 'interactive_timeout',' 28800') after change: ('wait_timeout',' 28800, 'interactive_timeout',' 10')

If the interaction is a client session, the wait_timeout should also be 10.

This is the end of the content of "what are the parameters wait_timeout and interactive_timeout in MySQL and the implementation of idle timeout". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.