RE: Insert contention on RAC
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "Tim Gorman" <tim@xxxxxxxxx>
- Date: Wed, 25 Jun 2008 16:15:51 -0400
Tim,
I'm way ahead of you! ;-) Take another look at my post; I did
pre-allocate one large extent per instance. Unfortunately, it didn't
help.
I'm pretty sure that commits are batched; they're sending a batch of
insert statements from Java and probably committing after each batch.
Transaction rates are only about 1.5 per second.
If I get down to log file sync, I'll be happy. The block contention is
what's driving me nuts. And it occurs to me that if I restrict the
inserts to one instance, I'll just get buffer busy waits instead of gc
buffer busy waits.
Help!
Thanks for your reply.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, June 25, 2008 4:04 PM
To: Baumgartel, Paul
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Insert contention on RAC
Paul,
Nice diagnosis on the "gc buffer busy wait" waits. Performance
tuning, especially on RAC, is like peeling an onion. You've peeled
away the first layer, now the next is "enq: HW contention"...
Try this...
ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 1);
ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 2);
ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 3);
IIRC, using freelist groups implies that the entire extent is assigned
to one of the freelist groups upon allocation. The used to be a
parameter called GC_FILES_TO_LOCKS that would do things more
granularly, but I'm sure it's deprecated by now. Anyway, automatic
allocation of extents makes this assignment to freelist groups and
instances a matter of rolling dice, so it might become necessary to
pre-allocate extents for each instance.
Try that and see if the high-water mark contention enqueue goes down?
Of course, there will be more layers to the onion under this. I'm
betting that, since it sounds like you're inserting (and committing)
one row at a time, it'll eventually come down to "log file sync" that
proves the ultimate limitation on performance, as the single-row
commits drive the each instance's LGWR process nuts. Just my $0.02...
Hope this helps!
-Tim
Quoting "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>:
> To borrow the style from some MetaLink notes--
>
> Fact: Oracle 10.2.0.1.0
> Fact: RedHat 3
> Fact: 3 node RAC
>
> Many concurrent sessions inserting one row at a time into the same
> table. On first run, table in ASSM tablespace, about 50% of database
> service time was gc buffer busy waits. Built a new tablespace with
> manual segment management, re-created table with three freelist groups
> and 4 freelists. Next run showed >90% of service time was enq: HW
> contention; total run time was slightly higher than it was with ASSM..
> Re-created table again, allocated three 4 GB extents, one for each
> instance. Current run is still showing high HW contention waits.
>
> Might I have too many freelists? Other than forcing all sessions to
use
> the same RAC instance, is there anything else I can do to reduce these
> waits?
>
> Thanks,
>
> Paul Baumgartel
> CREDIT SUISSE
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel@xxxxxxxxxxxxxxxxx
> www.credit-suisse.com
>
>
>
>
========================================================================
======
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>
========================================================================
======
>
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
