On Tue, Jan 13, 2009 at 11:00 AM, Steve Essery <Steve.Essery_at_sun.com> wrote:
> Dick Davies wrote:
>>
>> I got this error on my cluster over the weekend:
>>
>>
>> [#|2009-01-10T14:33:06.521+0000|WARNING|sun-appserver9.1|javax.enterprise.resource.resourceadapter|_ThreadID=154;
>> _ThreadName=httpSSLWorkerThread-39181-21;oracle-prod-pool;In-use
>> connections equal max-pool-size and expired max-
>> wait-time. Cannot allocate more
>> connections.;_RequestID=5e1a41cd-1a74-4a5e-8d3b-2582599ac0b6;|RAR5117
>> : Failed to
>> obtain/create connection from connection pool [ oracle-prod-pool ].
>> Reason : In-use connections equal max-pool-s
>> ize and expired max-wait-time. Cannot allocate more connections.|#]
>>
>> which I read as 'your connection pool is full, and I've been waiting
>> too long, so here's an Exception'.
>>
>
> Yes, that's the meaning. The question is why have you hit the maximum
> number of connections, and you head in the right direction below... You
> could also look at turning on JDBC monitoring and using the asadmin monitor
> command to capture statistics for the pool and see what the connection usage
> is like over time, as well as seeing how many connection requests are being
> forced to wait as the pool is fully in use.
Thanks, I've used that in the past but with asadmin get/list to check
from time to time,
not with asadmin monitor - does that give some sort of historical graphing
(a link to a howto would be really useful if you have one)?
>> Both appservers started giving 500s and the site was down for an hour or
>> two.
>> It looks like there was some kind of network blip that stalled a few
>> connections, when I restarted the glassfish instances
>> everything was back up and running.
>>
>>
>>
>> So I'm wondering how I tell Glassfish to step in and kill of idle
>> connections after a certain amount of time.
>>
>> in the admin console I see a couple of options, I'm wonder
>>
>> The first is Resources> JDBC> Connection Pools> oracle-prod-pool ->
>> advanced
>>
>> set a leak timeout and then tick 'leak reclaim'.
>>
>
> You might want to start off just turning on the leak detection and setting a
> timeout and seeing if there are connections leaking. If they are you'll see
> a call stack in the server.log indicating the state of the thread at the
> point the application code fetched a connection from the pool. Working down
> the stack you'll see where in the application the connection was obtained.
> The question then would be why wasn't that connection returned to the pool
> before the leak timeout expired? Is there a Connection.close() missing for
> example. Unfortunately I've quite often see issues in application code
> where its assume there will be no problems performing JDBC operations and
> because of exceptions JDBC resources such as Connections, Statements and
> ResultSets aren't closed correctly - even in a finally block.
Well I've had leak timeout set for a while (though not leak reclaim)
and don't recall seeing
any exceptions, so hopefully I'm ok there.
The keepalive timeout on the load balancer is less than the connection
pool leak timeout,
which makes me think I should be safe enabling leak reclaim
(users will have lost their HTTP connection before an exception bubbles up),
but I'm inclined to only tweak one thing at a time, and I'm pretty
sure you nailed it on the
validation mechanism issue (see below).
>> The second is Resources> JDBC> Connection Pools> oracle-prod-pool ->
>> general
>>
>> enable 'connection validation' and then 'on any failure' tick 'close
>> all connections'
>>
> With Oracle the only workable validation mechanism is table-based. Since
> DUAL is a table that's been present in Oracle as long as I can recall (I go
> back to v6) that's the table I'd suggest. Table based validation works by
> parsing and executing a select statement on the table, but it does not fetch
> back any data from the table. The parse makes the Oracle JDBC driver
> contact the backend database, which essentially validates that the
> connection is still good. This is effectively the same mechanism as used in
> the Oracle propriatary OracleConnection.pingDatabase() method.
BINGO! That's very likely it, thanks a lot. I've switched table (DUAL)
validation and will
see how that goes.
Thanks a lot!