Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

migrated from stackoverflow.com May 6 '13 at 12:55

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for the supplementary articles –  Usman Mutawakil Jun 18 '13 at 17:52

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.

share|improve this answer
    
Thanks for the response.Why go through all of that when I can just extend the wait_timeout and never have to worry about dead connections? –  Usman Mutawakil May 2 '13 at 13:27
    
I apologize. I removed the bit about c3P0 because it seemed to overshadow my fundamental question. –  Usman Mutawakil May 2 '13 at 13:34

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.