Monitoring WebLogic JDBC Connection Pool at Runtime

Before you start any performance tuning, you need to monitor your application runtime behavior using default application server settings first.

In this article, we will show you how to monitor the health of WebLogic JDBC connection pool. In a companion article^[7], we also show you how to tune Prepared Statement Cache in WebLogic Server for better web application performance.

Data Sources & JDBC Connection Pool

WebLogic Server maintains a pool of reusable physical database connections to minimize the overhead involved in connecting to a database. All the connections in a pool connect to the same database and use the same username and password for the connections.

WebLogic Server also manage your database connectivity through JDBC data sources. WebLogic Server data sources help separate database connection information from your application code.  Each data source that you configure contains a pool of database connections that are created when the data source instance is created—when it is deployed or targeted, or at server startup. The connection pool can grow or shrink dynamically to accommodate the demand.

At runtime, Java applications perform a lookup of the JNDI tree to find the data source and request database connections using the getConnectionMethod. Once the application completes using that connection, the connection goes back to the data source’s connection pool.

DataSource Runtime Monitoring

Using WebLogic Server Administration Console, you can monitor JDBC DataSource statistics by navigating to:

• Servers --> SalesServer_1 --> Monitoring --> JDBC

There are many KPIs that you can monitor with and you can customize which ones to be displayed in the table.  We have listed some important KPIs here:

• Waiting For Connection High Count
• Highest number of application requests concurrently waiting for a connection from this instance of the data source
• Wait Seconds High Count
• The highest number of seconds that an application waited for a connection (the longest connection reserve wait time) from this instance of the connection pool since the connection pool was instantiated
• Connection Delay Time
• The average amount of time, in milliseconds, that it takes to create a physical connection to the database
• The value is calculated as summary of all times to connect divided by the total number of connections

Configuring the Connection Pool

When the WebLogic Server starts up or when you deploy a data source to a new target, the connection pool is registered with the server, meaning that the connection pool and its connections are created at that time. You can configure various settings to control the connection pool size and the way the pool can shrink and grow.  You should tune pool sizes based on DataSource statistics. For example, you should ensure connection wait time is not high.

We have listed some settings here that you may want to tune for your applications:

• Initial Capacity
• Number of connections created when pool is initialized
• Minimum Capacity
• Minimum number of connections that will be maintained in the pool
• Should be tuned for steady load
• Maximum Capacity
• Maximum number of connections that pool can have
• Should be tuned to peak load
• Shrink Frequency 
• Should be enabled to drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources

It is common to set the initial capacity to a value that handles your estimated average, but not necessarily the maximum number of connections to the database. Ideally, you want to make sure that you have enough initial connections to match the number of concurrent requests that you expect to have running on any given server instance.

To be on the safe side, you can 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 you do want to dynamically adjust pool size at run-time, see [5].

The rule of the thumb for pool sizing is simply to make sure that the pool is large enough for all server threads to get access to the pooled resources they need concurrently.  In previous versions of WebLogic Server,this was usually simple.  For example,each execute thread needs access to one database connection from each pool, so you always make sure that the maximum capacity of the database connection pool was greater than or equal to the number of execute threads. With the introduction of server self-tuning, the number of execute threads isn't necessarily well defined.  Then, the tips provided here may be helpful to you.

Acknowledgement

Some writings here are based on the feedback from Sandeep Mahajan and Stevan Malesevic. However, the author would assume the full responsibility for the content himself.

References

1. Professional Oracle WebLogic Server
2. Oracle WebLogic Server 11gR1 PS2: Administration Essentials
3. The WebLogic Server Administration Console
4. Managing WebLogic JDBC Resources
5. Dynamically Sizing JDBC Connection Pool in WebLogic Server
6. Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server
7. Tuning WebLogic's Prepared Statement Cache
8. Data Source Connection Pool Sizing