What is the difference between maxTotal and maxIdl

Question

I'm using the Apache Commons Pool 2 implementation to have object pool mechanism for my application.

As of now, I have set the default value of maxTotal() and maxIdle() as 10 in my code.

But I am not able to understand what is the difference between them ? What if I set the maxIdle() as a value very small (let's say 0) or very large (equal to maxTotal()) ?

Note: Apache classes internally recommends a default value of 8 to both of the above configs.

Answer 1

I was still curious to know the difference between maxide and minidle, hence referred this, this and want to update here for others like me.

initialSize The initial number of connections that are created when the pool is started

maxActive The maximum number of active connections that can be allocated from this pool at any time. This attribute is used to limit the number of connections in a pool that can be open; so that capacity planning can be done on the database side

maxIdle = The maximum number of idle connections that should be kept in the pool at all times. Idle connections are checked periodically (if enabled) and connections that have been idle for longer than minEvictableIdleTimeMillis will be released

If maxIdle is set too low on heavily loaded systems it is possible you will see connections being closed and almost immediately new connections being opened. So it is better keep maxIdle to be close to maxActive.

minIdle The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail.

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

minEvictableIdleTimeMillis the minimum amount of time an object may sit idle in the pool before it is eligible for eviction.

Also if you want to ensure, no stale connection in your production system. You can use the below, but it is costly operation not good for performance critical one but suits for finance related application service.

database.connectionPool.testOnBorrow=true
database.connectionPool.testOnReturn=true
database.connectionPool.testWhileIdle=true

Answer 2

A connection pool is a technique for sharing a limited number of database connections with an unconstrained number of simultaneous users. The maximum total connections (maxTotal) includes both active and idle connections, that is connections being used and connections not currently being used (it is the sum total of all connections). The maximum idle connections (maxIdle) are connections that are ready to be used (but are currently unused). If you set the maxTotal to 100 then a maximum 100 connections will be opened to your database at one time, if the maxIdle were set to 10 then if none of the connections are being used up to 90 connections might be released. The pool will re-connect on demand.

In a pool, the idle connections are ready and a request for a connection will not block if a connection is currently idle (the pool returns the idle connection). If no connections are idle, then the pool will block to open a connection or until a connection is returned to the pool.

In your question, with 10 for the maximum of both, ten Connection(s) will be opened and the pool will not shrink or grow.