Re: dbcp pool freezing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Eric, Eric B. wrote: I've been trying to figure out how to create a Wrapper of SqlConnection, but am having trouble getting this to work. Would you mind giving me a couple of pointers where to start? See the thread from last week with the subject logging outgoing requests from Tomcat to SQL. I outline how to wrap JDBC Connection, Statement, and ResultSet objects. I also provide some code to auto-generate the wrappers for you. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmJ3dsACgkQ9CaO5/Lv0PC2DgCgwRfUcn9VVK2cxP0VivE9zclZ JnQAoLJNV79KRGKJsrAnaDO79tJRL6XY =qSct -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: dbcp pool freezing
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Eric,
Eric B. wrote:
I'm happy to test out an validate that theory, but based on what I've seen
so far, this isn't the case at all.
Try deploying nothing in your application but the following JSP. Browse
to it, and intentionally leak a connection. Watch your catalina.out file
to see if an error message occurs ~20 seconds later. If you see such
messages, Chuck's suggestion that you have some runaway looping code are
probably something to look into.
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Strict//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd;
!--
AbandonConnection.jsp
Checks-out a JDBC connection and intentionally leaks it, for testing
of JDBC connection pool abandonment configuration.
Copyright 2004 Chris Schultz - www.christopherschultz.net
Do whatever you want with this file.
Enjoy!
- --
%...@page language=Java
isErrorPage=false
import=
java.sql.*,
java.util.*,
java.io.PrintWriter,
javax.naming.Context,
javax.naming.InitialContext,
javax.naming.NamingException,
javax.sql.DataSource
%
%!
/**
* Gets a JDBC connection. This implementation uses JNDI to obtain a
* connection. Feel free to substitute your own.
*/
Connection getConnection()
throws SQLException, javax.naming.NamingException
{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(/* JNDI name */);
if(null == ds)
throw new NamingException(Cannot obtain DataSource);
return ds.getConnection();
}
%
%
Connection conn = getConnection();
%
html
body
pGot connection: %= conn %/p
pNow, I refuse to give it away!/p
/body
/html
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkmJ32sACgkQ9CaO5/Lv0PCaiACgs7NLWRf48A79e8s4J+AMGOmC
jioAn2st634zS4MbLAVQ/gmLNkCqA5nx
=aLa5
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: dbcp pool freezing
Christopher Schultz ch...@christopherschultz.net wrote in message news:4989df6b.3030...@christopherschultz.net... -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Eric, Eric B. wrote: I'm happy to test out an validate that theory, but based on what I've seen so far, this isn't the case at all. Try deploying nothing in your application but the following JSP. Browse to it, and intentionally leak a connection. Watch your catalina.out file to see if an error message occurs ~20 seconds later. If you see such messages, Chuck's suggestion that you have some runaway looping code are probably something to look into. Thanks for the pointers. After a bit more troubleshooting, I finally got the abandoned connection tracking to work, and indeed managed to find a connection leak in a very obscure place (not in a loop). However, during this process I noticed the following: The abandoned connection tracking only seems to fire when requesting a new connection. It is not a seperate background thread that cleans up abandoned connections. Consequently, if all connections are being legitimately used when a new one is requested, the new one will go into a wait state. Once a legit connection is closed, the thread waiting for a connection will be able to continue processing. However, if all the connection pool is maxed out with leak'ed connections that have not exceeded the abandonedTimeout, the thread requesting a new connection will go into a wait state and wait perpetually until a second new thread tries to access a connection and triggers the abandoned connection cleanup. I realize that the abandoned connection tracking is not something that a good app should rely upon, however, it is definitely a useful feature to have to ensure that your app doesn't crap out. However, it is a little misleading in the belief that abandoned connections will automatically be returned to the pool automatically rather than only be returned once a new connection is requested. Has there ever been any thought to doing abandoned connection tracking as a background thread, or was the current implementation decided upon to save on resources? Thanks! Eric - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: dbcp pool freezing
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B. Subject: Re: dbcp pool freezing Has there ever been any thought to doing abandoned connection tracking as a background thread, or was the current implementation decided upon to save on resources? Don't know the history of commons-dbcp, but Filip H has a replacement connection pooling mechanism that should be available Real Soon Now in Tomcat. http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/ Look at the doc directory for information about it. There are at least a couple of people on the list (not me, yet) who have tried it with good results, but it is brand new. - 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: dbcp pool freezing
Hi, The first thing to be checked is probably your application. If some method does not properly close an SQL connection, you will have a leak that will ultimately lead to this resource exhaustion. To better trace if this is a case, you may for example create a Wrapper of SqlConnection that counts connection creation/close. Best regards /Philippe Eric B. a écrit : Hi, I'm having some trouble with my dbcp pool freezing up when running a load, and am having trouble figuring out why. I have enabled JMX on the server and connected via jConsole, and see that all my threads are in the WAITING state from org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:810) I don't believe I have any connection leaks in my code (I explicity close all my connections in a try/catch/finally), and yet, once all my connections are used up, all processes hang. I have tried enabling removeAbandoned connections, but it doesn't seem to do anything either. If I look at the numActive threads in jConsole, it is always stuck at maxThreads (in my case, 8). Increasing the number of maxThreads will unblock the problem, but it doesn't resolve the underlying question as to why this happens in the first place. I would expect that if some threads are busy doing nothing, that they would be cleaned up by the removeAbandoned option, and released to the next available thread. Resource name=jdbc/db auth=Container type=javax.sql.DataSource username=db password=db driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/db?autoReconnect=true maxActive=8 maxIdle=4 removeAbandoned=true removeAbandonedTimeout=20 logAbandoned=true / Example jConsole thread trace: Name: http-8112-12 State: WAITING on org.apache.tomcat.dbcp.dbcp.abandonedobjectp...@1cee361 Total blocked: 634 Total waited: 285 Stack trace: java.lang.Object.wait(Native Method) java.lang.Object.wait(Object.java:474) org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:810) org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:84) org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96) org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) ... All the threads just wait in this state perpetually. I was hoping to go through the source code for the pooling, but I can't seem to find the srcs for tomcat-dbcp.jar anywhere. I realize that they are a subset of the commons-dbcp library, but was wondering if the actual sources that make up tomcat-dbcp exist anywhere. I can't find them anywhere on the tomcat site. Any ideas, suggestions or pointers would be greatly appreciated. Thanks! Eric - 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: dbcp pool freezing
Philippe Martinou philippe.marti...@sparkom.com wrote in message news:49887d2f.3000...@sparkom.com... Hi, The first thing to be checked is probably your application. If some method does not properly close an SQL connection, you will have a leak that will ultimately lead to this resource exhaustion. To better trace if this is a case, you may for example create a Wrapper of SqlConnection that counts connection creation/close. Thanks for the idea, but isn't that the whole point of the abandoned connections tracking? ie: removeAbandoned=true, removeAbandonedTimeout=20 and logAbandoned=true. I get no logs indicating thta any connections have been abandoned. Does the abandoned tracking work? Additionally, I see this problem in 2 seperate applications I am working on. One uses explicit calls to sql.connection, and the other one uses Hibernate exclusively to do all the transaction handling. Given that I am seeing the problem on both, it leads me to believe that it isn't actually the case of forgetting to close a connection, rather than a connection not being released properly. Thoughts? Thanks, Eric - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: dbcp pool freezing
Check that you don't have garbage collection problems. A heavy GC load will show up with these symptoms you are seeing (because GC stalls threads as they acquire or release locks). - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: dbcp pool freezing
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B. Subject: dbcp pool freezing and see that all my threads are in the WAITING state from org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject (GenericObjectPool.java:810) I don't believe I have any connection leaks in my code Belief is not a substitute for evidence... Your webapp is clearly not returning connection objects to the pool in a timely fashion. Given the other observations, it may not be a traditional leak, but a logic error in your webapp that is repeatedly obtaining multiple connection objects for one request. Look deeper in the stack trace to see where in *your* code the call originates. Setting the pool size to one often helps in finding such bugs. I would expect that if some threads are busy doing nothing, that they would be cleaned up by the removeAbandoned option, and released to the next available thread. That's not the way it works. Any connection objects not returned to the pool in the specified amount of time are simply discarded and new ones created to take their place. I was hoping to go through the source code for the pooling, It's always somebody else's fault (non-sexist version of a Jimmy Buffett line). The bug is in your webapp. I can't seem to find the srcs for tomcat-dbcp.jar anywhere. The source is created during the Tomcat build process by a script that edits the package names; the code is identical with what's in commons-dbcp otherwise. - 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: dbcp pool freezing
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B. Subject: Re: dbcp pool freezing Then the question becomes why aren't they being returned at all? My timeout is 20secs; I would expect, if the Abandon tracking was working properly for me, that the connections would be returned after 20secs to the pool, and another thread would be able to continue using it. If, as I suspect, you have a loop somewhere in your webapp that keeps requesting connections, each recovered connection would be consumed as soon as it's recovered. The webapp logic would use the connection briefly, then try to go back and grab another one, and wait up to 20 seconds for the next connection recovery. - 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: dbcp pool freezing
Philippe Martinou philippe.marti...@sparkom.com wrote in message news:49887d2f.3000...@sparkom.com... Hi, The first thing to be checked is probably your application. If some method does not properly close an SQL connection, you will have a leak that will ultimately lead to this resource exhaustion. To better trace if this is a case, you may for example create a Wrapper of SqlConnection that counts connection creation/close. I've been trying to figure out how to create a Wrapper of SqlConnection, but am having trouble getting this to work. Would you mind giving me a couple of pointers where to start? I tried to see if I could put my own wrapper to the MySQL driver in my context resource, but it doesn't have a get/close. And I'm not quite sure how to get my own wrapper used instead of the standard sql.connection implementation. Is there a way to indicate that you want a different implementation of sql.connection used? Thanks, Eric - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: dbcp pool freezing
Caldarale, Charles R chuck.caldar...@unisys.com wrote in message news:0aae5ab84b013e45a7b61cb66943c17215b600c...@usea-exch7.na.uis.unisys.com... From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B. Subject: dbcp pool freezing and see that all my threads are in the WAITING state from org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject (GenericObjectPool.java:810) I don't believe I have any connection leaks in my code Belief is not a substitute for evidence... Your webapp is clearly not returning connection objects to the pool in a timely fashion. I am tempted to agree with you; I just am having trouble understanding how or why they aren't being returned properly. I would expect that if some threads are busy doing nothing, that they would be cleaned up by the removeAbandoned option, and released to the next available thread. That's not the way it works. Any connection objects not returned to the pool in the specified amount of time are simply discarded and new ones created to take their place. Even better. Then the question becomes why aren't they being returned at all? My timeout is 20secs; I would expect, if the Abandon tracking was working properly for me, that the connections would be returned after 20secs to the pool, and another thread would be able to continue using it. However, this doesn't seem to happen; no threads seem to be returned to the pool and instead, everything just hangs in the WAITING state. Thanks, Eric - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Re: dbcp pool freezing
In addition to explicitly closing the connection, are you also
explicitly closing out any prepared or callable statements?
I had overlooked this issue one time, and while it wasn't causing a
great performance hindrance per se, I could tell by my monitoring tool
that many rogue connections were still left open, and that can get ugly
after a while, believe me.
-Original Message-
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B.
Sent: Tuesday, February 03, 2009 2:05 PM
To: users@tomcat.apache.org
Subject: Re: dbcp pool freezing
Caldarale, Charles R chuck.caldar...@unisys.com wrote in message
news:0aae5ab84b013e45a7b61cb66943c17215b604a...@usea-exch7.na.uis.unisys
.com...
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B.
Subject: Re: dbcp pool freezing
Then the question becomes why aren't they being returned at all? My
timeout is 20secs; I would expect, if the Abandon tracking was
working properly for me, that the connections would be returned
after 20secs to the pool, and another thread would be able to
continue using it.
If, as I suspect, you have a loop somewhere in your webapp that keeps
requesting connections, each recovered connection would be consumed as
soon as it's recovered. The webapp logic would use the connection
briefly, then try to go back and grab another one, and wait up to 20
seconds for the next connection recovery.
I'm happy to test out an validate that theory, but based on what I've
seen so far, this isn't the case at all. I've been trying to figure out
how to create an sql connection wrapper class to count the number of
times the connection is opened and closed (as suggested by Philippe
earlier in this thread), but am actually having trouble getting it to
work. I can only assume I'm doing it incorrectly.
In my current application, I know that one of my queries throws an SQL
exception. If I comment out the code that generates this query (or fix
the query properly), then this issue disappears. However, having now
found this issue, I am more concerned about finding the root cause of
the problem, as opposed to simply treating the symptom, as I am seeing
the same issue manifest itself in another application that doesn't have
the same SQL exception. And yes, my exception is wrapped in a
try/catch/finally statement, whereby I have the connection.close() in
the finally{} block.
Any ideas how I can further track this down?
Thanks,
Eric
-
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: dbcp pool freezing
Dear Eric, and see that all my threads are in the WAITING state from org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject (GenericObjectPool.java:810) I don't believe I have any connection leaks in my code Belief is not a substitute for evidence... Your webapp is clearly not returning connection objects to the pool in a timely fashion. I am tempted to agree with you; I just am having trouble understanding how or why they aren't being returned properly. I'd suggest you look into hooking your application up to Java- monitor.com. It will pick up on Tomcat-managed data sources automatically, and it can also help you do Hibernate session leak detection (if you use Hibernate). http://java-monitor.com/forum/livedemo.php shows you what it looks like. Just click on another server @ ... to see the database connection pool usage and Hibernate sessions over the past two days (scroll down to Hibernate Sessions). If you prefer a local solution, check out p6spy. It has the capability of giving you a stack trace for each abandoned database connection. Alternately, it can show you the stack trace of each allocated JDBC connection. Of course, such stack traces are relatively expensive to make for each database operation, so you may want to disable that functionality once you solved your issues. :) -- Kees Jan http://java-monitor.com/forum/ kjkos...@kjkoster.org 06-51838192 Human beings make life so interesting. Do you know that in a universe so full of wonders, they have managed to invent boredom. Quite astonishing... -- Terry Pratchett - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: dbcp pool freezing
Caldarale, Charles R chuck.caldar...@unisys.com wrote in message
news:0aae5ab84b013e45a7b61cb66943c17215b604a...@usea-exch7.na.uis.unisys.com...
From: news [mailto:n...@ger.gmane.org] On Behalf Of Eric B.
Subject: Re: dbcp pool freezing
Then the question becomes why aren't they being returned at
all? My timeout is 20secs; I would expect, if the Abandon
tracking was working properly for me, that the connections
would be returned after 20secs to the pool, and another
thread would be able to continue using it.
If, as I suspect, you have a loop somewhere in your webapp that keeps
requesting connections, each recovered connection would
be consumed as soon as it's recovered. The webapp logic would use the
connection briefly, then try to go back and grab another one,
and wait up to 20 seconds for the next connection recovery.
I'm happy to test out an validate that theory, but based on what I've seen
so far, this isn't the case at all. I've been trying to figure out how to
create an sql connection wrapper class to count the number of times the
connection is opened and closed (as suggested by Philippe earlier in this
thread), but am actually having trouble getting it to work. I can only
assume I'm doing it incorrectly.
In my current application, I know that one of my queries throws an SQL
exception. If I comment out the code that generates this query (or fix the
query properly), then this issue disappears. However, having now found this
issue, I am more concerned about finding the root cause of the problem, as
opposed to simply treating the symptom, as I am seeing the same issue
manifest itself in another application that doesn't have the same SQL
exception. And yes, my exception is wrapped in a try/catch/finally
statement, whereby I have the connection.close() in the finally{} block.
Any ideas how I can further track this down?
Thanks,
Eric
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
