JDBC connection pool

We have one DB2 database in Production (One instance). We are dealing with a cluster with two IS servers and each WM IS server is configured with a single JDBC connection pool to this DB2 database with a maximum of 150 connections each. Multiple webmethods projects/apps/services use this connection pool to connect to DB2 database. But during Production outage (ran out of available connections in the connection pool) the difficulty was isolating the specific services consuming the connections in the pool. So all the services hosted on the servers and using this connection pool were impacted.

What is the best pratice to avoid this scenario? Do we create multiple JDBC Connection pools? At what level? One pool per service? Or one pool per Project/Application? We have both non-enterprise project specific and enterprise (shared services) services.

Does having one JDBC connection pool v/s many pools for the DB2 database per IS server save WM IS server resources? At what point does having multiple JDBC connection pools to the same db2 database become a Administrative nightmare?

At the same time how do we achieve service isolation such that if there is a problem with connections in one pool, all the other applications are not impacted. I’m looking for best pratices. Any ideas are welcome. Thanks much.

Regards,
Srinivas

I would go with a connection definition for each project and shared service. Naming conventions of the connections will help with administration.

One connection pools vs. many connection pools would save IS resources (less overhead) but I wouldn’t worry too much about that.

If a pool encounters trouble, all services using that pool will be impacted. The only way to segregate things is to create multiple pools. And if you have mission critical processes, isolate them on their own IS instance and server so that non-critical items don’t impact critical items.

Could we truly achieve service isolation in webmethods platform? Even though we define a specific pool per project (or per shared service), whenever that pool has issues (For e.g. it could also be an improperly designed service using the pool, which consumes all the connections in the pool without releasing them), I have seen in the past that if there is sufficiently high volume of requests coming into the server hitting the service that uses this pool, it could make the entire server non-responsive.

In summary, whenever there is a resource contention of any kind(For e.g: it could be a connection from pool, read/write operations to a samba file share (while the samba share is corrupted) etc…) the REQs (no. of request pending shown in the stats log) in the server grow tremendously (1500 -2000), threads go into wait state and the server eventually stops responding because we have common service thread pool. In the above case just having multiple pools will not help.

What techniques are available to achieve service isolation? How can we prevent one service from impacting all other services hosted on the server and thereby causing severe production outages?

Thanks for all the ideas.

Regards,
Srinivas Yeruva

Since all services in one IS instance share memory, etc. then you’re not going to be able to isolate rogue processes–unless you put such processes on their own instance.

You’re right that one misbehaved service can cripple a pool for all that use that pool (be it JDBC connection pool, thread pool, etc.). That’s the nature of sharing.

If such behavior is not desired, then separate IS instances are needed.

Shared nothing is an administrative headache. And expensive. Shared everything has the issues you list. You’ll want to find a balance between the two.