RE: Average size of reads from online redo logs by ARCH process
- From: "McCartney, Bruce" <BMcCartney@xxxxxxxxxxxxxxxxxxx>
- To: "Alexandre Gorbatchev" <agorbatchev@xxxxxxxxxxx>
- Date: Tue, 16 Nov 2004 13:23:22 -0700
Hi Alex,
To clarify my previous response, the v$ references refer to LGWR and not ARCH
(I don't believe arch will write to the redo ).
We did some experimentation with EMC redo logs and stripe sizes and found a
couple of things for our application mix (high OLTP with commit frequency
extremely high). We found:
1) raw devices performed better ('log file parallel write') than solaris ufs by
between 10 and 20% on average time over long periods. I think this is notion
is supported by steve adams notes.
2) the drives were mirrored in the EMC array and we were told by EMC that the
array will perform sequential reads (ARCH copies) from the 'mirror' as to not
introduce measurable contention. We did not confirm this technically but were
unable to see any contention when ARCH was active reading. so this may not be
a critical issue for you. Are you spending a lot of time in log file sync?
There may be other reasons why...
3) the array provides a degree of isolation from physical writes to a point by
memory caching, but there is a limit of a number of writes that can be pending
on any one drive in the array. because of this behavior, our emc tech
specialist advised us to stripe the redo rather than but it on a separate disk
pair in the array. I can't recall the stripe size off hand...
You could talk to EMC about any best practice stuff they might have...
By the way,
select *
from v$session_event
where event = 'log file sequential read'
will show which sessions have read the redo since database startup and the
average time waited...
cheers.
-----Original Message-----
From: Alexandre Gorbatchev [mailto:agorbatchev@xxxxxxxxxxx]
Sent: Tuesday, November 16, 2004 11:19 AM
To: McCartney, Bruce
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Average size of reads from online redo logs by ARCH process
Hi Bruce,
On Steve's site I didn't find anything about determining size of read by ARCH.
I know how to determine avg. writes size by LGWR. Now I want to know what is
size of ARCH IOs (reading) in order to figure out what striping we need to
implement to reduce possible IO contention.
Ideally, we need to put every redo log on a separate disk but it's an expensive
solution, so I'm looking to possibility of stripe it. I think if both reads and
writes are small, we can have stripe size to set so the one single IO affects
most of the time one single physical disk.
Another complication is that we are on EMC and I'm not sure yet if caching will
play significant role in writes performance.
You and KGopal. are right - I could use 10046 to figure that out.
Thanks,
Alex
From: "McCartney, Bruce" <BMcCartney@xxxxxxxxxxxxxxxxxxx> on 16-11-2004
09:59 MST
To: <agorbatchev@xxxxxxxxxxx>
<oracle-l@xxxxxxxxxxxxx>
cc:
Subject: RE: Average size of reads from online redo logs by ARCH process
Alex,
you could strace the arch process to get detailed numbers. The read size is
dependent on the underlying file system setup (i.e. raw devices, veritas file
system or unix/windows file system). There is not direct relationship to the
buffer_size for read (or write). check out steve adams site for some detailed
info on redo i/o http://www.ixora.com.au/notes/
just curious, why do you care about the *read* size? the average *write* size
can be calculated from :
select value from v$sysstat where name='redo blocks written';
/
select value from v$sysstat where name='redo writes';
a log block size is os dependent as shown in
http://www.ixora.com.au/notes/log_block_size.htm
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Alexandre Gorbatchev
Sent: Tuesday, November 16, 2004 9:12 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Average size of reads from online redo logs by ARCH process
Hi all,
How do I figure out what is the average (or constant?) size of disk reads
from online logs by ARCH process?
Is there any relation to _log_archive_buffer_size? It is 2048 log blocks
by default, as far as I know, so on my platform it's 2Mb.
Thanks in advance,
Alex
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
