RE: Insert contention on RAC
Date: Wed, 25 Jun 2008 17:25:49 -0400
Message-ID: <006501c8d70a$0b7f8350$1100a8c0@rsiz.com>
You might need more freelists. Other than pre-reserving a few extra blocks
which might sometimes drive new extents sooner that otherwise required,
extra freelists won't hurt you. You quoted "many" concurrent sessions
inserting. Let's say that "many" is as low as 48 and for easy math let's say
they are 16 per node. Then on average 4 inserters are competing for space on
each free list on each node. When you bump the end of that freelist, one
session is going to be first to grab more space for the freelist and the
other three have a chance to collide. I'm trying to remember if they get
interrupted in the attempt to add blocks to that freelist or if they check
again after the enqueue to see if more space is already there in the
freelist the session has in hand. (Things are usually moving too fast to
observe if the freelist gets bumped more than it has to get bumped.)
The maximum freelists value depends on your blocksize, and if you have a lot of concurrent inserters you probably want as big a value as is allowed.
Pre-allocating extents for each instance is a good idea. I trust you got the syntax right, or else we'd probably still be seeing some gc wait as parts of each extent are doled dynamically from the master freelist instead of being assigned to a particular instance's freelist group.
Now there is also the issue of indexes and partitioning, and whether, rather than forcing all sessions to one instance, you could establish affinity to particular instances based on some knowledge with respect to a session that predicts which partition the session will insert into. For example, if you have a notion of timezones and use 1 plus the remainder of the greenwich offset hour divided by 3 to pick an instance, then you would phase your activity nicely as you follow the clock (likely peaktimes in many applications, though of course not all). Of course the east coast and the west coast fall to the same partition by this scheme so you might want to use something else if most of your action is on the coasts. If all your sessions originate in the same timezone this is obviously worthless, but I hope it gives you an idea of the sort of thing that MAY exist as both a useful instance connection router and partition insert key. When this can be done the system operates more like 3 separate databases as regards insert performance, and that is a good thing. If you do this, then in your extent allocation you'll want to skew the allocation of extents heavily to the instance likely to get the inserts for each partition.
Now let's say there is a way to make, say, 10 partitions per instance with some useful session key. Then you'd have 30 times more chances to avoid HW contention than you currently do.
Even if you can't establish instance affinity, you could use balanced allocation freelist groups for each partition if there is some reasonable way to partition your data. The downside tradeoff is if that will frequently mean multi-partition queries in an awkward way that runs slower later. Often it actually can be faster, but that is a texture of use question, balancing the potentially enormous wins on partition pruning with the overhead of the need to assemble the results from many partitions.
Finally, unless your application functional requirements dictate the one row at a time thing, I'd pretty much stand on my head to batch them up.
Good luck,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Baumgartel, Paul
Sent: Wednesday, June 25, 2008 3:19 PM
To: oracle-l_at_freelists.org
Subject: Insert contention on RAC
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_at_credit-suisse.com
<file:///\\www.credit-suisse.com> 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
==
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 25 2008 - 16:25:49 CDT