!
!
!
!
!!
!
!
!!!!!!
Marc!Staimer,!President!&!CDS!Dragon!Slayer!Consulting!
!
!
W
h
i
t
e
!
P
A
P
E
R
!
Database!Fatal!Flash!Flaws!No!One!
Talks!About!!!
And!How!to!Avoid!Them!
!
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
Database!Fatal!Flash!Flaws!No!One!Talks!About!
Marc!Staimer,!President!&!CDS!of!Dragon!Slayer!Consulting!
!
Introduction!
Flash! storage! has! been! touted! for! several! years! now! as! the! answer! to! inadequate! database!
performance.!! There! are! some! very! good! reasons! why! this! is! so.!! Flash! storage! is! generally! much! faster! than!
hard! disk! drives! (HDD).!! It! has! much! lower! latency,! much! greater! write! IOPS,! throughput,! and! read!
IOPS.!!Therefore!it!must!fix!database!performance!problems!!Not!so!fast.!!Sometimes!it!does!and!sometimes!it!
does!not.!!There!are!several!flash!issues!such!as!steady!state!performance,!write!amplification,!wear!life,!and!
electron!leakage!caused!high!bit!error!rates!that!can!sabotage!the!performance!gains!while!overwhelming!the!
budget.!!These!issues!(and!several!others!as!well)!are!rarely!discussed,!except!after!the!fact!when!performance!
is!no!longer!meeting!requirements.!
This! white! paper! examines! these! issues! in! detail! looking! at! how! they! specifically! affect! databases! and! the!
structured!applications!that!depend!on!those!databases.!!There!will!also!be!discussion!about!the!pros!and!cons!
of! common! workarounds.! It! then! examines! how!Tegile’s! Intelliflash™! storage! systems! actually! solve! those!
unpleasant!database!fatal!flash!flaws.!
!
!
Dragon!Slayer!Consulting!•!Summer!2014!
!
2
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
Table!of!Contents!
Introduction!.......................................................................................................................!2!
SQL!Database!Performance!Issues!......................................................................................!4!
How!DBAs!Frequently!Respond!...................................................................................................!4!
Tuning!.............................................................................................................................................!4!
ScaleVup!..........................................................................................................................................!4!
ScaleVout!.........................................................................................................................................!4!
Why!Flash!Storage!..........................................................................................................................!5!
General!Host!Based!Flash!Storage!Drives!Pros!and!Cons!...............................................................!5!
General!Flash!Storage!Hybrid!System!Pros!and!Cons!.....................................................................!5!
General!All!Flash!Array!(AFA)!System!Pros!and!Cons!.....................................................................!6!
Flash!Storage!SQL!Database!Surprises!............................................................................................!6!
Database!Fatal!Flash!Flaws!.................................................................................................!6!
How!Flash!Storage!Actually!Writes!and!Reads!Data!.......................................................................!6!
Flash!Idiosyncrasies,!Issues,!or!Flaws!That!Are!Often!Fatal!to!SQL!Databases!...............................!7!
How!Tegile!IntelliFlash™!Storage!Arrays!Solve!SQL!Database!Flash!Fatal!Flaws!...................!8!
Eliminating!The!Bits!Per!Cell!Tradeoffs!...........................................................................................!9!
Reducing!Write!Amplification!Increasing!Wear!Life!.......................................................................!9!
Eliminating/Mitigating!WriteVCliff!..................................................................................................!9!
Steady!state!performance!..............................................................................................................!9!
Eliminating!ReadVDisturb!................................................................................................................!9!
Correcting!High!BER!........................................................................................................................!9!
Tegile!IntelliFlash!SQL!Database!Proof!Points!..............................................................................!9!
Grass!Valley!....................................................................................................................................!9!
St.!Paul!Housing!Authority!..............................................................................................................!9!
USVYellow!Pages!...........................................................................................................................!10!
Unnamed!Hedge!Fund!..................................................................................................................!10!
In!Summary!......................................................................................................................!10!
For!More!Information!......................................................................................................!10!
!
!
Dragon!Slayer!Consulting!•!Summer!2014!
!
3
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
SQL!Database!Performance!Issues!
SQL! databases! are! typically! IOPS! (inputs! and! outputs! per! second)! intensive.! ! ! Meaning! that! they! require! the!
compute!infrastructure!to!deliver!high!amounts!of!IOPS!to!deliver!a!consistent!satisfied!user!experience.!!SQL!
database! IOPS! requirements! are! rapidly! increasing! because! of! application! proliferation! dependent! on! those!
databases.!!Those!IOPS!can!be!delivered!via!memory!or!storage.!!Memory!or!DRAM!is!a!very!expensive!option!
although! justifiable! in! some! cases.! ! Storage! is! much! less! expensive,! delivers! much! fewer! IOPS,! therefore!
requiring! a! lot! more! of! tehm! to! meet! modern! database! requirements.! This! puts! high! stress! on! the! storage!
infrastructure.!!!
How!DBAs!Frequently!Respond!
Database!administrators!(DBA)!have!been!endeavoring!to!cope!with!the!IOPS!performance!growth!for!years!by!
delving!into!their!bag!of!software!performance!enhancement!tricks.!Most!will!try!to!fix!the!IOPS!performance!
problem!before!adding!or!changing!their!storage!hardware.!!
Tuning!
One!of!the!first!things!they!attempt!is!HDD!IO!tuning.!!HDD!IO!is!optimally!sizing!SQL!database!files!and!then!
placing!them!on!different!LUNs!and!RAID!groups.!!Doing!this!delivers!the!maximum!hard!disk!drive!subsystem!
throughput.! ! But! it! requires! the! DBA! to! possess! extensive! SQL! database! as! well! as! storage! subsystem!
knowledge.!!Next!up!is!application!tuning,!which!is!another!way!of!saying!recoding!the!SQL.!!Application!tuning!
is!a!timeVconsuming,!laborVintensive!task.!!A!common!DBA!tuning!trick!is!to!pin!certain!data!into!memory!or!
cache.!!Doing!so!will!reduce!trips!to!the!disk!subsystem!and!decrease!query!latencies.!Another!tool!is!memory!
tuning!or!“rightVsizing”!the!SQL!database!buffers.!!Although,!this!one!tends!not!to!be!used!as!often!because!of!
its!hit!or!miss!nature.!!RightVsizing!SQL!database!buffers!is!as!much!art!as!science.!It!comes!from!experience!and!
DBA!intuition!based!on!“wait”!events,!buffer!hit!ratios,!system!swapping,!and!paging.!
Each!of!these!tools!is!dependent!on!the!DBA’s!inherent!expertise.!!All!of!them!take!effort,!labor,!and!time.!!And!
far!too!frequently!these!tuning!tricks!just!do!not!achieve!the!desired!performance!gains.!
When!tuning!fails!to!address!SQL!database!performance!issues,!the!next!step!is!to!spend!money.!!The!DBA!can!
scaleVup!the!database,!scaleVout!the!database,!or!buy!higher!IOPS!flash!storage.!
ScaleRup!
ScalingVup!is!the!process!of!moving!to!a!bigger,!more!powerful!server!for!the!SQL!database!such!as!64,!96,!128,!
or!256!processors!with!more!memory,!IO,!bandwidth,!PCIe!slots,!etc.!!It’s!simple!but!with!a!high!cost!in!both!
capital! expenditures! and! operating! expenditures.! ! Besides! more! expensive! hardware,! there’s! the! additional!
software!costs.!!SQL!database!licensing!escalates!rapidly!because!they’re!based!on!cores.!!ScalingVup!assumes!
processing!is!the!performance!bottleneck!and!that’s!rarely!the!case.!!
ScaleRout!
ScalingVout!is!the!next!approach!and!has!two!distinctive!options.!The!first!option!is!based!on!spreading!queries!
across! multiple! unique! database! instances.! It! requires! more! instances! of! the! current! database! with! a! twist.!!
There!are!several!variations!of!this!technique!including:!!
•
Sharding! (the! process! of! dividing! database! data! along! a! specific! application! boundary! among! multiple!
database!instances);!!
•
Read!only!slaves!(writes!are!dedicated!to!a!master!database!then!replicated!or!mirrored!to!the!readVslaves!
where!all!reads!are!routed);!!
•
PeerRtoRpeer! replication! (only! used! when! database! updates! are! infrequent,! maintains! multiple! database!
copies!where!replication!is!required!to!update!the!other!database!engines!when!a!change!has!been!made);!!
•
Linked!servers!and!distributed!queries!(local!database!queries!remote!databases!as!if!they!were!part!of!
the!local!database);!!
•
Distributed! partitioned! views! (ideal! for! update! intensive! apps,! the! primary! SQL! database! table! data! is!
partitioned!among!tables!in!numerous!distributed!databases!based!on!a!partitioning!key);!!
•
Data! dependent! routing! (partitions! the! data! to! multiple! databases! while! placing! responsibility! on! the!
application!or!middleware!to!route!the!queries!to!the!correct!database).!!!
Spreading! queries! across! multiple! unique! database! instances! is! not! for! amateurs! requiring! wideVranging! DBA!
knowledge,! skill,! expertise,! as! well! as! continuous! adjustments! and! tuning.! ! The! underlying! database!
Dragon!Slayer!Consulting!•!Summer!2014!
!
4
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
performance!problem!assumption!is!that!it’s!the!result!of!database!size!or!scale.!!Sometimes!that!is!the!case.!!
And! when! it! is,! it! can! sometimes! be! solved! by! spreading! queries! across! multiple! unique! database! instances!
leveraging!the!additional!compute!nodes!and!database!instances.!
Option! two! is! the! implementation! of! clustered! or! distributed! database.! ! Both! utilize! multiple! commoditized!
servers.!!Clustered!SQL!databases!are!a!shared!everything!architecture.!!It!creates!a!bigger!database!via!the!use!
of! clustering.! ! Distributed! SQL! databases! are! a! shared! nothing! architecture! built! on! grid! technology! (the!
underpinnings!of!cloud!technology)!for!elasticity!and!scalability.!!They!tend!to!require!less!DBA!expertise!and!
budget! than! multiple! instances! of! standard! SQL! databases;! however,! it’s! a! ripVout! and! replacement! of! those!
traditional!databases.!!And!once!again,!the!underlying!database!performance!problem!assumption!is!that!the!
bottleneck! is! not! enough! compute! power.! ! When! that’s! the! case! clustered! or! distributed! SQL! databases! can!
resolve!the!performance!issue.!
All! of! the! DBA! issues! discussed! up! until! this! point! have! been! manually! laborVintensive! options.! ! They’re! time!
consuming,!often!frustrating,!and!don’t!always!solve!the!problem.!!This!is!why!so!many!DBAs!have!turned!to!
flash!storage!as!the!answer!to!their!performance!issues.!!!
Why!Flash!Storage!
Flash! storage! (a.k.a.! NAND)! drives! produce! thousands! to! millions! of! IOPS! in! a! small!
package.! Flash! storage! is! fast! producing! as! much! as! 3! orders! of! magnitude! more!
random! IOPS! than! Enterprise! HDDs.! ! It! also! draws! much! less! power! and! cooling!
(reduced! operating! expenses)! than! HDDs! because! there! are! no! moving! parts,! does!
not! require! an! entire! flash! drive! to! be! rebuilt! when! a! block! fails,! and! does! not!
fragment! as! HDDs! do! eliminating! defragmentation! requirements.! ! But! it’s! those!
incredible!IOPS!that!make!flash!storage!of!all!kinds!so!attractive!to!the!DBA.!!Just!as!
importantly,!flash!storage!does!not!preclude!the!use!of!any!of!the!other!performance!
enhancing!efforts!just!discussed.!!Flash!storage!is!in!fact!SQL!database!methodology!agnostic.!!It!can!and!does!
for!the!most!part!instantly!increase!the!SQL!database!IOPS.!
SQL! database! flash! storage! is! implemented! as! either! host! flash! storage! drives! (DDR3! DIMMs,! PCIe! cards,! or!
SAS/SATA! solid! state! drives! (SSD)),! or! as! a! SAN! block! (iSCSI! and/or! Fibre! Channel)! connected! or! NAS! (NFS! or!
SMB)!file!system!connected!flash!storage!system.!!Flash!storage!systems!come!in!hybrid!storage!systems!(mix!
of!SSD!and!HDD)!and!all!flash!arrays!(AFA).!!Both!flash!storage!drives!and!flash!storage!systems!have!their!pros!
and!cons.!!
General!Host!Based!Flash!Storage!Drives!Pros!and!Cons!
The! host! based! flash! storage! drives! SQL! database! “pros”! include:! lowest! SQL!
database! to! flash! storage! latency;! simple! implementations/operations;! and!
relatively!low!$/IOPS.!!!
The! host! based! flash! storage! drive! SQL! database! “cons”! include:! limited!
1
scalability;! limited! ECC;! limited! DRAM;! NAND! chip! quality;! cost! in! $/GB! capacity;! TCO! is! high;! caching ! often!
limited! to! write! through! (read)! caching;! write! back! caching! creates! shorter! wearVlife! and! greater! writeV
amplification;!difficult!to!impossible!pinning!SQL!database!indexes!and/or!hot!data!into!the!flash!storage!cache;!
and!flash!storage!availability,!resilience,!continuity,!and!data!protection!are!nominal!at!best.!!!In!addition,!flash!
storage!in!one!host!is!not!addressable!from!another!without!additional! costly!licensed!software!that!clusters!
the!hosts!and!storage.!
General!Flash!Storage!Hybrid!System!Pros!and!Cons!
Flash! storage! hybrid! system! SQL! database! “pros”! include:! high! performance! IOPS! for! active! data! with! lower!
cost! storage! of! inactive! or! cold! data! (Dragon! Slayer! Consulting! surveys! put!
inactive!data!as!>!90%!of!all!IT!organizational!data);!inline!deduplication!and/or!
compression! increases! both! flash! and! HDD! usable! capacities! with! nominal!
impact! on! latency! and! performance! even! improving! performance! by! keeping!
more! data! in! cache;! excellent! total! usable! capacities;! exceptional! data!
protection! and! resilience! (zero! space! snapshots,! clones,! replication,! and! ECC);!
relatively!low!overall!$/IOPS;!relatively!low!cost!in!$/GB.!!!
1
!Caching!is!a!mandatory!requirement!when!data!must!be!shared!on!external!storage!such!as!when!a!hypervisor!is!involved.!
Dragon!Slayer!Consulting!•!Summer!2014!
!
5
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
!Flash! storage! hybrid! system! SQL! database! “cons”! include:! inline! dedupe! may! have! a! noticeable! negative!
impact!on!SQL!database!latency!and!performance;!there!are!several!hybrids!with!somewhat!constrained!usable!
flash!capacities;!hybrids!that!do!flash!caching!ordinarily!use!only!writeVthrough!(read)!caching;!may!not!be!able!
to!pin!or!fix!SQL!database!indexes!and/or!hot!data!into!the!flash!storage!cache;!storage!tiering!forces!all!writes!
to!start!on!flash!increasing!write!amplification!and!shortening!wearVlife!(same!issue!with!write!caching);!$/IOPS!
is!lower!than!HDD!systems!and!higher!than!AFAs;!$/GB!is!lower!than!AFAs!and!higher!than!all!HDD!systems.!!!
General!All!Flash!Array!(AFA)!System!Pros!and!Cons!
AFA! system! SQL! database! “pros”! include:! large! amounts! of! total! storage!
system! IOPS;! inline! deduplication! and! compression! increases! both! flash!
usable! with! nominal! impact! on! latency! and! performance;! fair! to! quite! good!
total!usable!flash!capacities;!exceptional!data!protection!and!resilience!(zero!
space!snapshots,!clones,!replication,!and!ECC);!best!overall!$/IOPS;!okay!but!
improving! cost! in! $/GB! especially! when! dedupe! and! compression! are! taken!
into!account!and!the!capacity!is!usable!capacity.!!!
AFA! system! SQL! database! “cons”! include:! inline! dedupe! may! have! a! noticeable! negative! impact! on! SQL!
database! latency! and! performance;! maximum! flash! usable! capacities/scalability! lags! behind! hybrid! and! HDD!
storage! systems;! write! amplification! tends! to! be! higher! than! hybrids! that! use! writeVthrough! cache! creating!
shorter!wearVlife!(same!issue!as!write!caching!or!storage!tiering);!$/GB!is!currently!higher!than!all!other!storage!
systems!(but!the!gap!is!shrinking).!!!
Flash!Storage!SQL!Database!Surprises!
DBAs! find! that! while! flash! storage! of! all! kinds! is! initially! an! IOPS! performance! godsend! that! performance!
frustratingly! declines! almost! immediately! degenerating! fairly! rapidly! until! it! levels! off! at! a! much! lower! level.!!
The! test! performance! is! higher! than! the! production.! ! This! is! the! result! of! planning! to! firstVoutVofVbox!
performance!versus!steady!state.!!Anyone!with!a!flash!storage!based!laptop!experiences!this.!!That!and!several!
other! flash! storage! performance! and! data! corruption! issues! from! electron! leakage,! read! disturb,! and! cell!
failures!will!reduce!SQL!database!performance!as!well!as!stability!if!they’re!not!properly!handled.!!!!
Database!Fatal!Flash!Flaws!
Most! nonVstorage! experts! have! a! lot! of! misconceptions! about! flash! storage.! ! From! how! data! is! programmed!
(written! to),! how! it’s! erased,! even! issues! caused! by! excessive! reads! are! an! annoying! surprise! to! most! DBAs.!!
Flash! caching! is! yet! another! area! of! confusion.! ! To! understand! the! flash! issues! that! cause! DBAs! heartburn!
requires!a!brief!level!set!on!how!flash!storage!actually!works.!
How!Flash!Storage!Actually!Writes!and!Reads!Data!
Flash!storage!is!a!nonVvolatile!storage!medium.!!Data!can!be!written!to!it!(programmed)!and!erased!from!flash!
storage.! ! The! two! different! types! are! NOR! and! NAND.! ! Based! on! price/performance! reasons,! today! the! vast!
majority!of!commercial!flash!storage!is!NAND.!!Data!storage!in!general!is!recorded!as!binary!zeros!and!ones.!!
For!HDDs!it’s!based!on!the!magnetic!polarity!of!plus!or!minus.!!Flash!storage!is!different!and!nonVintuitive!in!
how! it! captures! and! changes! data.! ! It’s! electrical! storage! not! magnetic! storage.! ! Flash! states! are! based! on!
voltage!levels!within!a!cell!measured!as!bits!per!cell.!!One!bit!per!cell!is!call!single!level!cell!(SLC).!Two!bits!per!
cell!is!called!multiVlevel!cell!(MLC).!!Three!bits!per!cell!is!call!tripleVlevel!cell!(TLC).!!SLC!has!two!unique!states!
per!cell!(0,1),!MLC!has!four!unique!states!per!cell!(00,!01,!10,!11),!and!TLC!has!eight!unique!states!per!cell!(000,!
001,!010,!011,!111,!110,!101,!100).!!The!amount!of!voltage!required!reading!a!cell!increases!with!the!number!of!
states.!!This!has!a!huge!impact!on!the!flash!performance!(latency),!bit!error!rates!(BER),!and!life!expectancy!or!
programVerase!cycles!(P/E)!also!known!as!writeVerase!cycles.!P/E!cycles!are!the!number!of!times!a!flash!cell!can!
be!written!to!and!erased!before!it!essentially!stops!functioning.!It’s!colloquially!known!as!flash!“wearVlife”.!!!
Unlike! HDDs,! flash! stored! data! cannot! be! changed! in! place.! ! This! is! because! of! the! way! flash! storage!
programs/writes!and!erases!data.!!Data!is!written!in!pages!of!256!bytes,!2K!bytes,!or!4K!bytes!plus!a!few!bytes!
for! storing! error! correcting! code! checksums.! ! Pages! are! combined! into! blocks.! ! Blocks! are! commonly! 16KB,!
128KB,!256KB,!or!512KB.!!Data!is!written!randomly!in!bytes!but!can!only!be!erased!in!blocks.!!In!fact,!once!a!
block!has!been!written!to!it!cannot!be!written!to!again!until!it’s!erased.!!That!means!if!only!2KB!is!written!to!a!
512KB!block,!the!remaining!510KB!is!inaccessible!until!the!whole!block!is!erased.!Each!time!a!block!is!erased!
the!wearVlife!is!shortened.!!That!unused!capacity!now!has!a!shorter!wearVlife.!!Known!as!writeVamplification,!
it’s!just!one!flash!idiosyncrasy!that!has!serious!database!consequences.!!
Dragon!Slayer!Consulting!•!Summer!2014!
!
6
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
Flash!Idiosyncrasies,!Issues,!or!Flaws!That!Are!Often!Fatal!to!SQL!Databases!
There! are! seven! key! flash! idiosyncrasies,! issues,! or! flaws! that! can! have! a! meaningful! negative! effect! on! SQL!
database!performance.!!They!are:!bits!per!cell!tradeoffs,!P/E!cycles!(wearVlife),!write!amplification,!write!cliff,!
steady!state!performance!(versus!firstVoutVofVbox!performance!or!FOB),!read!disturb,!and!high!bit!error!rates.!!
Bits%per%cell%tradeoffs!are!determined!by!the!flash!storage!being!SLC,!eMLC!,!MLC,!and!TLC.!More!bits!per!
cell!results!in!lower!cost!flash!storage!but!also!lower!performance.!!As!bits!per!cell!increase!so!does!the!
voltage!required!to!program!and!read!the!cell.!!Increased!voltage!adds!latency,!increases!bit!errors,!and!
reduces!flash!storage!wearAlife.!!SLC!has!highest!performance,!lowest!latency,!longest!wearAlife!
(measured!in!P/E!cycles)!and!highest!cost!per!GB,!whereas!TLC!the!slowest!performance,!highest!latency,!
shortest!wearAlife,!and!lowest!cost!per!GB.!!The!eMLC!and!MLC!flash!storage!fall!inAbetween!with!eMLC!
tending!towards!SLC!and!MLC!tending!towards!TLC.
SQL database performance consequences: SQL database performance will obviously vary by the flash
type utilized. But there is a value tradeoff of cost versus performance measured as cost/IOPS that must
be taken into account. In addition, at some point the SQL database performance bottleneck will be
pushed into the storage controller or the SQL database server resources making additional flash IOPS
impossibleBtoButilize.
P/E%cycles%(wear5life)!are!determined!by!flash!type!(SLC,!eMLC,!MLC,!TLC).!!SLC!is!typically!rated!between!
100K!and!1M!P/E!cycles,!eMLC!from!30K!to!40K!P/E!cycles,!MLC!ranges!between!3K!to!10K!P/E!cycles,!
and!TLC!from!500!to!1K!P/E!cycles.!Wear!leveling!algorithm!techniques!in!the!flash!storage!drive!help!
mitigate!the!issue;!although,!it!often!requires!more!sophistication!and!processing!power!found!in!a!
storage!array!system!for!best!results.
SQL database performance consequences: Flash storage wearDlife can be a major problem for SQL
databases when it comes to data reliability. Cells, pages, and blocks that wear out can cause data loss
thatBdestabilizesBtheBSQLBdatabase.BB
Write amplification shortens the flash usable capacity wearAlife because unused capacity is consistently
erased without ever being programmed or written to. There tends to be a series of tradeoffs when it
comes to write amplification. Smaller flash storage blocks reduce the amount of waste and write
amplification.!!The!downside!being!a!much!greater!number!of!blocks!for!the!flash!drive!controller!to!track!
for data obsolescence (data no longer valid), being marked for erasure (a.k.a. garbage collection), then
being made available in the pool of writeable blocks. That additional processing adds latency, reduces
IOPS, and requires additional flash storage controller DRAM. Large block sizes reduce flash drive
controller processing and latency but has higher wearAlife. The better bet is when the flash storage array
system caches writes to flash in DRAM and serializes the data to cache. Doing so very nearly eliminates
write amplification. It also enables costly system DRAM to be shared across all system flas(ECC) that
often!only!occur!in!flash!storage!systems.
SQL database performance consequences: Reduced write amplification is good, but the extra cost and
higher latency diminishes SQL database performance. Increased write amplification will cause faster cell,
page, and block failures leading to data loss and corrupted databases. DRAM caching has the best
potentialBofBsignificantlyBreducingBwriteBamplificationBwhileBincreasingBSQLBdatabaseBperformance.
Write cliff occurs when the flash storage performance drops suddenly, and permanently, all at once over
time. This is predominantly a flash storage drive issue and not so much of a flash storage system issue.
It can still be a flash storage system issue when the system’s flash capacity consumption exceeds 90%
and the garbage collection process has becomes more frequent while working harder and slower. Write
cliff is far more prevalent with flash storage drives generally and flash storage systems not architected
specifically!for!the!idiosyncrasies!of!flash.!
SQL database performance consequences: Write cliff will show up as a sudden rapid database
performanceBdeclineBthatBdoesBnotBrecover.
Dragon!Slayer!Consulting!•!Summer!2014!
!
7
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
Steady'state performance vs First'Out'of'Box (FOB) performance is the state of the flash storage when
it has experienced enough P/E cycles to stabilize the write performance to a predictable sustained level.
Enough<P/E<cycles<are<generally<when<most<of<the<flash<storage<blocks<have<been<written<to<at<least<once.<
FOB performance is the flash storage’s best performance. The gap between FOB and steadyCstate
performance is nonCtrivial often approaching an order of magnitude (i.e. steady state is approximately
10%<of<FOB.)<<
SQL database performance consequences: A failure to plan SQL database performance around flash
storage steady state will lead to the unpleasant experience of either living with lower SQL database
performance;than;expected;or;spending;more;money;on;additional;flash;storage;than;was;budgeted.;
Read'disturb<occurs<when<reads<of<a<NAND<cell<causes<data<to<change<on<bordering<or<nearby<NAND<cells<
within<the<flash<block.<Since<all<cells<on<flash<NAND<chips<exist<on<the<same<die<there<is<occasionally<cross<
coupling<between<bordering<cells<in<a<block.<The<energy<required<passing<through<a<cell<to<read<its<state<
periodically<bleeds<enough<of<a<charge<off<an<adjacent<cell<that<pushes<that<cell's<state<past<its<threshold<
changing<its<state.<<ReadCdisturb<is<more<likely<to<occur<as<the<bits<per<cell<increase<because<more<energy<
is<required<to<pass<through<the<cells.<<Because<the<reads<required<to<cause<a<read<disturb<is<measured<in<9<
figures<it’s<not<that<frequent;<however,<when<it<occurs<data<is<changed<or<lost.<<Flash<storage<drives<and<
systems<attempt<to<prevent<readCdisturb<by<setting<a<block<read<threshold<since<last<erase<cycle<that<
copies<the<block<to<another<erased<or<unused<block<resetting<the<counter.<<Correcting<readCdisturb<
occurrences<requires<more<sensitive<error<detection<and<correction<codes<(ECC)<that<often<only<occur<in<
flash<storage<systems.
SQL database performance consequences: Read>disturb is more likely to occur in databases where the
same data has an exceedingly high read rate and the flash storage does not have safeguards built>in.
When;it;does;occur;SQL;databases;will;most;likely;become;corrupted.
High bit error rates (BER) are endemic to flash storage. Error correction codes (ECC) are used to monitor
and correct errors. The good news is that as NAND flash continues to shrink (now at 19nm heading to
14nm and 10nm) price per GB continues to decline. The bad news is that NAND die shrinkage causes BER
to increase geometrically requiring ECC algorithm capabilities, sensitivity, and complexity to increase
right along with it. This in turn requires greater amounts of the flash storage controller’s resources to
manage that ECC. Therefore as NAND dies shrink, cost per GB drops, but overhead and IOPS latencies
rise.<Many flash PCIe and SSDs do not adjust their ECC algorithms enough to manage the increased BER
requiring<the<storage<system<or<server<to<handle<it.<<
SQL database performance consequences: Uncorrected high BER will cause corrupted SQL databases.
Today;19nm;NAND;flash;requires;41;ECC;corrections;per;1KB;of;data.;;Future;advances;to;smaller;die;sizes;
will require even more ECC corrections. Many flash storage controllers at the drive level do not have that
capability.;;Some;do,;whereas;most;flash;storage;systems;also;have;more;extensive;ECC;capabilities.
Flash! storage! is! an! outstanding! SQL! database! performance! storage! technology! if! implemented! correctly! and!
while!addressing!each!of!these!flaws!effectively.!Otherwise,!it!is!likely!to!cause!dashed!expectations!and!severe!
SQL!database!operational!and!financial!headaches.!!
How!Tegile!IntelliFlash™!Storage!Arrays!Solve!SQL!Database!Flash!Fatal!Flaws!
Tegile! designed,! developed,! and! implemented! their! IntelliFlash! hybrid! and! all! flash! arrays! to! transparently!
eliminate,!manage,!and!overcome!these!SQL!database!flash!fatal!flaws!transparently.!!!
All!Tegile!IntelliFlash!storage!systems!from!the!smallest!to!the!largest!deliver!these!capabilities:!!
Dragon!Slayer!Consulting!•!Summer!2014!
!
8
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
Eliminating!The!Bits!Per!Cell!Tradeoffs!
IntelliFlash!eliminates!the!SQL!database!price!performance!tradeoffs!by!making!the!lower!cost!MLC!flash!drives!
perform! and! behave! similar! to! SLC.! ! It! speeds! up! MLC! flash! performance! through! clever! use! of! the! DRAM.!
DRAM!is!up!10!times!faster!than!SLC!flash.!!IntelliFlash!utilizes!the!DRAM!to!coalesce!the!writes!to!sequentialize!
them!to!the!write!flash.!That!sequentialization!enables!larger!block!sizes!(more!on!that!in!the!next!paragraph),!
meaning! fewer! blocks! to! track! per! flash! storage! drive,! that! reduce! both! write! and! read! latencies! from! flash!
storage! drives! improving! performance.! DRAM! coalescence! also! increases! the! MLC! wearVlife.! ! SQL! database!
IntelliFlash!latencies!have!a!consistent!1ms!or!less!(better!than!most!SLC!flash!storage)!with!the!cost!of!MLC!
thus!eliminating!the!price!performance!tradeoffs.!!
Reducing!Write!Amplification!Increasing!Wear!Life!
IntelliFlash! DRAM! coalescence! eliminates! the! random! nature! of! writes! to! flash! that! is! so! prevalent! in! SQL!
databases!(server!virtualization!too).!!This!allows!IntelliFlash!to!utilize!larger!block!sizes!with!exceptionally!high!
efficiencies!(i.e.!minimal!unused!pages!in!blocks)!making!write!amplification!a!nonVissue!while!delivering!very!
high!wearVlife!and!endurance.!!
Eliminating/Mitigating!WriteRCliff!
IntelliFlash! flash! storage! arrays! have! the! builtVin! software,! processing! power,! memory,! and! capacities! that!
make!writeVcliff!a!nonVevent!in!a!well!designed!flash!storage!system.!!
Steady!state!performance!
IntelliFlash! storage! arrays! increase! the! ramp! to! process! to! steady! state! flash! performance! through! it’s! large!
flash!capacities,!intelligent!use!of!deduplication!and!compression,!and!DRAM!sequentialization!of!writes.!!That!
use!of!DRAM!caching!also!means!the!steady!state!issue!does!not!affect!writes.!!!
IntelliFlash!adds!another!treat!to!the!hybrid!systems.!!Inline!deduplication!and!compression!allows!up!to!10x!
more! data! to! reside! in! flash! read! cache.! ! This! allows! the! pinning! of! all! of! the! very! performanceVsensitive!
portions!of!databases!in!flash.!!The!result!is!significantly!lower!SQL!database!latencies,!increased!SQL!database!
cache! hits,! reduced! HDD! paging,! and! much! improved! overall! database! performance.! IntelliFlash! clearly!
mitigates!the!impact!of!steady!state.!
Eliminating!ReadRDisturb!
Intelliflash! flash! storage! arrays! utilize! the! block! read! threshold! since! last! erase! cycle! that! copies! the! block! to!
another!erased!or!unused!block!resetting!the!counter.!!Thus!eliminating!readVdisturb!issues.!!
Correcting!High!BER!
Intelliflash!flash!storage!arrays!have!a!major!edge!in!detecting,!correcting,!and!overcoming!high!levels!of!flash!
storage!BER.!!It!starts!with!the!highly!sensitive!and!extensive!endVtoVend!error!detection!and!correction!codes!
from!the!IO!through!the!CPU!to!memory!to!flash!and!even!on!to!HDDs!and!back.!!It!even!eliminates!HDD!silent!
data! corruption! through! checksum! matching! with! the! blocks.! ! There! is! no! better! endVtoVend! error! detection!
and! correction! in! the! market! today.! ! Additionally,! IntelliFlash! storage! systems! include! zero! capacity!
instantaneous! writeable! snapshots,! cloning,! and! replication.! ! That! data! protection! capability! empowers!
IntelliFlash!to!keep!a!good!copy!of!the!data!in!another!volume!or!system!should!it!not!be!able!to!autocorrect!a!
BER.!!
Tegile!IntelliFlash!SQL!Database!Proof!Points!
IntelliFlash!has!been!shipping!for!several!years.!!It’s!not!just!marketing!theory!but!production!proven.!!Tegile!
has!numerous!SQ!database!user!proof!points.!!Here!are!just!a!few:!
Grass!Valley!!!
Grass! Valley! is! a! well! know! manufacturer! of! broadcast! quality! digital! video! equipment.! Their!
implementation!of!IntelliFlash!storage!reduced!their!SQL!database!server!CPU!utilization!down!from!90%!to!2%,!
improve!database!response!times!from!30ms!to!1ms,!while!increasing!database!throughput!by!10x.!!That!CPU!
utilization!reduction!additionally!allowed!them!to!reduce!the!number!of!cores!the!database!runs!on!reducing!
their!SQL!database!licensing!costs.!!!
!St.!Paul!Housing!Authority!
The!St.!Paul!Housing!Authority!saw!a!5x!increase!in!SQL!database!performance!utilizing!IntelliFlash!
storage.! ! IntelliFlash! also! reduced! their! power! and! cooling! consumption! by! 66%.! ! But,! it! was! their! database!
Dragon!Slayer!Consulting!•!Summer!2014!
!
9
WHITE!PAPER!•!Database!Fatal!Flash!Flaws!No!One!Talks!About!–!And!How!to!Avoid!Them
!
backups! that! were! causing! them! the! most! heartburn.! ! Backups! forced! them! to! shutdown! their! database!
extended!periods!of!time.!!IntelliFlash!reduced!those!backup!windows!by!more!than!80%.!!
!
!!USRYellow!Pages!
The!Florida!based!USVYellow!Pages!have!seen!their!SQL!and!SAP!queries!improve!as!much!as!20x!while!
database!latencies!are!a!consistent!1ms.!
Unnamed!Hedge!Fund!
An! unnamed! Hedge! fund! experienced! a! 3! to! 5x! increase! in! data! warehouse! ingestion! rates! on! the!
IntelliFlash.!!So!instead!of!running!a!single!data!warehouse!analytics!job!on!what!kind!of!trade!should!they!run!
the! next! day,! they! now! run! three! or! more.! ! This! empowered! them! to! perform! far! more! detailed! “whatVif”!
analysis!while!the!markets!are!closed.!
In!Summary!
There!are!numerous!flash!storage!issues!that!most!DBAs!simply!do!not!know!about.!!Those!issues!can!and!do!
have!a!nasty!impact!on!SQL!database!performance.!!Solving!these!problems!requires!the!flash!storage!vendor!
to!address!them!specifically.!!Tegile!IntelliFlash!hybrid!and!all!flash!arrays!do!just!that.!
For!More!Information!!
Contact!Tegile!Systems,!Inc.!at:!www.tegile.com!or!via!Email:[email protected].!!!
!
Paper!sponsored!by!Tegile!Systems,!Inc.!!About!the!author:!Marc!Staimer!is!the!founder,!senior!analyst,!and!CDS!of!Dragon!
Slayer! Consulting! in! Beaverton,! OR.! The! consulting! practice! of! more! than! 16! years! has! focused! in! the! areas! of! strategic!
planning,!product!development,!and!market!development.!With!over!34!years!of!marketing,!sales!and!business!experience!
in! infrastructure,! storage,! server,! software,! databases,! and! virtualization,! he’s! considered! one! of! the! industry’s! leading!
[email protected].!
!
Dragon!Slayer!Consulting!•!Summer!2014!
!
10