Kill Sleeping MySQL Processes using Quick PHP Script

Our server was getting swamped with too many sleeping connections causing "too many connections" error. During production hours, manually killing them isn't an option, there are just too many and MySQL nor MariaDB doesn't have a native tool for it. I needed it fast, so I wrote a quick PHP code which can then be run every minute as a CRON job.

Here's the code:

<?php
mysql_connect('yourhost', 'username', 'password');
$res = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($res)) {
  $pid=$row["Id"];
  if ($row['Command']=='Sleep') {
      if ($row["Time"] > 3 ) { //any sleeping process more than 3 secs
         $sql="KILL $pid";
         echo "\n$sql"; //added for log file
         mysql_query($sql);
      }
  }
}

You can then save this to a file mysqlkillsleeping.php, and add it as a CRON job running every minute

vim /etc/crontab

*/1 * * * * root php /path/of/mysqlkillsleeping.php 2>&1 >> /var/log/mysqlsleep.log

Related Links:

• Related Links Widget for Blogspot