In WAS when I create a datasource I can edit the Connection Pool properties (# of active connections, max # of active connections). Now if I say max =20, and if 1000 user requests come in to the WAS and each request runs in its own thread and each thread wants a connection, in essence i am reduced to 20 parallel threads.
Is this right? Because a connection object cannot be shared between threads.
I ask the question because most times, i see this paramter has a max value 20 - 30 when clearly the peak # of simultaneous requests to the server is well over a thousand. It seems we are able to service only 20 requests at a time?
Not really. Connection pooling takes charge of eliminating the overhead of creating and closing connections, by reusing them on database access.
If you have a thousand requests, and a maxSize pool of 20, then 20 database accesses will be performed in parallel, and once every request releases the database access, the same connection will be reused to attend another request. This is assuming that database access will be for a limited period of time (short operations), and that, once data is fetched / inserted / updated, the connection will be released for another request.
On the other hand, requests that cannot retrieve a database connection because the pool is fully in use, (let's say, the 21th request) will be in a waiting status, until some connection is released, so it's a transparent condition on the client side. All of this is supposing that the code is requesting and releasing connections from the pool, in a efficient way. The timeout for the requests is also a configurable property of the pool
You can tune up these values to get the most of it, and you can also consider another alternatives to avoid exhausting the pool for repetitive querys (like always the same select for all requests) by using technologies like database caching (ie, open terracota).
Hope this helps!