How to kill Sleep Processes in MySQL server

If you are a developer and you are facing sleep processes on your MySQL server when you run the command line on your MySQL server:

show processlist;

Then you should definitely do something about it because it is a very serious issue for your running website and other visitors are in queue list when the sleep process gets closed then a new visitor will be served in that sleep process.

I have been working on how to close sleep processes in MySQL server for very long now but fortunately I’ve figured this thing out and today I’m here to give you solution guide on how you can handle sleep processes in your MySQL server.

How to avoid MySQL from creating Sleep or ‘idle’ connections

This happens because you have not process timeout limit set on your MySQL config file i.e., my.cnf and on MySQL server. Below I’ve described in details about server timeouts and how you can handle them to avoid MySQL from creating sleep or idle connections ever again.

mysqld server usually timeout database connections based on two server options:

• interactive_timeout
• wait_timeout

By default both are set to a server’s maximum limit 28,800 seconds (8 hours) by default, and 8 hours is pretty much wait time for a sleep process right. Reducing these timeout to something like 300 seconds or even lesser like 180 seconds can definitely make a difference.

To set 180 seconds for wait_timeout and interactive_timeout in mysql follow the steps below:

• First of all, login to Linux server and open my.cnf file using Linux command: nano /etc/my.cnf

• Now you are editing your server’s MySQL config file i.e., my.cnf
• Now add the following code in your my.cnf file

[mysqld]
interactive_timeout=180
wait_timeout=180

• To save my.cnf file, short keys are: Ctrl + C + X then prompt reply YES or NO (Press Y) and hit enter
• You have successfully saved your my.cnf file and now you have to restart MySQL server using the Linux command line below:

systemctl restart mysqld

But, if you don’t want to restart your MySQL server then you should run these two commands by logging into MySQL from Linux command below:

Steps to login to MySQL in Linux:

• mysql -u root -p (hit enter)
• Now paste your mysql password by right clicking on CMD command prompt (hit enter)
• Then you will be logged into your mySQL server where you have to run following commands:

SET GLOBAL interactive_timeout = 180; 
SET GLOBAL wait_timeout = 180;

Note: You should note that this will not close the current connections but it will cause new connections to close in 180 seconds.

Dear friends if you don’t know how to create website with wordpress, I recommend you a post which will help you to learn it. Just read this post How to Create a Website with WordPress