Re: [suse-oracle] Linux system overloaded by oracle and not responding

On 06/14/2011 05:31 PM, Ivo Libal wrote:
> Hi,
> i would like to ask you about the following issue.
> We have systems having 4 CPUs (4core, intel xeon) and quite a lot of ram
> (32G or more). it happens that if application (application server jboss
> for java and our own application system having connection pool handling
> as well for GUI) starts a lot of requests (maybe a lot of complicated
> select statemets, or some plsqls) the system gets overloaded and does
> not respond for some time. We are using failover cluster (lifekeeper)
> and this situation ends in the failover or other symptoms when some
> parts of the system dont respond.
> We use orarun to set kernel parameters, huge pages, async I/O (increased
> max-io-nr) etc.
> Is there a way how to control that situation that even if oracle has a
> lot of requests that system stays responding in some reasonable way?
> Is there any reswource, which could be limited for such situations?
> Can you please share your experience how to work with such systems?
> Is there any way in Oracle itself how to control it?
> Thank you in advance
> Ivo
> _______________________________________________
> suse-oracle mailing list
> [email protected]
> http://listx.novell.com/mailman/listinfo/suse-oracle
>   

Hi,

I've seen this a couple of times before.
The cases I remember the problem arises from a very stupid way to
implement connection pooling:

Basically the connection pool starts to create new sessions when
the already existing connections are busy.
This might be the right thing to do when the database server is not loaded.
But when the system gets loaded the lack of idle connections means
that the server is busy  - and what does the connection pool then do:
It creates more sessions.
Last time I encountered this behaviour I said to one of the developers
"Ok, when the database server is busy you react by throwing more work at it"
- the developer said after a pause: "well you could see it that way"

There are a couple of other ways to picture this:
- When a man falls over then run over to him and kick him to death
- Take a bucket and a shovel and walk into a stable and start to fill
the bucket with manure - somehow the developers behind above
connection pooling strategy would also be the guys that gets surprised
when the bucket starts to overflow.

Ok, I'll step of the soap box:

First of all: before starting to change parameters, check if the lack of
response is indeed caused by excessive load as suggested above.

If so: there are a number of actions to take:
- The hardware fix: get more or faster CPU's and more IOPS until the
problem does not occur any more (watch out: there might be future loads
you haven't seen before)
- Limit the number of connections and force the developers to implement
something known as a "queue" to wait (!) for an idle connection when the
maximum numnber of connections has been reached.
- If on Enterprise Edition: use the Database Resource Manager to
limit the number of concurrently active sessions (that is: implement
the queue in the database)
- as already suggested in another mail: use profiles with the cpu_per_call
resource limit set to terminate pl/sql or sql using too much time - this
is not a nice solution as calls to the database will start to fail and there
will still be the possibility to overload the system if too many sessions
are doing a lot of small calls.

Best regards
Martin Berg

_______________________________________________
suse-oracle mailing list
[email protected]
http://listx.novell.com/mailman/listinfo/suse-oracle