【MySQL运维】wait_timeout与interactive_timeout

一、两者的区别

· interactive_timeout：交互模式下会话超时时间，单位为秒

· wait_timeout：应用会话连接超时时间，单位为秒，默认为8小时。应用程序在连接成功后如果没有后续操作，则连接处于Sleep空闲状态。执行show processlist命令时Command 列显示为Sleep就表示该客户端为空闲连接。如果空闲时长达到wait_timeout的限制则断开该连接。客户端再次发送请求的话就会收到Lost connection to MySQL server during query的错误提示。如果需要继续操作就需要重新建立连接再执行请求。这会带来额外的开销。

二、超时时间的设置

1、全局永久生效参数设置，需重启服务

vi my.cnf

[mysqld]
interactive_timeout=3600
wait_timeout=3600

2、全局临时生效参数设置，无需重启

set global interactive_timeout 3600
set global wait_timeout 3600
show global variables like '%timeout%'  #查看设置生效

通常1、2两个方法一起设置，保证当前服务和重启服务后设置都能正常生效

三、超时时间带来的连接暴涨问题与处理

在线上配置中该值需要根据实际环境进行调整，如果设置太大会导致连接长时间不释放，而太短的话又会因为频繁断开请求影响工作。两者都存在连接数暴涨达到MySQL max_connections限制而产生Too many connections的错误，甚至可能OOM。当出现这种问题的时候除了调大max_connections的值（大量连接进来后系统负载也会变大）以外，还可以通过人工释放连接来解决。

1、运行show processlist 查看当前连接情况

2、上图中有2个事务处于sleep状态，id分别是4和5。通过information_schema库的innodb_trx表进行进一步查看，定位出哪些长连接是真正处于空闲，这样可以避免错误的终止掉一些长事务。

3、上图中trx_mysql_thread_id=4表示id=4 的线程还处在事务中。因此需要优先断开的是其它ID的事务。如果这样还不够再考虑断开事务内空闲太久的连接。如果一个客户端处于sleep状态被服务端主动断开，这个客户端需要发起下一个请求的时候才会收到ERROR 2013 (HY000): Lost connection to MySQL server during query的报错。从数据库端主动断开连接可能是有损的，有的应用收到这个错误后不重新连接，而是直接用这个已经不能用的句柄进行重试，导致从应用端看上去MySQL存在问题。