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 :)
Recent Posts

How DigitalOcean Simplifies Cloud Computing for Developers
2023-01-18 12:35:28
How to Get Current Route Name in Laravel
2020-11-08 08:57:11
How to check the logs of running and crashed pods in Kubernetes?
2020-10-28 09:01:44
Top 10 VScode Shortcuts For Mac and Windows to Help You be More Productive
2020-10-28 07:12:51