Chris Elleman Technical Stuff

16 May/10 Off

Apache Tomcat 5.5 Stale Connections to Oracle DB

The Problem: the media company was having a problem with its Tomcat configuration, whereby, DB connections were going stale. The systems involved were pretty standard, an Oracle 10g Database and Tomcat DBCP all running on Solaris 10 x86. The problem was that Tomcat was spinning up a set of datasource pools per web context which meant that there were a lot of idle connections which were never being used and so never re-cylced, leading to stale connections on the database; also, with the maxwait being set to never expire, this also contributed to the stale connections

The current datasource configuration: an example (context.xml):

<Resource name="jdbc/ECE_UPDATE_DS"
 auth="Container"
 type="javax.sql.DataSource"
 driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@(DESCRIPTION = (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL=TCP)(HOST=dtodbgz80)(PORT=1521)) (ADDRESS = (PROTOCOL=TCP)(HOST=dtodbgz80)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = TS11)))"
 connectionProperties="{ValidateConnection=true,ConnectionWaitTimeout=10}"
 username="uat"
 password="uat"
 validationQuery="select 1 from dual"
 testOnBorrow="true"
 poolPreparedStatements="true"
 maxOpenPreparedStatements="10"
 initialSize="2"
 minIdle="2"
 maxActive="5"
 maxIdle="5"
 maxWait="-1"
 removeAbandonedTimeout="60"
 removeAbandoned="true"
 logAbandoned="true"/>

The Solution: this was to change the datasource configuration, so that one a single set were spun up per tomcat server, and then each application context would get a reference so that they could use to the "master" pool; changing the maxwait to a resonable value of 5 mins (30000 milliseconds) also helpd to remove the stale connections and let the DBCP pool manage itself more effecitivly:

The new datasource configuration:

server.xml:

<Resource name="jdbc/ECE_UPDATE_DS"
 auth="Container" type="javax.sql.DataSource"
 driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@(DESCRIPTION = (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL=TCP)(HOST=dtodbgz80)(PORT=1521)) (ADDRESS = (PROTOCOL=TCP)(HOST=dtodbgz80)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = TS11)))"
 connectionProperties="{ValidateConnection=true,ConnectionWaitTimeout=10}"
 username="uat"
 password="uat"
 validationQuery="select 1 from dual"
 testOnBorrow="true"
 poolPreparedStatements="true"
 maxOpenPreparedStatements="10"
 initialSize="2"
 minIdle="2"
 maxActive="5"
 maxIdle="5"
 maxWait="30000"
 removeAbandonedTimeout="60"
 removeAbandoned="true"
 logAbandoned="true"/>

 context.xml:

<ResourceLink name="jdbc/ECE_UPDATE_DS"
    global="jdbc/ECE_UPDATE_DS"
    type="javax.sql.DataSource"/>

 

References: http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html