It's not a secret anymore!
The moose likes Performance and the fly likes Connection Pooling (Performance Tuning) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Connection Pooling (Performance Tuning)" Watch "Connection Pooling (Performance Tuning)" New topic

Connection Pooling (Performance Tuning)

Sumit Amar
Ranch Hand

Joined: Jun 08, 2002
Posts: 180
This is a question for Jack Shirazi.
I want to know , is there any other competitive way to utilise open database connection(s), if connection pooling is not used? And also I want to know what are the drawbacks of using Connection Pooling?

SCJP, SCWCD, OCA, OCP-DBA 9i, <a href="" target="_blank" rel="nofollow">BCIP</a>, MCP, MCAD, MCSD.NET, MCDBA, MCTS (SQL 05), MCITP (DBD, DBA)
john guthrie
Ranch Hand

Joined: Aug 05, 2002
Posts: 124
i'm not jack, obviously, but here's a sort-of drawback i ran into last week.
i am actually just using jakarta's commons-pool to pool postgresql connections. commons-pool allows you to add a validate() method and tell the pool to validate the object via the method at any of three points - when the object is pulled from the pool, when it is returned to the pool, and/or at random "idle" times. the nice thing is that if the validate() method returns false, commons-pool destroys the object and creates a new one.
so i wrote a validate method that checks if the jdbc connection is still valid. i tested it by running some sql in a loop and restarting postgresql in the middle of the run. here's what i found.
after restarting postgresql, Connection.isClosed() returns false (bad). Connection.createStatement() does not fail, nor does Connection.prepareStatement("SOME SQL") - remember, i want it to fail so i can throw away the old bad Connection and get a new one. the only thing that actually finally fails is running some actual sql, e.g. "select current_timestamp".
so now my implementation of the connection pool has to run an extra sql statement *each time* i get a connection. it's either that or force a restart of the application if postgresql gets restarted (and that's not acceptable).
Ranch Hand

Joined: Nov 29, 2001
Posts: 121
Hi John,
I know that Connection pool is a pool to holding pre-created connection.
Do you mind to tell me how to add a connection to the pool? I still not really understand the flow and what should we code to create the pool?
Thank you.
john guthrie
Ranch Hand

Joined: Aug 05, 2002
Posts: 124
well, i am using the generic jakarta commons-pool for my connection pool. for that you need only write an object factory that implements an interface the pool expects and provide it to the pool. see for some good documentation.
others have noted that jakarta also has a database connection pool project, so i am starting myself to look at that. see
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

Hi Steffy,
With regard to your questions:

how to add a connection to the pool?
and what should we code to create the pool?

There are many, third-party connection pools available -- one of which is the one that John has said he is using, from the Apache group's Jakarta project:
And then again, you can always write your own (as we have done at my place of work).
Naturally, each of the implementations will have similarities and differences between them. We wrote our own mainly due to historical circumstances -- not necessarily because it was more appropriate for our application (but that's another story ;-)
You don't mention whether you are using a third-party connection pool or one that you have developed yourself. I assume it is one you have developed yourself, due to the questions you are asking -- since a third-party implementation should have documentation that answers your questions.
In my opinion -- assuming you are writing your own connection pool -- it is probably better to use a third-party implementation, since it is probably going to be better than something you would do (unless you are in the business of making such utilities, of-course ;-), and there are enough of them, that there is a good chance that you will find one that is appropriate for your needs.
By the way, if you are interested in a connection pool as part of some application you are developing, you are aware that most application servers today do include their own connection pool implementations. So if you are already using an application server, I suggest that you investigate the possibility of utilizing its connection pool (if you haven't done so already).
I hope this has helped to answer your questions.
Good Luck,
Ranch Hand

Joined: Nov 29, 2001
Posts: 121
Thanks John and Avi!
I will find out more information from net..
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
As you look at (or write) a pool manager, consider its policies.
Do you load a fixed number of members and stay with that number all day?
Can it dynamically add members (perhaps through a reference to a factory) and grow?
Is there a "high water" limit? What happens when you hit it? Maybe the client requester waits, maybe it fails.
Does it shrink again as demand goes down? Is there a "low water" limit?

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
subject: Connection Pooling (Performance Tuning)
It's not a secret anymore!