MySQL经常出现sleep进程的解决办法

公司有一台Linux服务器专门用来跑公司的WEB服务，由于历史原因，站点数与页面非常多，导致经常出现大量的sleep进程；MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。

Mysql> show 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 | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+
11 rows in set (0.00 sec)

经查询发现原来mysql没有进行过优化，还是原来的默认值：28800（即8个小时）

编辑 /etc/my.cnf,在mysqld 下 新增 timeout参数，设置为120秒，如下：

【mysqld】
wait_timeout=120
interactive_timeout=120

注意：要同时设置interactive_timeout和wait_timeout才会生效。

最后重启一下mysql 生效 即可！

不过这个方法太生硬了，线上服务重启无论如何都应该尽可能避免，看看如何在MySQL命令行里通过SET来设置：

mysql> set global wait_timeout=10;

mysql> show global variables like 'wait_timeout';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| wait_timeout | 10 |

+----------------------------+-------+

这里一个容易把人搞蒙的地方是如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。

网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。

另一个值得注意的是会话变量wait_timeout初始化的问题，这一点在手册里已经明确指出了，我就直接拷贝了：

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()).

MySQL大拿Jeremy Zawodny曾在他的文章Fixing Poor MySQL Default Configuration Values里面列出了几个很恶心的MySQL缺省设置，不过没包含wait_timeout，但我觉得它也应该算一个，每次新装MySQL后最好都记得修改它。

参考文章2

睡眠连接过多，会对mysql服务器造成什么影响？

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

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

1.　使用了太多持久连接（个人觉得，在高并发系统中，不适合使用持久连接）

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

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

那么，如果要从根本上解决sleep连接过多，就得从以上三点反复检查，但是见效并不快。

php的垃圾回收机制，其实只针对于php本身。对于mysql，php没权利去自动去释放它的东西。如果你在页面执行完毕前不调用mysql_close()，那么mysql那边是不会关闭这个连接的。如果你是用的是pconnect方式，即使你在页面执行完毕前调用mysql_close()，也无法另mysql关闭这个连接。

也许在负载低的情况下，你感受不到有何不妥。下面我就来解释这两天我观察出的现象:

在php中使用pconnect方式建立连接，然后到mysql客户端下执行show processlist；如果你的负载到一定程度的话，你可以看到很多sleep的进程，这些进程就是人们常说的死连接，它们会一直保持sleep，直到my.cnf里面设置的wait_timeout这个参数值的时间到了，mysql才会自己杀死它。在杀死它的时候，mysql还会在error-log里面记录一条Aborted connection xxx to db: 'xxx' user: 'xxx' host: 'xxx'的日志，用google翻译一下，会得到一个相当强悍的解释"胎死腹中的连接"!

那么造成sleep的原因，有三个，下面是mysql手册给出的解释:

1.客户端程序在退出之前没有调用mysql_close().

2.客户端sleep的时间在wait_timeout或interactive_timeout规定的秒内没有发出任何请求到服务器.

3.客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了.

上面是我根据google的翻译改了下，可能有某些地方翻译的不准确。原文请见下面:

1 The client program did not call mysql_close() before exiting.

2 The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.

3 The client program ended abruptly in the middle of a data transfer

如果你的sleep进程数在同一时间内过多，再加上其他状态的连接，总数超过了max_connection的值，那mysql除了root用户外，就无法再继续处理任何请求无法与任何请求建立连接或者直接down了。所以，这个问题在大负载的情况下还是相当严重的。如果发现你的mysql有很多死连接存在，首先要先检查你的程序是否使用的是pconnect的方式，其次，检查在页面执行完毕前是否及时调用了mysql_close()，

还有一个办法，你可以在my.cnf里面加上wait_timeout和interactive_timeout，把他们的值设的小一些，默认情况下wait_timeout的值是8小时的时间，你可以改成1个小时，或半个小时。这样mysql会更快的杀死死连接。防止连接总数超过max_connection的值。或者把max_connection的值设置的更大，不过这样显然不妥，连接的数量越多，对你服务器的压力越大。实际上那些连接都是冗余的，把它们尽快杀死才是上策。

以前总是说，在使用php连接mysql的时候，尽量不要使用pconnect的方式，看完我上面所说的那些，应该可以明白为什么了吧，因为我们使用php大多数情况下都是做web开发，web开发是面向多用户，那么用户的数量与mysql连接数是成正比的。使用pconnect的方式，即使你的调用mysql_close()也是无法释放数据库连接的，那么mysql中的死连接的数量就会越来越多了。

我认为，只有当你的应用属于那种点对点方式，或者你能保证连接数量很少的情况，才有必要去采用pconnect的方式，因为连接数量少，那么让它一直处于连接状态，避免了重复打开关闭的过程。这样可能会比传统方式更好一些。

至于何时该去调用mysql_close()，最正确的做法是如果下面不再执行mysql的操作了，在你上一次执行完mysql操作后，立刻就调用mysql_close()。这才是最正确的做法，并不是总要把mysql_close()写在页面最后一行就可以了。