 |
|
Why use a small data block buffer cache with solid-state disks?
Oracle Tips by Burleson Consulting |
When less is more . . .
Question: I bought your book "Oracle
RAC & Grid Tuning with Solid-State Disk". It's a Great book! Can you
explain the technique of reducing cache size to "force" usage of the SSD?
I just don't "get" why reducing cache (i.e. reducing available RAM memory)
results in faster execution times! It's soooo counter-intuitive to me! The
reason I find it so hard to get my head around this is that the only resources
available in that scenario are RAM memory or SSD.
Surely RAM memory is faster than SSD, so why is forcing the database to use
SDD over RAM memory such a BIG win? I am simply not understanding the
"physics" behind the results!
Answer: This is a great set of
questions! Oracle has spent a lot of time and money optimizing IO to make it as
fast as possible given the constraints of physical devices, remove the physical
latency issues and all of that enhancement adds up to better performance when
the physical latencies are removed. Let's examine each option:
Option 1: Large RAM data buffers,
solid-state disk files (SSD)
In this option we have higher overhead within Oracle, as he tries to
manage the heap to reduce I/O, which is now a negligible time expense
(RAM-to-RAM data transfer is very fast).
Option 2: Small RAM data buffers with solid-state disk files (SSD)
In this option we force a read from SSD into a tiny data buffer.
The overhead of the repeated loads is negligible, and we have spaced Oracle
from having to manage a giant db_cache_size.
>> The reason I find it so hard to get my head around this is that the
only resources available in that scenario are RAM memory or SSD.
Yes, 100% solid-state is here today and SSD is inexpensive and lightening
fast. I have many clients who are solid-state today and they love it,
with I/O up to 300x faster, overnight. In 1986, it costs over $200,000
for 1.2 gig of disk, and today you can buy 200 gig of SSD for about the same
price, it's amazingly cheap. Remember, disk is a 1960's technology
that is already obsolete for real-time online storage (it's painfully slow),
and disk is now used in many shops for archiving backups (like tape used to
be used for back in the 1970's).
>> Surely RAM memory is faster than SSD
Ah, but SSD is RAM! SSD has the same super-fast access speeds, plus
it backs-up to disk without effecting performance (SSD has much higher I/O
bandwidth than disk). Bandwidth is very important today, especially
with the current
plague
of super-large disks. Disk bandwith is more important than
RAM speed, which has been relatively "flat" for the past 30 years, while
everything else (disk, network, CPU) sees radically improved speed every
year:
Because RAM speed has remained unchanged, RAM resources
must be "localized" for optimal performance.
In sum, SSD is real RAM, just like the data buffer.
>> I just don't
"get" why reducing cache (i.e. reducing available RAM memory) results in faster
execution times!
Today, many Oracle database have shifted from being I/O bound (a 32-bit
RAM constraint) to CPU bound (data buffer gets drive-up CPU consumption.
This I/O shift led Oracle to make the great change to the cost-based SQL
optimizer. Traditionally, the decision trees were built from estimated
I/O costs, and this default change in 10g such that the SQL optimizer (the
CBO) build his decision tree values based on estimated CPU costs.
(This is why Oracle shops that are not 64-bit (I/O bound) will want to
change the costing back to the earlier value of
"_optimizer_cost_model"=io.
The only reason for having a data buffer cache is to reduce the probability
of having to re-read the data block repeatedly from disk. When we have
no more disk, the data buffer becomes redundant.
It would not surprise me if a future release of Oracle allowed for
solid-state disks and removed the data buffer cache, but for now, the SSD block
must be transferred into the data buffer to allow Oracle to manage the locks
required for integrity and read consistency.
If we think of the data buffer as
nothing more than a place for Oracle to set locks, then we can understand why a
smaller data buffer has faster performance. If we have duplicitous RAM
(once on the SSD and yet again in db_cache_size), then we see higher
management overhead from Oracle:
Large data buffers take more time for
standard management tasks (i.e. DBWR), and in many cases, smaller is better.
It's the exact same techniques is used in the shared pool when it becomes
clogged with non-reentrant SQL statements (e.g. SQL without host variables).
Before the cursor_sharing=force parameter, we made a super-tiny
shared_pool_size tiny, and performance radically improved. Why?
Because the shared pool was no longer necessary (all SQL was unique) and there
was no benefit from caching SQL statements which would never be re-used.
Market Survey of SSD vendors for
Oracle:
There are many vendors who offer rack-mount solid-state disk that
work with Oracle databases, and the competitive market ensures that
product offerings will continuously improve while prices fall.
SearchStorage notes that SSD is will soon replace platter disks and that
hundreds of SSD vendors may enter the market:
"The number of vendors in this category could rise to several
hundred in the next 3 years as enterprise users become more familiar
with the benefits of this type of storage."
As of June 2008, many of the major hardware vendors (including Sun and
EMC) are replacing slow disks with RAM-based disks, and
Sun announced that all
of their large servers will offer SSD.
As of June 2008, here are the major SSD vendors for Oracle databases
(vendors are listed alphabetically):
2008 rack mount SSD Performance Statistics
SearchStorage has done a comprehensive survey of rack mount SSD
vendors, and lists these SSD rack mount vendors, with this showing the
fastest rack-mount SSD devices (as of May 15, 2008):
|
manufacturer |
model |
technology |
interface |
performance metrics and notes |
|
Texas Memory Systems |
RamSan-400 |
RAM SSD |
Fibre
Channel
InfiniBand |
3,000MB/s random
sustained external throughput, 400,000 random IOPS |
|
Violin Memory |
Violin 1010 |
RAM SSD
|
PCIe |
1,400MB/s read,
1,00MB/s write with ×4 PCIe, 3 microseconds latency |
|
Solid Access Technologies |
USSD 200FC |
RAM SSD |
Fibre Channel
SAS
SCSI |
391MB/s random
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
320,000 IOPS |
|
Curtis |
HyperXCLR R1000 |
RAM SSD |
Fibre Channel
|
197MB/s sustained
R/W transfer rate, 35,000 IOPS |
Choosing the right SSD for Oracle
When evaluating SSD for Oracle databases you need
to consider performance (throughput and response time), reliability (Mean Time Between failures) and
TCO (total cost of ownership). Most SSD vendors will provide a
test RAM disk array for benchmark testing so that you can choose the
vendor who offers the best price/performance ratio.
Burleson Consulting does not partner with any SSD vendors and we
provide independent advice in this constantly-changing market. BC
was one of the earliest adopters of SSD for Oracle and we have been
deploying SSD on Oracle database since 2005 and we have experienced SSD
experts to help any Oracle shop evaluate whether SSD
is right for your application. BC experts can also help you choose
the SSD that is best for your database. Just
call 800-766-1884 or e-mail.:
 for
SSD support details. DRAM SSD
vs. Flash SSD
With all
the talk about the Oracle “flash cache”, it is important to note that there
are two types of SSD, and only DRAM SSD is suitable for Oracle database
storage. The flash type SSD suffers from serious shortcomings, namely
a degradation of access speed over time. At first, Flash SSD is 5
times faster than a platter disk, but after some usage the average read time
becomes far slower than a hard drive. For Oracle, only rack-mounted
DRAM SSD is acceptable for good performance:
| |
Avg. Read speed |
Avg. write speed |
| Platter disk |
10.0 ms. |
7.0 ms. |
| DRAM SSD |
0.4 ms. |
0.4 ms. |
| Flash SSD |
1.7 ms. |
94.5 ms. |
|
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
