17

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);
Wand Maker
  • 18,476
  • 8
  • 53
  • 87

1 Answers1

36

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

Andrew Lygin
  • 6,077
  • 1
  • 32
  • 37
  • Thanks for detailed information. Those idle connections during the 10-20 minutes window - will they be reused by the connection pool? My guess is they will be reused. In your second example, when you say `If the idle time exceeds it`...do you mean does it exceed 10000 + 30000? – Wand Maker Jul 31 '16 at 16:16
  • @Wand Marker, you're welcome. Regarding reusing the connection, if I understood the question correctly, of course the pool will reuse them if it needs them and the 'idle timer' will reset. – Andrew Lygin Jul 31 '16 at 16:19
  • Yes, thats what I meant. Thanks again for all the help! – Wand Maker Jul 31 '16 at 16:21
  • One more tip: be careful with large `timeBetweenEvictionRunsMillis`. It will add up to the time connections live in the pool. There's nothing dangerous in checking the connections once in a several seconds instead of ten minutes. The overhead is minuscule, it's just a non-blocking adding and comparing a couple of numbers. The shorter the check period the more precisely you can adjust connection lifetimes. – Andrew Lygin Jul 31 '16 at 16:27
  • Shall I change it to 1 minute? – Wand Maker Jul 31 '16 at 16:34
  • I would surely do that. – Andrew Lygin Jul 31 '16 at 16:35
  • What would be an ideal value for `numTestsPerEvictionRun` in my case? – Wand Maker Jul 31 '16 at 16:39
  • It depends on the accessibility of your DB server and on resilience of your application to db connection dropouts. If everything is in a properly maintained local network, the DB is highly reliable, and you can easily handle db connection failures from time to time, you can leave it with the default value. If you don't trust the network, the DB or your code, increase it and let the pool check and recover connections more vigorously. – Andrew Lygin Jul 31 '16 at 16:53
  • Unfortunately, there's no formula to calculate it exactly. You usually just make some predictions, set a value, then monitor how the system works and adjust parameters if needed. – Andrew Lygin Jul 31 '16 at 16:53
  • what is the best practice, should we enable Evictor and validation for connection pool or not? Could you please help out? – Rahul Singh Oct 01 '18 at 06:45
  • @AndrewLygin, great answer, thank you! I wasn't aware of the `softMinEvictableIdleTimeMillis`. However, my experience doesn't seem to be lining up to your answer. What I am hoping to do is have a few idle connections in the pool but where they don't reconnect to the DB on every eviction run. I experiment with `TimeBetweenEvictionRunsMillis` of 2000ms and `SoftMinEvictableIdleTimeMillis` of 2 hours and my number of minIdle keeps getting new connections to the DB (based on looking at the LOGON_TIME in V$Session). For completeness my `MinEvictableIdleTimeMillis` is set to 5000 ms. Thank you! – Thomas Jahncke May 06 '21 at 18:22