Re: Most efficient large update?

From: broom <broom_at_voicenet.com>
Date: 30 May 2001 19:53:01 -0700
Message-ID: <c948eb61.0105301853.4d6664@posting.google.com>

Here's my summarization report.
This is really an final email to
Martin, but since I promised a summary, here goes:

---

While I really like the simple
elegent design of the update,
it's much slower than the PL/SQL.

It is also far more CPU intensive
for the amount of work it is doing.

While running, it consumes about 65%
of a 3 CPU U450. Note: I have 2GB
of real mem and give Oracle SGA about
1GB, plus I have 10MB for sortsize.

I use Veritas Oracle accelerator
for my Oracle data files, so they
perform very well compared to "regular"
files.

I also have VERY fast fibre channel disk arrays, with external hardware RAID 5,
Veritas RAID 0 stripes across several
LUNS (15 disks) and DMP across dual
fibre cards. During the initial gather
phase I have almost no 'wait for io',
but during the update phase it jumps
to about 20% (last 2->3 minutes).

It takes 577 seconds to update 942676
rows, giving it 1,633 RPS. In a best
case scale up (running more than 1 update at once) , I'd get 2,205 on this box,
(assuming 35% gain).

Also, since there are several phases,
ie: initial high CPU join phase followed by a high io wait write phase (assumptions), I doubt I'd scale at all. During the 20% wait for io, the service time on my 6 disk LUN channels jumped to 250ms, which is terrible for them, so I'd not want anything more thrown at them.

Note: I did several runs. I started
by bouncing the Oracle instance and
made sure there was no other activity on the box. Since I'm in development 'mode', I can kick everyone off. The initial run
was the 577 seconds. Other runs were
a bit faster or slower, but only by a few seconds either way, so I'm comfortable with this number.

As a comparison, my PL/SQL update consumes a consistent 22% CPU throughout it's run, with about a 12% wait for io, and the service time on the disks are between 12 and 20ms, which gives me lots of breathing room.

The total run time is 215 seconds update 942676 rows, giving it 4,384 RPS. In a real world test, grinding 10 of these at once, I got about 11,000 RPS, and it would have been much better but the source tables were skewed in size, which meant one ran much longer than the others and it dragged down the average.

Of course, I bounced the Oracle instance for mine also, and multiple runs gave almost an exactly same number.

Thanks for showing me the simple update. I'll use it when performance it not my main concern.

Barry Received on Wed May 30 2001 - 21:53:01 CDT