解决Mysql sleep线程过多导致堵塞问题

一.sleep连接过多，会对mysql服务器造成什么影响？
严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

二.造成睡眠连接过多的原因？
1.使用了太多持久连接(在高并发系统中，不适合使用持久连接)
2.程序中，没有及时关闭JDBC连接
3.数据库查询不够优化，过度耗时。

三.在Mysql层面,注意受如下两个参数控制:interactive_timeout和wait_timeout,详见：
https://www.cndba.cn/hbhe0316/article/22633

 

四.如下查看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)

原文链接：https://www.cndba.cn/hbhe0316/article/22634