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.
Paste the Contents and replace
$MYSQLUSER with username and
$USERPASS with the password of the MySQL user.
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.
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”.