• 2019-05-04 07:07:04

Killing MySQL Queries Which Have Been Sleeping for a Long Period of Time

In most cases the sleeping MySQL connections should not really be bothering you, however in some cases the very large number of sleeping connections could exhaust all of the available server resources (RAM and CPU) and also you could also hit your max user connections.

You can use this as a temporary solution while you investigate the core issue on why your MySQL connections are not being closed and get your website patched!

Here's a cool short script that you could use to kill those long sleeping MySQL queries that are exhausting the resourcing on your server:

#!/bin/bash

##
#  - Bobby I.
##

# Modify the allowedSleep var if you would like to kill the sleeping processes quicker

allowedsleep=60

sleepingProc=$(mysqladmin proc | grep Sleep)

for i in $(mysql -e 'show processlist' | grep 'Sleep' | awk '{print $1}'); do
	sleeptime=$(mysqladmin proc | grep "\<$i\>" | grep -v '\-\-' | grep -v 'Time' | awk -F'|' '{ print $7 }' | sed 's/ //g' | tail -1);
	sleeptime=$((sleeptime + 1))
        echo "${i} has been sleeping for ${sleeptime} seconds"

	if [ "$sleeptime" -gt "$allowedsleep" ]; then
               	echo "Killed proccess: ${i} as it has been sleeping for more than ${allowedsleep} seconds"; mysql -e "kill ${i}";
               	prockilled=$((prockilled+1));
	fi

done

You can get it from github if you prefer:

https://github.com/bobbyiliev/kill-sleeping-mysql-processes

Just download the script and execute it!

You can also run it as a cron job, the cron would look something like this:

* * * * * /path/to/the/script/mysql_sleeping.sh /dev/null 2>&1

Hope that this helps :)