Mysql sleep线程过多解决方案
原创
©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任
一.sleep连接过多，会对mysql服务器造成什么影响？
严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。
二.造成睡眠连接过多的原因？
1.使用了太多持久连接(在高并发系统中，不适合使用持久连接)
2.程序中，没有及时关闭JDBC连接
3.数据库查询不够优化，过度耗时。
三.在Mysql层面,注意受如下两个参数控制:interactive_timeout和wait_timeout,详见：
四.如下查看sleep线程
mysql> show processlist;
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
| 3 | root | localhost | mysql | Query | 0 | starting | show processlist |
| 5 | root | 192.168.56.88:43384 | testdb | Query | 4 | Sending data | select count(*) from t1 |
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+---------------------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+--------+---------+------+----------+------------------+
| 3 | root | localhost | mysql | Query | 0 | starting | show processlist |
| 5 | root | 192.168.56.88:43384 | testdb | Sleep | 23 | | NULL |
+----+------+---------------------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
如上所示：当线程处于sleep的时候，Command列会显示Sleep关键字，由于设置了interactive_timeout和wait_timeout为300，那么300S后，原理的3和5的线程被kill。
mysql> show processlist;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+----------+------------------+
| 6 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)
五.如何手动kill线程
mysql> show processlist;
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
| 7 | root | 192.168.56.88:43386 | testdb | Query | 3 | Sending data | select count(*) from t1 |
| 8 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+------+---------------------+--------+---------+------+--------------+-------------------------+
2 rows in set (0.01 sec)
mysql> kill 7;
Query OK, 0 rows affected (0.00 sec)
版权声明：本文为博主原创文章，未经博主允许不得转载。
MYSQL
