commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <ch...@christopherschultz.net>
Subject Re: DBCP woes (running out of cursors).
Date Thu, 15 Oct 2009 01:22:35 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill,

On 10/14/2009 5:05 PM, Bill Davidson wrote:
> Usually, we don't need that many [connections], 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.

Is it possible that your server just doesn't want to allocate 245 * 4
cursors, and that you are just hitting that barrier? I don't believe the
JDBC driver cares at all how many cursors are allocated, so it's
unlikely to be a client-side exception being thrown (or, if you prefer,
it's a server-side error being represented by a client-side exception).

> I thought [logAbandoned and removeAbandoned] was for Connection leaks.

They are. I just thought it would be a good idea to enable these, just
in case there was a case where leaks were occurring.

> 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 though you said that after a connection was checked-out for 120
seconds, it was forcibly closed by the connection pool.

>>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.

Oh, so this query is intended to find out what is happening on the
server side, so you can see what cursors are open and what their queries
are. I thought you meant that a query such as this was being executed
from your webapp.

> 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 = ?".

Do some of those methods have multiple queries being executed? If so,
it's possible that one statement remains open while the second one is
closed. For example:

PreparedStatement ps = null;
PreparedStatement ps2 = null;

try {
  ps  = conn.prepareStatement(...);
  ps2 = conn.prepareStatement(...);

  ...
}
finally
{
  ps.close();
}

>        }finally{
>            if ( rs != null ){
>                try{
>                    rs.close();
>                }catch ( SQLException ex ){
>                    // log it.
>                }
>            }

This is perfect. I noticed that I don't see a conn.close in there (which
is probably appropriate, given that the Connection object is a parameter
to the method). I assume you have similar finally blocks in calling
methods, right?

>>>- 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.

The eviction run will only to remove connections from the pool: it won't
fix any resource allocation problems. Your webapp and server ought to be
able to tolerate all connections being open and active at once (so, a
full 245 connections in each webapp instance, and 980 connections on the
server).

The only thing the eviction will really help with is reducing the memory
being used on both the client and server. I suppose that calling a
"true" close() on the connection might clean-up any sloppiness going on
in the client OR the server, and thus might solve your problem, but I
believe it will be merely hiding the symptom, not actually solving the
underlying problem.

Do you have access to an Oracle DBA? They may be able to help uncover
the implications of some of the queries being run... it's possible that
cursors are being allocated that you didn't expect, or that aren't being
closed for /other/ reasons.

Good luck,
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrWeVsACgkQ9CaO5/Lv0PClbgCgnYUGJ/Uzh/UvTDeT8NpdzD/p
94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
=9VFy
-----END PGP SIGNATURE-----

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


Mime
View raw message