Hello everyone, have you ever experienced a condition on a MySQL server where there were lots of processes that went to sleep? If so, that means we are the same: lol:. A few days ago, I experienced this condition which hampered web application operations. sleeping process that stacks can increase the use of CPU and other memory resources, thereby slowing down server performance. Processes that go to sleep usually occur when the mysql session is not closed properly. Many cases that can trigger this, if in the case of my server that triggers a buildup of processes that are in sleep status, is when application / program fails to process data from mysql, so that tmysql session cannot be closed properly and the process is in a sleep state. If something like that happens, we need to kill the sleep process. Otherwise (by default) the sleep process will only close after 28800s or 8 hours. To make this easier, I created a script whose function is to kill mysql sleep process automatically which I schedule for a cronjob. Here’s how to make it :
1. Create sql file to generate query kill mysql sleep process
First of all, create sql file to generate a query which will be used to kill mysql sleep process, for example I named it create_kill_query.sql (you are free to name it).
vim /usr/local/sbin/create_kill_query.sql
You can adjust the path where you want to put it, then enter the code below :
select concat('KILL ',id,';') as '# kill this'
from information_schema.processlist
where Command = 'Sleep' and Time > '30';
2. Create shellscript file to kill mysql sleep process automatically
Second, create shellscript file, for example, I name it kill_mysql_sleep_process.sh (you are free to name it).
vim /usr/local/sbin/kill_mysql_sleep_process.sh
You can adjust the path where you want to put it, then enter the code below :
#!/bin/bash USER="yourUser" PASSWORD="yourPassword" HOST="yourIpHost" cat /dev/null > /usr/local/sbin/kill_query.sql && mysql -u $USER -p$PASSWORD -h $HOST < /usr/local/sbin/check_kill_query.sql > /usr/local/sbin/kill_query.sql && cat /usr/local/sbin/kill_sleep_query.sql > /var/log/mysql_kill_sleep_query.log && mysql -u $USER -p$PASSWORD -h $HOST < /usr/local/sbin/kill_query.sql && ( echo "`date` === Sleep query has been killed" && echo "================================================") >> /var/log/mysql_kill_sleep_query.log 2>&1
In this shellscript I made, I recorded the process in a log which I entered in /var/log/mysql_kill_sleep_query.log . You can do a test first by running the shellscript with sh /usr/local/sbin/kill_mysql_sleep_process.sh. If it is successful and the log is formed, you can continue with step 3 to attach it to a cronjob.
3. Create cronjob to run it automatically
Finally, we just need to set the cronjob to run automatically according to the time we want, here I have made an example of making it run automatically every 30 minutes at 08.00 – 17.00 every day. First, open the cronjob editor
cronjob -e
then set the time to run a shellscript like this
*/30 08-17 * * * sh /usr/local/sbin/kill_mysql_sleep_process.sh
after that save it. So every 30 minutes at 08.00 – 17.00 cronjob will run shellscript to kill mysql sleep process automatically. For rules on other cronjobs, you can read my article about cronjobs in Rules on CronJob.
Well, that’s a little information that I can share, I hope this information can be useful, Thank you 😀