What is a JDBC connection pool?
A JDBC connection pool is a cache of connections to a database which gives Integration Bus administrators the ability to control the number of concurrent connections to a database. With IBM Integration BUS (IIB) a connection pool can be used to control the number of connections allowed to a particular Database.
Pooled vs. Non-Pooled Comparison
Figure 1- Administrating JDBC properties using MBX
Figure 2 – Administrating JDBC properties using Web Admin
Which configuration do I use?
When making a decision which method to use, consider the following:
Although the performance impact is minimal there is small impact to using a connection pool. It is recommended you run load/performance tests to ensure the flow meets your performance requirements.
Can your message flow application afford to wait for a pooled connection to become available if all are currently in use? Although both pooled and non-pooled configurations will return an immediate connection where one is available, for a pooled configuration the message flow will potentially have to wait (default up to 15 seconds) if all pooled connections are currently in use.
In cases where your flow has to respond immediately it maybe more suitable to not use a pooled configuration, this will prevent the possibility of having to wait for the connection to be available (unless the maximum connections have been exhausted for the database).
Do you have other applications that require connections to the Database? In cases where Integration Bus is not the sole application connecting then typically a connection pool is the recommended approach. This provides the ability to limit the connections that each Integration Server can acquire to the Database. This approach allows you to ensure connections are available for other applications.
When you scale your Integration message flows to use multiple threads and you configure JDBC to use a connection pool then you can scale all flows independently of the number of connections available to the database.
Can I have multiple connection pools?
Each Integration Server created within your Integration Node has an independent connection pool per JDBCProvider configurable service. As previously mentioned the connection pool configurable service is defined at the Integration Node so each Integration Server will have the same pool size per JDBCProvider.
How is the connection pool used at runtime?
Consider the following example.
Integration Node (IN1) has been configured to use a connection pool of 10 for JDBCProvider DS1. There are two Integration Servers (IS1/IS2) which have flows deployed which use the JDBCProvider (DS1) and each flow is deployed to use 15 additional instances.
As message volume increase across the flows and additional threads are initiated, each thread will obtain a connection from the connection pool on demand and managed as follows:
- Each pooled JDBC connection remains associated with a message flow instance only for the processing of one input message.
- When a message flow instance completes the processing of an input message, the association with a JDBC connection is removed, and the JDBC connection is returned to the pool.
The connection pool manages its connections to the database as follows:
- When a message flow instance requires a JDBC connection, IBM Integration Bus assigns an unused connection from the pool.
- If all pooled JDBC connections are being used, and the maximum pool size has not been reached, IBM Integration Bus creates a new pooled JDBC connection.
- Each pooled JDBC connection that is idle for 15 minutes is closed, and is removed from the pool.
As message volume continues to increase across the flows and more instances are started additional connections will be obtained by each pool. When the maximum connections of each pool are in use then each instance of a flow that requires a database connection will contend with each other causing potential delays in processing.
If an immediate connection is not available, future requests will be delayed until a connection is available. The flow will wait (up to 15 seconds by default) for a pooled connection to become available, otherwise an exception is thrown. It is therefore essential to configure the connection pool to an adequate size to ensure appropriate performance is met (see sizing the pool).
Sizing the Connection Pool
When determining the appropriate size of the connection pool for each JDBCProvider it is essential that you take the following into consideration:
- Maximum connections allowed by the Database
- How many Integration Servers will require a connection pool
- Message volume and performance requirements of each message flow accessing the DataSource
Consider the following example.
You have 2 Integration Servers to which you have deployed 1 message flow which requires access to your DataSource.
- The message flow is to be configured to use a maximum of 50 threads per Integration Server.
- Your DBA has indicated your Database has a maximum of 100 connections available but they would like to restrict the Integration Node to no more than 80 connections.
You can configure the Pool Size to 40 (in the Configurable Service), this will allow a maximum of 40 connections from each Integration Server (and 80 overall for the Integration Node).
At peak volume if your Integration Servers require all 50 threads to be initiated, then you may incur a delay waiting for a connection. As each message flow thread uses a connection it will release once it’s processing has completed and the connection will be available for the next thread waiting.
It is essential that you perform adequate performance testing under production type loads to determine if the processing delay during peak processing is within your service level agreements (SLA). For more details of how to monitor the connection pools during testing see how to monitor a connection pool.
How to monitor connection pools
Resource Statistics can be used to monitor connection pool usage at runtime. For details of how to enable Resource Statistics and view using the Web Admin console see IBM Integration Knowledge Center.
When using the Web Admin to view the JDBC Connection Pool the default view shows tables for Maximum Size of Pool, Actual Size of Pool and Cumulative Requests. The tabular view shows metrics for this data along with Cumulative Delayed Requests, Cumulative Timed Out Requests and Maximum Delay.
To analyze potential performance issues you may want to change the graph view to show Cumulative Delayed Requests, Cumulative Timed Out Requests and Maximum Delay views. This can be done by clicking on the arrow next to any of the graphs and selecting from the dropdown as shown below.
Summary
To summarize, using a JDBC connection pooling is an optional configuration for any JDBC configuration in IBM Integration Bus and provides the following benefits:
1. Allows the database access to be scaled independently of the message flow threads.
2. Give broker administrators the ability to control the number of concurrent connections to a database
3. Allow monitoring and audit of pool usage
4. Prevent resource starvation on the database server
5. Allocate connections according to business processes.