IBM Support

Tuning Connection Pool Properties In WebSphere Application Server

Technical Blog Post


Abstract

Tuning Connection Pool Properties In WebSphere Application Server

Body

 

There may be situations where it is necessary to tune the connection pool for optimal performance.  It may be difficult to determine what each property does and which ones need to be tuned.  To help you get a better understanding of the connection pool properties that are available in WebSphere Application Server (WAS), this blog post will explain in detail some of the more common properties and when to tune them. Remember that these are general recommendations and that you will need to test any setting changes thoroughly in your own environment to verify if they help.

 

The properties that will be discussed can be found at the following locations:

 

  • Click Resources > JDBC > Data Sources > data_source > [Additional Properties] Connection pool properties

 

  • Click Resources > JMS > Queue connection factories >queue_connection_factory > [Additional Properties] Connection pool

 

NOTE:  If you are using a stack product on top of WAS like Business Process Manager (BPM), Maximo, Portal, etc., please check the stack product recommendations for the connection pool settings before making any changes. 

 

  1. Maximum connections

 

This property defines the maximum number of physical connections that can be created in this particular connection pool to the backend resource.  Once the maximum number of connections are opened, no new connections can be opened to the backend resource.  If all of the connections are currently being used by the application, any new requests for a connection will have to wait until one is closed and returned to the free pool to be reused.  If a connection does not become available within the time specified by the 'Connection timeout' property, a J2CA0045E message will be thrown in the SystemOut.log file indicating a connection request timed out waiting for a connection to become available. 

 

If your data source is configured at the cluster, node, or cell scope, each server that uses that data source will have its own physical connection pool. 

 

When tuning this property, it is important to be aware of the following:

 

  • The backend resource needs to be tuned appropriately.  Since each server that uses a particular data source has its own connection pool, the backend resource needs to be able to handle the combined load.  For example:  If the Maximum connections size is set to 20 and there are 5 servers using that data source, there could potentially be up to 100 active, physical connections to the backend resource.  That means that the backend resource should be able to handle a load of 100 connections without any performance or other issues.  It is not wise to set the Maximum connections size value higher than what the backend resource is able to handle.  This will lead to performance issues and possibly connections staying open for longer than intended.

 

  • It is also important to check what the Webcontainer thread pool size is set to.  The Webcontainer thread pool size should be greater than the Maximum connections value.  We usually recommend that the Webcontainer thread pool size be double the Maximum connections value.  If the Maximum connections value is high like 100, doubling the Webcontainer thread pool size may not be practical.  In that case, it is important to just make sure the Webcontainer thread pool size is greater than the Maximum connections value.

    NOTE:  If you are using a stack product on top of WAS like Business Process Manager (BPM), Maximo, Portal, etc., please check the stack product recommendations for the connection and webcontainer thread pool settings before making any changes to the wecontainer thread pool size.

 

  • There is no recommended value for this property.  It needs to be tuned according to your environment's load and restrictions.  If you notice that when the load on your environment increases the connection pool maxes out, it may be a good idea to increase the value of this property to account for the increased connection requests.  We recommend doing some load tests in a non-production environment first to determine the optimal value for your environment.  

 

 

  1. Minimum connections

 

This property defines the minimum number of physical connections that will be kept open in the free pool.  When the JVM is started, WAS does not automatically create the number of connections that is defined by this property.  As the application requests, uses, and closes connections, they will be returned to the free pool.  If the free pool size is below this value when the Unused timeout value is reached for a connection, the physical connection will not be discarded.  If the free pool size is at or greater than this value when the Unused timeout value is reached for a connection, the physical connection will be discarded.  It is also important to note that if you set a value higher than 0 for the Aged timeout value, physical connections will be discarded once that timeout value is reached regardless of the Minimum connections value. 

 

When tuning this property, it is important to be aware of the following:

 

  • Setting this value higher than 0 could possibly lead to a StaleConnectionException under certain situations.  The recommended value for this property is 0.  When a StaleConnectionException (SCE) occurs, it means that the connection has become prematurely terminated without WAS being aware that it was closed.  This can be caused by various reasons like a firewall timeout or a database timeout.  Since WAS is not aware that the connection was closed, it will try to reuse that connection which will result in a SCE.  Often times connections become stale when they sit for long periods of time in the free pool without being reused.  Setting the Minimum connections value to 0 will help decrease the chances of connections becoming stale when they're in the free pool.  It is important to note that setting this value to 0 will not help situations where the SCE is occurring for a connection that is currently in use in the shared or unshared pool. 

 

 

  1. Connection timeout

 

