Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

But we didn't use any persistent connections. Why are there many idle connections? We noticed that by using 'show processlist' command and the time was about 4000-8000 seconds. Those connections looked like always 'sleep'. We're using Nginx with PHP in fast-cgi mode, and we use PDO library. Any suggestions?

share|improve this question

4 Answers 4

That sounds like a client isn't closing its connections properly, so the server keeps them hanging around until they time out. If a client just opens a connection, then runs a query and then abandons the connection, it is NOT closed (even though the client can't access it any longer after throwing the handle away). My understanding is that the web server should close these connections when the page completes. Looks like this isn't happening. As to why, I don't know.

The default timeout setting on the server is 6 hours, which means that on a web server this can easily build up to several thousand connections waiting for a timeout.

share|improve this answer

hmmm, it depends.

Certainly the DBMS won't invent these connections on its own. While the overhead in creating a connection to mysql is low, there's still going to be a gap between creating the connection and firing a query off, and between the query completing and the results being polled completely, and then a further gap before the script closes down the connection. These will be even more pronounced if your code opens the connection before its needed, and/or waits too long before closing the connection.

However in the absence of persistent connections, no web based software should be holding a connection open for more than a couple of seconds. If you have mysql replication enabled then this will show as a thread running since startup.

What does

SHOW FULL PROCESSLIST

(from the mysql shell) show?

share|improve this answer
    
those connections were always 'sleep', seems they did not do anything~ –  Mickey Shine Sep 27 '10 at 9:36
  • Disable persistent MySQL queries in your php.ini configuration file.
  • Edit your /etc/my.cnf file and set some additional limits.

set-variable = long_query_time=120 set-variable = wait_timeout=28000 set-variable = connect_timeout=25

share|improve this answer

I noticed you tagged fastcgi. I suspect thats your problem.

PHP (assuming thats what your doing this with) only terminates the connection once the interpreter has exited and cleanup starts, meaning the connection remains open as idle until garbage collection. FastCGI typically doesn't 'terminate' as it runs as a service which is why your experiencing these long sleep times.

The best way to fix this it to explicitly declare a mysql_close() at the end of your code. Anyway, its best practice to clean up after yourself! But as a kludge workaround you could set a wait_timeout in mysql to destroy the connections.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.