Question
There are a lot of times server might be reporting to be slow due to MySQL. You can check what's the currently active MySQL query by the following command:
mysqladmin pr
You might see a lot of the following queries:
| 44541 | cp_DB1| localhost | cp_DB1 | Sleep | 0 | | | 0.000 |
| 44544 | cp_DB2 | localhost | cp_DB2 | Sleep | 0 | | | 0.000 |
| 44552 | cp_DB2 | localhost | cp_DB2 | Sleep | 0 | | | 0.000 |
| 44553 | cp_DB2 | localhost | cp_DB2 | Sleep | 0 | | | 0.000 |
| 44556 | cp_DB2| localhost:33814 | cp_DB2 | Sleep | 0 | | | 0.000 |
| 44557 | cp_DB2 | localhost:33816 | cp_DB2 | Sleep | 0 | |
What are those "Sleep" MySQL queries?
Answer
When a database connection is created, a session is also created on the database server simultaneously, but if that connection and session is not closed properly, then the query goes into sleep mode after the wait time gets over.
As per MySQL reference manual, “Sleep is the thread waiting for the client to send a new statement to it”.
So, a sleep query is the query that waits for the timeout to terminate. That means query which takes time to execute and terminate goes in the sleep status.
Effects of a large number of MYSQL query – sleep :
- Increased consumption of CPU and memory resources (RAM, cache, and processor).
- Slowing down of server.
- Increased downtime for websites -hackers try to slow website using sleep SQL injections
How to overcome these issues?
To overcome this issue of SQL sleep command, MySQL uses two parameters:
interactive_ timeout and wait_ timeout.
These require certain values to be set to help query run-up to that set time.
By default, both the parameters have set the value as 28800 seconds (i.e. 8 hours).
It is recommended to have a qualified system administrator and web developer to review your site's coding to ensure those SLEEP queries does not stay active too long to reduce extensive CPU resources.