Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SHOW PROCESSLIST in MySQL command: sleep

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.

like image 417
gthm Avatar asked Aug 30 '12 09:08

gthm


People also ask

How do I see Sleep connections in MySQL?

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). Show activity on this post.

What is sleep command in MySQL?

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.

How can I see long running queries in MySQL?

Run the 'show processlist;' query from within MySQL interactive mode prompt. (Adding the 'full' modifier to the command disables truncation of the Info column. This is necessary when viewing long queries.) Pro: Using the full modifier allows for seeing the full query on longer queries.


1 Answers

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.

like image 147
Rufinus Avatar answered Oct 08 '22 18:10

Rufinus