Restarting tomcat clers this up.That's good! :)
And bad. Every time I restart, Tomcat loses the state information for established login sessions. Customer don't like that.
That (with a high probability) is because some objects they store in sessions are not Serializable. IOW, they violate the Servlet Specification.
I think there is a problem with some jsp's opening connections and thenIt's tough to make sure that database connections (and statements, and
not closig them but the developers claim (surprise) their code is clean.
result sets) get cleaned up in JSPs, unless you have a talented JSP
author. (Most JSP authors aren't that talented, unless they are also
good Java developers, in which case they would have implemented the DB
access in a servlet and just used the JSP for display. Anywho...)
I know they use ODBC for the database connections and there is a pool manager in there somewhere. there is a jar file shared by all the jsp's that handles the connection pooling and a bunch of other stuff. The pool manager is PoolmanBean.class. I don't know enough about Java to say if that is a standard library or not.
I guess I don't know a lot about this case but I'm learning more :-)
Anyway, right after startup there are 10 connections. If I open the main page and login, opens another connection closes. Logging out adds another connection. Both of these close but, apparently, none of the tne original connectiions are not being used and, as time goes on, more connections get added to this anomolous pool.
I see someone just uploaded a new version of the jar file with the connection code in it so teh above may not be accurate....
Most developers think their code is beautiful, and so on (and I am a developer)... unless you prove they are wrong. One point on your side is that they probably *are* wrong.
If the number of connections keeps going up and never tapers off or
stops altogether, then something is misconfigured with your connection
pools. Even if the engineers say that the pages are clean, you should
protect the app server (and the DB server) from being swamped by capping
the number of DB connections allowed. Ever. Any decent DB connection
pool lets you specify this kind of thing. You should set that value to
something reasonable. You can get away with a suprisingly low number of
these.
Tried that. Capped it at 35 and the webserver stopped servicing any DB request as soon as the pool reached 35. This is why I believe the pool management is faulty and/or something is hogging all the connections.
I share your belief. Let's try to prove it. Raise it to some other figure, and see if the same happens again. Ask them how big should the figure be.
(I was consulting on a big project that was a somewhat DB intensive,
web-based app. They had the app server configured to accept 75
simultaneous connections. They also set the db connection pool size to
75. I asked why and they basically said "so that every HTTP connection
can get a db connection". Duh. I talked to management and make them put
in debugging information to find out how many connections were ever in
use simultaneously. Seven. (Suckers). They also didn't realize that
Oracle takes like 10MB per connection on the backend, and they had six
physical app servers running two separate copies of the application.
That's 75 * 6 * 2 * 10MB = 900MB. Good thing the DB server had 3.5GB of
RAM, but still...)
Oracle 9i takes 16M per connections. So Oracle claims. I've tested it as high as 20M. I generally use 18M as a guideline
I've heard (not a DBA, though) that Oracle 9i has a mode where it does not spawn a process per connection, but uses threads instead (?) and in that mode it uses far less resources. This way, we have some modest Oracle servers hjandling up to 300 simultaneous (mostly idle) connections.
Cap that connection pool
size at something reasonable, like ten connections. After that, the
application starves. That's good for the app server and the database,
while bad for your application. You can use Jakarta Commons' DBCP as
your connections pool. It has some wonderful debug options, like giving
you a stack trace for the code that obtained the connection if that
connection isn't returned within a certain amount of time. That can save
days or weeks of code reviews. If your connections are in TIME_WAIT, see
how long they stay that way. Waiting 5-10 minutes for a connection like
that to get cleaned up is not unheard of. If they're piling up on top of
one anothor and /never/ going away, it's time to talk to a system
administrator. If the syadmin is you, it's time to talk to the guy you
go to when you don't know things. Everyone needs a guy (or girl!) like
that. :)
I'll mention DBCP and see what happens
DBCP has a nice "removeAbandoned" feature.
Otherwise, you can use this code (tweak it to your needs) to track where connections are opened and closed from:
(code not tested at all)
// open method signature
// code that opens the connection (and stores it in "conn" variable)
try {
throw new Exception("Pool Debugger says: Connection <" + conn + "> opened:");
} catch (Exception e) {
e.printStackTrace();
}
// return conn or whatever you need to do with it...
For closing, nearly the same:
// close method signature (Connection conn)
try {
throw new Exception("Pool Debugger says: Connection <" + conn + "> closing:");
} catch (Exception e) {
e.printStackTrace();
}
// code that closes the connection
Then you can...
#!/bin/sh
# Filter pool debugger statements.
cat catalina.out | grep "Pool Debugger says:" > pool.debug
# Get the open and close lines, remove all the line but the connection ID.
cat pool.debug | grep "> opened:" | cut -f 2 -d '<' | cut -f 1 -d '>' | sort > pool.debug.open
cat pool.debug | grep "> closing:" | cut -f 2 -d '<' | cut -f 1 -d '>' | sort > pool.debug.close
# Obtain the difference (those which were opened and not closed)
STALLED=$( diff pool.debug.open pool.debug.close | grep '^<' | cut -c 3- )
# Trace them in the log (hope the connection ID is not shown anywhere else)
for i in $STALLED
do
grep -A 5 $i catalina.out
done
Hope that helps.
Antonio Fiol
smime.p7s
Description: S/MIME Cryptographic Signature
