Thread BLOCKED on registerConnection/unregisterConnection?

[alrubinger]

Hiya.

Today we've done a stress test on our application and observed some new behavior - many of the functions that we're delegating to Quartz Scheduler (each with its own Thread) started to take quite a while to complete, and queued up.

Did a Thread Dump, revealing many of these Threads to be in BLOCKED state, referencing a WeakHashMap while either attempting to register or unregister a connection from the pool.

Any ideas what could cause this? Incorrectly configured pool size? Not enough DB Connections available?

Thread: DefaultQuartzScheduler_Worker-23 : priority:7, demon:false, threadId:58, threadState:BLOCKED, threadLockName:java.util.WeakHashMap@6ac57e7d

org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:288)
org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:417)
org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:69)
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)

S,
ALR

[vickyk]

There seems to be couple of possibilities
1) Long running genuine SQL's
2) Untuned SQL's which are taking longer time to get execute thus making the connection unavailable to pool for longer time .
For the "long running SQL's " you would have to configure the pool with larger number of connections and for the latter case you got to optimize the application code (fine tune SQL's) .

[alrubinger]

Very helpful, thank you. Took a look at the root of some of these, X-referenced with with "Slow Query Log" on our DB, and found this to very possibly be the root of the problem.

[vkopichenko]

During performance tests on JBoss 4.2.3.GA we've also faced much BLOCKED threads on org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:288)

and org.jboss.resource.connectionmanager.CachedConnectionManager.unregisterConnection(CachedConnectionManager.java:318)

The connection pool was not exhausted.

Digging in sources led to this:

http://grepcode.com/file/repository.jboss.org/nexus/content/repositories/releases/org.jboss.jbossas/jboss-as-connector/5.0.0.Beta4/org/jboss/resource/connectionmanager/CachedConnectionManager.java#286

286      if (debug)

287      {

288         synchronized (connectionStackTraces)

289         {

290            connectionStackTraces.put(connection, new Throwable("STACKTRACE"));

291         }

292      }

So the problem is not about slow sql but about debug synchronization.

This can be disabled in deploy/jbossjca-service.xml with <attribute name="Debug">false</attribute>.

It gave us 25% performance improvement!