Make MySQL Faster by Killing Sleep Connections Automatically

Here’s a Bash script to kill sleeping MySQL connections automatically and almost immediately to decrease MySQL CPU Usage and make MySQL Faster. This tutorial works only if you have a server with root access, and you’re logged in as root.

This script uses about 0.25% on a normal VPS with 1vCPU of the CPU with 500 milliseconds of sleep time between the loops, sleep time between the loops can be increased to decrease CPU usage.

#!/bin/bash
echo “MySQL Sleep Kill Process is running, Press Ctrl+C to STOP, Press CTRL+A+D to exit screen and leave the process running”
while :
do
mysql -u $MYSQLUSER -p$USERPASS -e ”
select
— *
id
from information_schema.processlist
where command = ‘Sleep’
and time > 1
and host =’localhost’
” | while read id;
do
if [[ “id” == “$id” ]]
then
continue
fi
mysql -u $MYSQLUSER -p$USERPASS -e “kill $id”;
done
sleep 0.5
done

How to Use?

Install nano and screen

#For CentOS Based Systems
yum install nano screen
#For Ubuntu Based Systems
apt-get install nano screen

Create a File Named mysql_kill_sleep.sh and paste the contents of bash file from above.

nano mysql_kill_sleep.sh

Paste the Contents and replace $MYSQLUSER with username and $USERPASS with the password of the MySQL user.

Now run,

chmod +x mysql_kill_sleep.sh

You can also use chmod 777 if only +x creates problems.

Now, Setup a Screen

screen -S mysqlkillsleep

ls and confirm if you’re in the same directory as the bash file created above.

Now run,

./mysql_kill_sleep.sh

Now Press Ctrl+A+D to detach from the screen. Now your MySQL sleep processes are getting killed within 1 seconds(maximum) of them being created, You can change the sleep from 0.5 to sleep 0.3 or something according to your usage in the bash file.

If you need to re-attach to the screen and stop the process –

screen -r mysqlkillsleep

Press Ctrl +C

exit

You can restart it by starting again from the step of “Setting up the screen”.