My (SQL) WorkLog: MySQL: Kill sleeping connections

Wednesday, September 01, 2010

MySQL: Kill sleeping connections

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]

13 comments:

frenus said...

It could be usefull to pass the timeout in function's argument

Umesh Shastry said...

Thanks..that's correct, I forgot to update the code.. :)

Matthew Montgomery said...

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?

Umesh Shastry said...

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

Brett Garland said...

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

Brett Garland said...

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

Justin Swanhart said...

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.

Umesh Shastry said...

Justin,
Completely agree with you on Maatkit..

sbester said...

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

Kedar said...

I use shell script for the same.
Thanks Umesh for sharing! Brett, I liked your way too :)

shanmukha said...

Thank You Verymuch Mr. Umesh Shastry. It was wonderful

Carol said...

Otimo post, Me ajudou :D

Obrigado.

www.divulgasite.net

Hisham said...

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