Sleeping Queries

Why would mysql not clear sleeping queries properly?

In order for our site to run, we have to set our wait_timeout to 5 in to properly clear the connections or we reach our max connections in a matter of seconds.

If we set the wait_timeout to the default of 28800, most of the sleeping connections never clear out until they hit that limit which causes the connection count to max out constantly.

Any pointers would be highly appreciated.




What operation system are you using ? And what kind of language ?

Are you closing connection properly in your application by calling mysql_close() ?

Typically if application does not close connection it is automatically closed when connection is aborted, ie process terminates.

In some cases however it may take a while for remote size to discover connection was aborted which may show up as sleeping connection for rather long time.

Also how does your processlist looks ?

We are running Windows Server 2003, MySQL 5.0.24, and PHP5. Also, I am explicitly closing the connection and the sleeps are still there.

The process list looks good with the exception of it being filled with sleeps from 1 second to 5 seconds, which is where they get killed by the wait_timeout.