commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bill Davidson <bill...@gmail.com>
Subject Re: DBCP woes (running out of cursors).
Date Wed, 14 Oct 2009 21:05:58 GMT
Christopher Schultz wrote:
 >On 10/14/2009 2:17 PM, Bill Davidson wrote:
 >>Redhat 5.2 Server
 >Wow.

Maybe I should have said RHEL 5.2.  5.3 would be the current
version, so it's actually not that old.  RedHat's starting over with
the numbers does get confusing.

 >This config looks fine, though the maxIdle might be a little low. IIRC,
 >setting-up Oracle connections is a time-consuming operation. If you
 >really need 245 connections, perhaps you might want to increase the idle
 >count to improve the performance of your webapp when demand suddenly
 >hits a mostly-idle server.

Usually, we don't need that many, but sometimes, we get hit really hard
with a lot of traffic and do need that many.  BTW, this is load balanced
across 4 servers that can each do 245 connections.

 >Similar capability is found in DBCP in the form of the "removeAbandoned"
 >and "logAbandoned" configuration parameters. See the configuration for
 >DBCP on this page:
 >http://commons.apache.org/dbcp/configuration.html
 >
 >/Important note:/ the "removeAbandoned" configuration parameter will
 >merely remove the discarded connection from the pool. It will /not/
 >close the connection after that timeout, so it won't solve your problem:
 >it will only help you find the problems in your webapp.

I thought that was for Connection leaks.  If we had Connection leaks,
then the old pools wouldn't work properly either, because the old pools
only kill connections when the servlets "free" the Connection (the same
as close() on a DBCP connection).  The Connection's are being sent
back to the pool, but apparently with open cursors lingering.

 >I don't know a thing about Oracle-specific queries, but what does:
 >
 >>      v$open_cursor a
 >
 >mean? Does this explicitly open a new cursor, or use an existing one
 >called a?

v$cursor is a view in the Oracle data dictionary that shows currently
open cursors in the current Oracle instance.  The sql_text column shows
the first 40-50 characters or so of SQL being executed for that cursor.
It shows them for both active and inactive sessions.  I'm only guessing
that the inactive sessions are from Connection's that are closed without
having all of their ResultSet's closed.  That might be incorrect.
Finding concrete information is difficult.

 >Technically speaking, the JDBC specification requires that calling
 >Connection.close() also close any Statement (and therefore ResultSet)
 >objects that were opened as well. The lines become blurred a bit when
 >you're talking about pooled connections, because Connection.close()
 >doesn't really get called... it's a grey area in the spec if you ask me,
 >but I'd prefer that a pooled connection act like a non-pooled connection
 >in this case, but there's no "recycle" or "reset" method in the
 >java.sql.Connection class, and calling Connection.close() on the actual
 >connection is not appropriate (since it's pooled) so there may be no way
 >to actually implement this mimicry.

Maybe, but as I said, I've tracked down the SQL for all of the open
cursors that don't seem to go away and they all have guaranteed close
calls on the Statement's, and many also have them on the ResultSet's.
A lot of the SQL is not that funky either.  A lot of it is as simple
as grabbing a single record "SELECT * FROM some_table WHERE id = ?"
or a few records like "SELECT * FROM some_table WHERE some_col = ?".

 >Can you post an example of your finally blocks? If each xyz.close()
 >isn't in a try/catch block of its own, it could potentially fail to
 >return the Connection to the pool. In this case, logAbandoned will help
 >you a lot. I recommend enabling that setting even in production,
 >especially because that's where it's easiest to exercise your webapp's
 >DB access code.

Basic pattern:

    public Vector someFinder( Connection conn, long param )
    {
        PreparedStatement statement = null;
        ResultSet rs = null;
        Vector result = new Vector();

        try{
            statement = conn.prepareStatement(SomeSQLString);
            statement.setLong(1, param);
            rs = statement.executeQuery();
            while ( rs.next() ){
                // findResult does the ORM in our app.
                result.add(findResult(rs));
            }
        }catch ( Exception e ){
            // logging, plus possible wrap-and-re-throw
        }finally{
            // sometimes the ResultSet is explicitly closed,
            // other times not.  It shouldn't matter.
            if ( rs != null ){
                try{
                    rs.close();
                }catch ( SQLException ex ){
                    // log it.
                }
            }
            // Statement's are always explicitly closed.
            if ( statement != null ){
                try{
                    statement.close();
                }catch ( SQLException ex ){
                    // log it.
                }
            }
        }
        return result;
    }

 >>- Should I use timeBetweenEvictionRunsMillis - What's an "eviction" run?
 >
 >It's what happens every so often to flush-out all the connections that
 >have been (for instance) idle too long, etc.

That might help.  The stuff I'm finding more recently is implying
to me that connections are never closed if I don't enable eviction
runs.



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message