Thursday, December 3, 2015

RDBMS -- Log Writer(LGWR), log file sync, log file parallel write and the Disk performance

In this post, you can find some quick tips for Log Writer IO waits.
Well, if the log file sync time and log file parallel write times are high, then these probably mean that our IO subsystem has performance problems.

If that is the situation; we need to consider the following;
  • We need to work with the Storage and System admins to analyze the filesystems where our redologs reside, because most probably, there is a problem with the performance at that level and the performance of the storage device needs to be improved.
  • We need to check if our redolog files are on RAID-5 or RAID-6., as it is not recommended to be so, as the redolog files should not be placed on raid configuration which needs the parities to be calculated. That is, we need to put our redolog files at least into Raid  mirrors.
  • We must not to use SSDs(Solid State Disks) for placing our redolog files unless our database is on Exadata , Supercluster or Oracle Database Appliance. Note that: Oracle Engineered Systems are optimized for using Redologs with SSDs.
  • We need to be sure that no other processes creating a load on the the disks where our redolog files reside , as this processes may cosume the I/O bandwidth. So if there is such a situation, we need to move those processes or we need to move the redolog files to other disks.
  • We need to ensure that our log_buffer is not a very big one. A big log buffer may introduce a performance problem, as LGWR will need a lot of I/O to flush the buffer when it will be filled. As, LGWR has to write all the data in to the redolog files and as LGWR will wait until all I/O is completed, having a big log buffer may make LGWR to aggresively write a big sized data(redo) in once, thus may introduce I/O waits according to the performance capacity of the underlying storage.
  • Even if our log file syncs are high and log file parallel writes are low; still this may be related with the I/O performance caused by the I/O peaks which are not reflected to AWR or average wait times.If that s the situation, we need to the OSWATCHER to identify the I/O peaks.
  • IF we see any I/O peaks which are encountered at the same of time(or little earlier) that we see the log file sync wait,  then again  we need work with Sys admin and storage admin and make them configure the I/O device handle these kind of peaks.
  • Lastly, we can use the following document to get traces from LGWR if there are log file related wait events. Document:601316.1 LGWR Is Generating Trace file with "Warning: Log Write Time 540ms, Size 5444kb" In 10.2.0.4 Database

3 comments :

  1. great post. could you please make it clear as why "We must not to use SSDs(Solid State Disks) for placing our redolog files"?

    ReplyDelete
  2. it is a recommendation by Oracle . This should be because a misaligned , misconfigured SSDs may create write performance during peaks.
    It may be considered as a myth also..

    Read this one:
    Using 4k Redo Logs on Flash and SSD-based Storage (Doc ID 1681266.1)

    and this one:
    https://communities.intel.com/community/itpeernetwork/datastack/blog/2013/07/26/configure-oracle-redo-on-solid-state-disks-ssds-with-asm

    ReplyDelete