RE: Insert contention on RAC
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <paul.baumgartel@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jun 2008 17:25:49 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Baumgartel, Paul
Sent: Wednesday, June 25, 2008 3:19 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx
<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
============================================================================
==
Other related posts:
