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.
echo "MySQL Sleep Kill Process is running, Press Ctrl+C to STOP, Press CTRL+A+D to exit screen and leave the process running"
mysql -u $MYSQLUSER -p$USERPASS -e "
where command = 'Sleep'
and time > 1
and host ='localhost'
" | while read id;
if [[ "id" == "$id" ]]
mysql -u $MYSQLUSER -p$USERPASS -e "kill $id";
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.
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.
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.
This should be the output on your screen and nothing else.
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
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!
Your Friends at WebHosting