Mysql show processlist lists many processes sleep and info = null?

Question

I'm injecting a stress test into my web app that connects to a mysql server and I'm monitoring the show processlist of mysql.

When the load is high (high swap i/o) I get many processes like that:

| 97535 | db| localhost | userA | Sleep   |  515 |         | NULL 
| 97536 | db| localhost | userA | Sleep   |  516 |         | NULL 
| 97786 | db| localhost | userA | Sleep   |  343 |         | NULL 
| 97889 | db| localhost | userA | Sleep   |  310 |         | NULL 

But I can't understand why are they still there and are not killed? This eventually leads to my app using all max_connections and stop processing incoming requests...

Any idea what are those processes and what are they doing there :) ?

Answer 1

Those are idle connections being held by a client. You should make sure that whatever client library you are using (JDBC, ...) is configured to not keep unused connections open so long, or that your # clients * max # of connections isn't too big.

Answer 2

My guess is that you are using persistent connections, e.g. pconnect in php:

[..] when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection

and

[..] the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use

I had a similar situation, and was using Codeigniter with pconnect turned on. After turning it to off (see how) every connection was closed down properly after use, and my MySQL processlist was empty.

Performance: The above does not argue about performance, but simply tries to explain why you might see a lot of Sleeping connections in MySQL. It might not be negative, with regard to performance, to have the connections stay active. More info at: http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/