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?

Evelyn

possible duplicate: #1904338 – Lurleen

Answer 1

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 .

Evelyn

Looks like a sleep SQL injection attack, Trying to find the source of attack may be a good idea. saotn.org/mysql-sleep-attacks – Geminian

mysql -u -p -e "show processlist;" | grep Sleep | awk '{print $1}' | while read LINE; do mysql -u -p -e "kill $LINE"; done – Edora

Hey! Can you provide an update to use this in mariadb 10.5? – Habergeon

Answer 2

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 "%"

Syncytium

Answer 3

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

Bushtit

@Jhamvi I already try this , Its show only sleep processes Like <pre> > 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) </pre> – Evelyn

@VishalKamal , it will show only sleep process, you need to parse the loop, what language are you using?? – Bushtit

@VishalKamal i mean are you using any programming language, so that you can use looping and fire the quey using that language?? – Bushtit

php, I want linux shell script who kill all sleep processes automatic. – Evelyn

@VishalKamal , using a single command its not possinble in linux shell, there are 2-3 queries needed, refer this – Bushtit

Ok Thanks Jhanvi, I will try build shell script using two queries. – Evelyn

ERROR 1109 (42S02): Unknown table 'processlist' in information_schema – Synergistic

Answer 4

A easy way:

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

Gower

Could you please how to kill sleep more than 100 sec ? thanks – Pomology

Answer 5

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

Irradiant