Here is one good relevant reason to separate online redo log files to a
different file-system from datafiles and other structures...
This is relevant only for UNIX file-systems, not for ASM or "raw"
devices. Also, attempting to optimize in the manner described below is
simply not necessary unless you are observing database performance
problems characterized by excessive "log file sync" waits and you have
good reason to believe that it is not application design/implementation
issues (the usual cause), but rather "log file parallel write" that are
contributing to the "log file sync" waits. If you don't have
performance problems involving "log file sync" or if you are on ASM or
"raw" devices, then don't bother considering implementing the rest of
this...
OK, most of the caveats and sanity checks out of the way...
Because of the nature of I/O on the online redo log files (i.e. one
LGWR process performing sequential writes, one (or maybe a couple)
other process(es) performing sequential reads), there is *absolutely*
no reason for file-system caching, as each block written to the online
redo log files is read once or (in the case of a recovery)
infrequently. So, direct I/O in UFS mount-points is a great fit for
the online redo log
files, and this makes for a potential reason to separate the online
redo log files to a different file-system from the datafiles and other
database files.
NOTE: please note I said separating the online redo log
files to another *file-system*, not to another disk drive or LUN or SAN
or whatnot...
On some UFS implementations, direct I/O cannot be used if the I/O
request size is not the same as or a multiple of the file-system block
size. Check with your UNIX sysadmin or documentation for your
platform. If direct I/O is not a restriction for the type of UFS you
are using, then (again) please disregard the rest of this...
On most platforms, the I/O size for LGWR is 512 bytes (except HP-UX
which is 1024 bytes, check BLOCK_SIZE in V$ARCHIVED_LOG). And, in most
Oracle database, DB_BLOCK_SIZE is 8K or 16K, so in most Oracle shops,
the UFS block size is generally 4K, 8K, or 16K. Not a good size for
direct I/O for the online redo log files...
So there is often a small incremental performance benefit to placing
the online redo log files in their own little file-system(s) built with
a blocksize of 512 bytes, matching the LGWR I/O size (usually 512
bytes), in order to enable direct I/O in the file-system(s) dedicated
to the online redo files. YMMV...
Of course, please do not accept all this as gospel, neither the
assertion that UFS blocksize enables/disables direct I/O, nor the
assertion that direct I/O will improve LGWR performance -- please test
it out in your environment.
Testing is easy. There is relatively low risk and relatively low level
of effort to perform such testing, as it is not difficult to move the
online redo log files without downtime while a database is up and
running and active. Measure the average wait times for "log file
parallel write" before, and again after, such a test in order to
determine if there was a performance benefit or not. If you determine
that the average "log file parallel write" has decreased, then that is
a good thing. And if "log file parallel write" was the primary
contributor to "log file sync" waits, then those should decrease
dramatically or disappear completely. However, if the "log file sync"
waits were not due to bottlenecking on LGWR, and instead were due to
some insanity with COMMITs in the application design or implementation,
then end-user performance will remain poor and increasing the
efficiency of LGWR will be a next-to-useless exercise.
Of course, stuff like this is what makes me prefer using ASM or "raw"
devices whenever possible, as the entire issue of "direct I/O" is
completely moot.
Hope this helps...
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
Michael Dinh wrote:
We use SAME and all files are
located under /oracle/orarata/sid
However, we are contemplating on
moving archived logs to a less expensive HW.
NOTICE OF CONFIDENTIALITY - This
material is intended for the use of the individual or entity to which
it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable laws. BE
FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH
INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE
FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD
PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY
TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE
RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments)
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is
strictly prohibited. Please notify the sender of the error and delete
the e-mail you received. Thank you.
Hello, listers
What are general pros
and cons on separating redo logs from database files? Both database and
redologs are already residing on RAID1 volumes. So this separation
would only mean – move redo logs to separate mount point (RAID1, still).
We have EMC consultant
insisting that it is in line with best practices. Why exactly is it a
good thing, what "best practice" actually means – he can't say.
Our systems are
moderately used OLTP databases. I we had lots of waits on redo log
writes (and we don’t), I would understand moving redo logs to flash
drives (for example). Or, as I have seen previously, database goes to
RAID5 and redo logs to RAID1 – depends a lot on database usage, etc.
etc.
Actual real life inputs
are welcome.
Regards,
Vladimir Barac
______________________________________________________________________
This e-mail message and any attachments to it are for the sole use of
the intended recipients and may contain confidential and privileged
information. This e-mail message and any attachments are the property
of Yusuf A. Alghanim & Sons w.l.l. or any of its subsidiaries or
affiliates (“Alghanim Industries”). Any unauthorized review, use,
disclosure, or distribution of this e-mail message or its attachments
is prohibited. Any opinions expressed in this message are those of the
author and do not necessarily reflect the opinion of Alghanim
Industries. If you are not an intended recipient, please notify the
sender by reply e-mail and destroy all copies of the original message
and any attachments.
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
|
