一、参数意思
这里简单解释一下两个参数,含义如下:
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()).
他们都是session/global级别的,简单的说前者用于描述交互式的客户端的空闲超时,后者用于非交互式的客户端的空闲超时。但是这里也揭示了,如果是交互式客户端连接的session那么wait_timeout将被interactive_timeout覆盖掉,换句话说如果是非交互式的客户端连接的session将不会使用interactive_timeout覆盖掉wait_timeout,也就是interactive_timeout没有任何作用了。
摘自官网内容:
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().
interactive_timeout控制交互式连接的空闲时间。如果调用mysql的mysql_real_connect()函数的时候,使用了CLIENT_INTERACTIVE参数,该连接就定义为交互式连接。
wait_timeout: The number of seconds the server waits for activity on a noninteractive connection before closing it.
翻译:wait_timeout控制非交互连接的闲置时间。
On thread startup, the session waittimeout value is initialized from the globalwait_timeoutvalue or from the globalinteractive_timeoutvalue, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See alsointeractive_timeout.
翻译:在连接建立的时候,session级别的wait_timeout会根据连接的分类来选择是继承global级别的interactive_timeout的值和global级别的wait_timeout的值。
二、参数内部表示
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));
我们可以看到内部而言参数interactive_timeout表示为net_interactive_timeout,wait_timeout表示为net_wait_timeout。
三、interactive_timeout覆盖wait_timeout
实际上,这个操作只会在用户登陆的时候才出现函数对应server_mpvio_update_thd,如下:
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)) //这里做判断
thd->variables.net_wait_timeout= thd->variables.net_interactive_timeout;//这里覆盖
这里我们可以明确看到有覆盖操作,并且也能看到这里只有if条件是client_interactive类型的客户端连接才会进行覆盖。
栈帧如下:
#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
那么,我们这里可以得到一个结论:只在登陆的时候会判断连接是否是交互式的。如果是,则覆盖掉参数wait_timeout,但是一旦连接后将不会发生覆盖操作,即便我们再次修改interactive_timeout的值也不会覆盖,后面我们看到实际上生效的参数只有wait_timeout。
四、超时的实现
实际上每次执行任何一个命令都会做一次wait_timeout值的重新检查和给网络read_timeout参数赋值。在函数do_command中我们可以发现步骤my_net_set_read_timeout(net, thd->get_wait_timeout()),这个步骤就是将我们的wait_timeout赋值给网络参数read_timeout,其中包含片段如下:
if (net->read_timeout == timeout) //如果read_timeout和wait_timeout相等
DBUG_VOID_RETURN;//不需要做操作直接return
net->read_timeout= timeout;//否则进行赋值。
if (net->vio)
vio_timeout(net->vio, 0, timeout);//这里会进行net->vio.read_timeout的赋值
执行完这个步骤后wait_timeout就生效了,然后就会执行命令。执行完命令后,整个线程会再次回到do_command函数,再做一次my_net_set_read_timeout函数,使其中的wait_timeout参数生效,并且阻塞等待接受命令(后面可以看到是poll实现的),这个时候wait_timeout就起作用了。整个栈帧如下:
#0 vio_io_wait (vio=0x7ffe7c015520, event=VIO_IO_EVENT_READ, time
out
=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 "