Why decrease the wait_timeout configuration parameter in MySQL?

Question

Question

I'm working on an application that uses a connection pool in Apache Tomcat to communicate with MySQL. I was wondering why would you want to use a smaller wait_timeout than the default of 28,800 seconds? I see plenty of downsides to decreasing the wait timeout but what are the upsides?

Background

The parameter wait_timeout is defined as

The number of seconds the server waits for activity on a noninteractive connection before closing it.

Problem

If I make the wait_timeout too small then MySQL kills connections that are still valid in the connection pool my web application is using. In my situation I am the only person currently using the application at any time so naturally the db connection goes idle when I'm not testing it. I could solve this problem by simply extending the wait_timeout on MySQL to something very large so that even if I'm away from my desk for 2 weeks the pool never returns a dead connection.

QUESTIONS

• Why would anyone ever want a shorter wait_time?
• Is the overhead of storing connections that bad?
• Although I understand connection pools alleviate the overhead of CREATING connections, is it such an issue to keep idle connections in memory?

Answer 1

You should be just fine extending wait_timeout

Notice the maximum value for wait_timeout for MySQL 5.0, 5.1, 5.5, 5.6

• Linux : 31536000 seconds (365 days, one year)
• Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)

These maximums would not exist of mysqld could not handle them.

Connection pooling only saves on overhead in terms the alternative: destroying and reallocating memory for new DB Connections. This could cause the OS on the DB Server to engage in a lot more swapping to juggle memory.

I wrote about this over a year ago : How costly is opening and closing of a DB connection?

Lowering the wait_timeout could definitely throw Apache into confusion because there and PHP and Apache objects for DB Connections still instantiated while the DB Connection decides to terminate early and without warning. In that event, you may have to scan your netstat for any TIME_WAIT on connections over port 3306 or whatever port MySQL is using.

• May 20, 2011 : Why is MySQL caching data on our dynamic PHP site?
• Aug 17, 2011 : Too Many Connections
• Oct 26, 2011 : What could be causing strange query timeouts between PHP and MySQL?
• Feb 27, 2012 : MySQL connection count much larger than query count

If such exist and impedes Apache from opening a new connection, you would have to kill the TIME_WAITs yourself. See my Feb 01, 2012 ServerFault post MySQL lowering wait_timeout value to lower number of open connections on how to do so.

Answer 2

Suppose you have a web app with no connection pooling.

Now suppose, also, that your web app leaks connections (does not close them properly - for example: by not using finally blocks in java). Leaky connections are much more problematic with a long wait-timeout (the default is 8 hours). The app will quickly run out of connections (mysql's "Too many connections" error).

In a typical web app, a connection is opened and closed all within the scope of a single http request. So no connection should be needed for nor than a few seconds. By lowering the wait-timeout, you should be able to eliminate a "too many connections" problem without having to fix the code.

With that said, the correct solution is still to go through an fix the code to eliminate any leaky connections.

Answer 3

A reason to decrease wait_timeout is to avoid a situation where an idle/sleeping connection is holding a lock. Consider this scenario:

1. Client connection acquires an exclusive lock.
2. Client machine disappears without closing the transaction and releasing the lock. E.g. a power outage on the client machine (or on a router on the way to the client machine). Or could be as simple as a client bug, for example not finishing the transaction.
3. Since the connection was not properly closed, MySQL keeps it around for wait_timeout period which defaults to 8 hours. Every other connection that tries to use the lock will be crashing/timing out for 8 hours.

You can fix the client bugs, but you can not guarantee that client connection will be properly closed. Something similar described here.

Answer 4

not testing at all is the very fastest approach, not necessarily the most scalable, and almost never the best. note that the docs you are referring recommend for occasional tests of idle Connections, even for apps that prioritize speed over Connection reliability: "Setting a fairly long idleConnectionTestPeriod, and not testing on checkout and check-in at all is an excellent, high-performance approach."

fairly long might be as little as ~300 seconds, unless your pool is very large. (A Connection tests takes on the order of a millisecond, so the overhead of say 50 every 5 minutes is very low, particularly if you set an efficient preferredTestQuery, which you should (try SELECT 1 on MySql.)

c3p0 gives you LOTS of tools for dealing with Connection timeouts -- any Connection testing scheme will purge timed out Connections, or you can simply use config parameters maxIdleTime or maxConnectionAge if you prefer not to test.