Platform: MySQL 5.x
Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec.. On the other hand DBA's can use "wait_timeout" etc parameters to control this..
[code]
# -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges
DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$
CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);
DECLARE my_cur CURSOR FOR
SELECT ID
FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN my_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH my_cur
INTO uID;
IF no_more_rows THEN
CLOSE my_cur;
LEAVE the_loop;
END IF;
SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
END$$
DELIMITER ;
# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();
[/code]
Wednesday, September 01, 2010
Subscribe to:
Post Comments (Atom)
13 comments:
It could be usefull to pass the timeout in function's argument
Thanks..that's correct, I forgot to update the code.. :)
It seems very similar to my own:
http://forge.mysql.com/tools/tool.php?id=106
Except that mine includes an exception for SUPER accounts and system threads (event sceduler, replication).
Currious; why do you use a prepared statement here? To protect from a unexpected result from information_schema?
Thanks Matthew for posting comment..
>>It seems very similar to my own:
http://forge.mysql.com/tools/tool.php?id=106
Except that mine includes an exception for SUPER accounts and system threads (event sceduler, replication).
That's correct, you have taken care of most of the things while killing the sessions, but I just wanted to kill "Sleep" sessions..
>>Currious; why do you use a prepared statement here? To protect from a unexpected result from information_schema?
:-) not really, just wanted to avoid parsing/data conversion etc stuffs.. if require to add some more parameters. etc
I've got a cron that does the same. I use it to kill all queries for a certain user.
* * * * * /usr/bin/mysql -u (superusername) -p(password) -e 'show processlist\G' |egrep -B5 'Time: [0-9]{3,}|Time: 4[1-9]{1}|Time: 5[0-9]{1}|Time: 6[0-9]{1}|Time: 7[0-9]{1}|Time: 8[0-9]{1}|Time: 9[0-9]{1}' | egrep -B2 'User: (query_username)' | grep 'Id:' | cut -d':' -f2 | sed 's/^ //' |while read id; do /usr/bin/mysql -u (superusername) -p(password) -e "kill $id;"; done
One correction on the above... to kill only sleeps just add the "egrep -B4 'Command: Sleep'" after the Time portion.
* * * * * /usr/bin/mysql -u (superuser) -p(password) -e 'show processlist\G' |egrep -B5 'Time: [0-9]{3,}|Time: 4[1-9]{1}|Time: 5[0-9]{1}|Time: 6[0-9]{1}|Time: 7[0-9]{1}|Time: 8[0-9]{1}|Time: 9[0-9]{1}' | egrep -B4 'Command: Sleep' | egrep -B2 'User: (username)' | grep 'Id:' | cut -d':' -f2 | sed 's/^ //' |while read id; do /usr/bin/mysql -u (superuser) -p(password) -e "kill $id;"; done
The ultimate tool for this is mk-kill, part of Maatkit:
http://www.maatkit.org/doc/mk-kill.html
It allows you to print the statements killed, log them to file, only kill certain types of statements, only kill idle connections, etc.
Justin,
Completely agree with you on Maatkit..
Hi!
I wrote my own php app that does the same thing.
Except, I made some exclusions on what to kill :)
if(
$time >= KILL_TIME &&
FALSE === stristr($info,"don't kill") &&
$command != "Killed" &&
$command != "Sleep" &&
$command != "Binlog Dump" &&
$command != "Daemon" &&
FALSE === stristr($info,"alter table ") &&
FALSE === stristr($info,"repair table ") &&
FALSE === stristr($info,"optimize table")&&
FALSE === stristr($state,"Waiting for ndbcluster to start"))
I use shell script for the same.
Thanks Umesh for sharing! Brett, I liked your way too :)
Thank You Verymuch Mr. Umesh Shastry. It was wonderful
Otimo post, Me ajudou :D
Obrigado.
www.divulgasite.net
Great! Exactly what I was looking for. Gives a 'no database selected' error, which went away when I selected a database before running the script or calling the procedure.
This script shouldn't be dependent on a selection of database though.
Post a Comment