 |
|
Oracle disk I/O on Linux Tips
Oracle Database Tips by Donald Burleson |
With Linux becoming the most
popular OS for Oracle, many professionals have questions about how to manage
disk I/O for Linux Oracle databases.
I've devoted over a hundred pages in my book "Oracle
Tuning: The Definitive Reference" to Linux disk I/O management,
but we still have the issue that super-large disks
will impose enqueues because the mechanical device can only relocate to a single
cylinder at a time.
On busy Oracle databases on a single disk
spindle, the disk can shake like an out-of-balance washing machine as competing
tasks enqueue for data service.
There are several ways to minimize disk I/O for
Oracle on Linux:
- Large data buffers
- The 64-bit Linux allows for super-large data buffers. The
new
solid state disks provide up to 100,000 I/Os per second, six times
faster than traditional disk devices.
- Multiple blocksizes
- I/O segregation with multiple blocksizes (i.e. indexes on a 32k blocksize)
provides additional I/O manageability. This is especially important if
you are doing full-scans in Linux with
multi-block reads.
- Linux Direct I/O -
Always make sure that you are using direct I/O.
Linux systems support direct I/O on a
per-filehandle basis (which is much more flexible) with the O_DIRECT
parameter. See
Kernel Asynchronous I/O (AIO) Support for Linux.
Linux datafile I/O
management for Oracle
Understanding the Linux I/O
calls (Completely Fair Queuing (CFQ), Deadline I/O scheduling, NOOP I/O and
Anticipatory I/O). This guy has a write-up on
Linux kernel I/O for large Oracle systems in Linux. Also full-scan
access speed is aggravated by Oracle willy-nilly block placement in Automated
Storage Management (ASM) and using bitmap freelists (Automated Segment Storage
Management).
The problem with most large
Linux Oracle databases is that the
super-large
disk devices have introduced seek-time latency, as the read-write heads
traverse between the cylinders.
David Aldridge notes this seek
latency issue in Linux and suggests how changing I/O drivers may be an option for
very large Oracle Linux databases:
"When Oracle is performing
a full table scan using parallel query it is continually issuing read
requests of around 1Mb (for example) for a large set of blocks that are
contiguous.
Hence there ought to be little or no latency due to disk head
movement.
When another parallel query
slave, possibly for the very same query as the first, is also trying to
retrieve a large set of contiguous data the danger is that the disk head
will continually be flicking around between the two processes, incurring
latency each time it does so.
The most efficient
scheduling method would therefore appear to me to be one that allows the
second process to wait while satisfying more requests from the first
process, thus reducing the disk head movement and increasing the rate of
blocks read from disk."
Seek time (read-write head movement
remains the largest component of Linux I/O latency. The Oracle
professional can work-around this issue by intelligently placing high I/O data files in
the middle absolute track number to minimize read-write head
movement, allocating "hot" data
files near the middle absolute track of the disk spindle:
Oracle has another approach in the
Oracle 11gr2 "ASM intelligent file placement" feature.
In
intelligent file placement the data file is broken down into "hot" and
"cold" disk platter areas, leveraging on the fact that the outermost sectors
of a disk contain more space per revolution than the inner "cold" sectors.
Using this ASM feature you can direct Oracle to place a datafile (or ASM
template) onto the "hot" area of the disk, the outermost cylinders.
See
ASM intelligent
file placement Tips.
Oracle 12c automatic data
optimization
In a manual system, popular data ages-out and become
less popular and read-only. In
this approach to data lifecycle management, aged-out popular data is
automatically compressed and move to a lower-tier tertiary storage (disk or
tape).
Finding disk
I/O bottlenecks in Linux
The majority of the wait time in most
large Linux Oracle databases is spent accessing data blocks. You can
also run
STATSPACK I/O queries to see Linux disk I/O details.
Top 5
Timed Events
% Total
Event
Waits Time (s) Ela Time
--------------------------- ------------ ----------- --------
db file sequential read
2,598 7,146
48.54
db file scattered read
25,519 3,246
22.04
library cache load lock
673 1,363
9.26
CPU time
44 1,154
7.83
log file parallel write
19,157 837
5.68
Far and away, the
easiest way to spot hidden Linux I/O bottlenecks is with
Ion, where the sources of
the Linux disk I/O contention become immediately apparent.
Ion is the most useful because it tracks workload-related I/O
bottlenecks that are often too transient to see with scripts:
The
Ion tool is amazing
at spotting hidden I/O trends on Linux databases. I
rarely recommend GUI tools, but Ion is one exception
because it removes the tedium of running dozens of scripts
to locate Linux disk I/O contention.
References on Linux I/O
for Oracle
and include the URL for the page.
