Tomcat ignores removeAbandonedTimeout and closes connections in pool

Question

I am working on tomcat 7.0 with a JDBC connection to DB2 on an as400 using the commons dbcp. As soon as I put a load on the server, it immediately opens and closes connections to the database ignoring the 30 minute removeAbandonedTimeout setting I have set. I've tried several settings to no avail. For example, in a 15 second interval, it will open 150 connections and close 140 for no apparent reason. We have this application running on an old WebSphere server and it does not close the connections unless they are truly idle.

Here is my configuration:

 <Resource name="MYDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
          driverClassName="com.ibm.as400.access.AS400JDBCDriver"
          url="jdbc:as400://mydb.na.sysco.net;blockCriteria=2;dateFormat=iso;libraries=*LIBL;naming=system;errors=full"
          username="user"
          password="pass"
          maxActive="350"
          maxWait="180000"
          minIdle="10"
          maxIdle="50"
          testWhileIdle="true"
          validationQuery="select 1 from sysibm.sysdummy1"
          timeBetweenEvictionRunsMillis="34000"
          minEvictableIdleTimeMillis="1800000"
          removeAbandonedTimeout="1800"
          removeAbandoned="true"
          logAbandoned="true"/>

When the system is idle or only a couple of test accounts, it behaves as expected but as soon as I put a load on the server, the massive opens and closes start immediately. For example, it will open 150 connections and close 90 connections in the same 15 second interval. I'm reading the socket opens and closes that correlate to the starting and ending of QZ jobs on the as400 from my monitoring software. It will continually do this while connections are being used.

This defeats the purpose of connection pools. Any thoughts or ideas are appreciated.

Also confusing because it's not clear as to which parameters I should use to start with. The commons dbcp parameters which are unique from the Tomcat JDBC connection pool parameters are not being accepted, i.e. removeAbandonedOnMaintenance. But the default value of 1800000 for minEvictableIdleTimeMillis is picked up and not 60000 from Tomcat jdbc pool parameters.

https://commons.apache.org/proper/commons-dbcp/api-1.2.2/index.html

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

When I don't explicitly set parameters, this is what I get for defaults. It appears to list all of the tomcat jdbc pool parameters and not the commons DBCP parameters. However, it picks up the default for minEvictableIdleTimeout from commons DBCP and not the 60 seconds from Tomcat JDBC connection pool parameters. The stranger part is removeAbandoned parameter is not listed for commons dbcp, it's removeAbandonedOnMaintenance and removeAbandonedOnBorrow. I've been trying to nail this jello to the wall for a week now.

I use jmxproxy to get the realtime stats and settings.

http://myserver:8080/manager/jmxproxy/?qry=Catalina%3Atype%3DDataSource%2C*

Name: Catalina:type=DataSource,class=javax.sql.DataSource,name="EOPDB"
modelerType: org.apache.tomcat.util.modeler.BaseModelMBean
maxIdle: 8
testOnBorrow: false
defaultTransactionIsolation: -1
testOnReturn: false
maxActive: 350
numActive: 104
numTestsPerEvictionRun: 3
minIdle: 0
maxWait: 180000
closed: false
defaultAutoCommit: true
numIdle: 8
validationQueryTimeout: -1    
testWhileIdle: false
driverClassName: com.ibm.as400.access.AS400JDBCDriver
accessToUnderlyingConnectionAllowed: false
url: jdbc:as400://mydb;blockCriteria=2;dateFormat=iso;libraries=*LIBL;naming=system;errors=full
removeAbandonedTimeout: 300
defaultReadOnly: false
logAbandoned: false
poolPreparedStatements: false
maxOpenPreparedStatements: -1
removeAbandoned: false
minEvictableIdleTimeMillis: 1800000
timeBetweenEvictionRunsMillis: -1
initialSize: 0

Answer 1

maxIdle is maximum number of connections that can remain idle in the pool. Once number of idle connections crosses maxIdle, any connection released by application is closed immediately without checking connection idle time against minEvictableIdleTimeMillis.

Answer 2

There are 2 values that could be potentially problematic in your scenario

      timeBetweenEvictionRunsMillis="34000"
      removeAbandoned="true"

timeBetweenEvictionRunsMillis
is 34 seconds, in your scenario, 150 connections were created in the middle of that period and 90 destroyed when the time came leaving 60 connections in the pool, almost the 50 ones requested by maxIdle=50. Looks disproportionate compared to minEvictableIdleTimeMillis since you expect idle connection to live 30 minutes (1800000 ms) in the pool but you check every 34 seconds without taking any action because connections are supposed to live much longer.

removeAbandonedTimeout It's forcing your application to run queries faster that 1.8 seconds.

The value should be set to the longest running query your applications might have.

Make sure your application is correctly returning connections to the pool, they will be considered abandoned and closed.
Hope this helps.

Options are explained here.