You wrote: "RDBMS systems are usually I/O bound, not CPU bound."
I doubt that is true, and I will explain why:
In, as my friend Kevin likes to note, "party like its 1999," Terascape
freely distributed Dipstick (you know, like checking your oil) to the
self-identified Oracle customers who thought they had a disk i/o problem.
Before Oracle provided segment level i/o statistics, some clever work was
involved in producing Dipstick, but that is another story.
Of that self-identified pool almost exactly 25% actually had a disk i/o
problem. (Now I have switched the problem to disk alone, but I believe that
is fair in context.)
This does NOT include I/O from memory to cpu, which often is the pacing
resource determining the maximum throughput of an RDBMS system and which
increases its "market share" as a pacing resource as persistent memory gets
faster and especially as seek times become proportional to address
calculations instead of mechanical head movements and data transfer rates
approach the speed of the communications wire stack instead of being limited
by rotational speed times data density.
I have not re-measured the data or surveyed. But I suspect that number is
about constant.
Of the total 25%, many were batch write limit rates on finely honed SQL
capturing the results of things like monthly rollovers and/or daily
transaction processing mixed with queries. The big thing SSD does for that
system profile is remove (most of ) the need to separate the write deposit
resources and read sources (rollover jobs do both, which is hilarious if the
source data and result data go to the same spinning device set, injecting an
oscillation of competing seeks if the job is not bound as a buffer grind
before it starts producing result rows.)
IF your system is like the above, HUGE win moving to SSD. And a lot less
work than segregating your batch i/o segments so that they own their i/o
stack when they are in play and scheduling batch jobs to not interfere with
each other.
If you've got a lot of code that presently is generating RAM memory into and
out of CPU, it won't help you much.
Then again it might not actually cost that much extra today. And putting it
all on media where seek times just don't matter that much should serve to
focus future activities on reducing LIOs driven by the code (if it is
possible).
If folks really understood how to determine whether they are actually disk
i/o bound, that would be a very interesting survey to re-do, with a note
about whether your system was SSD or spinners.
My view of the world is skewed by the fact I am usually only asked to look
at systems that are under severe stress, often involving code being ported.
Folks involved with storage systems likely are approached by people
predominantly interest in improving persistent i/o speed.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mladen Gogala
Sent: Friday, May 18, 2018 12:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Moving to flash storage
On 05/17/2018 10:15 AM, Matt Adams wrote:
It looks like we might be migrating the big production databases (2 of them
for a combined 40 Tb or so) to a NetApp 8080 Flash storage device. These
database have thousands of concurrent connections and turn over between 10 G
and 30 G of redo per hour.
I seem to vaguely recall a message or two here on the list over the last
couple of years regarding things to watch out for when migrating to flash
storage.
Well, the characteristics of the flash storage are very different from the
characteristics of the spinning disks. The first important difference is
that the difference between sequential access and random access is much
smaller. Make sure to gather new system statistics. Also, flash disks are
much more expensive than rotational disk. So much so that advanced
compression option suddenly starts making sense. The "compress for all
operations" can really save you some space and money. Benefits of the index
scan are much smaller with flash storage. Also, in my experience, using
larger block size like 16k can make some difference for some kinds of flash
memory. Talk to NetApp and ask them for their recommendations. Reading
bigger blocks in bursts can speed things up.
As for the transfer rates, you want 32 Gb/sec fibre channel adapters. It
doesn't get any faster than that. RDBMS systems are usually I/O bound, not
CPU bound. And flash is much faster variety of IO.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