When the application requests a connection and there are no connections available in the free pool and no new connections can be created, the connection request will wait for the value of the Connection timeout setting for a connection to become available.  If a connection does not become available in this time period, it will stop waiting for a connection and will throw a createOrWaitForConnection exception like below in the SystemOut.log file:

 

J2CA0045E: Connection not available while invoking method createOrWaitForConnection for resource jdbc/xxxxxx

 

If you see this message in the SystemOut.log files, it almost always indicates that the connection pool has maxed out. 

 

When tuning this property, it is important to be aware of the following:

 

  • This value does NOT timeout active connections.  Although the name of the property sounds like it will terminate a connection that has been in use for a long period of time, that is not the case.  There is no way to timeout a connection that is actively being used in the shared or unshared pool. 

 

  • You can set this value to 0 if you want a connection request to wait as long as necessary for a connection to become available.  It is important to note that if you have an issue where connections stay in use for a long period of time or there is a connection leak where the connections never close and cannot be reused, setting this value to 0 could lead to a server hang situation.  This is because the new connection requests will wait indefinitely for a connection to become available which may never occur. 

 

  • There is no recommended value for this property as each environment has different needs.  By default, this value is set to 180 seconds, but if you need a connection request to timeout quickly if no connection is available, it is best to lower the value of this property.

 

 

  1. Unused timeout

 

This property will time out connections in the free pool that have not been reused within the timeout period defined.  If the unused timeout value is reached, the pool maintenance thread will first check to see what value is set for the Minimum connections property.  If the number of connections in the free pool is at or higher than the Minimum connections value, the pool maintenance thread will close that physical connections to the backend.  If the number of connections in the free pool is less than the Minimum connections value, that connection will stay in the free pool even though the unused timeout value was reached. 

 

When tuning this property, it is important to be aware of the following:

 

  • The pool maintenance thread is controlled by the Reap time property which will be discussed below.  It will run at the interval specified by the Reap time property.  When the Unused timeout value is reached, it will be cleaned up the next time the pool maintenance thread runs.  This means that it potentially can take anywhere between the value you have set for the Unused timeout and the combined values you have set for the Unused timeout and the Reap time for the physical connection to be closed. 

 

For example, if you have the Unused timeout set to 1800 seconds and the Reap time set to 180 seconds, it could take anywhere from 1800 seconds to 1980 seconds for that connection to be removed from the pool.

 

As there is no recommended value for this property, keeping this in mind when you tune this property will help you determine which value to set for your environment. 

 

  • This property only takes effect for connections waiting in the free pool to be reused.  It will NOT time out connections that are currently being used by the application in the shared or unshared pool. 

 

  • If you set this value to 0, connections in the free pool will never timeout.  This can potentially lead to a situation where the connections waiting in the free pool have become stale for some reason and are no longer reusable.  This can occur if there is a connection sitting idle for a long period of time in the free pool without being reused. 

 

  • If you have a firewall or some other timeout outside of WAS that times out connections to the database after a certain period of time, it’s a good idea to set this Unused timeout property to half that timeout value.  This will lower, but not completely remove, the chances of connections becoming stale in the environment.  Setting this to a lower value will allow these connections to be cleaned up from the free pool if they are not reused by the application.

 

 

  1. Aged timeout

 

This property will time out a physical connection once it has reached this timeout value.  Since this times out the physical connection, the connection can be in use in the shared / unshared pool or waiting to be reused in the free pool when the timeout occurs.  If the connection is in the free pool when the Aged timeout value is reached, the physical connection will be closed the next time the pool maintenance thread runs.  Even if the you have the Minimum connections property set to some value like 10 and there are currently only 5 connections in the free pool, the physical connection will be closed once the Aged timeout value is reached.  If the connection is involved in a transaction in the shared / unshared pool when the Aged timeout is reached, the connection will be marked to be closed.  Once that transaction completes, the physical connection will be closed.

 

