|
Oracle is a data delivery engine, and accessing
data from disks often imposes a system bottleneck.
Also see
ASM Optimal
Disk Placement (ODP)
In my book "Oracle
Disk I/O Tuning" that placing too much data on a single disk
spindle will impose enqueues because the mechanical device can only
locate 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.
Originally, RAID was an acronym for INEXPENSIVE
DISKS, and Oracle professionals enjoyed the ability of spreading
their Oracle data across multiple disks and spreading the load.
Today's large disk arrays commonly utilize asynchronous write and
multi-gigabyte RAM buffers to minimize this latency, but it still is
a major concern for thousands of Oracle shops.
According to Mike Ault's book "Oracle
Solid State Disk Tuning", smaller solid-state RAM disks have far
less bandwidth issues because the RAM architecture of SSD allow high
concurrent access that is impossible on a mechanical platter.
This issue of single channel access also
imposes a bottleneck on Oracle disk devices, and the large disks
(over 144 gigabytes) often perform more slowly for high concurrent
access than their smaller predecessors.
-
Oracle's standard SAME (Stripe and Mirror
Everywhere, RAID 10) is largely useless for load balancing if
the whole database resides on just a few physical disks.
-
Seek delay (movement of the read-write
heads) composes over 80% of disk access latency, and high
concurrent requests against large devices make them very slow.
Disk enqueues can occur when the disk is unable to quickly service concurrent
requests. Super-large disks can be problematic, and the most popular
Oracle data files can be placed on the middle absolute track of the device to
minimize read-write head movement.
As manufacturing costs continue to fall, disk
vendors are offering larger and larger disks and this is imposing
some serious large disk
performance issues with seek delay is 1.5 times slower:
"In the "good old days" when 9G disks were
big, we didn't have this problem. Really, this
problem is new since then. Back then, if we
wanted 200G of storage RAID1, we needed about 45
of those disks.
Controllers could only handle 7 of them, you see
(the 8th device on the bus was the controller
itself) and that meant we had proportionally
lots more access roads, and lots more loading
docks per square foot of warehouse space than
you typically have today. . .
I note:
- The seek time is
actually
slower today.
- The bandwidth performance is maximum
only 10 times better
- Your controller is no more than 32
times faster
-
The disk, however is about 83
times bigger!"
As a result of this trend, many Oracle
professionals experience external I/O waits and they see that the
top-5 waits events (from a STATSPACK or AWR Report) show "db file
sequential reads" and "db file scattered reads" as a main system
bottleneck.
Solutions to the large disk
plague?
Obviously, the Oracle professional must take
action to relieve disk I/O bottlenecks. There are several
solutions to this issue:
-
Use large data buffer caches - The
majority of the Oracle 10g benchmarks (
www.tpc.org ) use 64-bit Oracle
with a db_cache_size over 50 gigabytes. Other large shops
segregate I/O into multiple data buffers by using Oracle
multiple blocksizes.
-
Get higher bandwidth storage
- Some
Oracle shops purchase the more-expensive smaller devices or disk
with fixed read-write heads (Winchester technology). Other
embrace SSD arrays which have unprecedented bandwidth for high
concurrent access since
Oracle SSD clobbers disk access speed.
-
ASM intelligent file
placement: Oracle 11gr2 introduces
ASM intelligent
file placement a new approach to speeding-up disk throughput
on very large disk platters. With ASM, the DBA marks "hot"
data files (or ASM templates) and Oracle relocates them to the
outermost sectors where you can retrieve more data per disk
revolution:
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).
My other notes on Oracle disks include:
|
and include the URL for the page.
