Optimal JDBC connection pooling configuration for tomcat server

[jay desi]

I have below mentioned configuration for connection pooling on tomcat server 6.0.20:-

 
	<Context path="/dbcp" docBase="dbcp" debug="5"
	             reloadable="true" crossContext="true">
	             
	             <Resource name="jdbc/TestDB" auth="Container"
	                type="javax.sql.DataSource" maxActive="100"
	                maxIdle="30" maxWait="10000" username="user" removeAbandoned="true"
                	removeAbandonedTimeout="60" password="password" driverClassName="com.mysql.jdbc.Driver"
	                url="jdbc:mysql://server1:3306/db"/>
	             
	</Context>

Issue here is, that as day passes by, same query that takes about 12-14 seconds to execute in morning takes about 50-70 seconds later in the day...after say 2-3 hours..
And performance degrades further on..I am sure, something is wrong in my connection pooling configuration in server.xml file.

I am not using hibernate or other ORM frameworks.

Pl let me know how do I optimize this configuration.

[Jeanne Boyarsky]

Jay,
Are you sure you are closing the connection always and in a finally block? This sounds like a connection leak.

[Eduardo Bueno]

You have crossContext declared as true; are you really using it?

There is nothing wrong in your context. Your problem is not in it.

[jay desi]

I use below procedure for closing connections:-

I have a separate class ConnectionUtils that deals with connections, which contains different methods like

public static boolean closeAll(Connection con,Statament s,ResultSet rs){
try{
            ConnectionUtils.closeResultset(rs);
            .....
            ConnectionUtils.closeStatement(s);
            .....
            ConnectionUtils.closeConnection(con);
            .....
            return true;
}catch(Exception e){
...
return false;
}finally{
rs=null;
s=null;
con=null;
}
}

public static boolean closeConnection(Connection con){
try{
....
con.close();
return true;
}catch(Exception e){
....
return false;
}
}

public static boolean closeResultset(ResultSet rs){
//try catch same as above
}

public static boolean cloaseStatement(Statement s){
//try-catch same as above
}

Then in my other classes I have methods in following manner:-

 
public void processResult{
...
...
Connection con=null;
ResultSet rs=null;
Statement s=null;
try{
...
..
stat=ConnectionUtils.closeAll(con,rs.getStatement(),rs)

}catch(Exception e){
...
...
}
}

So, I do not essentially close connections in finally block, but in try block and set references to null in finally block.
Is this not a good approach?

[jay desi]

jeanne/Eduardo .. is the above mentioned way of closing connections appropriate way?

[jay desi]

I logged in messages like :-

......
rs.close();
logger.info("rs closed");.....
.....
s.close();
logger.info("s cloased").....
.....
con.close();
logger.info("con closed")....

I added these messages in respective separate "close" methods..And found that
for each query that is executed, these messages were written to log file...So, I guess
connections leak is not taking place...

Is there any other way where we can find if any connections/statements/resultsets are still
open?