Understanding the database connection pool properties
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 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 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.
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.
