MySQL空闲会话超时之interactive_timeout 与wait_timeout
背景描述
在用MySQL Client链接MySQL Server进行操作的是偶，如果一段时间没有操作，可能会遇到以下错误：
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
或者
ERROR 2013 (HY000): Lost connection to MySQL server during query
这个报错意味着当前的链接已经断开，需要重新建立链接。 那么这个超时的时长是跟哪些有关呢？
其实，这个超时时间跟interactive_timeout、wait_timeout 的设置有关。
定义
先来看看这两个参数的含义
说明：我这里查的是MySQL 5.7版本的官方文档。https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout
- wait_timeout
- Default Value： 28800
- The number of seconds the server waits for activity on a noninteractive connection before closing it.
从字面上理解的话，就是服务器在非交互链接之前等待活动的秒数。
- interactive_timeout
- Default Value： 28800
- The number of seconds the server waits for activity on an interactive connection before closing it.
从字面上理解，就是服务器在交互链接之前等待活动的秒数。
看起来就是
（1）wait_timeout 针对非交互连接
（2）interactive_timeout 针对交互连接
测试
（1）只修改interactive_timeout值，观察交互式以及非交互的表现
mysql> set global interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 28800 | 10 | 28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)
- 交互式
Client开启一个新的链接.
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 10 | 10 | 10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.03 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 10 | 10 | 10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.13 sec)
MySQL [(none)]>
可见，当前会话的interactive_timeout=10,并且超过10s后，服务器断开了会话连接。
- 非交互式
def main():
num = 0
while True:
max_num = session.query(func.max(JenkinsResponeTime.id)).first()
logger.info(f'max_num:{max_num}')
print(f'num:{num}')
session.commit()
num += 1
time.sleep(15)
这里用一个python demo 去测试。
# python3 01.py
num:0
num:1
num:2
num:3
num:4
num:5
num:6
....
可见， interactive_timeout并不影响非交互式的应用。
（2）只修改wait_timeout值，观察交互式以及非交互的表现
MySQL Server端用root@localhost 登录修改
mysql> set global interactive_timeout=30;
Query OK, 0 rows affected (0.00 sec)
mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 10 | 28800 | 30 | 28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)
mysql>
- 交互式
Client端用mysql命令登录db
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 10 | 30 | 30 | 30 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.03 sec)
MySQL [(none)]>
可以看到，@@session.wait_timeout 不是等于global.wait_timeout， 而是等于@@global.interactive_timeout。 注意：但是这并不代表非交互的@@session.wait_timeout 等于global.wait_timeout
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:48:34 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.04 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:48:58 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.03 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 34
Current database: *** NONE ***
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:50:39 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.13 sec)
MySQL [(none)]>
并且，经过了interactive_timeout空闲后，超时断开。
- 非交互
python demo 03.py
while True:
try:
sql = "SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();"
result = connect.execute(sql).fetchall()
print(result)
except Exception as e:
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + f' catch exception:{e} ')
time.sleep(15)
输出如下:
# python3 03.py
[(10, 10, 30, 30, datetime.datetime(2024, 7, 25, 16, 14, 13))]
2024-07-25 16:14:28catch exception:(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
可以看到，非交互中session.wait_timeout并不等于interactive_timeout。
非交互式经过session.wait_timeout后超时。
总结
（1）交互式： 关注interactive_timeout参数。
（2）非交互式： 关注 wait_timeout参数。
延伸
我们知道Linux上有关于TCP KEEPALIVE 的一些参数， 比如tcp_keepalive_time。
tcp_keepalive_time 默认值720,（2小时），就是空闲多久，开始发keepalive探测包。 那么这个是否可以会影响mysql上的超时时间呢？
这里用非交互式的做讨论。
比如wait_timeout=20 ，Client A应用 每隔30s才查询一次， Client的tcp_keepalive_time设置为15， 那么20s后，A应用是否会超时异常呢？
答案是肯定的，操作系统的tcp_keepalive_time只是一个四层的ACK空包，mysql并不鸟它。
测试如下：
- Client端
# echo 15 > /proc/sys/net/ipv4/tcp_keepalive_time
- MySQL Server设置
mysql> set global wait_timeout=20;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.wait_timeout, @@session.wait_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+-------------------------------+
| 20 | 30 | 30 |
+-----------------------+------------------------+-------------------------------+
1 row in set (0.00 sec)
16:26:22 启动应用， 可以看到在16:26:37操作系统发keepalive包， 16:26:42 超时断开。抓包如下：
16:26:22.181299 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [P.], seq 438:451, ack 696, win 5, length 13
16:26:22.214075 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [P.], seq 696:707, ack 451, win 237, length 11
16:26:22.253305 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 707, win 5, length 0
16:26:37.214296 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 707, win 5, length 0
16:26:37.246975 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [.], ack 451, win 237, length 0
16:26:42.234322 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [F.], seq 707, ack 451, win 237, length 0
16:26:42.274277 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 708, win 5, length 0
16:26:52.240972 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [P.], seq 451:537, ack 708, win 5, length 86
16:26:52.241067 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [F.], seq 537, ack 708, win 5, length 0
16:26:52.273747 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [R], seq 2919111959, win 0, length 0
16:26:52.273787 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [R], seq 2919111959, win 0, length 0
