Understanding the database connection pool properties

Rama Satya Diwakar Grandhi
3 min readMar 3, 2022

The basics

Recently, I faced an issue related to a very high load on the database layer. The database was having too many connections in parallel. I had to review my application’s database connection pool properties very closely. Since I was dealing with legacy code, I needed to understand the value assigned to each property and also analyze whether it is relevant for the present-day load or not. As I started looking at the properties, their values, and the consequent implications, I was able to find a decent explanation in the tomcat documentation. However, I wasn’t able to immediately map each property to the scenario where it will be used.

Since we were using Apache tomcat’s JDBC connection pool, I started reading the source code to get a better understanding. I was able to get a lot of clarity by going through the ConnectionPool class. As I didn’t find any easy resource to understand the same, I am summarizing my understanding in the form of simple flowcharts. I hope this will help others in a similar situation.

I am not covering every property. I looked at the major properties such as, initialSize , maxActive , maxWait , maxIdle , timeBetweenEvictionRunsMillis , removeAbandoned , removeAbandonedTimeout , and minEvictableIdleTimeMillis .

Of these, initialSizeis the simplest. It is the number of connections created by the Connection Pool Manager at the start. If it is not defined, the Connection Pool Manager will create 10 threads by default.

For the other properties, I have recognized three scenarios where they will be used. Let’s go through each scenario and understand how these properties are used.

Scenario 1 — Get a data source connection.

It involves the usage of maxActive (default value = 100) and maxWait (default value = 30 seconds).

Scenario 1 — get a data source connection from the pool

Scenario 2 — A data source connection is released by the application thread.

It involves the usage of maxIdle property. The default value of maxIdle is the value assigned to maxActive. If maxActive is not set, we set the same default value of 100 to both these properties.

Scenario 2 — release data source connection

Scenario 3 — Pool cleaner run.

Pool Cleanup is performed at regular intervals to maintain the connections. It takes care of the abandoned connections as well as the idle connections.

It involves the usage of timeBetweenEvictionRunsMillis (default value = 5 seconds), removeAbandoned (false by default), removeAbandonedTimeout (default value = 60 seconds), minIdle (default value = value of initialSize. In case it isn’t defined, both are set to 10), and minEvictableIdleTimeMillis (default value = 60 seconds) properties.

Scenario 3 — pool cleanup

Note: There are different implementations for the database connection pool. The code referred to in this article is an implementation by Apache Tomcat. For folks who are interested in the alternatives, Apache Commons has an implementation as well.

This will help us understand the cause for load from an application’s perspective. For understanding how to debug the load from the information inside the Oracle database, please refer to my other article — https://diwakargrandhi.medium.com/debugging-heavy-load-on-oracle-databases-78cca94b848a. Thanks.

Sign up to discover human stories that deepen your understanding of the world.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Responses (1)

What are your thoughts?

Rama, I was wondering if you have ever tried to run DBCP with Oracle RAC and use Transparent Application Continuity? We have an older app where I work that uses DBCP and when one node on a two node RAC goes down it loses the connection instead of…