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);
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).