The MySQL database server is used for both desktop and web-based applications, and so ships with default values that try to meet both of their needs. In some cases, what’s appropriate for one is not appropriate for another, and idle/sleeping connections is one of them.

Database connections remain open as long as the caller requires them, subject to some timeout options. An open connection that is not executing a query is ‘sleeping’, ready to process the next statement. The default value for timeouts is 28,800 seconds, or 8 hours.

Why this is a good thing

For desktop applications, sleeping connections are a good thing – you wouldn’t want to keep having to check your connection and/or reconnect each time you want to run a query. Desktop applications typically run for reasonable amounts of time, and so they make good (re)use of sleeping connections.

For web applications, sleeping connections aren’t so useful. Most scripts only run for a couple of seconds, at most, and then terminate, so sleeping connections aren’t re-used (unless you’re running with ‘persistent’ connections or using a connection pooling system).

Why this is a bad thing

Although most programming/scripting languages automatically close database connections when the programme/script terminates, this doesn’t happen if the it is terminated abnormally (if the web server is suddenly killed, for example, or the programme crashes). This can leave sleeping connections on the database server, which will remain there until they time-out or someone explicitly kills them.

In particular, if a page/site receives a large number of visitors in a short amount of time, it will create a lot of database connections in ‘connect’ state while the authentication is processed. If this web server load causes the web server to die, the connections will not automatically be closed, and so may leave a significant number of sleeping connections.

Each ‘sleeping’ connection takes up an entry in the connection list, and so you can hit your maximum connection limit if you’re not careful. These idle connections will prevent new, active connections being made, which can never be a good thing.

Also, each idle connection runs as a thread, tying up system resources. Although sleeping connections won’t use much CPU power or disk I/O, they will hold memory, and so an abnormally large number of connections could push the system into using swap memory, which could have a major negative effect in performance.

Closing sleeping connections automatically

The length of time a connection can be sleeping for before being automatically closed is controlled by the ‘wait_timeout’ system variable.

This only affects scripted connections – if you connect using an interactive client (like the ‘mysql’ command) then it will use the ‘interactive_timeout’ value. If you want to check your change has been made, write a script to output the value – ‘SELECT @@wait_timeout’ should be enough.

For a database server used only by a web application, this timeout should be set fairly short – I usually use 10 seconds. This may seem quite short, but this is the time between SQL statements being run – in many cases, a visitor to a web site will not wait much longer than 10 seconds before either going somewhere else or hitting the ‘refresh’ button (which may create another database connection, which may make the script take so long they hit ‘refresh’ button again…). Even if you’ve got some kind of feedback system in place to show progress to the visitor (so they don’t do either of those actions) it still means that the script would have used 10 ‘real’ seconds of processing power – if your application requires more than that kind of processing, increase the timeout accordingly!

Depending on how you access the database server, you may also want to lower the ‘interactive_timeout’ value as well. This would prevent people with interactive sessions holding on to connection slots, although it can be quite useful to have a session always open in case of emergencies (although the MySQL database server reserves an additional connection for superusers, this won’t help if someone else already has that slot or your host is is blocked and you need to run ‘flush hosts’)

If you are using persistent connections, or a connection pooling system, keep the timeout to a reasonable value – even though the scripts may not run for very long, they may be able to share the sleeping connections. The timeout is reset each time a query is run, so for a moderately busy site a low figure can still be used. On a quiet site, you need to decide whether holding the database connection open for a long period of time is more efficient than having to reconnect.