RE: Insert contention on RAC
Date: Wed, 25 Jun 2008 16:15:51 -0400
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.
Thanks for your reply.
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
From: Tim Gorman [mailto:tim_at_evdbt.com] Sent: Wednesday, June 25, 2008 4:04 PM
To: Baumgartel, Paul
Subject: Re: Insert contention on RAC
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"...
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!
Quoting "Baumgartel, Paul" <paul.baumgartel_at_credit-suisse.com>:
> 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
> the same RAC instance, is there anything else I can do to reduce these
> Paul Baumgartel
> CREDIT SUISSE
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> Phone 212.538.1143
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
Please access the attached hyperlink for an important electronic communications disclaimer:
Received on Wed Jun 25 2008 - 15:15:51 CDT