Increase load and decrease performance while replace DBCP to Tomcat JDBC-pool

Question

After problems with connection leak and deadlocks in DBCP we made a decision to replace it with Tomcat JDBC-pool. Of course migration was really simple.

But after deploy it on a production environment I noticed, that load on a server with running two Tomcats increase from 4-4.5 to 5.5. We didn't do anything more, except change of pool. Moreover, performance measured with JMeter decrease by about 5%.

I spent some time to tune pool parameters, but without visible effects. I pasted my current config (from <GlobalNamingResources> in server.xml) below:

<Resource name="jdbc/xxxxxx"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          initialSize="10"
          maxActive="100"
          minIdle="10"
          maxIdle="50"
          maxWait="10000" 
          testOnBorrow="true"
          testOnReturn="false"
          testOnConnect="false"
          testWhileIdle="false"
          validationQuery="SELECT 1 from dual"
          validationInterval="30000"
          suspectTimeout="60"
          timeBetweenEvictionRunsMillis="30000"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          abandonWhenPercentageFull="50"
          minEvictableIdleTimeMillis="60000"
          jmxEnabled="true"
          username="xxxxx"
          password="xxxxx"
          driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:oci:xxxxx"/>

FairQueue and PoolSweeperEnabled are true

In Spring applicationContext-jdbc.xml I have only:

  <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="resourceRef">
      <value>true</value>
    </property>
    <property name="jndiName">
      <value>java:comp/env/jdbc/PortalDB</value>
    </property>
  </bean>

What am I doing wrong? I thought, that JDBC_pool should be faster than DBCP out of the box.

Try testWhenIdle="true", and also try to minimize the number of maxActive from 100 to something like 20. perhaps having too many connections in the pool is slowing things down. — george_h, Jan 29 '13 at 07:20
@rootkit007 - no, with dbcp I didn't use ant validation query. — Dzinek, Feb 12 '13 at 21:38
Try a validation query that does not access the database. Some SQL servers are quirky but generally something like 'select true' works just fine, and does not involve accessing any tables — rootkit, Feb 12 '13 at 23:10
You probably shouldn't `testOnBorrow` - this is faily expensive, try `testOnReturn`. — Boris the Spider, Feb 15 '13 at 23:02
"I spent some time to tune pool parameters, but without visible effects." : Can you verify you are actually testing what you modify? You could shrink the pool to 1 and see the drop in concurrency. — Szocske, Feb 23 '13 at 19:20
Could you post your total server thread from the server.xml for comparing the server thread count to db thread count? — ledlogic, Jan 17 '15 at 15:49

score 1 · Accepted Answer · answered Feb 10 '14 at 20:35

Your settings and tuning appear correct. Your load increase may be as a result of the server handling more concurrent requests at the same time. DBCP may have prevented the server to take on this load because of how it locked the pool from all threads. The jdbc-pool doesn't do this, so now you've increased your concurrency. And if the load increases, the response may decrease, but your throughput would increase.

I'd start tuning

maxActive

to match your maxThreads in order to handle concurrency.

score 0 · Answer 2 · edited May 23 '17 at 11:52

Your diagnosis is strange: AFAIK Tomcat's DBCP lib is just a repacked version of commons-dbcp... so changing from one to the other should not lead to any change in behaviour or performance. (see here)

What may have changed though is the version(s) you use: in particular, beware of the 1.3/1.4 differences. (see here)

Anyways, your configuration seems alright (and it works indeed, despite the issues you may experience). Updating your libs was probably the only way to solve your problems without entering in debug mode...

To go further, could you be more specific about what you mean by "problems with connection leak and deadlocks in DBCP"? It is a quite precise diagnosis to determine there is a link between deadlocks and the connection pool: how did you come to this? You may be experiencing deadlocks because your SQL statements are prone to make deadlocks happen, whereas the pool only makes it possible by providing many connections simultaneously - which is just its job actually.

OP is talking about Tomcat jdbc-pool, which is different from Tomcat's repackaged dbcp. http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html — dnault, Dec 03 '13 at 22:00

Increase load and decrease performance while replace DBCP to Tomcat JDBC-pool

2 Answers2