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.

If you don’t have a MySQL password Delete the " -u $MYSQLUSER -p$USERPASS " part from the file from both lines.

You can also use MySQL’s root password, Please Note that Server’s root password and MySQL’s root password aren’t same.

 

 

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

A new screen will open, Type ls and confirm if you’re in the same directory as the bash file created above.

Now run,

./mysql_kill_sleep.sh

This should be the output on your screen and nothing else.

 

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”.

Use this script if you are not using persistent connections with MySQL, Using it otherwise may make the Websites Slower. I’ll let you be the judge of that. Best of Luck!

 

~
Regards
BaseZap
Your Friends at WebHosting