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.
How about sharing with us more insights on best managed VPS hosting?
Hi, Here is the configurations of msyql server:
wait_timeout=30
interactive_timeout=30
max_connections=300
but still mysql server has connections in sleep mode. Storage engine is innodb and can’t change it to MYISAM.
Kindly suggest how can I remove the connections in sleep mode automatically.
Gaurav
Check slow queries logs and apache access logs. check which request and mysql queries are taking time. Use Mysql cache…
i fixed all these issues even though i am getting same,even i am unable to insert through application……..
MySQL query is taking longer time to execute and it is getting killed. Kindly refer the killed queries:
killed_thread_id:XXXX user:XXXX host:localhost db:XXXX command:Sleep time:23 query:
I’ve contacted my hosting provider and they said that mysql queries are being killed as they are exceeding maximum time to 15 seconds allocated for shared server. So how to get the query optimized please tell me
i fixed all these issues even though i am getting same,even it is killing the insert queries which are executing through application……..
MySQL query is taking longer time to execute and it is getting killed. Kindly refer the killed queries:
killed_thread_id:XXXX user:XXXX host:localhost db:XXXX command:Sleep time:23 query:
I’ve contacted my hosting provider and they said that mysql queries are being killed as they are exceeding maximum time to 15 seconds allocated for shared server. So how to get the query optimized please tell me
ask enable to slow mysql query logs and check your slow mysql queries… you need to add indexes proper database normalization….
MySQL query is taking longer time to execute and it is getting killed. Kindly refer the killed queries:
killed_thread_id:XXXX user:XXXX host:localhost db:XXXX command:Sleep time:23 query:
I’ve contacted my hosting provider and they said that mysql queries are being killed as they are exceeding maximum time to 20 seconds allocated for shared server. So how to get the query optimized please tell me…