Saturday Nov 30, 2013

Things to Consider when Planning the Redo logs for Oracle Database

Very basic and generic discussion from the performance point of view. Customers still have to do their due diligence in understanding redo logs, and how they work in Oracle database, before finalizing redo log configuration for their deployments.

  • size them properly
    • log writer writes to a single redo log file until either it is full or a manual log switch is requested
          Oracle supports multiplexed redo logs for availability, but this behavior of writing to a file until it is full or a log switch happens, still hold
    • if the transactions generate a lot of redo before a database commit, consider large sizes in tens of gigabytes for redo logs
    • if not sized properly, it leads to unnecessary log switches, which in turn increase checkpoint activity resulting in unnecessary slow down of the database operations
          two redo logs each with at least 5G in size might be a good start. observe the log switches, checkpoints and increase (or decrease, though there is no performance benefit) the file size accordingly

  • do not mix redo logs with the rest of the database or anything else
    • in a normal functioning database, most of the time, log writer simply writes redo entries sequentially to redo logs
    • any slow down in writing the redo data to logs hurt the performance of the database
    • best not to share the disks/volumes on which redo logs are hosted, with anything else
          set of disks, volumes exclusive to redo logs, that is

  • ensure that the underlying disks or I/O medium used to store the redo logs are fast, optimally configured and can sustain the amount of I/O bandwidth needed to write the redo entries to the redo logs
        if those requirements are not met, it could lead to 'log file sync' waits, which will slow down the database transactions

  • redo logs on non-volatile flash storage may have performance benefits over the traditional hard disk drives
    • check this blog post out, Redo logs on F40 PCIe Cards, for related discussion (keywords: 4K block size for redo logs, block alignment)

Saturday Aug 31, 2013

[Oracle Database] Unreliable AWR reports on T5 & Redo logs on F40 PCIe Cards

(1) AWR report shows bogus wait events and times on SPARC T5 servers

Here is a sample from one of the Oracle 11g R2 databases running on a SPARC T5 server with Solaris 11.1 SRU 7.5

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
latch: cache buffers chains 278,727 812,447,335 2914850 13307324.15Concurrency
library cache: mutex X212,595449,966,33021165427370136.56Concurrency
buffer busy waits219,844349,975,25115919255732352.01Concurrency
latch: In memory undo latch25,46837,496,8001472310614171.59Concurrency
latch free2,60224,998,5839607449409459.46Other

Unknown. There is a pending bug 17214885 - Implausible top foreground wait times reported in AWR report.

Tentative workaround:
Disable power management as shown below.

# poweradm set administrative-authority=none

# svcadm disable power
# svcadm enable power

Verify the setting by running poweradm list.

Also disable NUMA I/O object binding by setting the following parameter in /etc/system (requires a system reboot).

set numaio_bind_objects=0

Oracle Solaris 11 added support for NUMA I/O architecture. Here is a brief explanation of NUMA I/O from Solaris 11 : What's New web page.

Non-Uniform Memory Access (NUMA) I/O : Many modern systems are based on a NUMA architecture, where each CPU or set of CPUs is associated with its own physical memory and I/O devices. For best I/O performance, the processing associated with a device should be performed close to that device, and the memory used by that device for DMA (Direct Memory Access) and PIO (Programmed I/O) should be allocated close to that device as well. Oracle Solaris 11 adds support for this architecture by placing operating system resources (kernel threads, interrupts, and memory) on physical resources according to criteria such as the physical topology of the machine, specific high-level affinity requirements of I/O frameworks, actual load on the machine, and currently defined resource control and power management policies.

Do not forget to rollback these changes after applying the fix for the database bug 17214885, when available.

(2) Redo logs on F40 PCIe cards (non-volatile flash storage)

Per the F40 PCIe card user's guide, the Sun Flash Accelerator F40 PCIe Card is designed to provide best performance for data transfers that are multiples of 8k size, and using addresses that are 8k aligned. To achieve optimal performance, the size of the read/write data should be an integer multiple of this block size and the data transferred should be block aligned. I/O operations that are not block aligned and that do not use sizes that are a multiple of the block size may suffer performance degration, especially for write operations.

Oracle redo log files default to a block size that is equal to the physical sector size of the disk, typically 512 bytes. And most of the time, database writes to the redo log in a normal functioning environment. Oracle database supports a maximum block size of 4K for redo logs. Hence to achieve optimal performance for redo write operations on F40 PCIe cards, tune the environment as shown below.

  1. Configure the following init parameters
  2. Create redo log files with 4K block size
    SQL> ALTER DATABASE ADD LOGFILE '/REDO/redo.dbf' size 20G blocksize 4096;
  3. [Solaris only] Append the following line to /kernel/drv/sd.conf (requires a reboot)
    sd-config-list="ATA     3E128-TS2-550B01","disksort:false,\
                 cache-nonvolatile:true, physical-block-size:4096";
  4. [Solaris only][F20] To enable maximum throughput from the MPT driver, append the following line to /kernel/drv/mpt.conf and reboot the system.

This tip is applicable to all kinds of flash storage that Oracle sells or sold including F20/F40 PCIe cards and F5100 storage array. sd-config-list in sd.conf may need some adjustment to reflect the correct vendor id and product id.


Benchmark announcements, HOW-TOs, Tips and Troubleshooting


« April 2014