SHOW PROCESSLIST in MySQL command sleep

Question

When I run SHOW PROCESSLIST in MySQL database  I get this output   mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot Sleep quot  Entries leading to  quot Too many Connections quot   and https   dba stackexchange com questions 1558 how-long-is-too-long-for-mysql-connections-to-sleep for more information

[mysql] SHOW PROCESSLIST in MySQL command: sleep

Examples related to mysql

Examples related to processlist