mysql5.7 大量sleep进程常规处理方式

1. 现象      

在日常的mysql运维中我们在巡检的时候经常会发现大量的sleep进程，如下图：

2. 原因

这种现象一般由三种情况导致的：

1.程序中对mysql_close的调用不佳

2.数据库中sql的查询时间过长

3.wait_timeout、interactive_timeout两个参数的值过大

3.解决方案

常见的问题是第三种，我们先来理解一下wait_timeout、interactive_timeout这两个参数的含义：

根据mysql5.7官方文档的说明：

wait_timeout可设置为全局、会话级别，吗，默认值28800，最小值1，最大值3153600 ，单位是秒 。

他是指服务器在关闭非交互式连接之前等待该连接活动的秒数。

在线程启动时，会话wait_timeout值从全局wait_timeout值或从全局interactive_timeout值初始化，这取决于客户端的类型(由mysql_real_connect()的CLIENT_INTERACTIVE connect选项定义)。另请参见interactive_timeout。

interactive_timeout可设置为全局、会话级别，吗，默认值28800，最小值1，最大值3153600 ，单位是秒 。

服务器在关闭交互式连接之前等待其活动的秒数。交互式客户端定义为使用mysql_real_connect()的CLIENT_INTERACTIVE选项的客户端。另请参见wait_timeout。

在配置这两个参数的时候要同时配置保持一致。

下面我们理解一下这两个参数真正的作用：

wait_timeout 用来控制jdbc等应用程序连接数据库长时间不活跃MySQL主动断开连接的时间

interactive_timeout 用来控制mysql客户端ide工具连接数据库长时间不活跃MySQL主动断开连接的时间

在MySQL中，Sleep是一个状态，表示连接已建立，但当前没有任何活动。Sleep进程是指MySQL中当前处于Sleep状态的进程，这些进程占用了MySQL资源，但却没有实际运作。当连接建立之后，如果没有任何操作，连接将一直保持在Sleep状态，直到连接超时或被关闭。

Mysql通过wait_timeout 、interactive_timeout 这两个参数完成对Sleep进程的释放，当sleep的时间达到wait_timeout 、interactive_timeout的阈值时会被mysql自动清理。

注意：这里引申一个概念：一般数据库与中间件连接的时候都会通过连接池去管理客户端对数据库发起的连接，在代码里conn.close()方法，在实际意义上只是把这个连接交还给了连接池，和数据库的连接并没有真正断开。wait_timeout时间到了以后,服务端MySQL断掉连接后，并不会通知（也没法通知）连接池，所以连接池里的连接如果被拿去使用时，就会产生Communications link failure 报错。

处理方式:

1.查看sleep默认的超时时间
SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; 
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

2.设置超时时间编程30秒，注意：必须两个参数同时设置才会生效。
set global wait_timeout=30;
set global interactive_timeout=30;

具体的参数值要结合连接池的超时时间，最大sql执行时间，业务的最大持续时间来设定。比如阿里的默认配置时间是120s。对于高并发业务对于这两个值的配置建议在1~3分钟之内。

建议写到mysql配置文件中否则下次重启mysql参数失效。

手动处理sleep进程的方法：

1.查询需要kill掉的无效连接进程
SELECT GROUP_CONCAT(CONCAT('kill ',Id) SEPARATOR';') AS cmd FROM information_schema.processlist WHERE command='Sleep'
USER='root'
AND command='Sleep'
and db = 'test';

具体条件根据实际情况修改

2.执行查询结果

注意:查询结果会显示大量的kill语句，受到字符长度限制在执行后验证并继续执行.

3.重新执行查询验证sleep是否消失
SELECT
*
FROM
information_schema.processlist WHERE USER='root'
AND command='Sleep'
and db = 'test'
order by time desc;

具体条件根据实际情况修改

配置示例

3.最佳实践：

3.1 MySQL参数设置

wait_timeout :900秒 #通常设置为900秒超时，各业务线可以根据实际情况调整
interactive_timeout :900秒 #建议和wait_timeout保持一致

3.2 jdbc端设置:

jdbc.connection-pool.max-idle-time  <  wait_timeout
jdbc.connection-pool.idle-test-interval  <  interactive_timeout

3.3 apache连接池配置

validationQuery = "SELECT 1"  验证连接是否可用，使用的SQL语句
testWhileIdle = "true"      指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.
timeBetweenEvictionRunsMillis = "30000"  每30秒运行一次空闲连接回收器
minEvictableIdleTimeMillis = "1800000"  //1000 * 60 * 30  连接在池中保持空闲而不被空闲连接回收器线程,(如果有)回收的最小时间值，单位毫秒池中的连接空闲30分钟后被回收,默认值就是30分钟。
numTestsPerEvictionRun="5" 在每次空闲连接回收器线程(如果有)运行时检查的连接数量，默认值就是5.

这篇好文章是转载于：学新通技术网