Wednesday, July 22, 2009

websphere jdbc connection pool aged timeout setting in websphere might shrink the pool size below the specified Minimum Connection setting

If you have used websphere datasources you might have seen a jdbc connection pool setting called Aged Timeout specifies the interval in seconds before a physical connection is discarded irrespective of whether the connection was idle or used before. This property is usually set to a specified interval to maintain the freshness of the connection between the database and also to avoid any firewall sitting between your application and database terminate the connection after staying idle for a period of time, which most firewall does. Other instance where this property might be useful is if you have an Oracle RAC (Real Application Clusters) kind of environment where you wanted to do some kind of rolling upgrade or scheduled maintenance across different nodes in the cluster , in that case DBA's might want to bring down each node gracefully after all connections are drained from the node instead of shutting down the node and terminate the connections abruptly. so this property comes in handy that after a specified time interval in the aged timeout property the connections are closed and when needed it gets created again where at this point the connections are established to a different node based on the Oracle RAC configuration change allowing them to redirect the connection to node other than the node scheduled for downtime. This is all good based on the requirements but the downside to this is the connection manager will simply close the connection based on the aged timeout when the time expires and will not recreate the connection even if the pool is below the minimum connection settings that will caused the pool to go shrink below the expected minimum size which might impact the performace of the application at certain times. Theoretically speaking you don't need to set a aged timeout particulary if you are using Oracle RAC with properly implemented high availability techniques either using Fast Connection Failover or Transparent Application Failover where tthese are totally capable to handle failover in case of an scheduled/plan outage or unplanned outage, but DBA's sometimes seems to be overly cautious, wanted application administrator to enforce these settings. There may be environments where you will be using Oracle RAC but none of those failover mechanisms been implemented and in that case insist you DBA's to implement any one of those failover mechanism. Note if you are using tomcat default's commons dbcp connection pooling there isn't a aged timeout parameter in the connection pooling settings, then you might have to rely on the database failover features. If you are using c3po another popular connection pooling datasource there is a maxConnectionAge property where it will close the connection after the specified interval and also create a connection at the same time if necessary in order to not to shrink the pool below the specified minimum pool size.

1 comment:

Anonymous said...

Cool blog you got here. I'd like to read a bit more about that matter. The only thing it would also be great to see on this blog is some pics of some gizmos.
John Trider
Phone Blocker