Managing Oracle disk I/O in AIX

Oracle Database Tips by Donald BurlesonNovember 12, 2015

Managing Oracle disk I/O in AIX

The IBM document "Configuring IBM System Storage DS4000 Series For Oracle Database Applications" (October 2015) notes that I/O bottlenecks remain a significant issue for Oracle databases, and that RAID randomization across disk platters will load-balance the I/O sub-system:

?In many cases, I/O performance problems can be traced directly to ?hot? files that cause a bottleneck on some critical component (e.g. a single physical disk). This can occur even when the overall I/O storage system is lightly loaded.

When bottlenecks occur, Storage or Data Base Administrators may have to identify and manually relocate the high activity data files contributing to the bottleneck condition.

This tends to be a very resource intensive and often frustrating task.

As the workload content changes in concert with the ebb and flow of normal business cycles (e.g. hour by hour through the business day or day by day through the accounting period), bottlenecks may mysteriously appear and disappear or migrate over time from one data file or device to another.?

IBM has now implemented a RAID-5 technique that minimized the notorious ?write penalty?, making RAID-5 suitable for low-DML AIX databases. IBM recommends a load balancing combination of RAID and PP (Physical Partitions within LVM) as a solution to disk hot spots:

Conceptually, the technique simultaneously employs two separate but complementary I/O balancing strategies:

? The use of hardware RAID-5 and/or RAID-10 technology within the DS4000 storage system. The fine granularity 'striping? inherent to the RAID-5 and RAID-10 algorithms results in highly balanced I/O activity across all of the physical disks in the RAID array.

? The use of AIX Logical Volume Manager (LVM) to physically partition data files across multiple DS4000 logical drives, thereby 'spreading? I/O activity across all the logical drives in the DS4000 storage system.

This 'striping and spreading? strategy has been used in a number implementations with IO intensive workloads with excellent results.

Oracle ASM and AIX

While Oracle Automatic Storage Management (ASM) is used on almost all Oracle platforms, IBM notes that there may be faster hardware-based alternatives to ASM:

"Since ASM mirroring is software based, it can potentially involve higher overhead (CPU, redundant I/O adapter and SAN traffic, etc.) than hardware based alternatives.

Prior to implementing ASM mirroring, take a moment to consider I/O storage system options that might be available.

? RAID-5 and RAID-10 arrays provide data redundancy protection against single disk
failures.

? Volume Copy and Remote Volume Copy can be used to mirror logical drives within a single DS4000 storage system or between multiple DS4000 storage systems."

Striping disk in an AIX environment

The stripe size is important for full-scan operations in Oracle (table access full, index fast-full scan) and IBM offers these suggestions for choosing an optimal RAID stripe size:

When choosing a PP size or LVM stripe size for VGs containing RAID-5 or RAID-10 based hdisks, it is generally a good idea to choose a value that is several times the stripe size used in the underlying RAID array - typically, 1MiB or larger.

The one major exception to this would be for single-threaded sequential I/Os streams with a throughput capacity greater than the throughput capacity of a single RAID array.

Oracle with AIX CIO

Oracle on AIX offers thee main I/O options, and the DBA needs to pick the one that's right for their database:

JFS - Journaled File System
CIO - Concurrent I/O (uses JFS)
DIO - The Direct I/O option

AIX has the Concurrent IO (CIO) method within their JFS filesystem, allowing the use of JFS while achieving fast I/O (almost as fast as raw devices). Raw partitions in AIX are quite cumbersome and CIO is a great alternative since you get the ease of administration and the speed of ?almost raw?.

However, high-DML database may want to use direct I/O (DIO instead of JFS or CIO. See these notes on using Oracle AIX with direct I/O which is implemented with the AIX "dio" option and the init.ora parameter filesystemio_options:

Oracle init.ora: filesystemio_options = SETALL

JFS2 mount option: mount ?o dio /oradata/ts1.dbf

IBM says that direct I/O (DIO) tends to benefit heavily random access workloads while CIO tends to benefit heavily update workloads.

Enabling CIO for Oracle AIX

Within Oracle, the CIO feature can be enabled in two ways:

Oracle init.ora: filesystemio_options = SETALL

JFS2 mount option: mount ?o cio /oradata/ts1.dbf

The IBM documentation ?Configuring IBM System Storage DS4000 Series For Oracle Database Applications? (October 2015) notes special considerations for AIX blocksizes:

?When CIO is used, the POSIX standard filesystem level I/O serialization mechanisms are disabled, which avoids inode contention issues.

If planning to use JFS2 CIO, be sure to isolate online redo logs and control files in a separate file system that was created with agblksize=512.

File systems which contain Oracle data (.dbf) files should be created with agblksize=4096 if db_block_size >= 4K or agblksize=2048 if db_block_size=2K.

On CIO-mounted filesystems that contain datafiles, the database block size needs to be multiple of agblksize.

This is not something that customers need to worry about except if the database block size is 2KB, in which case you need to make sure agblksize is 2KB, 1KB or 512 bytes.?

The IBM documentation also suggests that some Oracle workloads may benefit more from full JFS than from CIO, but in my experience, only the Oracle redo logs should be on a JFS filesystem.

?Not all workloads benefit from the use of CIO (i.e. they get more benefit from filesystem caching). In some cases, it may be desirable to create multiple filesystems and allocate files based on their anticipated workload characteristics.

Therefore, individual filesystems could be mounted in CIO mode, or default caching mode, depending on the I/O characteristics of the files within the particular filesystem.?

See my related AIX notes here: