JDBC Pool and WmDB

, ,
1

Folks

Appreciate your experience here.

Currently, we are running on I.S. 6.0.1 SP2.

We have defined 1 single JDBC Pool Alias (Mix conn 20 / Max 80) for ISCoreAudit, ProcessAudit, XRef, TN.

One of our new architect suggests that we should define 4 seperate
JDBC Pool Aliases (still pointing to the same Oracle Database Instance/Schema though) with connection Min 5 / Max 20, and suggests that it should result in noticeable performance improvement, based on his pervious experience.

However, my understanding of JDBC Connection Pooling suggests otherwise (i.e there should not be any noticeable performance improvement, if there is any at all).

My theory is that the only ‘contention’ with 1 ‘large’ connection pool versus 4 ‘small’ connecion pool is that all 4 threads (ISCoreAudit, ProcessAudit, TN and XRef) will / may try to access the in-memory synchronized Java Object - Connection Pool Object at the same time to get connection (where the most expensive operation should be establishing DB connection, and all these DB connection should have been established). This Java Object ‘contention’ should be unnoticeable at all (talking about ms ), if there is any.

Can anyone share his/her experience here ! Thank in advance ! My question is what is the performance difference with 1 conection pool (with higher maximum number of connection) vs 4 connection pools (with
lower maximum number of connection) for ISCoreAudit, ProcessAudit, XRef and TN

In addition, the archtect also suggest that we migrate all application from WmDb to JDBC Adapater (which I totally agree). But I would like to know, since both WmDB and JDBC Adapter are using the same JDBC Driver, what kind of improvement (performances, connection management, memory, etc) does JDBC Adapter by itself brings to the table (remember, same JDBC DataBase Drivers). The architect suggests that the migration should be treated as high priority (which I disagree on this point).
However, I do agree we have to migrate the application since WmDB may be deprecated in future webMethods releases.

Once again, appreciate your experience here.

2

Stephen,
The first question I would ask is, am I having performance issues? If I am , have I identified the source of the issues? The next thing I would ask is, noticeable performance improvement in what? Which goes back to the first question, are there problems? Connection pooling is a synchronous process in webMethods which can cause contention which leads to your second question. WmDB versus JDBC Adapter.

I would agree with the “Architect” there. JDBC is more reliable. Because of the aforementioned problem with the synchronous nature of the connection pool, WmDB tends to lock this up when a database connection is not closed properly. In other words when I forget to close the connection in my flow service, I hose the connection pool completely. JDBC Adapter does not have this affliction. Which in my opinion is well worth the trouble of migrating. I not sure I would call it an emergency however, unless I had a bad group of developers who were not managing their connections.

My overall recommendation, for what it is worth, would be not to do major changes to the environment especially if you are not experiencing issues. You can make incremental changes as new code goes up to gradually migrate off the WmDB. As far as the internal server connection pools for IS, Process etc. I would beware of making changes without knowing what I am hoping to fix.

markg
http://darth.homelinux.net

3

Mark

Thanks a lot. Regarding WmDB, as I said, I totally agree that we should migrate. No question asked. Just would like to assess the priority here.
Actually, our developers do manage the connection quite well (e.g. calling db Close after execSQL). However, it is webMethods (e.g. WmPartner and SAP Package) that does not do it well all the time.

Regarding the internal server connection pools for ISCoreAudit, ProcessAudit, etc, just want to know what is the difference between one single well configured pool (with properly configured Min/Max connection) versus 4 well configured ‘smaller’ pool. I still think there should be no difference given how JDBC Connection Pool works.

Comments ?

Thanks

4

Stephen,
Just my opinion, but I would put the priority at low and just make the changes over time as I moved changes up. The JDBC adapter can bring it’ s on set of issues when used in a try/catch in conjunction with the broker. If you want the retry capability you will have to change your services to trap the specific runtime exceptions that are connection oriented, like the database is down etc.

As far as the connection pools for the internal services, I can’t imagine that it would make much difference but I have not done any performance testing to confirm that. We have not had any issues with the performance of the internal services configured with one connection pool. We are talking about a small number of ms here so any incremental improvement would probably be to small to actually notice or record for that matter.

But there is only one way to find out for sure. Performance testing in a test environment. But I sure wouldn’t do that unless I was experiencing a problem. My two cents.

markg
http://darth.homelinux.net