In this article, we will discuss the trade-offs between memory footprint and CPU utilization in the task of JDBC connection pool sizing. Before you start, you may want to read this companion article first:
Fixed-Sized vs Dynamically-Sized Pool
Sometimes you would like to set the initial capacity to the same value as the maximum capacity—this way, the connection pool will have all the physical connections ready when the pool is initialized. However, sometimes it's not possible to estimate what your run-time workloads (either average or peak load) would be in advance and it could become wasteful to over-allocate connection instances. Then dynamically-sized pool may be the better approach.
Monitoring JDBC Connection Statistics
As shown above, you can navigate to:
- Services -> Data Sources -> ApplicationDB -> Monitoring -> Statistics
In our case, ApplicationDB was deployed to multiple servers. As you can see, the active connections on each server is low (i.e., maximum is 6). However, we have set its Initial Capacity to be 20 and all five pools inherit the setting and have a current capacity of 20.
Also, in our case, only SalesServer_1 will ever need over 20 connections concurrently and allocating 20 connections for all pools can be wasteful. So, based on your own situation, you may want to reduce ApplicationDB's initial capacity appropriately.
After you estimate your peak load, you can choose a Maximum Capacity for the data source. In this case, initial and maximum capacity will be different. Then you can configure the way the pool can shrink and grow by using two additional properties:
- Shrink Frequency
- The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand.
- When set to 0, shrinking is disabled.
- Minimum Capacity
- The minimum number of physical connections that this connection pool can contain after it is initialized.
For best performance, you should always tune pool sizes based on DataSource statistics.
- Monitoring WebLogic JDBC Connection Pool at Runtime
- Oracle® Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server 11g Release 1 (10.3.4)
- Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server
- Monitoring and Tuning Oracle Fusion Applications
- Why My WebLogic Managed Server is in ADMIN State?
- Read this for a good example of when to set Initial Capacity to be zero.