mysql sleep processes issue solved

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

mysql sleep processes issue solved
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.

Published by

Purab

I am Purab from India, Software development is my profession and teaching is my passion. Programmers blog dedicated to the JAVA, Python, PHP, DevOps and Opensource Frameworks. Purab's Github Repo Youtube Chanel Video Tutorials Connect to on LinkedIn

9 thoughts on “mysql sleep processes issue solved”

  1. 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

    1. 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

  2. i fixed all these issues even though i am getting same,even it is killing the insert queries which are executing through application……..

    1. 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

  3. 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…

Leave a Reply

Your email address will not be published.