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

mysql mysql-management
AlfaTeK picture AlfaTeK · Oct 29, 2010 · Viewed 34.5k times · Source

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

Keith Randall picture Keith Randall · Oct 29, 2010

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.

Related questions

ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

I seem to be unable to re-create a simple user I've deleted, even as root in MySQL. My case: user 'jack' existed before, but I deleted it from mysql.user in order to recreate it. I see no vestiges of …

Read More
How to set root password to null

How can I change the password for root user of MySQL to null -- meaning no password or '' -- from the MySQL command line client?

Read More
mysql command for showing current configuration variables

Can not find a command that displays the current configuration of mysql from within the database. I know I could look at /etc/mysql/my.cnf but that is not what I need.

Read More