commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Sicker <>
Subject Re: [DBCP] Connection pool not behaving as I expect
Date Fri, 02 Mar 2018 03:48:52 GMT
Take a look inside commons-pool for the instrumentation (e.g., JMX). You
can also track usage on borrow and other leaks.

Also, Tomcat uses DBCP as it is.

On 1 March 2018 at 21:46, Shawn Heisey <> wrote:

> On 3/1/2018 4:46 PM, Gary Gregory wrote:
>> I do not think this is a question I, or anyone here, can answer
>> generically. I can read between that lines that you must feel frustrated
>> and I certainly empathize with that. I think you might want to debug your
>> application and come up with some parameters for us to start helping you.
>> A
>> reproducible example is always best but I understand it might be hard to
>> provide in this particular case.
> There is a lot of frustration.  Until today all of it was directed at our
> developers, for creating programs and configs that make way too many
> connections to the DB.
> But then today, I had that small eureka moment, thinking "wait a minute
> ... how can this even be happening at all, if the connection pool has
> connections that the DB server says are active and idle?"
> Reiterating something I said before: I know you can't help me with the
> pools that the Tomcat servers are creating for our webapps.  So I'll limit
> the rest of the discussion to my own program, which uses DBCP, and has the
> same problems.
> Please tell me what information you'd like me to provide. Anything that is
> in my power, I will get it to you.
> This is how I set up DBCP in my code:
>   /*
>    * Create a datasource (connection pool) for the master database server.
>    */
>   ConnectionFactory cfMaster = new DriverManagerConnectionFactory(masterUrl,
> dbUser, dbPass);
>   PoolableConnectionFactory pcfMaster = new PoolableConnectionFactory(cfMaster,
> null);
>   pcfMaster.setValidationQuery(validationQuery);
>   pcfMaster.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
>   opMaster = new GenericObjectPool<>(pcfMaster);
>   opMaster.setMaxWaitMillis(Const.THIRTY_SECONDS);
>   opMaster.setMaxIdle(numShards);
>   opMaster.setMaxTotal(numShards * 5);
>   opMaster.setNumTestsPerEvictionRun(numShards * 5);
> opMaster.setTimeBetweenEvictionRunsMillis(Const.FIVE_SECONDS);
>   opMaster.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
>   opMaster.setTestOnCreate(true);
>   opMaster.setTestOnBorrow(true);
>   opMaster.setTestOnReturn(true);
>   opMaster.setTestWhileIdle(true);
>   pcfMaster.setPool(opMaster);
>   dsMaster = new PoolingDataSource<>(opMaster);
> The JDBC driver we use is MySQL.  As of a few weeks ago, it was the newest
> stable version available, 5.1.something.  Also at that time, I was using
> the latest DBCP and POOL versions.  If any new versions have come out very
> recently, I probably don't have them yet.
> Typically the numShards value we're using is 6, to help with understanding
> the code above.
> Observations: When the MySQL server has reached its connection limit, at
> least one of the idle connections is from this program using DBCP.  But
> when the program attempts to use the DB, it gets the "Too many connections"
> error response -- which means that it must be opening a brand new
> connection, despite the fact that there SHOULD be at least one that is
> ready and sitting in the pool.
> The code that uses the DB is basic JDBC code.  It calls getConnection() on
> the dataSource, verifies that the connection is valid, creates a statement,
> executes it, and if it was a query, processes the resultset.  Then it
> closes any resultset, closes the statement, and closes the connection.  As
> I understand it, that close should return the connection to the pool, still
> open, and ready for re-use.  This all happens within a single thread.  I
> went through this code pretty closely for another issue on this mailing
> list.  It's possible that I missed something, but it looks very clean.
> I was going to add some debug logging to my code, but I can't see any way
> with PoolingDataSource to get the number of active and idle connections,
> just to make SURE that the pool really has what I think it does.
> I have a code change ready to switch everything to BasicDataSource and add
> the debug logging.  It's generally less verbose code, and looks to be just
> as configurable as PoolingDataSource.  Would that change be a good idea?
> Thanks,
> Shawn
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:

Matt Sicker <>

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message