Connection pooling verse one connection per session
Hi, I have a sort of theoretical question. I'm wondering about the pros and cons of using a one connection per tomcat session strategy for connecting to a Postgresql server rather than connection pooling. My users generally login in the morning and keep my app open for extended periods of time, usually serveral hours at least. The one connection per session method seems to work well. Here are the reasons I'm using it. 1. Can cache prepared statements, something that is more problematic to do with a generic connection pool. 2. Have better control of connection releases via the finalize() method in a session helper class that contains the one single connection. 3. Easier to code and implement than connection pooling. 4. Potentially faster than connection pooling because of only one connection open per session. Are there issues that I'm overlooking? If I had more users with shorter sessions, would it make a difference? Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection pooling verse one connection per session
On Thu, Mar 10, 2005 at 10:31:41AM -0800, Mark Winslow wrote: : Hi, I have a sort of theoretical question. I'm : wondering about the pros and cons of using a one : connection per tomcat session strategy for connecting : to a Postgresql server rather than connection pooling. The great benefit of pooling is that objects that aren't being (actively) used by one person/login/etc can be used by another. Put another way, the Connection objects aren't specific to a user, so there's no need to treat them as such. (If you've done any EJB, think of Stateless vs Stateful Session Beans.) You say your users are logged into your app all day; but are they constantly streaming data from the minute they login to the minute they logout? If not, then holding open the DB connection for them isn't helping much. (I'd hesitate to say it's doing harm or anything bad, just that it's not helping.) For this same reason, pooling also helps in scalability: when a Connection is idle (not being used by anyone), someone else can use it. : 1. Can cache prepared statements, something that is : more problematic to do with a generic connection pool. True; but have you seen a significant performance gain due to prepared statement caching? : 2. Have better control of connection releases via the : finalize() method in a session helper class that : contains the one single connection. I'm not sure I understand this. If your app is written such that data-access code fetches a Connection as needed, then returns it to the pool when it's done (Connection#close()), then what other control would you need? You realize, for a pooled connection, close() doesn't really shutdown the network connection. It just sends the Connection object back to the pool. : 3. Easier to code and implement than connection : pooling. Again, I don't understand this. Please explain. : 4. Potentially faster than connection pooling because : of only one connection open per session. Yes and no. The connection pool keeps the connection open all the time; so users who go through a pooled Connection object don't suffer any first time access hits. -QM -- software -- http://www.brandxdev.net tech news -- http://www.RoarNetworX.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection pooling verse one connection per session
OK, I see your points and they are well taken. A lot of my concern has to do with this http://jakarta.apache.org/tomcat/tomcat-5.5-doc/printer/jndi-datasource-examples-howto.html#Database%20Connection%20Pool%20(DBCP)%20Configurations There is one problem with connection pooling. A web application has to explicetely close ResultSet's, Statement's, and Connection's. So I don't close my result sets, etc. I have say 5 unique connection hits per page. Say the tomcat connection trash collector (set with removeAbandoned=true) runs every 1 minute. In one minute a user can easily hit 5 pages. That's 25 connections I've created and used for that user instead of just 1. Am I wrong about this? A lot of my confusion has to do with the details of how the underlying connection pool code works and how efficient it is. For instance can you assume that the overhead to create a Pooled Connection based an an already established connection is negligable? Releasing it the same thing? There are threading issues involved with connection pools. Do they create inefficient blocking conditions? I understand that there are problems associated with hanging onto a resource like a connection for extended periods of time. It's sort of a non-standard thing to do and maybe not worth any potential cpu/memory benefits. I think I'm probably trying to talk myself into droping my strategy and implementing connection pools. Thanks. --- QM [EMAIL PROTECTED] wrote: On Thu, Mar 10, 2005 at 10:31:41AM -0800, Mark Winslow wrote: : Hi, I have a sort of theoretical question. I'm : wondering about the pros and cons of using a one : connection per tomcat session strategy for connecting : to a Postgresql server rather than connection pooling. The great benefit of pooling is that objects that aren't being (actively) used by one person/login/etc can be used by another. Put another way, the Connection objects aren't specific to a user, so there's no need to treat them as such. (If you've done any EJB, think of Stateless vs Stateful Session Beans.) You say your users are logged into your app all day; but are they constantly streaming data from the minute they login to the minute they logout? If not, then holding open the DB connection for them isn't helping much. (I'd hesitate to say it's doing harm or anything bad, just that it's not helping.) For this same reason, pooling also helps in scalability: when a Connection is idle (not being used by anyone), someone else can use it. : 1. Can cache prepared statements, something that is : more problematic to do with a generic connection pool. True; but have you seen a significant performance gain due to prepared statement caching? : 2. Have better control of connection releases via the : finalize() method in a session helper class that : contains the one single connection. I'm not sure I understand this. If your app is written such that data-access code fetches a Connection as needed, then returns it to the pool when it's done (Connection#close()), then what other control would you need? You realize, for a pooled connection, close() doesn't really shutdown the network connection. It just sends the Connection object back to the pool. : 3. Easier to code and implement than connection : pooling. Again, I don't understand this. Please explain. : 4. Potentially faster than connection pooling because : of only one connection open per session. Yes and no. The connection pool keeps the connection open all the time; so users who go through a pooled Connection object don't suffer any first time access hits. -QM -- software -- http://www.brandxdev.net tech news -- http://www.RoarNetworX.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection pooling verse one connection per session
On Thu, Mar 10, 2005 at 12:30:22PM -0800, Mark Winslow wrote:
:
http://jakarta.apache.org/tomcat/tomcat-5.5-doc/printer/jndi-datasource-examples-howto.html#Database%20Connection%20Pool%20(DBCP)%20Configurations
:
: There is one problem with connection pooling. A web
: application has to explicetely close ResultSet's,
: Statement's, and Connection's.
I'd hardly say that's a problem; that's just good coding practice. =)
(example: When I'm done cooking, I should turn off the stove. Is that a
problem with stoves, or just how stoves work?)
Connection cleanup maintenance is straightforward: liberally sprinlke
finally{} blocks around data access code. -and if that's abstracted out
into a layer, and a separate set of objects, you shouldn't have to look
in that many places to insert said finally{} blocks.
: For instance can you assume that the
: overhead to create a Pooled Connection based an an
: already established connection is negligable?
A pooled connection usually *is* an established connection. The idea of
pooling (any sort of object pooling) is that the app (here, Tomcat)
instantiates some number of said objects ahead of time, such that
they're ready to use when needed. In some cases the objects are created
on-demand but then kept around for future use.
Pooled Connection objects are wrapped in another object (that also
implements Connection) that intercepts close() calls. Instead of
actually closing the connection, it returns the object to the pool.
: There are threading
: issues involved with connection pools.
Such as...? As long as you treat a Connection as a hot potato -- hold
on to it only as long as you need, then pitch it -- there should be no
such threading issues. -and as long as you only fetch Connection objects
from the DataSource, then you should never run into an issue where two
sections of code get the same Connection.
: It's sort of a non-standard thing to
: do and maybe not worth any potential cpu/memory
: benefits. I think I'm probably trying to talk myself
: into droping my strategy and implementing connection
: pools.
For me, it's mostly a design issue. I design my apps such that I can
switch the connection source (pooled, one-off, etc) and the code is none
the wiser. It just knows, I get a Connection here, and I call close()
on it when I'm done. Whether close() really terminates a network
connection/DB session, or just returns an object to the pool, it doesn't
matter...
-QM
--
software -- http://www.brandxdev.net
tech news -- http://www.RoarNetworX.com
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection pooling verse one connection per session
I'm still not entirely sure about this issue. The close/=null + finally blocks make for pretty ugly and error prone code if you ask me. Why doesn't the connection pool encapsulate closing anyway? Can't it encapsulate closing into the finalize() methods? Are there ordering issues for closing ResultSets, Statements, and Connections? Is infrequent garbage collection an issue? I'm asking about encapsulating into the finalize() methods because I don't like the asthetics of all the close/=null + finally statements. I believe they make for confusing and error prone code and would like a way to make my own encapsulating classes. --- QM [EMAIL PROTECTED] wrote: On Thu, Mar 10, 2005 at 12:30:22PM -0800, Mark Winslow wrote: : http://jakarta.apache.org/tomcat/tomcat-5.5-doc/printer/jndi-datasource-examples-howto.html#Database%20Connection%20Pool%20(DBCP)%20Configurations : : There is one problem with connection pooling. A web : application has to explicetely close ResultSet's, : Statement's, and Connection's. I'd hardly say that's a problem; that's just good coding practice. =) (example: When I'm done cooking, I should turn off the stove. Is that a problem with stoves, or just how stoves work?) __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
On Thu, Mar 10, 2005 at 10:31:41AM -0800, Mark Winslow wrote: 2. Have better control of connection releases via the finalize() method in a session helper class that contains the one single connection. I hope you meant finally clause rather than finalize() method. A finalize() method is only called when an object is garbage collected, which may not happen for a long, long time, if ever. - Chuck THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
if ever.??? Is that really the case? My personal experience with Tomcat is that it does indeed have memory leak problems. In theory, shouldn't all objects created in a web user session eventually be garbage collected after the session ends? I in fact did mean the finalize() method. Is that the main reason not to encapsulate close() methodology there because of slow garbage collection? --- Caldarale, Charles R [EMAIL PROTECTED] wrote: On Thu, Mar 10, 2005 at 10:31:41AM -0800, Mark Winslow wrote: 2. Have better control of connection releases via the finalize() method in a session helper class that contains the one single connection. I hope you meant finally clause rather than finalize() method. A finalize() method is only called when an object is garbage collected, which may not happen for a long, long time, if ever. - Chuck THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Sports - Sign up for Fantasy Baseball. http://baseball.fantasysports.yahoo.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
From: Mark Winslow [mailto:[EMAIL PROTECTED]
Subject: RE: Connection pooling verse one connection per session
if ever.??? Is that really the case? My personal
experience with Tomcat is that it does indeed have
memory leak problems.
Nearly all the memory leaks I've seen have been in the apps, frequently related
to use of static variables to hold references to various session- or
request-related items. The few that have actually been a Tomcat problem seem
to get fixed fairly quickly.
In theory, shouldn't all objects created in a web user session
eventually be garbage collected after the session ends?
The GC algorithms do not guarantee that any specific object will be collected.
If minor (young generation) GCs are sufficient to keep the JVM running, a full
(tenured generation) GC need not be performed. Objects in the tenured
generation may never be collected and their finalize() methods may then never
be called - the JVM may terminate first.
I in fact did mean the finalize() method. Is that the
main reason not to encapsulate close() methodology
there because of slow garbage collection?
I don't know that I'd characterize it as slow; the current GC algorithms try
to do as little work as possible, so it may be a long time before the desired
object gets collected. Since the point at which a finalize() method is called
is so indeterminate, its usefulness is rather limited. (Any class with a
finalizer method is also somewhat inefficient, since it disrupts GC - each such
object must be collected twice - and it slows down object allocation, since
each finalizable object has to be registered.)
The try {}, catch {}, finally {} sequence is much more robust.
- Chuck
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection pooling verse one connection per session
On Thu, Mar 10, 2005 at 01:52:02PM -0800, Mark Winslow wrote: : I'm still not entirely sure about this issue. The : close/=null + finally blocks make for pretty ugly and : error prone code if you ask me. Well, certainly no one's forcing you to code that way. It's just a fairly standard practice in Java webapps to pool DB connections and to use try/catch/finally blocks where appropriate. ;) : Why doesn't the connection pool encapsulate closing : anyway? It seems you're missing the point of the pool. Pooled connections remain open (logged in to the remote DB). The close() method on the Connection wrapper object puts the (underlying) Connection object back in the pool so others can use it. : Can't it encapsulate closing into the : finalize() methods? Maybe it does; but there's no guarantee finalizers will be called. Besides, do you want to wait for gc to run to free up connections? : Are there ordering issues for : closing ResultSets, Statements, and Connections? This is in the docs... I *think* closing a Connection closes all underlying Statement and ResultSet objects, but it's good form to just close them when you're done with them. : Is infrequent garbage collection an issue? If you're waiting for finalizers to do your cleanup work for you, yes. : I'm asking about encapsulating into the finalize() : methods because I don't like the asthetics of all the : close/=null + finally statements. Again, your call. -QM -- software -- http://www.brandxdev.net tech news -- http://www.RoarNetworX.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
I don't know. I have one pure Tomcat (no Apache) server that all it does is serve about 300,000 static files per day. The memory usage grows and grows unexplicably. I run a cron job that restarts it everyday, which I had to started running with version 5.0.something or else it would eventually run out of memory and crash. I haven't seen what not restarting will do on 5.5 and the latest version of Java/Linux. I guess I'll try and see. I'm going to switch to using connection pools regardless. I really just had some questions about the standard way of releasing them and gc issues regarding them which have been answered by the nice people on this list. Thanks. Nearly all the memory leaks I've seen have been in the apps, frequently related to use of static variables to hold references to various session- or request-related items. The few that have actually been a Tomcat problem seem to get fixed fairly quickly. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
Sorry, just had one other question about the use of
static variables. Can this really be a problem? I
thought that a static variable only gets a single copy
per JVM/Context. For instance the use of static
variables to define formats shouls save on memory
usage shouldn't it?
public class Helper
{
public static final DecimalFormat dFormat1 = new
DecimalFormat(00);
public static final DecimalFormat dFormat2 = new
DecimalFormat(#,##0.00);
public static final SimpleDateFormat df1 = new
SimpleDateFormat(HH:mm);
public static final SimpleDateFormat df2 = new
SimpleDateFormat(EEE, d MMM, );
}
the apps, frequently related to use of static
variables to hold references to various session- or
__
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
From: Mark Winslow [mailto:[EMAIL PROTECTED] Subject: RE: Connection pooling verse one connection per session The memory usage grows and grows unexplicably. Modern JVMs also try to avoid doing GC if they can. So, if you've given the JVM a large amount of memory, it will use it all before attempting a GC. - Chuck THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling verse one connection per session
From: Mark Winslow [mailto:[EMAIL PROTECTED] Subject: RE: Connection pooling verse one connection per session Sorry, just had one other question about the use of static variables. Can this really be a problem? I thought that a static variable only gets a single copy per JVM/Context. For instance the use of static variables to define formats shouls save on memory usage shouldn't it? I didn't mean to imply earlier that all static variable usage was bad, just that it can be easily abused in a fashion that leads to memory leaks. The usages you've outlined seem quite appropriate. - Chuck THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
