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.

8 comments:

  1. Just one clarification please - Does CP30 make use of tomcat-dbcp.jar(deprecated) or tomcat-jdbc.jar (latest) on a Tomcat ver 7.x server??

    ReplyDelete
  2. Hi Perry, I don't have a Tomcat 7 system to hand, but I'd be surprised if c3po makes use of tomcat-jdbc as they are separate connection pool systems. Their website states a dependency on mchange-commons-java-0.2.7.jar but that's all.

    ReplyDelete
  3. Does these settings also apply to High Volume Batch Processing application as well?

    ReplyDelete
  4. No guarantees, you would need to test the settings on your own workloads. Usual advice these days is to avoid DBCP in any case, try the other mentioned pools first.

    ReplyDelete
  5. Thanks Richard. We've hit a roadblock where we are using Commons DBCP 1.4 with Spring Batch+JSR352 approach. We're processing 10k records, the batch runs smooth for 9983 records, however when the last partition starts, the batch goes into the hang state and shows gives the below error:

    at java/net/SocketInputStream.socketRead0(Native Method)
    at java/net/SocketInputStream.read(SocketInputStream.java:161(Compiled Code))
    at java/net/SocketInputStream.read(SocketInputStream.java:132(Compiled Code))
    at oracle/net/ns/Packet.receive(Packet.java:283(Compiled Code))
    at oracle/net/ns/DataPacket.receive(DataPacket.java:103(Compiled Code))

    Do you think this is related to DBCP and quick test either with DBCP 2.2.1 ( last active release as of Aug-15) OR c3p0 should help eliminate this issue?

    Thanks in advance.
    Yogendra

    ReplyDelete
  6. Could be worth a try, but the oracle/net/ns is a clue that this may be driver related. Are you on Oracle?

    A random search on that frame of the stacktrace throws up this link, could be a clue to a fault in the driver:

    http://www-01.ibm.com/support/docview.wss?uid=swg21670914

    ReplyDelete
  7. Yes, I am on Oracle 11g and running the batch under IBM JDK 7.0.

    ReplyDelete
  8. @Yogendra Joshi, as you mentioned you are using IBM JDK 7.0, but have you checked https://commons.apache.org/proper/commons-dbcp/index.html for dbcp1.4 they mentioned to use JDK 6.

    ReplyDelete