mysql sleep连接过多的问题解决

公司开发反应数据库加载慢，mongodb和mysql的负载较高；后进入mysql中发现，sleep进程特别多，如下：

mysql> show full processlist;
+-------+------+----------------------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+----------------------+-------+---------+------+-------+-----------------------+
| 14 | root | 192.168.138.12:36854 | DMUSS | Sleep | 0 | | NULL |
| 73 | root | 192.168.138.12:36870 | DMUSS | Sleep | 3 | | NULL |
| 78 | root | 192.168.138.12:36886 | DMUSS | Sleep | 1 | | NULL |
| 72800 | root | 223.71.188.222:53298 | DMUSS | Sleep | 1450 | | NULL |
| 72978 | root | 223.71.188.222:64691 | DMUSS | Sleep | 1347 | | NULL |
| 72979 | root | 223.71.188.222:63611 | NULL | Sleep | 1346 | | NULL |
| 73009 | root | 223.71.188.222:63617 | DMUSS | Sleep | 1336 | | NULL |
| 73016 | root | 223.71.188.222:64727 | DMUSS | Sleep | 1337 | | NULL |
| 73518 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 73709 | root | 223.71.188.222:63821 | DMUSS | Sleep | 1028 | | NULL |
| 73712 | root | 223.71.188.222:54680 | DMUSS | Sleep | 1027 | | NULL |
| 73714 | root | 223.71.188.222:63826 | DMUSS | Sleep | 1026 | | NULL |
| 73723 | root | 223.71.188.222:63828 | DMUSS | Sleep | 1021 | | NULL |
| 73942 | root | 223.71.188.222:55011 | DMUSS | Sleep | 681 | | NULL |
| 73943 | root | 223.71.188.222:55012 | DMUSS | Sleep | 698 | | NULL |
| 74250 | root | 223.71.188.222:65397 | DMUSS | Sleep | 771 | | NULL |
| 74266 | root | 223.71.188.222:65408 | DMUSS | Sleep | 765 | | NULL |
| 74289 | root | 223.71.188.222:65415 | DMUSS | Sleep | 757 | | NULL |
| 76719 | root | 223.71.188.222:57470 | DMUSS | Sleep | 0 | | NULL |
+-------+------+----------------------+-------+---------+------+-------+-----------------------+
19 rows in set (0.00 sec)

sleep进程严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

下面是mysql手册给出的解释:

1）客户端程序在退出之前没有调用mysql_close()。[写程序的疏忽，或者数据库的db类库没有自动关闭每次的连接]

2）客户端sleep的时间在wait_timeout或interactive_timeout规定的秒内没有发出任何请求到服务器。[类似常连，类似于不完整的tcp ip协议构造，服务端一直认为客户端仍然存在（有可能客户端已经断掉了）]

3）客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了。[参看：tcp ip协议的三次握手]

造成睡眠连接过多的原因？

1）使用了太多持久连接。

2）程序中，没有及时关闭mysql连接。

3）数据库查询不够优化，过度耗时。

解决方法

wait_timeout, 即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止

修改mysql配置文件

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
query_cache_type = 1
wait_timeout = 20

interactive_timeout=20

重启mysql

mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 20 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 20 |
+-----------------------------+----------+
12 rows in set (0.00 sec)

mysql的配置文件中，有一项：

不停实例：
mysql> set global interactive_timeout=30;

Query OK, 0 rows affected (0.00 sec)

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

mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 30 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 30 |
+-----------------------------+----------+
12 rows in set (0.00 sec)

生效；

mysql> show variables like '%timeout'; 看不到配置生效；

说法1：interactive_timeout和wait_timeout的默认值都是28800（8小时）当这两个参数同时出现在里时，会以interactive_timeout的值为准。也就是说不管wait_timeout的值是多少，用show variables like '%timeout%';查看时显示的两个值都是一样的，并且都是interactive_timeout的值。

说法2：如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。

当然，更根本的方法，还是从以上三点排查之：
1.　程序中，不使用持久链接，即使用mysql_connect而不是mysql_pconnect。
2. 程序执行完毕，应该显式调用mysql_close
3.　只能逐步分析系统的SQL查询，找到查询过慢的SQL,优化之.

参考文章：https://yq.aliyun.com/ziliao/22247 https://www.cnblogs.com/azhqiang/p/5454000.html