How to delete sleeping processes in Mysql

Posted on April 3, 2018 by Suresh Kamrushi in Mysql

Whenever a new query executed, a freash process start and once the query is completed process should be end but many application does not clean up their connection after end of process and hence process goes to sleeping mode. MySql will clean up such process as per the configurable value of “wait_timeout” . Default value of “wait_timeout” is “28800”.

 SHOW VARIABLES LIKE 'wait_timeout'

Sleeping states of Mysql process does not harm anything unless it is in thousands. Even if you want to remove it manually you can do like this:

login to Mysql as admin using command prompt:

mysql -uroot -ppassword;

And than run below command:

mysql> show processlist;

You will get something like below :

+----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 49 | application | 192.168.44.1:51718 | XXXXXXXX | Sleep | 183 | | NULL ||
| 55 | application | 192.168.44.1:51769 | XXXXXXXX | Sleep | 148 | | NULL |
| 56 | application | 192.168.44.1:51770 | XXXXXXXX | Sleep | 148 | | NULL |
| 57 | application | 192.168.44.1:51771 | XXXXXXXX | Sleep | 148 | | NULL |
| 58 | application | 192.168.44.1:51968 | XXXXXXXX | Sleep | 11  | | NULL |
| 59 | root        | localhost          | NULL     | Query | 0   |   NULL | show processlist |
+----+-------------+--------------------+----------+---------+------+-------+------------------+

You will see complete details of different connections. Now you can kill the sleeping connection as below:

mysql> kill 52;

Query OK, 0 rows affected (0.00 sec)

  Comments or questions are welcome  

Tagged as