 |
|
Managing Oracle disk I/O in AIX
Oracle Tips by Burleson Consulting
November 12, 2007
|
Managing Oracle disk I/O in AIX
The IBM document "Configuring
IBM System Storage DS4000 Series For Oracle Database Applications" (October
2007) 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 - Journalled 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
or
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
or
JFS2 mount option: mount –o cio
/oradata/ts1.dbf
The IBM documentation “Configuring IBM System Storage
DS4000 Series For Oracle Database Applications” (October 2007) 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:
