Shell Script to auto kill mysql sleep processes

Question

How We Kill mysql sleep processes Like:

+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+
| Id   | User      | Host      | db                     | Command | Time | State          | Info                                                                                      |
+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+
| 2477 | stageuser | localhost | jj_production_11102013 | Query   |    0 | end            | SELECT * FROM wp_comments WHERE blog_id = 1071 ORDER BY comment_date_gmt DESC LIMIT 0, 50 |
| 3050 | stageuser | localhost | jj_production_11102013 | Query   |    0 | Sorting result | SELECT * FROM wp_comments WHERE blog_id = 1071 ORDER BY comment_date_gmt DESC LIMIT 0, 50 |
| 3052 | stageuser | localhost | jj_production_11102013 | Sleep   |  336 |                | NULL                                                                                      |
| 3056 | stageuser | localhost | NULL                   | Query   |    0 | NULL           | show processlist                                                                          |
| 3057 | stageuser | localhost | jj_production_11102013 | Sleep   |  301 |                | NULL                                                                                      |
| 3058 | stageuser | localhost | jj_production_11102013 | Sleep   |  299 |                | NULL                                                                                      |
| 3059 | stageuser | localhost | jj_production_11102013 | Sleep   |  298 |                | NULL                                                                                      |
| 3061 | stageuser | localhost | jj_production_11102013 | Sleep   |  273 |                | NULL                                                                                      |
| 3068 | stageuser | localhost | jj_production_11102013 | Sleep   |  251 |                | NULL                                                                                      |
| 3072 | stageuser | localhost | jj_production_11102013 | Sleep   |  233 |                | NULL                                                                                      |
| 3111 | stageuser | localhost | jj_production_11102013 | Sleep   |    1 |                | NULL                                                                                      |
+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

Is this sleep processes affect site performance like slow other queries?

possible duplicate: http://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist — feeela, Nov 18 '13 at 13:58

Vishal Kamal · Accepted Answer · 2017-03-29T11:38:43.733

14

I made it.

Create kill_sleep.sh file

mysql -u<user> -p<password> -h<host> -e "select concat('KILL ',id,';')  into outfile '/tmp/sleep_processes.txt' from information_schema.processlist where Command = 'Sleep'"
mysql -u<user> -p<password> -h<host> -e "source /tmp/sleep_processes.txt;"
rm -rf /tmp/sleep_processes.txt

And set kill_sleep.sh to cron job .

edited Mar 29 '17 at 11:38

answered Nov 18 '13 at 13:55

Vishal Kamal

1,104
2
10
35

Looks like a sleep SQL injection attack, Trying to find the source of attack may be a good idea. https://www.saotn.org/mysql-sleep-attacks/ – rosh3000 May 20 '16 at 08:19
4

mysql -u -p -e "show processlist;" | grep Sleep | awk '{print $1}' | while read LINE; do mysql -u -p -e "kill $LINE"; done – user3770797 Nov 04 '17 at 20:57
Hey! Can you provide an update to use this in mariadb 10.5? – zecaluis Oct 01 '20 at 02:39

score 6 · Answer 2 · answered Feb 23 '14 at 06:36

Vishal's answer works well if you're running the command on the MySQL server, but it won't work if you're connecting to the server remotely or if you don't have permission to run SOURCE or SELECT ... INTO OUTFILE (eg. Amazon's RDS). It's possible to rewrite it not to rely on those features though, and then it'll work anywhere:

mysql -h<host> -u<user> -p -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE Command = 'Sleep'" > sleep.txt
cat sleep.txt | xargs -I% mysql -h<host> -u<user> -p -e "%"

score 5 · Answer 3 · answered Nov 18 '13 at 13:00

5

The syntax is:

KILL thread_id

In your case:

  mysql > KILL 3057

But in order to delete all the sleep processes,one command cant be used, you need to loop through whole processlist,after taking all the processes in tmp table and looping through it:

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep' into outfile '/tmp/a.txt';

Referred from here

answered Nov 18 '13 at 13:00

Jhanvi

5,069
8
32
41

@Jhamvi I already try this , Its show only sleep processes Like

> select concat('KILL ',id,';') from information_schema.processlist where user='stageuser';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3477;             |
| KILL 3072;             |
| KILL 3068;             |
| KILL 3061;             |
| KILL 3059;             |
| KILL 3058;             |
| KILL 3057;             |
| KILL 3056;             |
| KILL 3052;             |
| KILL 3050;             |
| KILL 2477;             |
+------------------------+
11 rows in set (0.00 sec)

– Vishal Kamal Nov 18 '13 at 13:07

@VishalKamal , it will show only sleep process, you need to parse the loop, what language are you using?? – Jhanvi Nov 18 '13 at 13:09
@VishalKamal i mean are you using any programming language, so that you can use looping and fire the quey using that language?? – Jhanvi Nov 18 '13 at 13:12
php, I want linux shell script who kill all sleep processes automatic. – Vishal Kamal Nov 18 '13 at 13:12
@VishalKamal , using a single command its not possinble in linux shell, there are 2-3 queries needed, refer [this](http://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist) – Jhanvi Nov 18 '13 at 13:22
Ok Thanks Jhanvi, I will try build shell script using two queries. – Vishal Kamal Nov 18 '13 at 13:35
ERROR 1109 (42S02): Unknown table 'processlist' in information_schema – joHN Aug 06 '14 at 03:39

score 1 · Answer 4 · answered Aug 10 '20 at 15:14

1

A easy way:

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

answered Aug 10 '20 at 15:14

Rodrigo Alviani

21
2

Could you please how to kill sleep more than 100 sec ? thanks – dev moore Aug 23 '22 at 16:29

score 0 · Answer 5 · answered Mar 16 '16 at 14:24

Percona Tools:

pt-kill --match-command Sleep --idle-time 100 --victims all  --interval 30 --kill

This will find all connections that are "Sleep" state and idle for 100 seconds or more and kill them. --interval 30 will make it keep do this every 30 seconds. So you can open a screen -S ptkill then in that screen run the above command, then ctrl-A, D to detach and exit the terminal and it will just keep running cleaning up your connections.

https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html

Shell Script to auto kill mysql sleep processes

5 Answers5

Linked