Interestingly I had the luxury of playing with a
out-of-the-box Solaris server the other day, so
(whilst I was meant to be configuring it :-) ), I
played around with raw volumes under plain old disk
partitions (no veritas, no sds etc).
Using straight 'dd' to copy 100m or so, partitions
starting at cylinder 1 ran about 20-25% faster than
starting at cylinder 7000 (I think) on an 18G
10000rpm.
So I smacked some redo logs on the low cylinders ,
then the high ones, and crunched away as hard as I
could (in noachivelog). I got about 8-10% improvement
in performance - I didn't bother to do any further
analysis, I'm guessing the reduction in gain was due
to other work the db had to do other then just lgwr.
The box is gone now - so I can't repeat the test.
Still, for an high update db, it does reinforce the
ol' log files on dedicated disks on the outer bits
argument.
Cheers
Connor
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote: > I guess this test was in response to a claim
that
>
> 'you have to put your log files on the
> inner/outer/middle section of the disc
> for best response time.'
>
> I think this demonstrates that there are never any
> easy answers - and rarely any conclusive tests
> other than real-life.
>
> One thing you might want to check is whether
> the average 'redo write size' increases as the
> writes move through the redo logs (in the second
> case). You MIGHT be demonstrating that the original
> hypothesis is true, you MIGHT be demonstrating
> that in this case LGWR becomes self-balancing -
> as the disc access speed increases because it
> buffers
> more data and does bigger writes, so that the slower
> seek time is hidden by a side-effect of a larger
> write.
>
> BTW - this is not a fair test of normal OLTP
> activity -
> some experiments I've been doing recently suggest
> that inside a PL/SQL loop the COMMIT does not
> cause a log file sync - if you check your
> redo synch writes
> and
> redo writes
> statistics, you may find that redo synch writes is
> extremely small (possibly 1, possibly 1 per log
> file)
> and you redo writes will almost certainly be less
> than the 15,000,000 commits you issued.
>
> I'm still working on the 'commit is not a sync'
> thing, but it really does seem that in pl/sql
> a 'commit is complete' BEFORE lgwr has written
> to disc.
>
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Screen saver or Life saver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> Date: 18 September 2001 23:11
>
>
> |
> |Out of curiosity and as a test I scrubbed my 16Mb
> RAID1 array and set
> it up
> |again with 5 x 50Mb redo groups. I ran the
> following script to
> generate
> |lots of redo:
> |
> |begin
> | for i in 1..15000000 loop
> | insert into test_table values
> (mod(i,10));
> | delete from test_table where
> anumber =
> (mod(i+1,10));
> | commit;
> | end loop;
> |end;
> |
> |ARCHIVELOG mode was on and the archive destination
> (on another disk)
> was
> |initially empty.
> |There were 239 log switches and the test completed
> in 2 hours 56
> minutes.
> |
> |I then added a further 250 50Mb redo groups,
> cleared the archive
> |destination and ran the test again. Again it
> completed in exactly 2
> hours
> |56 minutes, this time 240 log switches (difference
> of 1, I guess due
> to
> |whereabouts in the first log it was when the test
> started).
> |
> |v$session_wait shows permanent "log file parallel
> write" for LGWR
> while the
> |test runs.
> |
> |Any suggestions why I failed to see any slow down
> when I (almost)
> filled
> |the disk with redo groups? (This server wasn't
> doing anything else
> and the
> |time of the test). The RAID1 array had a Linux
> ext2 filesystem on it
> and I
> |had disabled the write cache.
> |
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Sep 19 2001 - 06:57:22 CDT
