I have a web application developed in Java/J2EE connecting to a warehouse at the back end.
We have IBM WAS as the application server and there is a data source defined in it for database connections.
The connection pool has is set to a maximum pool size of 30.
There are several times when the DB queries can take upto 1-2 minutes and hence user waits at the front end.
What will happen in a scenario when 40 or more different concurrent users access a screen that is running a query at the back end taking 1-2 minutes.
1. As per the WAS setting, there can be a maximum of 30 connections to DB,right ??
2. WAS will wait untill a DB connection finishes processing and returns the connection to pool; so it can be assigned to next request in queue ??
Well, first of all I'd say that 1 or 2 minutes to run a query is quite a long time... I think that you should try and see if that query may be optimized or if warehouse data clients ask for may be summarized somehow in an synthetic table..
About your questions, as far as I know you may have at most 30 connections simultaneously used in WAS with described configuration: other requests for a connection will not be satisfied if maximum limit has been reached, and requesting thread (i.e web request in your scenario) will be queued until a timeout is reached (this timeout depends upon your config, if I'm not wrong it's 180 secs by default, but I should check this settings, I don't remember well).
Can you suggest the calculation guidelines for a connection pool ? Any reference material will also help ? I have a breif idea; but want to be sure.
First of all I would check IBM's website (because we're talking about WebSphere), there's plenty of examples and good documentation. I don't think that there's not an "one size fits all" solution, anyway.
A very detailed approach may involve clients ' requests distribution on a statistical base, but that would exceed real needs in most scenarios.. you may monitorate via WebSphere Performance Activity tool
how your appserver is working and what is the medium load it has to work with, and then apply some practical rule to size your pool.
The public websites like banking/payments must be having huge connection pool and ensuring short lived transactions only to satisfy a huge customer base, right '