Re: Connection Pooling questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Chuck, On 3/31/2009 10:57 PM, Caldarale, Charles R wrote: From: allen.ir...@smartintegration.com.au Subject: Connection Pooling questions I do close the connections within my program using conn.close(); The evidence suggests otherwise. You also need to close result sets and statements associated with the connection, and all of the close() calls should be in a finally block to insure they always get executed. Agreed. See http://blog.christopherschultz.net/?p=68 for examples. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAknTW6AACgkQ9CaO5/Lv0PA6pwCfTDrXTqCfV94BTNKR6tDZl8Gf uE8AnRBMm2Iraobvs4NNSnIPzUgcdiov =xPNW -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Yes! And also, close out any other connections that may be embedded, like a prepared statement, result set or stored procedure. These will also show that a connection is still opened if not explicitly closed! -Original Message- From: allen.ir...@smartintegration.com.au [mailto:allen.ir...@smartintegration.com.au] Sent: Tuesday, March 31, 2009 8:43 PM To: users@tomcat.apache.org Subject: Connection Pooling questions Do I have to turn on some kind of cleanup explicitly? I do close the connections within my program using conn.close(); as the howto suggests. I thought that when the connections were not used that they would over time be really closed and I would end up with the maxIdle value of 10 open connections within my Pool. thanks! mail2web.com - Enhanced email for the mobile individual based on Microsoft(r) Exchange - http://link.mail2web.com/Personal/EnhancedEmail - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Hey Chuck,
What happens when the statement isn't closed but the connection is returned to
the pool? I have some code blocks that don't have a finally block to close the
result set and prepared statement. If an exception happens the statement and
the result set stay open but the connection gets returned to the pool.
What is the effect of this ?
-Original Message-
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Sent: Tuesday, March 31, 2009 11:58 PM
To: users@tomcat.apache.org; chuck.caldar...@unisys.com
Subject: RE: Connection Pooling questions
Sorry, I included an older context.xml than the one I've been testing with.
That one has 30 for maxActive (everything else is the same).
Thank you very much for your replies Chuck, I think you gave me plenty to
think about and play with to resolve my problem. Thanks again!
Original Message:
-
From: Caldarale, Charles R chuck.caldar...@unisys.com
Date: Tue, 31 Mar 2009 22:44:32 -0500
To: users@tomcat.apache.org
Subject: RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Subject: RE: Connection Pooling questions
Resource name=jdbc/AttunitySProcDS auth=Container
type=javax.sql.DataSource
driverClassName=com.attunity.jdbc.NvDriver
url=jdbc:attconnect://10.20.5.107:/baynav;DefTdpName=webdemo
maxActive=50 maxIdle=10/
You said maxActive was 30, but it's 50 in your config. I've never used the
Attunity drivers, but the above looks ok as far as I can tell; perhaps
someone else might see a problem. You might want to consider adding these:
removeAbandoned=true
removeAbandonedTimeout=60
logAbandoned=true
That will tell you if the pool thinks you're returning connections or not.
Change the timeout setting (in seconds) to whatever you think is
appropriate.
so you're saying that the pool will never close any of the connections
due to them not being used?
Correct; that's normally left up to the database.
Here's the block I use:
finally
{
// cleanup
if (cs != null) cs.close();
if (conn != null) conn.close();
}
Where cs = the statement.
If the cs.close() throws an exception, you won't close the connection. Do
you only ever have one statement per connection? And depending on the
location of the finally block relative to the rest of the code, you might
not even go through there.
I don't close the result set as closing the
statement is supposed to do that.
Regardless, it's good practice to clean up DB resources explicitly,
preferably as soon as you're done with them.
Maybe this timeout value is what I'm looking for?
Depends on what you want; for a production environment, you never want the
connections to close. They're cheap to keep around but expensive to create.
- 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
mail2web.com - What can On Demand Business Solutions do for you?
http://link.mail2web.com/Business/SharePoint
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Pretty sure it's a rogue connection that stays open.
-Original Message-
From: Shaun Farrugia [mailto:sfarru...@fry.com]
Sent: Wednesday, April 01, 2009 9:13 AM
To: Tomcat Users List; allen.ir...@smartintegration.com.au;
chuck.caldar...@unisys.com
Subject: RE: Connection Pooling questions
Hey Chuck,
What happens when the statement isn't closed but the connection is returned to
the pool? I have some code blocks that don't have a finally block to close the
result set and prepared statement. If an exception happens the statement and
the result set stay open but the connection gets returned to the pool.
What is the effect of this ?
-Original Message-
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Sent: Tuesday, March 31, 2009 11:58 PM
To: users@tomcat.apache.org; chuck.caldar...@unisys.com
Subject: RE: Connection Pooling questions
Sorry, I included an older context.xml than the one I've been testing with.
That one has 30 for maxActive (everything else is the same).
Thank you very much for your replies Chuck, I think you gave me plenty to
think about and play with to resolve my problem. Thanks again!
Original Message:
-
From: Caldarale, Charles R chuck.caldar...@unisys.com
Date: Tue, 31 Mar 2009 22:44:32 -0500
To: users@tomcat.apache.org
Subject: RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Subject: RE: Connection Pooling questions
Resource name=jdbc/AttunitySProcDS auth=Container
type=javax.sql.DataSource
driverClassName=com.attunity.jdbc.NvDriver
url=jdbc:attconnect://10.20.5.107:/baynav;DefTdpName=webdemo
maxActive=50 maxIdle=10/
You said maxActive was 30, but it's 50 in your config. I've never used the
Attunity drivers, but the above looks ok as far as I can tell; perhaps
someone else might see a problem. You might want to consider adding these:
removeAbandoned=true
removeAbandonedTimeout=60
logAbandoned=true
That will tell you if the pool thinks you're returning connections or not.
Change the timeout setting (in seconds) to whatever you think is
appropriate.
so you're saying that the pool will never close any of the connections
due to them not being used?
Correct; that's normally left up to the database.
Here's the block I use:
finally
{
// cleanup
if (cs != null) cs.close();
if (conn != null) conn.close();
}
Where cs = the statement.
If the cs.close() throws an exception, you won't close the connection. Do
you only ever have one statement per connection? And depending on the
location of the finally block relative to the rest of the code, you might
not even go through there.
I don't close the result set as closing the
statement is supposed to do that.
Regardless, it's good practice to clean up DB resources explicitly,
preferably as soon as you're done with them.
Maybe this timeout value is what I'm looking for?
Depends on what you want; for a production environment, you never want the
connections to close. They're cheap to keep around but expensive to create.
- 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
mail2web.com - What can On Demand Business Solutions do for you?
http://link.mail2web.com/Business/SharePoint
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions What happens when the statement isn't closed but the connection is returned to the pool? The connection object your code sees isn't the real connection; it's a wrapper created by the pool to control access to the real thing. Likewise, the ResultSet, Statement, PreparedStatement, etc., objects visible to the webapp are also wrappers around the real objects. Using these, the pool knows if the real connection has been cleaned up properly; the pool won't reuse the connection until it is has returned to an idle state. I have some code blocks that don't have a finally block to close the result set and prepared statement. If an exception happens the statement and the result set stay open but the connection gets returned to the pool. A brief scan of the code in commons-dbcp indicates that when a Connection.close() is done, an attempt is made to close the associated Statement and ResultSet objects. Some experimentation (which I don't have time for right now) would make me more comfortable with that observation. - 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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Sweet, I set up a test.jsp page that will Connection.close() without rs.close() and ps.close(). Will let you know the results.. And I know you answered this before but I have to ask again. Tomcat is using it's own version of DBCP (based on properties files) Are there any changes to DBCP other than package moves? Can I utilize the DBCP source package and expect that it's the same code tomcat is using? -Original Message- From: Caldarale, Charles R [mailto:chuck.caldar...@unisys.com] Sent: Wednesday, April 01, 2009 10:47 AM To: Tomcat Users List Subject: RE: Connection Pooling questions From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions What happens when the statement isn't closed but the connection is returned to the pool? The connection object your code sees isn't the real connection; it's a wrapper created by the pool to control access to the real thing. Likewise, the ResultSet, Statement, PreparedStatement, etc., objects visible to the webapp are also wrappers around the real objects. Using these, the pool knows if the real connection has been cleaned up properly; the pool won't reuse the connection until it is has returned to an idle state. I have some code blocks that don't have a finally block to close the result set and prepared statement. If an exception happens the statement and the result set stay open but the connection gets returned to the pool. A brief scan of the code in commons-dbcp indicates that when a Connection.close() is done, an attempt is made to close the associated Statement and ResultSet objects. Some experimentation (which I don't have time for right now) would make me more comfortable with that observation. - 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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions Are there any changes to DBCP other than package moves? No, just the package renaming to avoid collisions. Can I utilize the DBCP source package and expect that it's the same code tomcat is using? Yes; current versions of Tomcat are using version 1.2.2 of commons-dbcp, so make sure you look at that level. - 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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Connection Pooling questions
Caldarale, Charles R wrote: From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions What happens when the statement isn't closed but the connection is returned to the pool? The connection object your code sees isn't the real connection; it's a wrapper created by the pool to control access to the real thing. Likewise, the ResultSet, Statement, PreparedStatement, etc., objects visible to the webapp are also wrappers around the real objects. Using these, the pool knows if the real connection has been cleaned up properly; the pool won't reuse the connection until it is has returned to an idle state. I have some code blocks that don't have a finally block to close the result set and prepared statement. If an exception happens the statement and the result set stay open but the connection gets returned to the pool. A brief scan of the code in commons-dbcp indicates that when a Connection.close() is done, an attempt is made to close the associated Statement and ResultSet objects. Some experimentation (which I don't have time for right now) would make me more comfortable with that observation. - Chuck Just for completeness -- This is assuming the developer didn't unwrap any of the DBCP objects to access driver specific features. In that case, the original wrapped objects need to be maintained and closed instead of the unwrapped objects. --David - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
I ended up testing this based on the following Tomcat 5.5.27, JTDS 1.2, Abandoned Logging and Return set to 30 seconds and true. Test A) Open connection, open Prepared Statement, Open Result set, Close Connection Test B) Open Connection, open prepared Statement, open resultset. Didn't close anything. Test A Resulted in statements and result sets not affecting or throwing errors on the client side. Didn't check to see if cursors were open in the db Test B Resulted in Abandoned Pool thrown after waiting 30 seconds and executing the test again. -Original Message- From: Shaun Farrugia [mailto:sfarru...@fry.com] Sent: Wednesday, April 01, 2009 11:00 AM To: Tomcat Users List Subject: RE: Connection Pooling questions Sweet, I set up a test.jsp page that will Connection.close() without rs.close() and ps.close(). Will let you know the results.. And I know you answered this before but I have to ask again. Tomcat is using it's own version of DBCP (based on properties files) Are there any changes to DBCP other than package moves? Can I utilize the DBCP source package and expect that it's the same code tomcat is using? -Original Message- From: Caldarale, Charles R [mailto:chuck.caldar...@unisys.com] Sent: Wednesday, April 01, 2009 10:47 AM To: Tomcat Users List Subject: RE: Connection Pooling questions From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions What happens when the statement isn't closed but the connection is returned to the pool? The connection object your code sees isn't the real connection; it's a wrapper created by the pool to control access to the real thing. Likewise, the ResultSet, Statement, PreparedStatement, etc., objects visible to the webapp are also wrappers around the real objects. Using these, the pool knows if the real connection has been cleaned up properly; the pool won't reuse the connection until it is has returned to an idle state. I have some code blocks that don't have a finally block to close the result set and prepared statement. If an exception happens the statement and the result set stay open but the connection gets returned to the pool. A brief scan of the code in commons-dbcp indicates that when a Connection.close() is done, an attempt is made to close the associated Statement and ResultSet objects. Some experimentation (which I don't have time for right now) would make me more comfortable with that observation. - 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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Connection Pooling questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Chuck, On 4/1/2009 10:47 AM, Caldarale, Charles R wrote: From: Shaun Farrugia [mailto:sfarru...@fry.com] Subject: RE: Connection Pooling questions What happens when the statement isn't closed but the connection is returned to the pool? The connection object your code sees isn't the real connection; it's a wrapper created by the pool to control access to the real thing. Likewise, the ResultSet, Statement, PreparedStatement, etc., objects visible to the webapp are also wrappers around the real objects. Using these, the pool knows if the real connection has been cleaned up properly; the pool won't reuse the connection until it is has returned to an idle state. That might violate the JDBC spec. Calling Connection.close should close all statements that were created from that connection (JDBC4.0:section 9.4.4). Likewise, closing a statement closes any associated ResultSet objects (section 13.1.4). The spec also allows for statement pooling across connection uses (so the statement survives a connection recycle) but that is required to be hidden from the application and the application should feel like the statement is /not/ pooled. My interpretation of this is that Connection.close in a pooled environment should close the result sets and statements, at least in such a way that the application can't tell the difference. If the statements are intended to be pooled, then they can survive, but the result sets should go bye-bye. I have some code blocks that don't have a finally block to close the result set and prepared statement. If an exception happens the statement and the result set stay open but the connection gets returned to the pool. A brief scan of the code in commons-dbcp indicates that when a Connection.close() is done, an attempt is made to close the associated Statement and ResultSet objects. Some experimentation (which I don't have time for right now) would make me more comfortable with that observation. I think the JDBC spec should strengthen your faith at least in the correctness of this behavior... whether it is completely implemented or the implementation meets your (or my) interpretation is another matter. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAknT1dIACgkQ9CaO5/Lv0PCd1gCcD747GJijxm/aM8foUY6wSXeo cGUAniZUoLg7j7+rnIf0RtGPZK0VcDuJ =c+bE -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Connection Pooling questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David, On 4/1/2009 11:25 AM, David Smith wrote: This is assuming the developer didn't unwrap any of the DBCP objects to access driver specific features. In that case, the original wrapped objects need to be maintained and closed instead of the unwrapped objects. I suppose that depends on what exactly you did. If all you did was unwrap a Statement object to do some BLOB operations or something, I would expect that you can safely ignore the fact that you unwrapped it. Actually, I can't think of a reason why you'd have to use the unwrapped version of the statement or result set (or even connection) to perform state management (i.e. calling close()). In fact, I would think taking such action would confuse the hell out of the connection pool and break lots of things. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAknT1qAACgkQ9CaO5/Lv0PDlagCgjNWwDR8aHoJbPBzoEN5sm5pB +iAAn2HiHInywSn60x/u1h+s+OLPoucc =k0BQ -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
From: Christopher Schultz [mailto:ch...@christopherschultz.net] Subject: Re: Connection Pooling questions I think the JDBC spec should strengthen your faith at least in the correctness of this behavior... I wasn't questioning the correctness of the intended behavior, and certainly the spec requires it, but rather any side effects in a pooled environment (e.g., exceptions thrown when perceived to be abandoned). After this many years, I'd be quite surprised if commons-dbcp wasn't following the spec. - 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.
RE: Connection Pooling questions
From: Christopher Schultz [mailto:ch...@christopherschultz.net] Subject: Re: Connection Pooling questions Actually, I can't think of a reason why you'd have to use the unwrapped version of the statement or result set (or even connection) to perform state management (i.e. calling close()). Not for state management, but some drivers (Oracle, as usual) have operations that aren't supported by the wrapper. The risk is the webapp code inadvertently using the real connection object rather than the wrapper when calling close(); might be some time before that were discovered, especially if all the defensive mechanisms available in commons-dbcp are employed. - 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.
RE: Connection Pooling questions
the configuration you describe only defines parameters to be passed to the Connection Pool Library..which connection pool library are you using? are you using Thread starve algorithm? or explicit no-activity-on-connection algorithm? Most connection pools with close the connection only after statement handles are quiesced (explicit close) or starved for activity ? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: allen.ir...@smartintegration.com.au To: users@tomcat.apache.org Date: Tue, 31 Mar 2009 21:42:52 -0400 Subject: Connection Pooling questions Hello, I searched on the mailing list back to 2007 and didn't see the answers I needed for these DataSource related questions (though I did see similar, not quite what I needed though). I'm using Tomcat 5.5, Java 1.5 on Windows XP and have used the excellent guide: http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html for configuring the default data source within Tomcat I configure it using the suggested /META-INF/context.xml with: maxActive=30 maxIdle=10 I assumed that maxActive would limit the total amount of DB connections that could ever be open at one time (in this case 30). Yet from what I see it seems like I am limited to maxActive + maxIdle connections open (in this case 40). Is that true? Also, the default behavior seems to be that the connections never close within the pool when they are not being used. I had my Tomcat server sit there for about an hour idle with no client requests incoming and when I ran netstat -a I still saw 39 connections open, they only closed when I killed Tomcat. Do I have to turn on some kind of cleanup explicitly? I do close the connections within my program using conn.close(); as the howto suggests. I thought that when the connections were not used that they would over time be really closed and I would end up with the maxIdle value of 10 open connections within my Pool. thanks! mail2web.com – Enhanced email for the mobile individual based on Microsoft® Exchange - http://link.mail2web.com/Personal/EnhancedEmail - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org _ Internet Explorer 8 – Get your Hotmail Accelerated. Download free! http://clk.atdmt.com/MRT/go/141323790/direct/01/
RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au [mailto:allen.ir...@smartintegration.com.au] Subject: Connection Pooling questions I configure it using the suggested /META-INF/context.xml with: maxActive=30 maxIdle=10 Post your entire context.xml so we can see the rest of the Resource element. Your settings may conflict with what the database expects, resulting in orphaned connections. I assumed that maxActive would limit the total amount of DB connections that could ever be open at one time (in this case 30). That limits the number the pool is managing. If the pool considers some of them to be abandoned or in error, the number the DB knows about may be higher. Yet from what I see it seems like I am limited to maxActive + maxIdle connections open (in this case 40). Is that true? Don't think so. Also, the default behavior seems to be that the connections never close within the pool when they are not being used. That is the whole point of having a pool. I do close the connections within my program using conn.close(); The evidence suggests otherwise. You also need to close result sets and statements associated with the connection, and all of the close() calls should be in a finally block to insure they always get executed. I thought that when the connections were not used that they would over time be really closed and I would end up with the maxIdle value of 10 open connections within my Pool. That usually depends entirely on how your DB is configured; if it has a high timeout value (most do), the connections will persist for a long time. You can configure a timeout value for the pool to evict idle connections, but that's disabled by default. - 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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Thanks for your reply Martin. Your response kind of lost me though. I used the howto guide at http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html which mentions the default Data Source (dbcp I believe). But mentioned nothing about the algorithms you said. Original Message: - From: Martin Gainty mgai...@hotmail.com Date: Tue, 31 Mar 2009 22:33:36 -0400 To: users@tomcat.apache.org Subject: RE: Connection Pooling questions the configuration you describe only defines parameters to be passed to the Connection Pool Library..which connection pool library are you using? are you using Thread starve algorithm? or explicit no-activity-on-connection algorithm? Most connection pools with close the connection only after statement handles are quiesced (explicit close) or starved for activity ? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: allen.ir...@smartintegration.com.au To: users@tomcat.apache.org Date: Tue, 31 Mar 2009 21:42:52 -0400 Subject: Connection Pooling questions Hello, I searched on the mailing list back to 2007 and didn't see the answers I needed for these DataSource related questions (though I did see similar, not quite what I needed though). I'm using Tomcat 5.5, Java 1.5 on Windows XP and have used the excellent guide: http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html for configuring the default data source within Tomcat I configure it using the suggested /META-INF/context.xml with: maxActive=30 maxIdle=10 I assumed that maxActive would limit the total amount of DB connections that could ever be open at one time (in this case 30). Yet from what I see it seems like I am limited to maxActive + maxIdle connections open (in this case 40). Is that true? Also, the default behavior seems to be that the connections never close within the pool when they are not being used. I had my Tomcat server sit there for about an hour idle with no client requests incoming and when I ran netstat -a I still saw 39 connections open, they only closed when I killed Tomcat. Do I have to turn on some kind of cleanup explicitly? I do close the connections within my program using conn.close(); as the howto suggests. I thought that when the connections were not used that they would over time be really closed and I would end up with the maxIdle value of 10 open connections within my Pool. thanks! mail2web.com Enhanced email for the mobile individual based on Microsoft® Exchange - http://link.mail2web.com/Personal/EnhancedEmail - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org _ Internet Explorer 8 Get your Hotmail Accelerated. Download free! http://clk.atdmt.com/MRT/go/141323790/direct/01/ mail2web.com - Microsoft® Exchange solutions from a leading provider - http://link.mail2web.com/Business/Exchange - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Thank you very much for your reply. my response to your responses follow:
Post your entire context.xml so we can see the rest of the Resource
element. Your
settings may conflict with what the database expects, resulting in
orphaned connections.
Here it is:
Context
Resource name=jdbc/AttunitySProcDS auth=Container
type=javax.sql.DataSource
driverClassName=com.attunity.jdbc.NvDriver
url=jdbc:attconnect://10.20.5.107:/baynav;DefTdpName=webdemo
maxActive=50 maxIdle=10/
/Context
Also, the default behavior seems to be that the connections
never close within the pool when they are not being used.
That is the whole point of having a pool.
so you're saying that the pool will never close any of the connections due
to them not being used?
The evidence suggests otherwise. You also need to close result sets and
statements
associated with the connection, and all of the close() calls should be in
a finally
block to insure they always get executed.
Here's the block I use:
finally
{
// cleanup
if (cs != null) cs.close();
if (conn != null) conn.close();
}
Where cs = the statement. I don't close the result set as closing the
statement is supposed to do that.
That usually depends entirely on how your DB is configured; if it has a
high timeout
value (most do), the connections will persist for a long time. You can
configure a
timeout value for the pool to evict idle connections, but that's disabled
by default.
Maybe this timeout value is what I'm looking for?
Original Message:
-
From: Caldarale, Charles R chuck.caldar...@unisys.com
Date: Tue, 31 Mar 2009 21:57:01 -0500
To: users@tomcat.apache.org
Subject: RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Subject: Connection Pooling questions
I configure it using the suggested /META-INF/context.xml with:
maxActive=30 maxIdle=10
Post your entire context.xml so we can see the rest of the Resource
element. Your settings may conflict with what the database expects,
resulting in orphaned connections.
I assumed that maxActive would limit the total amount of DB
connections that could ever be open at one time (in this case 30).
That limits the number the pool is managing. If the pool considers some of
them to be abandoned or in error, the number the DB knows about may be
higher.
Yet from what I see it seems like I am limited to maxActive + maxIdle
connections open (in this case 40). Is that true?
Don't think so.
Also, the default behavior seems to be that the connections
never close within the pool when they are not being used.
That is the whole point of having a pool.
I do close the connections within my program using conn.close();
The evidence suggests otherwise. You also need to close result sets and
statements associated with the connection, and all of the close() calls
should be in a finally block to insure they always get executed.
I thought that when the connections were not used that they
would over time be really closed and I would end up with
the maxIdle value of 10 open connections within my Pool.
That usually depends entirely on how your DB is configured; if it has a
high timeout value (most do), the connections will persist for a long time.
You can configure a timeout value for the pool to evict idle connections,
but that's disabled by default.
- 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
mail2web.com What can On Demand Business Solutions do for you?
http://link.mail2web.com/Business/SharePoint
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Subject: RE: Connection Pooling questions
Resource name=jdbc/AttunitySProcDS auth=Container
type=javax.sql.DataSource
driverClassName=com.attunity.jdbc.NvDriver
url=jdbc:attconnect://10.20.5.107:/baynav;DefTdpName=webdemo
maxActive=50 maxIdle=10/
You said maxActive was 30, but it's 50 in your config. I've never used the
Attunity drivers, but the above looks ok as far as I can tell; perhaps someone
else might see a problem. You might want to consider adding these:
removeAbandoned=true
removeAbandonedTimeout=60
logAbandoned=true
That will tell you if the pool thinks you're returning connections or not.
Change the timeout setting (in seconds) to whatever you think is appropriate.
so you're saying that the pool will never close any of the connections
due to them not being used?
Correct; that's normally left up to the database.
Here's the block I use:
finally
{
// cleanup
if (cs != null) cs.close();
if (conn != null) conn.close();
}
Where cs = the statement.
If the cs.close() throws an exception, you won't close the connection. Do you
only ever have one statement per connection? And depending on the location of
the finally block relative to the rest of the code, you might not even go
through there.
I don't close the result set as closing the
statement is supposed to do that.
Regardless, it's good practice to clean up DB resources explicitly, preferably
as soon as you're done with them.
Maybe this timeout value is what I'm looking for?
Depends on what you want; for a production environment, you never want the
connections to close. They're cheap to keep around but expensive to create.
- 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Connection Pooling questions
Sorry, I included an older context.xml than the one I've been testing with.
That one has 30 for maxActive (everything else is the same).
Thank you very much for your replies Chuck, I think you gave me plenty to
think about and play with to resolve my problem. Thanks again!
Original Message:
-
From: Caldarale, Charles R chuck.caldar...@unisys.com
Date: Tue, 31 Mar 2009 22:44:32 -0500
To: users@tomcat.apache.org
Subject: RE: Connection Pooling questions
From: allen.ir...@smartintegration.com.au
[mailto:allen.ir...@smartintegration.com.au]
Subject: RE: Connection Pooling questions
Resource name=jdbc/AttunitySProcDS auth=Container
type=javax.sql.DataSource
driverClassName=com.attunity.jdbc.NvDriver
url=jdbc:attconnect://10.20.5.107:/baynav;DefTdpName=webdemo
maxActive=50 maxIdle=10/
You said maxActive was 30, but it's 50 in your config. I've never used the
Attunity drivers, but the above looks ok as far as I can tell; perhaps
someone else might see a problem. You might want to consider adding these:
removeAbandoned=true
removeAbandonedTimeout=60
logAbandoned=true
That will tell you if the pool thinks you're returning connections or not.
Change the timeout setting (in seconds) to whatever you think is
appropriate.
so you're saying that the pool will never close any of the connections
due to them not being used?
Correct; that's normally left up to the database.
Here's the block I use:
finally
{
// cleanup
if (cs != null) cs.close();
if (conn != null) conn.close();
}
Where cs = the statement.
If the cs.close() throws an exception, you won't close the connection. Do
you only ever have one statement per connection? And depending on the
location of the finally block relative to the rest of the code, you might
not even go through there.
I don't close the result set as closing the
statement is supposed to do that.
Regardless, it's good practice to clean up DB resources explicitly,
preferably as soon as you're done with them.
Maybe this timeout value is what I'm looking for?
Depends on what you want; for a production environment, you never want the
connections to close. They're cheap to keep around but expensive to create.
- 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
mail2web.com What can On Demand Business Solutions do for you?
http://link.mail2web.com/Business/SharePoint
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
