DBCP2 - When are Idle connections removed from the Pool

Wand Maker picture Wand Maker · Jul 31, 2016 · Viewed 15.3k times · Source

While configuring DBCP2 pool, and based on documentation I noticed that - there is a configuration called timeBetweenEvictionRunsMillis which is described as:

The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.

Its default value is -1.

Does this mean that evictor thread will never run in default configuration? Then how is the configuration parameter maxIdle enforced - the pool has to evict idle connections if their count is greater than maxIdle.

It seems very confusing to me that default configuration is such that idle connections are never evicted.

There is also another configuration softMiniEvictableIdleTimeMillis which seems to play some role on top of timeBetweenEvictionRunsMillis.

Any clarifications in this regard will be of immense help.

For time being, I am configuring the pool like below - as my goal is to not have any idle connections in my pool for too long (this was needed as we are using AWS RDS and there seem to be a weird issue with it which we are running into frequently)

    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl(properties.getProperty("app.mysql.url"));
    dataSource.setUsername(properties.getProperty("app.mysql.username"));
    dataSource.setPassword(properties.getProperty("app.mysql.password"));
    dataSource.setMaxIdle(20);
    dataSource.setMaxWaitMillis(20000); //wait 10 seconds to get new connection
    dataSource.setMaxTotal(200);
    dataSource.setMinIdle(0);
    dataSource.setInitialSize(10);
    dataSource.setTestOnBorrow(true);
    dataSource.setValidationQuery("select 1");
    dataSource.setValidationQueryTimeout(10); //The value is in seconds

    dataSource.setTimeBetweenEvictionRunsMillis(600000); // 10 minutes wait to run evictor process
    dataSource.setSoftMinEvictableIdleTimeMillis(600000); // 10 minutes wait to run evictor process
    dataSource.setMinEvictableIdleTimeMillis(60000); // 60 seconds to wait before idle connection is evicted
    dataSource.setMaxConnLifetimeMillis(600000); // 10 minutes is max life time
    dataSource.setNumTestsPerEvictionRun(10);

Answer

Andrew Lygin picture Andrew Lygin · Jul 31, 2016

Yes, the evictor thread will not run by default. The reason is that the values of maxIdle and maxTotal are the same by default, which means there will be no connections to close immediately, and no need in evicting idle connections. So, the pool just saves some resources by not running a useless thread.

But when you change maxIdle and make it lower than maxTotal without starting the evictor thread, it doesn't mean that your connections will not be closed. It means they will be closed immediately after releasing, with no delay, until their count not drop down to maxIdle.

And then minEvictableIdleTimeMillis and softMinEvictableIdleTimeMillis come to play (be careful, there's a typo in the documentation, it's ...MinEvictalbe..., not ...MiniEvictable...). The difference between them is that the former doesn't respect minIdle while the latter does. It's a bit tricky given the fact that softMinEvictableIdleTimeMillis is only checked when minEvictableIdleTimeMillis has elapsed.

Let's assume we have minEvictableIdleTimeMillis=10000 and softMinEvictableIdleTimeMillis=-1 (by default). In such case an idle connection will remain in the pool for no longer than 10 seconds. Even if the number of connections does not exceed minIdle, it will be closed. If it lead to dropping of the connections count lower than minIdle, a new connection will be created immediately.

Now, let's assume that we have minEvictableIdleTimeMillis=10000 and softMinEvictableIdleTimeMillis=30000. In such case an idle connection after checking against minEvictableIdleTimeMillis and detection of exceeding will be additionally checked against softMinEvictableIdleTimeMillis. If the idle time exceeds it, the connection will be closed. Otherwise, it will sit in the pool till the next positive check against minEvictableIdleTimeMillis.

Eventually, you'll have connections between maxTotal and maxIdle closed immediately, connections between maxIdle and minIdle closed after minEvictableIdleTimeMillis and connections between minIdle and 0 closed after softMinEvictableIdleTimeMillis and reopened immediately. Give or take the eviction check period.

With your configuration, you will have all the connections closed immediately while the pool is larger than 20. And those 20 connections will live from 10 to 20 minutes (even if idle) because you have 10 minutes of both EvictableIdleTimeMillis plus 10 minutes of TimeBetweenEvictionRunsMillis.

I'd like to also mention a potential problem with large gap between maxIdle and maxTotal. If you expect that maxIdle will be exceeded very often, it's better to increase it. Otherwise, you'll face constant connection openings and closings which will create additional pressure on your database (because establishing a new db connection is relatively heavy operation) and the app server network infrastructure (because closed connections will hang in TIME_WAIT status depleting your network ports pool).