Excellent summary. Two additional point:
One additional point, which was the critical factor in what I believe were the
first serious experiments done with solid state disk drives (which happened to
NOT be Flash, but rather VAX VMS RAM drives) on Oracle in the fall of 1993 or
1994:
The biggest performance win from putting redo logs on SSD comes from avoiding
the small and frequent seek interruptions to your HDD farm. Even if you isolate
redo to a specific disk (or four specific disks for mirrored ping ponging), the
interruptions to all the pathways on the i/o stack remain.
In cases where the SSD media uses a distinct i/o stack from the bulk of your
media farm, this remains true.
All the information about some Flash SSD being slower than HDD for continuous
writes is not a germane unless you have a bottleneck on the actual physical
write part of log file sync. Drop both me and Kevin Closson a line if you have
an actual case of that involving a device isolated for redo log writing. (Any
of those write experiments that did not include archiving as well as the
original write are irrelevant unless you are not archiving in production.)
Kevin has a very fine blog about why you’re probably not bound on the physical
write of a log file anyway (see point 4 below). He quite well lampoons the idea
of putting redo on SSD for that specific purpose. (His article does NOT speak
to the de-heating seeks on the HDD farm as that is not his purpose in the blog.)
As SSD of all variety becomes more important, de-heating specific portions of
the media farm will become less important and the movement of data into and out
of CPU has already become the gating issue in many systems.
The second additional point is that the redo thread is your lifeline to
recovery. To the extent that putting it on SSD allows you to isolate plexes of
the SSD multiple times on portions of the physical stack that fail separately
from each other cost effectively, that is an important economic win whether or
not it improves performance.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Timur Akhmadeev
Sent: Wednesday, December 16, 2015 11:52 AM
To: RStorey@xxxxxxxxxxxxxxxxxx
Cc: Oracle L
Subject: Re: SSD usage
Hi
Few random thoughts on the topic:
1) SSD/flash world is growing and changing rapidly. Soon enough most storage is
going to be in some form of flash
2) SSD/flash is perfect for random reads and not so good for continuous writes
by design (search write cliff SSD). With your values for redo it most likely
shouldn't be an issue
3) anything under few hundred MB of redo/sec can be served with reasonable
latency by rotational disks provided you have a good write cache
4) log file sync waits are not only about writing redo, it's much more complex
than that, especially with modern oracle releases. If you need better advice
then you need to start with 1h statspack/awr report of your system
5) usually these days people use ASM and its striping for the best results from
all available disks, and rarely spend time deciding how to assign parts of
database to different drives.
On Wednesday, 16 December 2015, Storey, Robert (DCSO)
<RStorey@xxxxxxxxxxxxxxxxxx> wrote:
Morning,
I’ve been reading multiple docs on SSD/HDD and the benefits within an oracle
database.
If I am reading this write, for an OLTP system, the SSD/FLASH will provide
excellent random read/write abilities.
But folks seem contradicted on using the SSD for the Redo logs. Some say its
best practice, others say it does not buy you anything.
I have a 24/7 OLTP application that generates a modest amount of redo. My
overall DB is about 95 gigs, I generate about 600megs of redo in a day (10meg
files, switching every 30 or so). My system is getting to end of life, and I
see more log_file_sync waits, and buffer busy waits. I suspect my I/O
subsystems are getting worked hard. My top waits appear to be sequent file
reads and scattered reads, log file sync, and buffer busy.
What’s the general verdict on using SSD for the data files? Best idea for
redos? My thoughts there are separating them to different drives, ie, two
separate single disks, with a group member on each disk.
Thoughts?
Thanks
--
Regards
Timur Akhmadeev
