110

When I run SHOW PROCESSLIST in MySQL database, I get this output:

mysql> show full processlist;

+--------+------+-----------+--------+---------+-------+-------+-----------------------+
| Id     | User | Host      | db     | Command | Time  | State | Info                  |
+--------+------+-----------+-------+---------+-------+-------+-----------------------+
| 411665 | root | localhost | somedb | Sleep   | 11388 |       | NULL                  | 
| 412109 | root | localhost | somedb | Query   |     0 | NULL  | show full processlist | 
+--------+------+-----------+-------+---------+-------+-------+------------------------+

I would like to know the process "Sleep" that is under Command. What does it mean? Why it is running since a long time and showing NULL? It is making the database slow and when I kill the process, then it works normally. Please help me.

JYelton
  • 35,664
  • 27
  • 132
  • 191
gthm
  • 1,878
  • 4
  • 26
  • 37
  • it does nothing it just sits there and "waits" for a connection. – Rufinus Aug 30 '12 at 09:56
  • 1
    can we find which query is waiting for connection ? does my que makes some sense ? ANd why it is slowing down my database ? – gthm Aug 30 '12 at 10:01
  • Is it *really* slowing your database down? its doing nothing. Its basically a connection thats doing nothing - eg perhaps you connected on another terminal a while back, and didnt disconnect etc. – BugFinder Aug 30 '12 at 10:03
  • 9
    its not a query waiting for connection. its a connection pointer waiting for the timeout to terminate. and it doesn't have an impact on performance. The only thing its using is a few bytes as every connection does. The really worst case its using one connection of your pool, if you would connect multiple times via console client and just close the client without closing the connection you could use up all your connections and have to wait for the timeout to be able to connect again... but this is highly unlikely :-) – Rufinus Aug 30 '12 at 10:12
  • 3
    @Rufinus, I have the same problem. Why you say **but this is highly unlikely** ? And which parameters is related to config timeout sleeping connections in my.cnf? – Hamidreza Nov 29 '12 at 10:33
  • 1
    @hamidreza66 see http://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-to-many-connections and http://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep – Rufinus Nov 29 '12 at 13:22
  • Does this answer your question? [MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?](https://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-too-many-connections) – Dr. Gianluigi Zane Zanettini Sep 02 '22 at 14:06
  • Something is off. While state=sleep, trx_rows_modified = 823 so definitely not something we wanted to kill yet killing it got our server back to working again. you do not show your trx_rows_modified above. There has to be more to this. – Dean Hiller Jun 13 '23 at 13:54

4 Answers4

95

It's not a query waiting for connection; it's a connection pointer waiting for the timeout to terminate.

It doesn't have an impact on performance. The only thing it's using is a few bytes as every connection does.

The really worst case: It's using one connection of your pool; If you would connect multiple times via console client and just close the client without closing the connection, you could use up all your connections and have to wait for the timeout to be able to connect again... but this is highly unlikely :-)

See MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"? and https://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep for more information.

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
Rufinus
  • 29,200
  • 6
  • 68
  • 84
  • 3
    The problem can be if you have limited connections to database. Because even those connections don't have an impact on performance, they still count as a connection. – mrded Sep 25 '17 at 12:17
33

"Sleep" state connections are most often created by code that maintains persistent connections to the database.

This could include either connection pools created by application frameworks, or client-side database administration tools.

As mentioned above in the comments, there is really no reason to worry about these connections... unless of course you have no idea where the connection is coming from.

(CAVEAT: If you had a long list of these kinds of connections, there might be a danger of running out of simultaneous connections.)

Drew
  • 6,311
  • 4
  • 44
  • 44
8

I found this answer here: https://dba.stackexchange.com/questions/1558. In short using the following (or within my.cnf) will remove the timeout issue.

SET GLOBAL interactive_timeout = 180; SET GLOBAL wait_timeout = 180;

This allows the connections to end if they remain in a sleep State for 3 minutes (or whatever you define).

Antony
  • 3,875
  • 30
  • 32
0

Sleep meaning that thread is do nothing. Time is too large beacuse anthor thread query,but not disconnect server, default wait_timeout=28800;so you can set values smaller,eg 10. also you can kill the thread.

ding
  • 11