Mailing-List: contact user-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Commons Users List" <user@commons.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
Message-ID: <4AD78A1D.6070609@christopherschultz.net>
Date: Thu, 15 Oct 2009 16:46:21 -0400
From: Christopher Schultz <chris@christopherschultz.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
 rv:1.9.1.4pre) Gecko/20090915 Thunderbird/3.0b4
MIME-Version: 1.0
To: Tomcat Users List <users@tomcat.apache.org>
CC: Commons Users List <user@commons.apache.org>
Subject: Re: DBCP woes (running out of cursors).
References: <4AD615CD.1050005@gmail.com>
 <4AD61F59.50605@christopherschultz.net> <4AD63D36.7090404@gmail.com>
 <4AD6795B.50105@christopherschultz.net> <4AD766AE.4050300@gmail.com>
In-Reply-To: <4AD766AE.4050300@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill,

On 10/15/2009 2:15 PM, Bill Davidson wrote:
> Christopher Schultz wrote:
>>Is it possible that your server just doesn't want to allocate 245 * 4
>>cursors, and that you are just hitting that barrier?
> 
> cursor != connection

Right... but presumably your code is doing something useful with that
connection, rather than simply fetching it from the pool and then
discarding it. IIRC, all SELECT queries allocate a cursor. Others may as
well.

> Oracle is set up to allow up to 300 cursors per session (connection).
> I could up that limit, but it probably won't fix the problem, as these
> things seem to just keep accumulating.

Agreed. But, I wonder if there's a max cursors globally or something
like that, so that when you have all those connections going at once,
their sum total cursors exceeds that limit?

When this happens, does Tomcat continue running otherwise unscathed? Or,
do you get an avalanche of exceptions after the first one occurs. What
about on the other members of the cluster (just a descriptive term, not
a technical one)?

>>I though you said that after a connection was checked-out for 120
>>seconds, it was forcibly closed by the connection pool.
> 
> Only when it is sent back to the pool by the servlet.  The pool manager
> doesn't have a background thread looking for old connections to kill.
> It's not a work around for connection leaks.  Apparently it's a work
> around for cursor leaks.

Hah, okay. Well, it doesn't look like commons-dbcp has a similar
configuration option; you can only evict connections that have been
sitting idle, not those that have simply lived too long. Perhaps the
commons-dbcp folks have some ideas.

>>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.
> 
> Correct.  Sorry if I didn't make that clear.  Those queries are not in
> my webapp.  They are only used to help track down lingering cursors.
> For people not familiar with Oracle, special characters like '$' in FROM
> clauses are usually an indication of something being in Oracle's "data
> dictionary" which keeps track of everything in Oracle.  The other one
> in that query was "v$session" which keeps track of session information.
> A session, in this context, is a connection.

Gotcha. I wonder if somehow your app-based queries are allocating
cursors and never freeing them. Simple SELECT queries (or even complex
ones) shouldn't really be allocating cursors that aren't freed when you
call ResultSet.close or Statement.close (whichever actually kills them,
I'm not sure). Also, "closing" a pooled connection should call
setAutoCommit(true) (this is the default setting for autocommit) which
will commit any in-progress transactions lingering, which I would
imagine results in all cursors being closed as well. I must admit, I'm
at the limit of my (quite limited) Oracle knowledge at this point. Got a
DBA handy?

>>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 Oracle instance is set up to handle 1000 connections.  Tomcat has
> maxThreads=240, just because I'm paranoid and want to leave a little slop
> factor.  I shouldn't ever see more than 240 actual connections per server.

Agreed.

> I suspect that you are correct.  I'm baffled as to why I have old cursors
> lying around.  The close call on the statements has to happen.

Yeah, I agree. The only thing I can think of is that you are pooling
prepared statements. If you have more than 300 different queries in your
webapp (not at all a stretch IMO), and they are all being pooled, it's
possible that each one of those cached prepared statements consumes a
cursor. In that case, you'll easily consume your cursor allocation per
request. I'm just grasping at straws, now.

What happens, other than an awful slowdown, if you disable statement
pooling?

>>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.
> 
> The Oracle DBA that I have access to doesn't know much about Java/JDBC
> which is why I was hoping I could find some Oracle expertise in the
> commons or tomcat lists.

Yeah, see what the commons-dbcp list has to say. Things are ... pretty
slow on the commons-user list, so you may have to wait a bit for a
reply. You could also ask on some Java-oriented Oracle forums.
Definitely post back if you find a satisfactory explanation so everyone
can ready about your new-found sage advice ;)

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

iEYEARECAAYFAkrXih0ACgkQ9CaO5/Lv0PCiXACgjMwYfWJIuILB0NfPe0VMzLie
IMAAn0sYF+cpl3JMef9oXUugj2dvLuOd
=xZV9
-----END PGP SIGNATURE-----

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