Home / server / Questions / 24191
In Process
deadprogrammer
Asked: 2009-06-12 07:45:07 +0800 CST

MySQL sleeper queries

  • 772

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.

mysql

2 Answers

  1. 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.

    • 5

  2. 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...

    • 2