Re: Scalable Performace - Inserts/Updates

In theory there should be a difference using async log writes - at present if you have two members in a group, Oracle has to write them in sequence, and wait for the first acknowledgement before writing the second copy. With async, the second write request goes immediately after the first write request then Oracle starts to poll for two write completions. It is true that both copies have to complete before lgwr can take any further action, and completion time is likely to be slower (on average) than for a single write, but significantly faster than for two serialised writes.

I haven't had a system recently where the commit rate has been sufficient demanding that it has been an issue, so I haven't tried stress-testing the features yet. On my HP9000 the default values on and 8.1.7 install came out as:

   _lgwr_io_slaves = 0
   _lgwr_async_io = true

But there are so many parameters now (506 in my 8.1.7) that it is quite possibly that changing a couple of them (e.g. dbwr_io_slaves) will alter the default settings for these two.

On top of everything else, Oracle keeps moving the technology on silently - making some of the arguments redundant: maybe one side of a redo could become corrupt on a mirrored system, but it doesn't crash the system, it simply (!) makes the recovery fail - on the other hand doesn't ARCH recognise a corrupted log file on 8.1 (not sure about that) and give you a warming at that point (okay, so it's a pain having to do an immediate hot backup) - in fact Oracle is working on technology to read 'the other side of the mirror', so a retry could find the non-corrupted side anyway. On top of all that, how do make archive copies ? Mirror, of duplexed destinations - archiving a load slower than generating redo log anyway because it has to create files. The number of options with platform and version-dependent pros and cons (let alone considering the requirements of the application ;) is so large that a blanket 'best practice' statement is a bit optimistic at best. (But when things get busy, I quite like hardware mirrors)

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Nuno Souto wrote in message <3a43275d.927808_at_news-server>...


>On Thu, 21 Dec 2000 15:24:35 -0000, "Jonathan Lewis"

><jonathan_at_jlcomp.demon.co.uk> wrote:

>

>>

>>The basis of your discussion is no doubt why

>>Oracle has played around with parameters like:

>>

>>    lgwr_io_slaves

>>    lgwr_async_io

>>

>>over the last few years.  (Both are hidden in 8.1.7)

>>In theory they allow for Oracle doing the multiplexing

>>without the loss of time due to the need to serialise

>>copies of each write.

>>

>

>I'm not so sure they would help.  For all intents and purposes, it

>still needs to have two I/O's (two members) fully synced before the

>next write can start. While AIO is good to let the main process

>proceed while the write takes place, truth is with redo log writes,

>the redo writer can't do anything else until the write actually

>finishes.  Which means it has to wait anyway, therefore defeating the

>purpose of the async I/O.

>

>The slaves is another thing. I've played around with them but I could

>not measure any diff.  Maybe I didn't have the right test situation or

>methodology? Possible and I am prepared to admit it.

>

>What does 8i do? Async or auto-slaves? Or both?

>

>I'm still convinced that two members per group is not needed in

>RAID-like environments (EMCs, Sharks, and so on).  I have yet to see

>one redo log corruption in such an environment that can clearly be

>corrected by using two members.

>

>My point being that either the corruption is caused by the ORACLE code

>itself in which case it will corrupt both files, or it's caused by the

>hardware (disk error), in which case the RAID will do its job and fix

>it.  I'm assuming of course the hardware is reliable.  If not then

>it's multiple members, obviously!

>

>Of course like in anything, YMMV, IMHO, etcetc.  But if I want top

>speed, I stay with single member per group.  That, I was able to

>measure clearly, makes a diff.

>

>

>Cheers

>Nuno Souto

>nsouto_at_bigpond.net.au.nospam

>http://www.users.bigpond.net.au/the_Den/index.html