Tuesday, March 18, 2014

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:

mysql_connect('yourhost', 'username', 'password');
$res = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($res)) {
  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

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 Widget for Blogspot

No comments: