19Feb/093
Kill long running MySQL processes
It's all too common for me to come across a box that is hopelessly overloaded with Apache requests and MySQL queries. Sometimes, it takes some brute force to keep the whole box from going under. When all else fails, I use the following one-liner to kill off all of those queries that have been running for too long.
mysql -e 'show processlist\G' |\ egrep -b5 'Time: [0-9]{2,}' |\ grep 'Id:' |\ cut -d':' -f2 |\ sed 's/^ //' |\ while read id do mysql -e "kill $id;" done
This goes with the assumption that you're MySQL authentication credentials are in ~/.my.cnf. You aren't running this as root with no root password set, are you?
November 20th, 2011 - 21:32
Hello there, You’ve performed an excellent job. I will definitely digg it and in my view recommend to my friends. I’m sure they’ll be benefited from this website.
November 29th, 2011 - 04:41
Hello,
There’s mess with the above script which will kill the queries which we’re not intended.
Below is my script :-
—————————-
mysql -e ‘show processlist\G’ |\
egrep -b5 ‘Time: [1-9]{3,10}’ |\
grep ‘Id:’ |\
cut -d’:’ -f2 |\
sed ‘s/^ //’ |\
while read id
do
mysql -e “kill $id;”
done
Before execution My show processlist :-
——————————————————
mysql> show processlist;
+—–+——+———–+————-+———+——+————–+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+————-+———+——+————–+——————————————————————————————————+
| 157 | root | localhost | TPS_LIVE_DB | Query | 1211 | Sending data | SELECT `root`.`entity_id` FROM `customer_entity` AS `root` WHERE (website_id=’1′) AND ((IFNULL((SELE |
| 235 | root | localhost | TPS_LIVE_DB | Query | 955 | Sending data | SELECT `root`.`entity_id` FROM `customer_entity` AS `root` WHERE (website_id=’1′) AND ((IFNULL((SELE |
| 282 | root | localhost | TPS_LIVE_DB | Query | 146 | Sending data | SELECT `root`.`entity_id` FROM `customer_entity` AS `root` WHERE (website_id=’1′) AND ((IFNULL((SELE |
| 288 | root | localhost | TPS_LIVE_DB | Query | 11 | NULL | SELECT concat( table_schema, ‘.’, table_name ) table_name, concat( round( data_length / ( 1024 *102 |
| 289 | root | localhost | TPS_LIVE_DB | Query | 7 | NULL | SELECT concat( table_schema, ‘.’, table_name ) table_name, concat( round( data_length / ( 1024 *102 |
| 290 | root | localhost | TPS_LIVE_DB | Query | 0 | NULL | show processlist |
+—–+——+———–+————-+———+——+————–+——————————————————————————————————+
6 rows in set (0.00 sec)
mysql>
After execution :-
———————
-bash-4.1# sh MySQL_Kill_script.sh
-bash-4.1#
-bash-4.1#
-bash-4.1# mysql TPS_LIVE_DB -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 296
Server version: 5.1.47 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show processlist;
+—–+——+———–+————-+———+——+——-+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+————-+———+——+——-+——————————————————————————————————+
| 289 | root | localhost | TPS_LIVE_DB | Query | 20 | NULL | SELECT concat( table_schema, ‘.’, table_name ) table_name, concat( round( data_length / ( 1024 *102 |
| 296 | root | localhost | TPS_LIVE_DB | Query | 0 | NULL | show processlist |
+—–+——+———–+————-+———+——+——-+——————————————————————————————————+
2 rows in set (0.00 sec)
mysql>
It killed the PID : 289 & 290, because of there’s a small glitch in the above script. I got the glitch where exactly is ( i.e., in the selection of egrep it’s considering unnecessary PIDs eventhough they are not in the conditional selection. ).
Best,
Srinivas Mutyala
+91- 97393-43223
November 29th, 2011 - 04:46
Because, in the below search syntax it considered the PID : 9366156. even though it’s not > 200 seconds. This is the main Glitch of this script.
Please rectify this and let me know.
[root@384704-db1 ~]# mysql -e ‘show processlist\G’ | egrep -b5 ‘Time: [2-9]{3,}’
322- Id: 9366154
339- User: magento
356- Host: 10.180.184.18:59755
385- db: privatesale
406-Command: Sleep
421: Time: 259
434- State:
444- Info: NULL
458-*************************** 3. row ***************************
521- Id: 9366156
538- User: magento
[root@384704-db1 ~]#
Best,
Srinivas