Friday, October 12, 2012

Dynamically Sizing JDBC Connection Pool in WebLogic Server

A data source in WebLogic Server has a set of properties that define the initial, minimum, and maximum number of connections in the pool. A data source automatically adds one connection to the pool when all connections are in use. When the pool reaches maxCapacity, the maximum number of connections are opened, and they remain opened unless you enable automatic shrinking on the data source or manually shrink the data source.

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
and monitor the connection statistics of a specific data source (i.e., "ApplicationDB").

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.
You may want to drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources. When you shrink a data source, WebLogic Server reduces the number of connections in the pool to the greater of either the Minimum Capacity or the number of connections currently in use.

For best performance, you should always tune pool sizes based on DataSource statistics.

References

  1. Monitoring WebLogic JDBC Connection Pool at Runtime
  2. Oracle® Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server 11g Release 1 (10.3.4)
  3. Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server
  4. Monitoring and Tuning Oracle Fusion Applications
  5. Why My WebLogic Managed Server is in ADMIN State?
    • Read this for a good example of when to set Initial Capacity to be zero.
  6. JBO-26061: Error while opening JDBC connection

1 comment:

Blogger said...

Bluehost is ultimately one of the best web-hosting provider for any hosting plans you might need.