Dick Davies wrote:
> 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)?
>
Its unfortunately not historical - its more like iostat than sar -
reporting on current monitoring information rather than past aggregrations.
Most of it should be in the asadmin monitor --help command.
1) Enable monitoring for JDBC in the configuration your instances are
part of.
2) Once the pools are in use you can use:
asadmin monitor --user <admin user> --port <admin-port> --type jdbcpool
--filter <pool name> <instance-name>
A pool that has never been used won't have been created by the
application server and hence will not be monitorable.
If you are running it in a terminal window, hitting "h" will give you a
key explaining the column headings. If you add --filename <somefile>
then the terminal output is also written to the specified file in CSV
format.
If you have more than one pool to look at you can quickly find the
values for the the --filter argument by leaving that out of the command,
the asadmin monitor will then list the possible choices, e.g.
asadmin monitor --user <admin user> --port <admin-port> --type jdbcpool
<instance-name>
Some of the "average" statistics unfortunately can be less than helpful
over a long period of time, e.g. if you have a long running system and
there is a sudden short peak in requests waiting for connections, that
spike can be completely lost in the average wait time per request
statistic - only the current number of waiting connections would show
the problem.
>
>
>>> 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.
>
It is probably the number one thing I tell customers to configure. Once
validation is turned on and we know stale/dead connections in the pool
are not part of the problem we can move on to figure out what is. From
the one pager for JDBC posted recently the intention would seem to be to
make table-based validation the default for all pools in V3 - there was
an enhancement done for a particular patch release of Sun's AS 8.1 which
did this purely for Oracle pools but that change wasn't forwarded ported
to AS 9.1/Glassfish.
> Thanks a lot!
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>
>