13

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 :) ?

AlfaTeK
  • 7,487
  • 14
  • 49
  • 90

2 Answers2

15

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.

Keith Randall
  • 22,985
  • 2
  • 35
  • 54
  • is there any mysql timeout variable that i can adjust to avoid this? – AlfaTeK Oct 29 '10 at 16:52
  • 4
    You can set wait_timeout to something smaller (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_wait_timeout) or raise the connection limit. You really want to solve this on the application side, however. Hanging up the connection from the database side risks closing a connection just as the application tries to use it, potentially causing an application error for no good reason. What database connection library are you using? – Keith Randall Oct 29 '10 at 18:24
  • 2
    @KeithRandall but how to identify the Null processes? – Khuram Aug 06 '12 at 09:16
  • @Khuram: What do you mean? They are listed in `show processlist`, you get the ip address it connects from and the username used, if that helps. – Keith Randall Aug 06 '12 at 22:48
3

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/

zpon
  • 1,482
  • 1
  • 15
  • 21