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?
The parameter wait_timeout is defined as
The number of seconds the server waits for activity on a noninteractive connection before closing it.
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.
- 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?