Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When show full processlist; returns a number of "sleep" status queries with 0 time column, what could be causing this? How does MySQL actually decide if a connection needs to sleep or not? The application that is talking to mysql is not using persistent connections.

share|improve this question
add comment

2 Answers

The sleep state just indicates an idle connection. That is a client which is connected but not performing any queries.

I would suggest checking that your application closes any unused MySQL threads once it has finished with them, even if you aren't using persistent connections.

The idle connections can be "controlled" server side with the variable wait_timeout. But you are better off finding the root cause of the problem.

share|improve this answer
    
+1 Quite right about the root cause. –  msanford Jun 12 '09 at 14:32
add comment

What do you get when you

mysql> show global variables like "wait%";

Try reducing it by editing my.cnf, or with the following code (as root)

mysql> SET GLOBAL wait_timeout=28801;

There is a good article on (the excellent) MySQL Performance Blog that deals with sleeping queries http://www.mysqlperformanceblog.com/2007/02/08/debugging-sleeping-connections-with-mysql/

This is worth a look as well http://rackerhacker.com/2007/05/20/mysql-connections-in-sleep-state/

Another thought re-reading Dan's comment: The sleep(0) time is odd, though. It would seem to indicate to me that there are persistent connections that aren't really sleeping, but doing something at least every second to reset the timer. If you know the webapp isn't using persistent connections, perhaps it's calling sleep() directly, somewhere in a loop or timer function?

Additionally, if you're using, say, PHP, you might head into php.ini and set mysql.allow_persistent = Off and see what happens.

PS What version of MySQL are you running? Something similar was reported as a serious bug in 4.1, but that was back in 2005...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.