Mysql – Why decrease the wait_timeout configuration parameter in MySQL

jdbcMySQLwaits

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?

Best Answer

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.

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.

Related Question