Solved

Websphere database connection minimum pool size - 0 or 1

Posted on 2010-08-25
11
5,622 Views
Last Modified: 2013-12-11
The websphere 6.1 application server and Oracle 10g database on our site have a firewall between with timeout of about 3 hours. The database datasource usage is purely based on user access which is restricted to working days (weekdays only). This causes the first call on the monday morning to return staleconnection (as firewall has disconnected it). My requirement is to honor the first request in case of staleconnection or pool being empty or otherwise (the first call will purge and recreate the pool).

Connection timeout      - 180 seconds  
Maximum connections - 100 connections  
Minimum connections  - 1 connections  
Reap time  seconds    - 180 seconds
Unused timeout          - 1800 seconds  
Aged timeout                  - 0 seconds  
Purge policy                  - EntirePool

To remedy this the minimum connection was reduced to 0. The above datasource connection timeouts are kept at installation defaults, whereas purge policy is updated to entirepool.

Here are a few questions I would like to ask regards to websphere connection pool.

1. Does test connection on websphere console affect connection timeout (resets it?) / affect pool (clears the staleconnections?)

2. If minimum connection size is reduced to 0, will the first connection request be returned as exception, triggering pool cleanup OR will the first request wait till pool is created assuming no delay on DB.

Do I require code change to retry connection in case of exception.

3. All the connection timeouts are within 30 mins, so after 30 mins of inactivity will the connection pools be 0, if so causing issue 2.

4. Is there any concept of keepalive database connection to get away with this entire configuration mess.  I could set a connection keepalive setting for every say 2 hours to avoid firewall drops.

(5.) Even when my min pool size was 1, I am still confused why it gave staleconnection as the connections should have gone long from the pool. Is aged timeout to be used here with specific value?

Would appreciate even if the answers are yes/no format wihtout explanation.
0
Comment
Question by:aibdev
  • 5
  • 5
11 Comments
 
LVL 8

Assisted Solution

by:mustaccio
mustaccio earned 350 total points
ID: 33525551
WebSphere (actually, the JDBC driver) does not know if a connection has gone stale until it is being used, so the solution will be to "ping" the database periodically and re-establish the connection if it turns out to be stale. On the Datasource properties there is a way to specify the pretest interval and the query to use for this (something like "select 1 from dual" will do).
0
 

Author Comment

by:aibdev
ID: 33525837
Hi mustaccio,

thanks for the reply, is there any way in which websphere periodically 'pings' the database. as doing this programmatically will be design hell.(timer/resource adapter or likes)

pretest connection is not useful here as you said 'WAS does not know if a connection has gone stale until ...' hence it will not call the connection till it is invoked through the code.With pretest connection my question again boils down to point 2. (will the first call return successfully with good connection OR fail triggering pool purge)
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 33525932
Check the manual. It will run the pretest SQL before returning the requested connection from the pool and, if necessary, re-establish the connection. There will be a small delay, obviously, because establishing a connection is expensive.
0
 

Author Comment

by:aibdev
ID: 33529377
Thanks m,
I will check the manual for your suggestion.

But the question that I am asking is subtly different, although it could be resoolved by your method (with overhead).

Without pretest connection setting, and with min connection of 0,
when the connection pool is empty will the first connection request be returned exception OR will it wait for the pool to build.
I feel that this questions the very basic of websphere connection pool concept
0
 
LVL 11

Assisted Solution

by:Radek Baranowski
Radek Baranowski earned 150 total points
ID: 33529487
it will wait for new one from pool. when setting  min connections to 0 scenario is as follows:
- seeing it from WAS perspective - each unused (by was) connection will be drop down according to settings:
Minimum connections  - 0 connections  
Reap time  seconds    - 180 seconds - is a interval of pool purge/check
Unused timeout          - 1800 seconds  - obvious
Aged timeout                  - 0 seconds - how much time must pass until connection is treated as unused

say, we have 10 connections used by application till 5 pm bu business users. at 5pm last application use is finished, and websphere stops to use them. after 1800 seconds they become unused, and after max 180 seconds the reaper discards them. as min connections is set to 0, all of them should be gone and pool should be empty. so next time, when application requests connection, pool creates a new one. I think this is the scenario you're aiming for, isn'it ?

the thing you experience now is because you have min conn set to 1, so despite being aged, unused and timed out, websphere keeps it alive because you want it to. was doesn't know that firewall has killed it long time ago and you get stale connection.

hope it helps.


0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:mustaccio
ID: 33530750
Clearly, when the pool is empty and a connection is requested from it, a new connection will be created, and obviously it will not be stale. By the way, "Minimum connections" is not a hard setting - there may be more or fewer connections in the pool. In other words, even if you set "Minimum connections" to 0, there's no guarantee that there will be 0 connections in the pool. You may want to set "Aged timeout" to something other than 0 as well.
0
 

Author Comment

by:aibdev
ID: 33530971
Thanks again baranello/mustaccio for your comments,

I understand the pool empty and new creation workflow,also I understand staleconnection issue. My question is maybe not clear.

When pool becomes zero (if at all and whenever), then will the first  request for connection return with any exception (connection not found etc) OR wait and return fresh connection (assuming no timeout due to pool creation etc).

I understand that the first request will create connections and subsequent calls will go through as expected. But it is the first request that I all care about.

Thanks again.
0
 
LVL 8

Accepted Solution

by:
mustaccio earned 350 total points
ID: 33531261
Just like I said before, if the pool is empty and an application requests a connection, a new connection is established and returned to the application. An exception will be raised only if the connection cannot be made (network error or database is unavailable or authentication problem).
0
 

Author Comment

by:aibdev
ID: 33540579
Thanks mustaccio,
I will test this out and check.
0
 

Author Closing Comment

by:aibdev
ID: 33544480
Does manual testing the connection reset the underlying connection pool (purge and recreate or reset other time specific parameters)
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 33544623
It will probably just reset the Unused timer for the connection that was used for the test. If there is no connection available, one will be created and the pool will grow, if Max. connections is not exceeded.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
Convert websphere application server default chained Certificates from 1024 to 2048 keysize or higher size and also you can change signatureAlgorithm . Please make sure Websphere Application Server fixpack 7.0.0.23 or Above. The following steps a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now