tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Darryl L. Miles" <dar...@netbauds.net>
Subject Re: Tracking Datasource Connection Usage?
Date Fri, 07 Oct 2005 01:57:33 GMT
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


Mime
View raw message