Friday, 7 March 2014

Decent settings for DBCP Connection Pools

The Spring Framework course from 2009 is the first course that we've re-recorded at VirtualPairProgrammers. Although surprisingly little has changed in Spring since then, we felt it was time to polish the course up a little, to use the latest Spring 4, and in particular to use a more modern format for the video - with the second edition you'll be able to view it on iPads and mobile devices, as with most of our other courses.

Note: everyone who bought the first edition of the course will automatically receive the second edition on the day of release - currently slated for around the 14 March 2014, but there may be delays as we complete the editing process.

I have actually made very few changes from the original. One area that I felt worthy of update was in our choice of connection pool. In the first edition we used the Apache DBCP connection pool, largely because it was the pool of choice at that time for the reference manual.

Since then, it's fair to say that DBCP has come in for a lot of criticism, and other pools such as C3PO, Proxool or the Tomcat pool have become more popular.

There's a great debate about this at StackOverflow (see here: http://stackoverflow.com/questions/520585 - a shame they closed the question as "not constructive" because it most certainly was constructive).

In the end, however, I decided to continue using DBCP for the second edition, partly to keep consistency with the old course, but also because actually DBCP isn't that bad - we've used it successfully on several large scale projects with high traffic.

I think the biggest problem with DBCP is that the defaults are so poor. If you configure DBCP with just a driver, url, user and pass, then you're going to end up with a  pool that soon locks up.

On the re-recorded version I alert the viewers to this, and tell you that you really need to tweak the pool to bring it to a performant level. But there isn't time on the course to get bogged down in this, so I pointed the viewers to this blog post, where some more sensible values can be found.

Our default settings are:

  • maxActive = 150
  • maxIdle = 10
  • minIdle = 5
  • initialSize = 5
  • minEvictableIdleTimeMillis = 1800000
  • timeBetweenEvictionRunsMillis = 1800000
  • maxWait = 10000
  • validationQuery = "SELECT 1"
  • testOnBorrow=true
  • testOnReturn=true
  • testWhileIdle=true

And you set each of these properties in the Spring XML in the same way you set the driver etc. Eg <property name="maxActive" value="150"/>

I'm not saying these values are good for any application - you need to test, tweak and tune, but at VPP we use these settings as a starting point, and they are in fact the exact settings we currently have on our live site. Our live site isn't exactly high traffic in the Facebook/Google sense, but we do get heavy traffic when we release a new course, so these settings should be reasonably good for most average websites.

Having said that, you can also switch to other pools quite easily, but I wanted to capture these defaults somewhere.