Forums Register Login

Connection Pooling (Performance Tuning)

+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
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).
+Pie Number of slices to send: Send
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.
steffy
+Pie Number of slices to send: Send
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 http://jakarta.apache.org/commons/pool/ 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 http://jakarta.apache.org/commons/dbcp/
+Pie Number of slices to send: Send
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:
http://jakarta.apache.org
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,
Avi.
+Pie Number of slices to send: Send
Thanks John and Avi!
I will find out more information from net..
+Pie Number of slices to send: Send
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?
You will always be treated with dignity. Now, strip naked, get on the probulator and hold this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1149 times.
Similar Threads
Transactions in Tomcat
ConnectionPooling for standalone java app
Questions on EJB
Connection pooling in weblogic 8.1
want sample source code reg. Connection pooling
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 07:12:49.