You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by JWM <te...@malcolms.com> on 2005/10/06 22:09:17 UTC
Tracking Datasource Connection Usage?
I started getting exceptions saying no connections were available on my JDBC
datasource (org.gjt.mm.mysql.Driver). The pool was definitely large enough
to handle the load. So it appears that I'm not freeing the all the
connections as I should. I noticed that I did not have the
'removeAbandoned' flag set on the Resource tag. Changing that has
apparently fixed the out of connections problem. But I really want to clean
up the code and fix it the right way. I've got the close() statements in
place. But obviously, I'm missing some of them somewhere. Is there any
process for logging/tracking allocating and freeing connections (and absence
thereof.), available connections, etc? Or are there any methods I can call
to give me this type of debug info? What's the recommended way to debug
this?
Thanks.
JWM
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Tracking Datasource Connection Usage?
Posted by Kyle <kl...@attitia.com>.
Brian Cook wrote:
> JWM wrote:
>
> Two things you can do here.
>
> 1. Set both "removeAbandoned" and "logAbandoned" parameters to
> true. This will reclaim most lost connections. And log a trace of
> what code called a connection that was never closed.
>
See, I don't get all this "removeAbandoned" discussion.
I seem to recall reading somewhere that it doesn't actually do anything
and the commons api docs seem to indicate the getter/setter's for
removeAbandoned are deprecated. As is apparently logAbandoned.
K
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
RE: Tracking Datasource Connection Usage?
Posted by JWM <te...@malcolms.com>.
Brian and others,
Good information. Thanks. What happens with result sets if they don't get
closed? Do they live on forever as memory leaks, or do they get cleaned up
by GC when the object referencing them goes out of scope? If a result set
is left open, does it prevent the connection from getting successfully
closed?
Thx.
JWM
-----Original Message-----
From: Brian Cook [mailto:bcook@printtime.com]
Sent: Friday, October 07, 2005 3:38 PM
To: Tomcat Users List
Subject: Re: Tracking Datasource Connection Usage?
JWM wrote:
Two things you can do here.
1. Set both "removeAbandoned" and "logAbandoned" parameters to
true. This will reclaim most lost connections. And log a trace of what
code called a connection that was never closed.
2. Use a finally block to close all of your Resultsets and
connections. This will insure they are closed even if your code throws
an exception.
Example :
ResultSet rs = null;
Connection con = null;
try {
........
........
........
........
}
catch(Exception e) {
........
}
finally {
rs.close();
con.close();
}
>I started getting exceptions saying no connections were available on my
JDBC
>datasource (org.gjt.mm.mysql.Driver). The pool was definitely large
enough
>to handle the load. So it appears that I'm not freeing the all the
>connections as I should. I noticed that I did not have the
>'removeAbandoned' flag set on the Resource tag. Changing that has
>apparently fixed the out of connections problem. But I really want to
clean
>up the code and fix it the right way. I've got the close() statements in
>place. But obviously, I'm missing some of them somewhere. Is there any
>process for logging/tracking allocating and freeing connections (and
absence
>thereof.), available connections, etc? Or are there any methods I can call
>to give me this type of debug info? What's the recommended way to debug
>this?
>
>Thanks.
>
>JWM
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
>
--
Brian Cook
Digital Services Analyst
Print Time Inc.
bcook@printtime.com
913.345.8900
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Tracking Datasource Connection Usage?
Posted by Brian Cook <bc...@printtime.com>.
JWM wrote:
Two things you can do here.
1. Set both "removeAbandoned" and "logAbandoned" parameters to
true. This will reclaim most lost connections. And log a trace of what
code called a connection that was never closed.
2. Use a finally block to close all of your Resultsets and
connections. This will insure they are closed even if your code throws
an exception.
Example :
ResultSet rs = null;
Connection con = null;
try {
........
........
........
........
}
catch(Exception e) {
........
}
finally {
rs.close();
con.close();
}
>I started getting exceptions saying no connections were available on my JDBC
>datasource (org.gjt.mm.mysql.Driver). The pool was definitely large enough
>to handle the load. So it appears that I'm not freeing the all the
>connections as I should. I noticed that I did not have the
>'removeAbandoned' flag set on the Resource tag. Changing that has
>apparently fixed the out of connections problem. But I really want to clean
>up the code and fix it the right way. I've got the close() statements in
>place. But obviously, I'm missing some of them somewhere. Is there any
>process for logging/tracking allocating and freeing connections (and absence
>thereof.), available connections, etc? Or are there any methods I can call
>to give me this type of debug info? What's the recommended way to debug
>this?
>
>Thanks.
>
>JWM
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
>
--
Brian Cook
Digital Services Analyst
Print Time Inc.
bcook@printtime.com
913.345.8900
RE: Tracking Datasource Connection Usage?
Posted by George Sexton <gs...@mhsoftware.com>.
http://findbugs.sourceforge.net/
Will tell you where in your code you are not freeing resources.
George Sexton
MH Software, Inc.
http://www.mhsoftware.com/
Voice: 303 438 9585
> -----Original Message-----
> From: JWM [mailto:techstuff@malcolms.com]
> Sent: Thursday, October 06, 2005 2:09 PM
> To: tomcat-user@jakarta.apache.org
> Subject: Tracking Datasource Connection Usage?
>
> I started getting exceptions saying no connections were
> available on my JDBC
> datasource (org.gjt.mm.mysql.Driver). The pool was
> definitely large enough
> to handle the load. So it appears that I'm not freeing the all the
> connections as I should. I noticed that I did not have the
> 'removeAbandoned' flag set on the Resource tag. Changing that has
> apparently fixed the out of connections problem. But I
> really want to clean
> up the code and fix it the right way. I've got the close()
> statements in
> place. But obviously, I'm missing some of them somewhere.
> Is there any
> process for logging/tracking allocating and freeing
> connections (and absence
> thereof.), available connections, etc? Or are there any
> methods I can call
> to give me this type of debug info? What's the recommended
> way to debug
> this?
>
> Thanks.
>
> JWM
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Tracking Datasource Connection Usage?
Posted by "Darryl L. Miles" <da...@netbauds.net>.
Rob Hills wrote:
>
>>I started getting exceptions saying no connections were available on my JDBC
>>datasource (org.gjt.mm.mysql.Driver). The pool was definitely large enough
>>to handle the load. So it appears that I'm not freeing the all the
>>connections as I should. I noticed that I did not have the
>>'removeAbandoned' flag set on the Resource tag. Changing that has
>>apparently fixed the out of connections problem. But I really want to clean
>>up the code and fix it the right way. I've got the close() statements in
>>place. But obviously, I'm missing some of them somewhere. Is there any
>>process for logging/tracking allocating and freeing connections (and absence
>>thereof.), available connections, etc? Or are there any methods I can call
>>to give me this type of debug info? What's the recommended way to debug
>>this?
>>
>>
>
>There have already been useful tips on debugging, but I've found that
>the most common way to chew up connections is to not put the code
>that frees the conneciton inside a finally {} block. If you don't free the
>connection in a finally {} block, it may not be freed if an exception
>occurs.
>
>
>
My understanding of finally is that it's ALWAYS executed, (even if
something in any block (try or catch) does a return statement). So I
would say "finally" is good practice to close both Statement handles and
Database Connection handles. My first mistakes were in not closing
statement handles this leaves resources open on handles that affect how
the database connection can be reclaimed. I was used to closing the
database connection and expecting the open statement or resultset to be
mopped up right away. But if you think about it for a moment these
resources might also be open on the SQL server through the connection
and the GC takes a while to reclaim.
try {
// code 1
} catch(FooException e) {
// code 2
} finally {
// code 3
}
// code 4
No exception execution is: code1 + code3 + code4 + end_of_func
Caught FooException: code1 + code2 + code3 + return
Uncaught Exception: code1 + code3 (thrown to callee)
This is confirmed in my Just Java 2 (6th Edn) page 215.
On another matter I am using Apache DBCP and am finding that it does not
seem to use the pooled connections, it always created a new connection
to the MySQL server, even if there are a number of already existing free
connection. I am using a JNDI binding so the auth credentials should be
working. I am still in development with the application.
<Resource name="jdbc/foobar" auth="Container" scope="Shareable"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="jdbc:mysql://mysql.domain.com:3306/databasename"
driverClassName="com.mysql.jdbc.Driver" username="sqlusername"
password="sqlpassword" maxWait="3000" maxIdle="2" maxActive="90"
removeAbandonedTimeout="5" removeAbandoned="true" />
I too had to add extra flags in to get a stage connection pooler but
believe there is something broken in DBCP that stops is re-using
connections like it should. This has simply covered over the problem.
Maybe we can get together and compare notes, any ideas how to enable
verbose logging of DBCP so I can confirm the application is doing what
it should ?
I am using Hibernate3 have confirmed everytime that my servlet filter is
closing the session and thus returning the connection back to DBCP like
it should, the connection from MySQL "SHOW PROCESSLIST" are still
connected and thus in the pool. But the next servlet request always
opens a new connection, when I dont think it should, it should use the
one already in the pool.
Darryl
--
Darryl L. Miles
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Tracking Datasource Connection Usage?
Posted by Rob Hills <ro...@netpaver.com.au>.
Hi,
On 6 Oct 2005 at 15:09, JWM wrote:
> I started getting exceptions saying no connections were available on my JDBC
> datasource (org.gjt.mm.mysql.Driver). The pool was definitely large enough
> to handle the load. So it appears that I'm not freeing the all the
> connections as I should. I noticed that I did not have the
> 'removeAbandoned' flag set on the Resource tag. Changing that has
> apparently fixed the out of connections problem. But I really want to clean
> up the code and fix it the right way. I've got the close() statements in
> place. But obviously, I'm missing some of them somewhere. Is there any
> process for logging/tracking allocating and freeing connections (and absence
> thereof.), available connections, etc? Or are there any methods I can call
> to give me this type of debug info? What's the recommended way to debug
> this?
There have already been useful tips on debugging, but I've found that
the most common way to chew up connections is to not put the code
that frees the conneciton inside a finally {} block. If you don't free the
connection in a finally {} block, it may not be freed if an exception
occurs.
HTH,
Rob Hills
Western Australia
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org