tomcat 7.0.42 pooling, hibernate 4.2, mysql rock s

i've read a lot of posts regarding problems with auto reconnecting to mysql from hibernate session. Others mention an increase of mysql wait_timeout (not my favorite), using autoReconnect=true (not recommended), testing connection e.t.c. I am currently trying a few options but i would like to ask if anyone has a rock solid solution using tomcat's connection pooling (not hibernate's c3po). I am looking at the most bullet proof jndi settings even if they are not the best performance tuned.

Thank you very much,

Regards

Excellent question. I use to struggle with this question. The most common answer on stackoverflow is "It depends...." for virtually every problem. I hate to say it but no where is that more relevant than tweaking your connection pool. It really is a game of supply and demand, where your connection requests are the demand and the supply is the number of connections MySQL has available. It really comes down to whether your primary concern is preventing stale connections from being returned from the pool, or whether your concern is ensuring MySQL is not being overloaded with idle connections because your not killing them fast enough. Most people lye in the middle some where.

If you really understand why someone would choose any one connection pool configuration then believe me you will stop searching for the "Rocket Solid" setting because you will know that is like googling for a business plan to your shop; It's entirely rooted in how many connection requests you get and how many persistent connections you are willing to make available. Below I give examples of why you would use certain settings. I reference variables that you will have to change inside the "Resource" tag of the "Context" tag of your Context.xml file. A sample full configuration can be seen at the very bottom.

Low Traffic

In this situation you have few requests to your application so there is a good chance ALL connections in your connection pool will go stale and the first request by your application by a stale connection will cause an error. (Depending on the MySQL driver you are using the error may explain the last successful packet received exceeded the database's wait_timeout setting). So your connection pool strategy is to prevent a dead connection from being returned. The following two options have little side effect for a low traffic site.

Wait Longer Before Killing Connections - You would do this by changing the value of wait_timeout in your MySQL configuration. In MYSQL workbench you can find that setting easily under Admnin > Configuration file > Networking. For a site with lots of traffic this is not often recommended because it may lead to the pool always being filled with lots of idle connections. But remember this is the low traffic scenario.
Test Every Connection - You can do this by setting testOnBorrow = true and validationQuery= "SELECT 1". What about performance? You have low traffic in this situation. Testing every connection returned from the pool is not an issue. All it means is that an additional query will be added to every MySQL transaction you are performing on a single connection. On a low traffic site is this really something you will be worrying about? The problem of your connections going dead in the pool because they are not being used is your primary focus.

Medium Traffic

Check All Connections Periodically -If you don't want to test every connection every time it is used ,or extend the wait timeout, then you can periodically test all connections with a default or custom query of your choosing. For example set validationQuery = "SELECT 1", testWhileIdle = "true", and timeBetweenEvictionRunsMillis = "3600" or whatever interval you want. For very low traffic this is absolutely going to require more work. Think about it. If you have 30 connections in the pool and in 1 hour only 4 get called, then you could have easily checked all 4 connections on each request using the previous testOnBorrow approach with little performance hit. But if instead you do the "Check all every hour" approach then you make 30 requests to check all connections when only 4 were used.

High Traffic

Kill Idle Connections Sooner - This is the situation that has everyone saying you shouldn't extend the wait_timeout and you shouldn't test every connection. It's not a model ideal for every situation. When you have significant traffic every connection in the pool will be utilized and your actual problem will become increasing the number of available connections while actually shortening the length of your wait_time so you don't end up lots of idle connections on the DB. Here is an example of a chap talking about how he has up to 10,000 idle connections a day for a busy site so he wants to lower the wait_timeout Lowering the wait_timeout for busy site

Sample Context.xml Configuration

<Context>   

<Resource name="jdbc/TestDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          timeBetweenEvictionRunsMillis="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000"
          jmxEnabled="true"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
          username="root"
          password="password"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mysql"/>
</Context>

Sample web.xml configuration

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Documentation on Tomcat Pool properties to tweak Tomcat Pool