I am using Apache Generic Connection pooling in our application for connecting to database.We are creating our connection pool with following parameters,
connectionPool = new GenericObjectPool(null, 60);
it was configured by initial designer of the system who is no longer with the company. We have number of concurrent queries with the database. We some time get out of Connection exception even after making sure we are closing connections , statements and resultset. My question is if I configure pool to be more than 60 say 100 what are its implications or side-effects., if any
You will consume more resources in your database by increasing the connection pool size. Each connection comes at a cost.
What type of program are you running? Is it a web application, or an app with a lot of concurrent flows?
Does the program have long running queries?
60 connections is already a lot.
OCUP UML fundamental and ITIL foundation
Joined: Jul 19, 2008
It is a StrutsJava SE application and we do have concurrent queries/updates with the database.
I assume this is a typo and you meant Java EE since Struts is a web application framework. It is good that you reviewed your code to verify that connections are being closed properly. Your should also ensure that your code does not hang onto connections longer than it needs to.
There should be no major issues with increasing the pool size to 100, most databases should be able to handle the extra connections. I typically see connection numbers in the low hundreds (200-400) for databases such MySQL, PostgreSQL and Oracle.
The other possibility is to look at the waiting queue size for the pool. Thus keep the connections at 60, but enlarge the wait queue. You should monitor the wait queue size and the amount of time that threads are waiting for an available connection. If the wait time is relatively small, say 200ms or less, and the number of threads in the wait queue is small, say 5 or less, you should still be fine.
My usual recommendation is to look at the number of concurrent requests that you system is getting and determine what percentage of the processing of the requests require database connections and work from there. For example, if your are getting at max 100 concurrent requests, and those requests require a database connection for about 75% of their processing, then 75 database connections should work just fine.
Time for application review, I think. If a single user Swing application needs 60+ connection, that is realy a lot.
Those applications typically work with one connetion.
You will have to find out where the other 59 connections are held captured.