JDBC Lost Connections

Hello. We are using webM 6.5 and we noticed that if one of our databases is down for awhile then the IS just freezes and the only way to get it back up and going is to restart it - even if the database has come back up.

So, shouldn’t the connection pool be smart enough to re-establish connections once a database comes back, and should the IS not lock up? The DB doesn’t even have any webM specific audit tables, so not sure why it would lock up.

Thanks,
Dan

What are the min connections settings on the connections you’ve defined? They should be zero. Also consider the block timeout setting.

We are currently using 5/20 for the min/max. What do you recommend for the block timeout setting?

Dapriett,

You should always have minimum connection setting to 0, because when the database base is down, adapter connection keeps 5 connection(in your case) even when the database is down and those connection becomes stale. Changing the minimum connection to 0 should solve your problem.

The block timeout controls how long a thread will block waiting for a DB connection to become available. The value to use here depends on load, max connections, performance/response needs, etc. You’ll want to consider the tradeoffs between waiting too long or failing too soon.

The default expire and block timeouts of 1 second is generally much too short for most integrations. You might start at 30 seconds and increase from there. Keep in mind that the longer the block timeout, the more loaded your server may become–e.g. you may end up with lots of threads (if inbound activity is high) waiting on a DB connection, which can adversely affect the stability of the server if the number of waiting threads gets too high.

To add to Rob’s comment about the server possibly being overloaded the longer the block timeout becomes, there’s a system property that you can set to help you manage that problem. The property is: watt.server.jca.connectionPool.thresholdWaitingRequest.

For more information, follow this link: http://advantage.webmethods.com/docid2000002296

- Percio