4

Can anyone tell me how can I kill all the sleeping processes?

I searched for it and I found that we can do it by command 

mk-kill --match-command Sleep --kill --victims all --interval 10

I connected the DB server(Linux) but I find the message that command not found.

I tried to connect via MYSQL administrator and it doesn't say that command not found but also doesn't executes the query , just says you have an SQl error

Ionică Bizău
  • 109,027
  • 88
  • 289
  • 474
Hussain Akhtar Wahid 'Ghouri'
  • 6,527
  • 8
  • 50
  • 85
  • 1
    The answer to this question is here: http://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist Remember to Google! – Artem Goutsoul Dec 26 '12 at 08:50
  • By processes - do you mean MySql threads? If so why would you want to kill them? – Anthill Dec 26 '12 at 08:51
  • @ArtemGoutsoul : thnx , ill just try it out – Hussain Akhtar Wahid 'Ghouri' Dec 26 '12 at 09:18
  • @Anthill : i need to kill the queries at rest – Hussain Akhtar Wahid 'Ghouri' Dec 26 '12 at 09:18
  • 1
    @HussainAkhtarWahid'Ghouri' Killing the queries at rest may be a BAND AID fix. Post your code that is responsible for 'Connect', 'Process', 'Close' your connection and we will likely find WHY you have orphaned processes that are SLEEPing. When you KILL ANY processses there are many possible side effects that you will NOT appreciate. RAM may not be freed as it should be, table/row unlocks not accomplished, and more. – Wilson Hauck Jul 09 '22 at 15:47

2 Answers2

3

login to Mysql as admin:

 mysql -uroot -ppassword;

And than run 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 55;
Query OK, 0 rows affected (0.00 sec)
Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
0

kill $queryID; is helpful but if there is only one query causing an issue;

Having a lot of MySQL sleeping processes can cause a huge spike in your CPU load or IO Here is a simple one-line command (if behind the MySQL server is linux) which would kill all of the current sleeping MySQL processes:

for i in `mysql -e "show processlist" | awk '/Sleep/ {print $1}'` ; do mysql -e "KILL $i;"; done

This is only a temporary repair; I strongly advise identifying and addressing the problem's main cause. For instance, you may set the wait timeout variable to the amount of time you want MySQL to hold open connections before shutting them. But if the issue still persists and you have to investigate the DB queries that cause the problem there is another way. In screen session, you can use another while cycle to continuously kill the sleeping queries. (while there is an output of the mysql show processlit | grep -i sleep | awk id column and kill it.) If you are using MySQL replication between different hosts this will help them to catch up. So when using show slave status\G; Seconds_behind_master will be going to catch up.

Of course, you should investigate the root cause again.

Christiyan
  • 1
  • 2