File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Configuring org.apache.commons.pool.impl.GenericObjectPool Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Configuring org.apache.commons.pool.impl.GenericObjectPool" Watch "Configuring org.apache.commons.pool.impl.GenericObjectPool" New topic
Author

Configuring org.apache.commons.pool.impl.GenericObjectPool

Supriya khare
Greenhorn

Joined: Jul 19, 2008
Posts: 10
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);
connectionPool.setMaxWait(1000);
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

-thanks


Smileyk
SCJP5
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Hi Supriya,

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.

Regards, Jan

OCUP UML fundamental and ITIL foundation
youtube channel
Supriya khare
Greenhorn

Joined: Jul 19, 2008
Posts: 10
It is a Struts Java SE application and we do have concurrent queries/updates with the database.
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5845
    
    7

Struts Java SE application

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.


JBoss In Action
Supriya khare
Greenhorn

Joined: Jul 19, 2008
Posts: 10
Sorry , I meant Swing not Struts. I am using Struts for another application. This is Java SE Swing application.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

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.
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5845
    
    7

I agree with Jan 100%.

I knew I should have waited for the answer to my question regarding the typo before I spent all of that time providing information on connection pooling in Java EE apps...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Configuring org.apache.commons.pool.impl.GenericObjectPool