The database redo log files are amongst the most mission critical of all Oracle files.
The redo log files hold a journal of all changes made to the database. As logs are filled up they are typically archived to the Flash Recovery Area (FRA) to ensure full recoverability.
DBAs therefore frequently seek to place redo log files on the fastest storage available such as the Extreme Performance tier.
Oracle suggests in Metalink document ID 1376916.1 that only Oracle Engineered systems feature flash drives optimized for redo log writes. This statement is untrue and Dell EMC does not discourage the use of the Extreme Performance tier for Oracle redo log files.
Where databases do suffer from redo related performance problems, such as the event Log File Sync frequently appearing in the top five timed events of an AWR report, Dell EMC recommends that database performance analysis be undertaken to determine a precise cause.
In most cases, Log File Sync and Log Buffer Wait are not consistently top events in an AWR report. Where they are, the reason might be I/O related, or might be related other factors such as OS resource limitations or network latency to a Data Guard replicated database.
Excessive application commits or Oracle bugs may also cause excessive Log File Sync events.
Determine that I/O is the cause of the Log File Sync events in the database before undertaking the provisioning of new storage and the relocation of redo log files.
Prior to Oracle Database11gR2, most platforms used a hard coded 512 byte block size for redo log files, although some platforms used 1 KB or even 2 KB. The block size was a function of the lowest common denominator for supported direct I/O operations on the platform in question.
Since Oracle Database 11.2.0, Oracle now offers user definable redo block sizes if the database is running on an operating system that can understand 4 KB sector sizes such as Windows 8, Windows Server 2012, Red Hat Enterprise Linux kernel 2.6.32 or later or Solaris 11.1.
Flash drives are typically rated using 4 KB random I/O, but ScaleIO presents all devices to Linux as 512-byte, therefore Oracle will fail with an ORA-1377 error if the DBA tries to create a redo log file with a block size other than 512 bytes:
SQL> alter database add logfile thread 1 group 5 '+FRA' size 500M blocksize 4096;
alter database add logfile thread 1 group 5 '+FRA' size 50M blocksize 4096
ERROR at line 1:
ORA-01378: The logical block size (4096) of file +FRA is not compatible with
the disk sector size (media sector size is 512 and host sector size is 512)
While the DBA can override this by setting the _disk_sector_size_override parameter, there is little performance benefit to doing so on ScaleIO.
Dell EMC recommends a 512-byte block size for redo log files on ScaleIO.
Redo log files should normally be able to sustain a minimum of fifteen minutes of transactions before requiring a switch to the next group.
For workloads that experience excessive bursts, it is acceptable for redo log switches to occur more frequently at peak, but excessive redo log switches should be minimized as much as possible.
Log file switches are relatively expensive in database resource terms, since the entire database must be checkpointed including updating the data file headers of every file of the database in addition to the control files.
Dell EMC recommends redo log group members sized between 512 MB to 2 GB for demanding OLTP systems.
If the DBA is concerned that a larger redo log group will result in insufficient switching during non-peak period, the archive_lag_target parameter should be set to force periodic switching. This setting will help ensure a smooth recovery in the event of a database failure.
Dell EMC recommends setting the archive_lag_target parameter to 900 to force a log switch every fifteen minutes.
An insufficient number of redo log groups may cause ORA-16014 Log X sequence# Y not archived errors in addition to Checkpoint Not Complete warnings in the database alert log and will affect overall performance.
Dell EMC recommends a minimum of five redo log groups per thread for any database. Enterprise grade storage arrays largely negate the benefits of multiple redo log group members.
Redo log data is written using a synchronous process. LGWR must confirm that redo blocks are written to disk before it can acknowledge a user commit.
Exact implementation details differ depending on the platform, but async I/O will still benefit the LGWR process by allowing additional writes to be queued before pending writes complete.
Dell EMC recommends setting disk_asynch_io to TRUE if I/O performance is determined to be a contributing factor to Log File Sync being listed as a top timed event.