HikariCP and MySQL Assessment

[Timothy Sandberg]

After trying valiantly to implement HikariCP with Tomcat 8, Hibernate 4.3.10 and a MySQL 5.5 database, I can say that, at this time, it's no better than a "bare bones" Tomcat connection pooling configuration.

One of the biggest myths on the internet is the notion that this MySQL connection string works (you can find Apache Tomcat's example looks like this):

<Resource name="jdbc/coolDb" auth="Container" type="javax.sql.DataSource"
              driverClassName="com.mysql.jdbc.Driver" 
              url="jdbc:mysql://localhost:3306/coolDb?zeroDateTimeBehavior=convertToNull"
              username="coolUser"
              password="coolPassword" />

Yes, it may work for a while, but, eventually, your web application succumb to a slow death.  With each database request, a connection will be opened and left open, until no more connections are available.  In MySQL, the client connections panel will be littered with dead connections.  So this is what I've come up with so far: 

<Resource name="jdbc/coolDb" auth="Container" type="javax.sql.DataSource"
              driverClassName="com.mysql.jdbc.Driver" 
              url="jdbc:mysql://localhost:3306/coolDb?zeroDateTimeBehavior=convertToNull"
              username="coolUser"
              password="coolPassword"  
               
              timeBetweenEvictionRunsMillis="30000"
              minEvictableIdleTimeMillis="60000" removeAbandonedOnBorrow="true" removeAbandonedOnMaintenance="true"
              maxActive="30" maxIdle="10" maxWait="10000"  initialSize="5" validationQuery= "SELECT 1" validationInterval="30000"
              removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />

I'm probably missing even a few more configuration properties because I'm still dissatisfied with the performance and reliability of this connection. If you know of any obvious properties that I've omitted, please let me know.

For this reason, I turned to HikariCP.  Unfortunately, I assumed this idiotic configuration was contained in HikariCP -- under the covers--when the implementation instructions on the HikariCP GitHub site did not indicate configuration of these properties.   But, apparently, it's not, because my MySQL client connections panel was again peppered with dead connections.  I agree--MySQL is horrible, but I'm stuck using it.  Some more TLC with regard to this database and HikariCP might be a worthy alternative for the MySQL scenario.

[Brett Wooldridge]

There is nothing "horrible" about MySQL.  Your application is broken.

If using Tomcat's removeAbandoned "feature" mostly fixes your issue it is further indication to me that the connection leak lies within your code.  What is "horrible" is the very concept of the  removeAbandoned function, and that is why HikariCP does not include such a feature.

What that feature is doing equates to saying, "Look, your application has a leak.  It allocates a connection via getConnection(), and then it never closes it.  But it's OK, we [tomcat] will come around and cleanup the mess [abandoned connections] that you made, and then your application can continue on its merry way."

A connection pool should never hide or cover-up bugs in the user's application.  Your application has a leak, that is crystal clear from the information you provided above.  HikariCP is not going to "fix" that for you, and you shouldn't want it to.  But what HikariCP can do is to help pinpoint the source of the leak.  That is the purpose of the leakDetectionThreshold property.  If you set leakDetectionThreshold to 60 seconds (60000ms), for example, then any code that calls getConnection() but does not return it to the pool by calling close() within one minute will generate a log message.

That log message will include a stacktrace that points to exactly the line of code in your application where the connection was allocated.   Following your code from there you should be able to find exactly where the connection should be closed but is not being closed.  Generally speaking, it is very risky not to surround connection usage with try...finally blocks, wherein the connection is released in the finally.

Stop blaming Tomcat, HikariCP, or MySQL -- they're used by some of the largest and most heavily trafficked websites on the internet without the problems your application is experiencing.  Fix your application.