When tuning this property, it is important to be aware of the following:

 

  • The recommended value for this property is 0.  This helps you take full advantage of the connection pooling feature.  If you set this value higher than 0, you could potentially be closing out valid connections that could have been reused by the application.  Since there is a slight performance impact of creating a new physical connection, keeping this value at 0 helps connections to be reused as long as possible until a scenario where the unused timeout value is reached.  This can decrease the performance impact of having to continuously create new physical connections because valid connections were closed by the Aged timeout.

 

  • The only time we really recommend setting this value greater than 0 is when there is a firewall or some other timeout outside of WAS that terminates the physical connection.  In that case, setting this Aged timeout value lower than the other timeout value allows WAS to clean up the connection first.  This reduces the chances of the connection becoming stale.  If the firewall times out the connection first, WAS will not be aware and will try to use that connection.  This will eventually lead to a StaleConnectionException which your application would then have to catch and handle. 

 

  • If the Minimum connections property needs to be set to a value greater than 0 (especially when it is something high like 10 or 20), there is a good chance that physical connections could stay open for the life of the server.  The connections would continuously get reused, and even if the Unused timeout was reached during off hours, they would continue to stay in the free pool due to the Minimum connections value.  This could lead to situations where physical connections stay open to the database for days.  The length of time a physical connection has been open can be verified on the database side as well.  To avoid this situation, you can set the Aged timeout property.  It is recommended that this property be set to a high value like several hours so valid connections are not just being closed unnecessarily.  This will allow the connection pooling feature to be utilized more effectively.  If it is necessary for physical connections to be closed faster, it is better to set the Minimum connections property to 0 and adjust the Unused timeout property accordingly. 

 

  • It is important to remember that this property will not immediately time out connections that are currently being used by the application in the shared or unshared pool.  It will mark them to be closed.  Only after the transaction is completed will the physical connection be closed. 

 

 

  1. Reap time

 

This property controls how often the pool maintenance thread runs.  The pool maintenance thread is responsible for closing out any physical connections that have been timed out by the Unused timeout and Aged timeout properties.  When the Unused timeout is reached in the free pool, the pool maintenance thread will check to see what the Minimum connections property is set to.  If the number of connections in the free pool is less than the Minimum connections value, the pool maintenance thread will not close out that connection even though it has reached the Unused timeout.  If the number of connections in the free pool is equal to or greater than the Minimum connections value, the pool maintenance thread will close out that physical connection.  When the Aged timeout is reached in the free pool, that connection will be removed by the pool maintenance thread regardless of what you have the Minimum connections property set to.  When the Aged timeout is reached for a connection in the shared / unshared pool, it will wait until that transaction completes before it is closed. 

 

When tuning this property, it is important to be aware of the following:

 

  • There is a slight performance impact every time the pool maintenance thread runs which is not normally noticeable.  If you set this value too low, it means that the pool maintenance thread will run more often which could lead to a noticeable performance impact.  It is a good idea to do a thorough load test in a non-production environment to help determine the optimal value for your environment. 

 

  • This property should be set lower than the Unused timeout and Aged timeout (if this is set higher than 0) properties.  As explained for the Unused timeout property above, a physical connection in the free pool gets closed after an unused timeout when the pool maintenance thread runs.  This is also the case for connections timed out by the Aged timeout property in the free pool.  This means that the smaller the interval for the Reap time property, the faster timed out connections will be closed.  It is also important to keep in mind that this property is not set too low that a noticeable performance impact is seen. 

 

 

  1. Purge policy

 

This property specifies whether to purge all the connections in the pool or just the failing connection when a fatal connection error or stale connection exception is thrown.

 

When tuning this property, it is important to be aware of the following:

 

  • The recommended setting for this property is EntirePool.  This is especially helpful when connections are becoming stale waiting to be reused in the free pool.  For example, if the database goes down unexpectedly, the connections currently in the pool are no longer valid.  That means a StaleConnectionException (SCE) will be thrown if they try to be used.  Since all the connections are no longer valid, it is best to have them all purged when the first SCE is thrown rather than have each connection throw the exception.  This helps WAS clear out all invalid connections faster and provide new, valid connections to the application. 

 

 

 

For more information about the connection pool settings, you can review the Connection pool settings topic in the product documentation.

 

 

 

 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

UID

ibm11081359