connection pooling
Hi,
I have a web app running on jboss and oracle as database. My app is basically a reporting app
where I have many sql queries being called to get result from db.I am using JDBC connection pool
to get and return connection from the pool just before and after each query.There will be about
45-50 users accessing this application at the same time and I am trying to find out
1) howmuch should be the size of the connection pool.
2) What should be minimum no. of connections defined for the pool.
3) In case a user doesnt have any available connection how long will it look for new connection
before timing out. Can I change this default timeout.
This is urgent for me as its going into production next week.
Any response appreciated.
Thanks,
kal
[829 byte] By [
kal132a] at [2007-11-27 8:40:00]
# 1
Here's my opinon:
1) The size of the connection pool should be 1 or more. If you make it zero, the first user logged on will have to wait about 1 or 2 seconds to connect. If you make it 1, he'll have to wait only a few milli seconds. The connection pool will automatically increase if it has too many users requesting connections at the same time. Even with 50 people using your application at the same time, I doubt more than 2 or 3 will need a connection exactly at the same time (most users will be logged in filling out info rather than clicking the submit button all within the same 2 milliseconds your code takes to run). Since you get and close the connection quickly and I assume it takes only a few miliseconds to run your code, I suggest setting the size to 3(?). If you set it to a high number (say 1000), the database reserves all those connections for you may run out of connections to hand out to other applicaitons. The database may crash.
2) min number of connection should be 3 (same as the size), max should be I guess about 5 or 10. If your program ever needs 10 simultanious connections to handle 50 people, there is something really wrong with your code (its not returing connections).
For testing your code, set the size to 1, min to 1, and max to 1 and have several people try out your code at once. This will help determine if you have a concurency problem (not returning connections).
3) a user will probably get a connection in a few milliseconds. Worse case is about 2 seconds(?) which isn't likely to happen.
4) I dont know what the default timeout for no longer used connections are. That's on the database and not set in your application. Its up to the database DBA to set that. Whatever the default is, is proabably adequate.
Lastly; I assume your practicing good coding. Something like (note the finally block):
Example:
Connection connection= null;
Statement statement=null;
ResultSet resultSet=null;
try{
connection= //get connection
statement= //get statement
resultSet=/// get resultSet
} catch(Exception e){
throw e;
} finally {
if(resultSet!=null)
resultSet.close();
if(statement!=null)
statement.close();
if(connection!=null)
connection.close();
}
}
# 2
It depends beneath the amount of users also on your application itself - How many connections need a user in average for doing a business task?
In our web application which does not cache any data we run with 30 connections (20 heavy user and ~70 normal one).
But in case of going production i would not really limit the max amount of connections (give it 100 and hope not to need), but watch carefully the behaviour at the first time.
We send an email to ourself if some limits of connections raised.
Set the warning level at the max expected amount(30?) and the more connections the higher the warning (error) level in the mail.
