Sleeping MySQL threads

Saw this interesting script on how to deal with sleeping MySQL connections. From the beginning I would say this should only be used as a temporary solution until you can fix the real issue.

I have modified the original script somewhat.

$link = @ mysql_connect('localhost', 'xxxx', 'xxxxxxxx');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully'."\n";

$result = mysql_query("SHOW processlist");
while ($myrow = mysql_fetch_assoc($result)) {
if ($myrow['Command'] == "Sleep" && $myrow['Time']>0 && $myrow['User']!="root") {
//if ($myrow['Command'] == "Sleep" && $myrow['Time']>0) {
//mysql_query("KILL {$myrow['Id']}");
echo  $myrow['User']." ".$myrow['Command']." ".$myrow['Time']." ".$myrow['State']."\n";
if($myrow['Time']>20){
//mysql_query("KILL {$myrow['Id']}");
echo "Killed process id: ". $myrow['Id']."\n";
}
}
}
mysql_close($link);
?>

For starters you can see all the sleeping MySQL processes by running the command

show processlist

You will need to look out for all the threads with state being 'sleep. MySQL 5.1.7 allows filtering  For e.g. you can find  sleeping processes running over 5 seconds using the command below.

SELECT user, time, state, info FROM information_schema.processlist WHERE command = 'Sleep' AND time >5 ORDER BY time DESC, id;

What causes

Most sleeping MySQL connections are caused by queries initiated client side that not be properly closed on the clients side. Most times these are cleaned up by wait_timeout variable. Sometimes depending on the query a sleeping connection can lock up a table (MyISAM) or row(InnoDB) and lead to problems especially to the timeouts are high and the number of connections waiting on the locked resource,

References

• MySql Proccesslist filled with “Sleep” Entries leading to “Too many Connections”?
• Show MySQL process list without sleeping connections