DBCP2 BasicDataSource Idle connections not getting cleared

Question

i see that Idle connections are not getting cleared. I am not sure what the reason is?

initialSize-10 maxtotal-20 maxidle-10 minidle-0 minEvictableIdleTimeMillis-30min numTestsPerEvictionRun-60min numTestsPerEvictionRun-20 testOnBorrow-true testWhileIdle-true validationQuery-select 1 from dual

From various sources provided the following is my understanding maxtotal- maxactive connections to Datasource which is 20 in above case

maxidle- number of idle connections that can remain in pool. these are removed by the sweeper. In the above case, a connection is idle if it remains idle for 30min. If the sweeper runs every 60min which checks 20 idle connections to and clears idle connections. Idle connections exceeding this would be closed immediately.

Is the above understanding correct?

I am using BasicDataSourceMXBean to print the stats

{"NumActive":"0","NumIdle":"10","isClosed":"false","maxTotal":"20","MaxIdle":"10","MinIdle":"0"}

The idle connections are never getting cleared even though there is no traffic. Is there anything wrong in the above config?

Also what is minIdle and when should we set it to non zero value?

Recently upgraded hibernate version from 3.6.0.Final to hibernate 4.3.11.Final and spring to 4.2.9 from older spring version.

Earlier the idle connections were getting cleared. But since the upgrade the idle connections are not getting cleared.

Answer 1

Everywhere I have looked, it seems that property should be testWhileIdle rather than testOnIdle. The setting is by default false, so your idle threads aren't being tested for validity, thus aren't being evicted.

The minIdle basically tells the connection pool how many idle threads are permissible. Its my understanding from the documentation that when minIdle is 0, there should be no idle connections.

Typically minIdle defaults to the same value as initialSize.

Answer 2

i see that Idle connections are not getting cleared. I am not sure what the reason is?

https://commons.apache.org/proper/commons-dbcp/configuration.html

The testWhileIdle vs. testOnIdle issue that others have pointed out should resolve your question as to why the idle connections remain open. You are correct in assuming that your initialSize=10 connections will be cleared by the eviction sweeper at the 60 minute mark to bring you down to minIdle=0. Why you would want to have a minIdle=0 is a different question? The whole point of connection pooling is really to pre-authenticate, test, and establish your connections so they can sit in your pool "Idle" and available to "Borrow" by incoming requests. This improves performance by reducing execution time to only the SQL session.

Also what is minIdle and when should we set it to non zero value?

These idle connections will pre-establish and maintain wait for your future SQL requests. The minIdle sizing depends on your application, but the default from DBCP2 is 8 and probably not a bad place to start. The idea is to keep enough on hand to keep up with the average demand on the pool. You would set a maxIdle to deal with those peak times when you have bursts of traffic. The testWhileIdle=true configuration you have applied will run the validationQuery when the sweeper comes around, but will only test 3 connections per run by default. You can configure numTestsPerEvictionRun to a higher number if you want more to be tested. These "tests" ensure your connections are still in a good state so that you don't grab a "bad" idle connection from the pool during execution.

I suspect that you may be more concerned with "hung" connections rather than "idle" connections. If this is the case, you will want to review the "abandoned" configurations that are designed to destroy "active" connections that have been running longer than X amount of time. removeAbandonedOnMaintenance=true along with removeAbandonedTimeout={numberOfSecondsBeforeEligibleForRemoval}.