MySQL：参数wait_timeout和interactive_timeout以及空闲超时的实现

水平有限，如果有误请指出
源码版本：percona 5.7.22

一、参数意思

这里简单解释一下两个参数含义如下：

  • 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：
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, 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

最终会调入vio_io_wait函数，如下是其中的部分片段，我们可以清楚看到实际上所谓的空闲超时实际上就是我们的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/O event has completed. */
    DBUG_ASSERT(pfd.revents & revents);
    break;
  }

因此整个步骤就是

  • loop
  • 做wait_timeout参数检查并且赋值。
  • 堵塞接受命令由poll函数实现，通过poll函数的超时参数也实现了空闲等待超时。(如果不发送命令就堵塞在这里)
  • 命令来到退出堵塞。
  • 再次做wait_timeout参数检查并且赋值。
  • 执行命令。
  • goto loop

五、测试

我这里就用mysql客户端和pymysql进行交互和非交互连接的测试。

  • 交互式mysql客户端会话interactive_timeout 参数覆盖wait_timeout参数
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.01 sec)

mysql> set global interactive_timeout = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@gp1 log]# /mysqldata/mysql3340/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.22-22-debug-log Source distribution

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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.01 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 20    |
+---------------+-------+
1 row in set (0.02 sec)
  • 交互式mysql客户端会话登陆期间修改interactive_timeout不生效，更改wait_timeout生效。
mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800  |
+---------------------+-------+
1 row in set (0.02 sec)

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 (0.01 sec)

mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 5     |
+---------------------+-------+
1 row in set (0.02 sec)

等待5秒，并未生效

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2019-02-28 17:24:29 |
+---------------------+
1 row in set (0.00 sec)

mysql> set wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

等待5秒 发现断开了
mysql> show variables like 'wait_timeout';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10
  • 使用python连接非交互式客户端interactive_timeout 参数不会覆盖wait_timeout参数

我们可以简单的写一个python脚本如下：

import socket
import pymysql.cursors
import psutil
import subprocess


mysql_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()
    ##查看初始值
    cursor.execute(sqlwait)
    ret_wait = cursor.fetchone()
    cursor.execute(sqlinter)
    ret_inter = cursor.fetchone()
    print("before change: {}".format(ret_wait+ret_inter))

    ##更改值
    cursor.execute(sql_c_inter)
    connect.close()##关闭连接

    ##重新登陆开启连接
    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: {}".format(ret_wait+ret_inter))

    ##恢复值
    sql_c_inter = "set global interactive_timeout=28800"
    cursor.execute(sql_c_inter)

    connect.close()#关闭连接

##程序开始
if __name__ == '__main__':
    main()

得到的测试结果如下：

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

如果是交互是客户端会话的话wait_timeout也应该是10。

六、总结

  • 内部来讲只有wait_timeout参数会传递到网络层设置，而interactive_timeout参数只会在会话登陆的时候判断是否是交互式客户端会话如果是则进行wait_timeout=interactive_timeout的覆盖，如果不是则不生效的。
  • 一旦会话登陆成功如果想要会话级别修改超时参数，不管交互式还是非交互式都是修改wait_timeout(set wait_timeout)参数才会生效。
  • 内部实现空闲超时是通过poll函数的超时参数实现的。
  • 简单来说interactive_timeout对交互式客户端连接生效，wait_timeout对非交互式客户端连接生效。
  • 对于参数生效的过程如下：
  1. loop
  2. 做wait_timeout参数检查并且赋值。
  3. 堵塞接受命令由poll函数实现，通过poll函数的超时参数也实现了空闲等待超时。(如果不发送命令就堵塞在这里)
  4. 命令来到退出堵塞。
  5. 再次做wait_timeout参数检查并且赋值。
  6. 执行命令。
  7. goto loop

作者微信：gp_22389860

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,509评论 6赞 504
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,806评论 3赞 394
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,875评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,441评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,488评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,365评论 1赞 302
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,190评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,062评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,500评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,706评论 3赞 335
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,834评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,559评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,167评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,779评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,912评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,958评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,779评论 2赞 354