Should I worry about mysql sleep status process in processlist

Solution 1:

Even the most powerful ones of us need to sleep sometimes. Without sleep one becames anxious and insomnia can lead to all kinds of serious symptoms.

More seriously: sleep state means that MySQL process has done with its query, but the client-side did not yet exit. Many web applications don't clean up their connections afterwards, which leads to sleeping MySQL processes. Don't worry if there are only handful of them; MySQL will clean up those after a configurable timeout period (wait_timeout).

Or if your web application uses persistent connections and connection pooling, then it's perfectly normal to have even lots of sleeping processes: in that case your application just opens up for example 100 SQL connections and keeps them open. That reduces connection opening/closing overhead. Unless your application is a very busy one, it's normal that not nearly every SQL process has something to do, so they sleep.

Solution 2:

No, don't worry about them unless you have thousands of them. Usually they indicate a database connection that is currently not doing anything, but otherwise still alive.

Many websites are built such that at the beginning of processing a page, a database connection is opened, then used throughout the generation of the page and finally discarded at the end. If the discarding is done properly, the database connection is closed and the server will then kill the relevant thread, which means this connection disappears from the process list.

If the connection is not closed, it may remain in "SLEEP" state until it times out. In that case you may end up with lots of sleeping processes. but unless you run into memory issues on the db server, this isn't a big problem either.

Solution 3:

Before increasing the max_connections variable, you have to check how many non-interactive connection you have by running show processlist command.

If you have many sleep connection, you have to decrease the value of the "wait_timeout" variable to close non-interactive connection after waiting some times.

  • To show the wait_timeout value:
SHOW SESSION VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

the value is in second, it means that non-interactive connection still up to 8 hours.

  • To change the value of "wait_timeout" variable:
SET session wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)

After 10 minutes if the sleep connection still sleeping the mysql or MariaDB drop that connection.

Tags:

Mysql

Recent Posts