Wednesday, April 25, 2007

Understanding performance impact of shareable JDBC connections in struts based web application deployed on Websphere Application Server

The purpose of this article is to highlight the root cause of JDBC related performance issues that I have seen in multiple J2EE applications using struts framework. In many cases the root cause is the use of "shareable" connections, which is the default configuration in WebSphere.

The difference between shareable and unshareable connection is very nicely described in the following article.
http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html
Quoting from the above mentioned article an important point to note is that: -
"When the application closes a shareable connection, the connection is not truly closed, nor is it returned to the free pool. Rather, it remains in the Shared connection pool, ready for another request within the same LTC for a connection to the same resource."

This has serious impact if we are not using global or JTA transactions in our struts application. In that situation, WebSphere will create an LTC for each action forward. Let us take the following example of a simple search-results use case: -

1. A search request comes to the SearchAction.

2. SearchAction invokes "SearchHistoryDAO" to save the search parameters.
2.a) SearchHistoryDAO retrieves a connection from the pool, executes an insert and returns the connection back to the pool. (This takes 20ms)
2.b) SearchAction forwards the control to "SearchReultsAction"
3. SearchResultsAction invokes the search web service to perform the search and get the results. (This takes 3 seconds)
4. SearchResultsAction finally forwards the control to results.jsp to display the results to the user.
5. Control returns to SearchResultsAction
6. Control returns to SearchAction

In the above use case, if the connections are unshareable, then the connection fetched from the pool in step 2.a will be returned to the pool in around 20 ms in the same step. Thus it will be available for other threads to use in no time. "Connection use time" in this case will be 20 ms.

However, if the connections are shareable then the connection, even if closed will not be returned to the pool until the LTC of SearchAction is completed. That happens in step 6. So, the connection will not be available for other threads for around 3 seconds. The "connection use time" in this case will be more than 3 seconds.
This severely limits the number of connections available in the pool at any given time and does not allow the application to scale, maxing out the connection pool. With this configuration, there are many connections doing nothing while other threads (or the same one) wait for a connection.
This problem is compounded if each action in the chain performs a DB operation, and we have 3 or more actions in a chain. In this scenario, each HTTP request will require more than 1 simultaneous connection to process the request.


This issue manifests in the following way when "shareable" connections are used:-
1. Hung threads & deadlocks in Websphere
This situation occurs if the "ConnectionWaitTimeOut" parameter is left to the default in WebSphere and more than 1 action in an action chain requests a DB connection.
This parameter defines the time a thread will wait to obtain a connection from the pool before timing out, and the default is half an hour. Let us take an example where there are 10 connections in a pool. Now, if we send 10 simultaneous requests to the application server, each request will grab one connection and exhaust the pool. Then each action in the thread forwards the control to the next action in chain. That action will try to get a connection but none is available and so it will wait for half an hour to get it. However, no one will release it because it is held in the LTC of the previous action which will complete only when the current action waiting for the connection completes causing a deadlock.

2. JDBC Connection Wait Timeout exceptions
Incase the default value is changed from 30 minutes to a more reasonable 5-10 seconds, then deadlocks will not happen. In this case, at least one transaction will fail with a “ConnectionWaitTimeoutException” and deadlock will not happen.

3. Non-existent JDBC connection leaks
As described above, with shareable connections under load conditions the connection pool generally maxes out and we may start getting SQLException with the error " Unable to get a PooledConnection from the DataSource". This exception generally indicates that either the pool is too small, or there is a connection leak. Assuming that the pool size is set to a reasonable number, many times developers believe that this might be happening because of a connection leak and start reviewing code to find that "leak" wasting a lot of time and resources when there is no leak at all.

If you are facing any of the above mentioned issues, then it is highly recommended that you switch to the unshareable connections by configuring the same in web.xml as shown below.


<resource-ref>
<description>Database</description>
<res-ref-name>java:comp/env/jdbc/MyDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Unshareable</res-sharing-scope>
</resource-ref>

Also ensure that you are using a local JNDI reference using java:comp/env for lookup. Using the global lookup by /jdbc/MyDS is deprecated and websphere will use the default value in that case which is shareable connections.

8 comments:

急惊风与慢郎中 said...

Hi,

I'm trying to solve performance issue due to the shareable connections too. It seems to work for my case. However, do you know what could be the most obvious side-effects? Will the memory usage be higher? What are some of the simple tests I could conduct?

Thanks!

/CS

Samarth Bhargava said...

Hi CS,
Can you please elaborate a bit more on the issue you are trying to solve? I may be able to provide some help then.

Regards,
Samarth

急惊风与慢郎中 said...

I've found "Nested Local Transactions" (refer to technote: http://www-1.ibm.com/support/docview.wss?rs=180&context=SSEQTP&q1=J2CA0045E&uid=swg21217062&loc=en_US&cs=utf-8&lang=en).

And as recommended by the technote, the situation can be solved by having "Unshared connections".

However, could you enlighten whether the change to "Unshared" connections would impact the application logic in anyway?

Is it common for applications based on SPRING framework to require "Unshared" connections in Websphere?

Thanks!

/CS

Samarth Bhargava said...

Hi CS,
Making the connections unshareable will not have any impact on the application logic.
Instead of being kept in the LTC for reuse, the connection will now be returned to the pool as soon as the work is complete.

Whether shareable connections impact performance is a function of the number of servlets (servlet threads) being used to process a request.
If there is no kind of servlet chaining or action chaining then shareable connections are recommended.
However, if multiple servlets/actions are chained to process a single request, then you should use unshareable conenctions.

Whether you use spring f/w or not should not dictate the usage of shareable/unshareable connections.

However, I would like to say that if you are using Struts F/W, then most probably you might be forwarding from one action to another. In that case, you must use unshareable connections.

Regards,
Samarth

急惊风与慢郎中 said...

Hi,

Thanks for your insightful inputs.

Is this "Shareable" connection unique to Websphere? Because my application (which is based on STRUTS f/w) are pre-configured with Shareable connections and it seems to work fine in other container such as JBOSS.

Thanks!

/CS

milus said...

In "However, if the connections are unshareable then the connection, even if closed will not be returned to the pool until the LTC of SearchAction is completed." sentence should be shareable instead of unshareable

De Matte said...

Very good article, helped me in a lot of ways.

Just a quick remark: i think u used shareable a couple of times where u actually meant unshareable and vice versa

eg:
- However, if the connections are unshareable then the connection, even if closed will not be returned to the pool until the LTC of SearchAction is completed.

- If you are facing any of the above mentioned issues, then it is highly recommended that you switch to the shareable connections by configuring the same in web.xml as shown below.

Samarth Bhargava said...

Thanks for pointing out the typos. Fixed them.