Technical Blog Post
Abstract
Connection Pooling in IBM Sterling B2B Integrator - How it works?
Body
This blog gives information about what is a connection pool and how the connection pool implemented in IBM Sterling B2B Integrator (SBI). Before tuning any of the connection pool parameters it is important to understand how it works.
What is a connection pool?
A Connection Pool is a way of increasing performance and reducing the overhead of creating a new connection to the database. Creating, validating, opening, and then destroying a database connection is an expensive operation. A connection pool is a way of allowing multiple objects to share and reuse existing connections. The basic idea of a connection pool is:
- It is a 'collection' of open 'connections' with the database.
- The connectionPool object when instantiated will open a predefined number of connections with the database.
- The connectionPool will keep track of returned and 'in use' connections
Following are the default database pools that are created by default with SBI (i.e jdbc.properties file in <SBI_INSTALL_DIR>/properties folder).
- <db>Pool
- <db>ArchivePool
- <db>Pool_local
- <db>Pool_NoTrans
- <db>UIPool
Note: Each pool is prefixed by the actual database chosen by the customer. Here, the naming convention will be <db>poolName, where <db> would be replaced by the database used to run SBI. Example oraclePool, mssqlPool, etc..
Each pool has the following primary parameters.
1. initsize
2. maxsize
3. buffersize
4. behavior
Connection pool behavior:
Each database pool has an initial number of connections that are created as a starting point. As more connections are needed (beyond the initial number of connections) SBI will request additional connections be made, up to the maximum number of connections allowed for each pool. As and when SBI finished using the connection, rather than destroying the connection, it will put the connection back into the pool of connections to be used later.
If SBI has a greater need for connections, beyond what is defined in the max size limits for each database pool (for example every pool is in use, and new connections have been made up to the max size), SBI will then request new connections up to the “buffer size” value. Connections created beyond max size (in the buffer range) are created as needed and destroyed when the process finishes. This is a very expensive process, and performance will drop if SBI runs in this range for a long period of time.
Once the number of connections defined in the buffer is exceeded, then additional request for connections will be denied. This is a hard max, and an error will be written to the logs indicating the pool has been exhausted and a failure to create a new connection has occurred.
In short, The connection pool behavior is based on the value set for the behavior parameter. It can be set to any of the following values.
- 0 = return (i.e Do not use the buffersize at all)
- 1 = wait (i.e Wait for waittime milliseconds in the hopes that SBI will finish with a connection and return it to the pool for reuse and do not use the buffersize at all)
- 2 = UseBuffer (i.e This is the default behavior. Use the buffer and choke off connections once the buffer is exhausted).
In summary:
- Each JVM could create connections for each of the pools defined in the jdbc.properties file (or customer_jdbc.properties file, or the customer_override.properties file)
- Each pool would grab the initial number of connections (initsize) as configured per each pool
- As a pool has a need for additional connections, beyond the initial size, it will create new connections up to the maxsize limit for that pool.
- Connections created less than the maxsize are put back into that pool to be reused as needed. This is for performance, based on the overhead of going to the driver and creating/destroying a new connection each time one is needed. )
- If a pool has all the connections in use up to the maxsize limit, it will then create a new connection as needed within the buffer size limit. Connections beyond maxsize and below the buffersize are not reused. Each connection is created when needed, and destroyed when no longer needed by the calling process. This is very expensive and will be a hit on performance if SBI runs for continued amounts of time in this state.
- Once the number of connections (maxsize + buffersize) has been hit, then new request for connections will be refused.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS3JSW","label":"IBM Sterling B2B Integrator"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
UID
ibm11121943