I faced the sleep query issue sometimes. Many times that kills the server. When ever you are using dedicated virtual hosting server that time you need to me very careful how you are setting up the apache and mysql. we solved mysql sleep processes issue
mysql sleep processes issue solved
I found some major reasons for mysql sleep processes. Here I am giving some reasons:
[viral-lock message=”Reasons and Solution is Hidden! It’s Visible for Users who Liked/Shared This article on Facebook or Twitter or Google+. Like or Tweet this article to reveal the content.”]
1. apache requests increase
2. mysql queries increase
3. mysql slow queries increase
4. apache connections start to wait on slow mysql queries
5. apache connections take longer to close
6. more apache connections are opened
7. with each apache request a new sleeping mysql connection is made
8. mysql & apache reach max_connections
9. server slows to a crawl
[/viral-lock]
Using linux top command you can check the load average and which process is taking so much memory of your server. If you saw mysqld is taking much memory of server then you should check using following mysql commands:
> show full processlist;
using this command you can check the mysql processes. Which sql queries being fired on mysql server. If you found so much sleep processes then you should check your mysql variables and settings. If so much mysql queries being fired on specific table then you should change the table storage engine type.
Using following command you can change the storage engine type.
> ALTER TABLE tablename ENGINE = MYISAM;
MYISAM storage engine type is faster then inodb. you can create the mediator tables also for big size table to handing a load of table.
Then major change for solving the issue of sleep processes is, you should change the wait_timeout variable. Default value is 2800 seconds.
mysql> show variables;
you can change the setting using following mysql command. Best value is 60 second need to be set for wait timeout.
mysql> show variables like ‘wait_timeout’;
mysql> set global wait_timeout=60;
mysql>show variables like ‘wait_timeout’;
After doing above changes you can check the mysql processlist. If you are still facing issues with mysql then please write to me.
you will see the button as like this.
