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?
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.
set-variable = long_query_time=120 set-variable = wait_timeout=28000 set-variable = connect_timeout=25
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
(from the mysql shell) show?
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.