Apache Commons DBCP strange behavior

Question

I'm trying to figure out a problem that we have in a high traffic site using Tomcat 6.0.37 and MySQL 5.5 underneath the cover. The test load was 200 concurrent connections to Tomcat - all connections to the same URL and all of them hitting the database.

To do our connection pooling we're using the embedded tomcat-dbcp package. Here's the resource configuration that we use:

    <Resource name="jdbc/appDataSource" auth="Container"
            type="javax.sql.DataSource"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost/myapp?autoReconnect=true&amp;characterEncoding=UTF-8"
            username="root"
            password="*****"

            testOnBorrow="true"
            maxWait="5000"
            maxIdle="1"
            maxActive="30"
            validationQuery="SELECT 1"
            validationInterval="30000"
            timeBetweenEvictionRunsMillis="30000"

            logAbandoned="true"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
    />

When I run some test traffic on that site I see that the connection pool looses connections pretty extensively. After about a minute I've had over 10000 connection attempts to MySQL and the pool never held more than 20 connections - all of them were almost immediately closed.

I've tried switching to Apache Commons by adding it to the lib folder and including the property

            factory="org.apache.commons.dbcp.BasicDataSourceFactory"

in my configuration but it gave me the exact same results.

I tried switching to C3P0 and BoneCP which in fact worked quite nice and I saw the max of 30 connections being held - no new or dropped connections on MySQL

I've also tried using the connection pool from Apache Tomcat 7 (just for the sake of it) and it also worked quite nicely with the following configuration:

    <Resource name="jdbc/appDataSource" auth="Container"
            type="javax.sql.DataSource"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost/myapp?autoReconnect=true&amp;characterEncoding=UTF-8"
            username="root"
            password="******"

            maxIdle="1"
            maxActive="30"
            validationQuery="SELECT 1"
            validationInterval="30000"
            timeBetweenEvictionRunsMillis="30000"

            logAbandoned="true"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
    />

I wonder if there's something really wrong with Apache Commons DBCP that it doesn't work even in the simplest case? Is there an explanation to all this?

Answer 1

The source of your problem is the low maxIdle setting. Setting maxIdle to 1 means that no more than 1 connection can sit idle in the pool at a given time. Under heavy concurrent load, connections will sometimes be returned at a faster rate than they are being borrowed, resulting in a momentary buildup of idle connections in the pool. With maxIdle set to 1, all but the first idle connection will be closed on return. Either leaving maxIdle at the default (8 for 1.x) or setting it equal to maxActive should improve your performance.

See https://commons.apache.org/proper/commons-dbcp/api-1.4/org/apache/commons/dbcp/BasicDataSource.html#maxIdle.

Answer 2

DBCP is considered out of date and not production grade for system with heavy load on them (multi-threading), the use of C3P0 is recommended over DBCP.

C3P0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2.

It seems that Tomcat 7 came with a new connection pool as a new feature; it is a replacement to the commons-dbcp connection pool. While the commons-dbcp connection pool works fine for small or low traffic applications, it is known to have problems in highly concurrent environments (think multi-core/multi-cpu).

Please refer to Connection pooling options with JDBC: DBCP vs C3P0 for more detail about DBCP vs C3P0.