Transcript Flash Best Practices

SQL Server on Flash: Re-Thinking Best Practices
Jimmy May, MCM
SQL Server Solutions Architect | Fusion ioMemory™ Solutions
c
1
Diamond & Platinum Sponsors
2
Preamble
3
Introduction


Flash storage is a game-changer for virtually every app in which it
is used. Whether used locally—SATA, SAS, or PCIe; or for tiered
storage or all-flash arrays—flash is changing what we thought we
knew about SQL Server performance.
This presentation provides a brief survey of history and a highlevel, vendor-agnostic review of options for implementing flash in
the enterprise.
4
Data Propulsion Laboratory — DPL
6
C:\>sqlservr.exe /faster
7
Acknowledgements











Kevin Kline, MVP
Brent Ozar, MCM, MVP
Thomas LaRock, MCM, MVP
Denny Cherry, MCM, MVP
Gavin Payne, MCA, MCM
Chuck Suter, Pure Storage
Mike Anderson, Disney
Mike Ruthruff, Bungie Studios
Thomas Kejser
Tim Radney, MVP
Mindy Curnutt, MVP





Brian Walters, SanDisk
Sean Hehir, SanDisk
Grant Jacobson, SanDisk
John Gatch, SanDisk
Niall MacLeod, SanDisk
8
Agenda
9
Agenda
History and Introduction to Enterprise Flash
 Flash Best Practices for SQL Server
 SQL Server Use Cases and Implementation Details
 Flash Myths, Misinformation,
& Re-visiting Best Practices
 PCIe Application Accelerators
 Microsoft SQL Server 2014 Fast Track (DWFTRA)

10
Enterprise Flash Unlocks
Microsoft SQL Server 2014 Business Value
•
Increase business productivity – more transactions, faster
•
Improve customer experience – faster response times
•
Reduce costs – scale more users and workloads per server
•
Increase SLAs – faster SQL database startup, failover, & maintenance
11
Introduction to Enterprise Flash
12
Flash is Revolutionizing Enterprise Storage
13
Disk I/O Subsystems are Black Boxes

We must remain mindful of Bruce Worthington's
blackbox metaphor
–


This onion has many layers
Remember The Matrix?
–
When Morpheus and Neo meet, Morpheus offers Neo two pills.
–
The blue pill will allow Neo to carry on as before.
–
The red pill will answer the question, “What is the Matrix?”
–
As Neo reaches for the red pill Morpheus warns, “Remember, all I'm
offering is the truth. Nothing more.”
–
If you prefer a glimpse of the truth...
These complexities are true not only for spinning media,
but also flash
14
What is I/O? Performance Monitor: Logical Disk and PhysicalDisk

Latency
– Measured in milliseconds (ms)
– Measured in microseconds (µs)
– Performance Monitor: Logical Disk
• Avg. disk sec/transfer
• Avg. disk sec/read
• Avg. disk sec/write

IOPS
• Disk transfers/sec
• Disk read/sec
• Disk writes/sec

Throughput
• Disk bytes/sec
• Disk read bytes/sec
• Disk write bytes/sec

Capacity
– Measured in GB/TB
– The easy one!
15
May’s I/O Mantra
X capacity (GB)
at Y throughput (MB/sec)
at Z IOPs (transfers/sec)
at <=10ms latency for OLTP data files
at <=30ms latency for DW data files
at 0ms-2ms for log files
16
What is the business impact of flash on I/O?
Google found an extra half-second in search-page generation time
dropped traffic by 20%.
Amazon found every 100ms of latency cost them 1% in sales.
If a trading platform is 5 milliseconds behind the competition, it
could lose millions in revenues per millisecond.
Performance is paramount: Asking users to wait is like asking them to leave.
If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it.
—Computer science professor, entrepreneur, & billionaire David Cheriton
Application performance is not an event or something you do once—it's a practice.
—Mark Kremer, CEO, Precise Software Solutions Inc.
17
Early Spinning Media
18
The Traditional Hard Disk Drive
Cover mounting holes
(cover not shown)
Base casting
Spindle
Slider (and head)
Case mounting holes
Actuator arm
Platters
Actuator axis
Actuator
SATA interface
connector
Flex circuit
(attaches heads
to logic board)
Power connector
Source: Panther Products
19
The Challenge of Traditional Disk


Conventional HDDs are mechanical.
– Failure-prone (RAID)
– Power-hungry (operation and cooling)
– Capacity has increased significantly, but performance has
not.
– Short-stroking
Data layout:
– HDD needs to seek to find correct data.
• Latency impacts read / write performance
• Data fragmentation occurs
• Data access patterns differ: OLTP vs. OLAP/DW
• Disks must be aggregated to improve performance

Disks should now be considered a storage medium, not a
performance medium.
15K FC/SAS/SCSI
10K SAS
SATA
Tape
20
The “New” Hard Disk Drive (Flash)
No moving parts …
21
Flash Commands

Three Basic Commands
–
Read
–
Write (Program)
–
Erase

Read: 25μs Reads bits

Write: 200μs Sets selected bits to “0”

Erase: 2000μs Turns selected bits to “1”
23
Flash: SLC, MLC, TLC
24
The I/O Path for Flash:
Any I/O
IO STACK
APPLICATION
OS LAYER
Block Storage Drivers
DRIVER
CHANNEL
Bus (e.g., SAS, PCIe)
Storage
Device
FPGA
BUFFER
NAND Flash
25
General Data Flow WITH TIMES:
Read Unwritten
Write
Read Written
APPLICATION
APPLICATION
APPLICATION
OS LAYER
OS LAYER
Block Storage Drivers
Block Storage Drivers
DRIVER
DRIVER
DRIVER
Storage
Device
PCIe Bus
PCIe Bus
PCIe Bus
FPGA
FPGA
FPGA
BUFFER
BUFFER
BUFFER
~62.68 us
CHANNEL
~14.70 us
OS LAYER
Block Storage Drivers
~12.59 us
IO STACK
3.4 Ghz Processor with 1.2 TB ioDrive 2 with 512-Byte Block
ASYNCHRONOUS
NAND Flash
NAND Flash
NAND Flash
27
Flash Formats

Protocols and form factors:
–
SATA
–
2.5” SAS
–
PCIe
–
SANs:
• Tiered Storage
• All-flash Arrays
• Caching Appliances
30
Relative Performance of Conventional HDD vs. Flash




There are many ways to look at disk performance:
–
Read/Write
–
Random/Sequential
–
IOPs/Throughput
–
Block size impacts IOPs, throughput, & latency
–
Let’s look briefly at one perspective: IOPs vs. latency
Spinning Media vs. Flash
180x – 1,000x+ IOPs
Spinning media (15K Enterprise SAS)
–
Random 4K reads: 210 IOPs @2000 µs
–
Random 4K writes: 190 IOPs @2000 µs
30x – 70x faster
Flash vendor 1:
–
Random 4K reads: 75,000 IOPs @50µs
–
Random 4K writes: 36,000 IOPs @65µs
Flash vendor 2:
–
Random 4K reads: 252,000 IOPs @29µs
–
Random 4K writes: 236,000 IOPs @29µs
31
Flash Best Practices for
SQL Server
32
Flash Best Practices for SQL Server

Though flash can seem like a magic bullet …
–
Flash can mask many performance, configuration, and design challenges
Best practices remain fundamental
 There is no substitute for engineering discipline in terms of
application & database design

If you don't have time to do it right, when will you have time to do it
over?
—John Wooden
33
Flash Best Practices: SQL Server Configuration
Multiple data files per filegroup?
 Segregate log files?
 tempdb Configuration

-T1117 (to enable consistent AutoGrow)
– -T1118 (to eliminate SGAM contention)
– Multiple, equisized tempdb data files
–

Local Security Policy
secpol.msc > Local Policies > User Rights Assignment
– Add SQL Server service account to the following:
–
• Perform volume maintenance tasks (to enable Instant File Initialization).
• Lock pages in memory (to prevent paging).

Etc.
34
Flash Best Practices: Windows Power Configuration

Power options
–
Windows power options:
Control Panel > Hardware > Power Options >
High Performance
35
Flash Best Practices: BIOS

Logical processor / hyper-threading: Enable
–
Contemporary hyper-threading vs. the original incarnation

Fan: high or increased cooling

C-States/P-States: Disable all “power saving” options
–
Configure for maximum clock speed & voltage
36
Flash Best Practices: Storage Devices



Logical volume configuration
–
Disk Partition Alignment remains important
–
Windows-level format (FAUS): 64KB
Drivers:
–
Update drivers
–
More important for flash devices than for conventional spinning media
Vendor-specific recommendations:
–
Default power settings for device may not be optimal
• This is in addition to physical server & Windows power settings
–
“Low-level” formatting
37
Flash Best Practices: Bandwidth


Maximum performance is limited by system bottlenecks
2.5” flash drives in server front-end
–
–
–

It doesn’t take much flash to overwhelm a bus
Consider unexpected costs associated with linking to the PCIe bus
Be aware of potential thermal issues
Validate PCIe slots best-suited for cards
–
–
–
PCIe version
Bus speed: 4x, 8x, 16x
Bus power
38
SQL Server Use Cases
39
SQL Server Use Cases:

Single-Instance Options

tempdb as a Cluster Shared Resource

AlwaysOn HA using Readable Replicas

AlwaysOn Multi-site HA with DR

Compression

Fragmentation

Flash for Development Environments
40
Single-Instance Options
x2
“Hot” files
selected on
flash
x2
Entire database on
flash
x2
AlwaysOn for HA/DR
External
database

Ideal where entire database cannot
fit within one or more devices
6x
IMPROVEMENT
I N D AT A L O AD
20x
F AS T E R U P D AT E
QUERIES

Ideal where entire database easily fits
within one or more devices
12x
REDUCTION IN
AV E R AG E D I S K Q U E U E
LENGTH
41
tempdb as a Cluster Local Resource
•
•
“Classic” multi-node
failover clustering
configurations can now
employ tempdb as a
local resource.
Configuring tempdb on
local flash mitigates
performance challenges
related to conventional
shared storage.
tempdb
tempdb
Primary server
Secondary server
Conventional
shared storage
42
AlwaysOn HA using Readable Replicas

Optimizes hardware utilization

Secondary server:

–
readable replica
–
H/A failover
Secondary readable replica offloads:
–
Reporting
–
Backup
–
DBCC CHECKDB
43
AlwaysOn Multi-site HA with DR
Disaster Recovery DC
Primary DC
Primary (server 1)
Asynchronous
Synchronous
Remote Replica
Availability Group
Local Replica
44
Compression

Long a best practice for SQL Server data

Yet it’s often not implemented

Typically, relatively small CPU cost (3%) in exchange for storage
savings of ~2x – 5x

Corollary: If extant data is not compressed, compression while
migrating to flash can effectively discount your new storage costs
by half or more.

Note: Some flash vendors provide compression or de-dupe by
default.
45
Comparing Space Savings
19.7GB
10.9GB
6.9GB
5.0GB
TABLE WITH
CUSTOMARY
INDEXING
4.0GB
TABLE WITH
TABLE WITH NO TABLE WITH NO
TABLE WITH
CUSTOMARY
INDEXING
INDEXING (PAGE COLUMNSTORE
INDEXING (PAGE
COMPRESSION)
INDEX
COMPRESSION)
1.8GB
CLUSTERED
COLUMNSTORE
446
Flash for Development Environments

Consider using flash for dev environments

I’ve always been a fan of providing developers with a robust
environment

Providing a flash platform (& plenty of all the right resources)
encourages experimentation & innovation
47
Flash Myths
48
Flash Myths, Misinformation, & Re-visiting
Best Practices

Log files & data files

Fragmentation

OLTP vs. OLAP/DW

Cost

Durability/Reliability

Availability & RAID for PCIe Cards

CPU0 bottlenecks
49
Fragmentation

Concept of “sequential” access has no physical analog in flash.

Defragmentation remains a nominal best practice, especially for
OLTP/DW workloads
–

SQL Server readahead works with logically contiguous data
Yet even within Microsoft, benchmarks on flash are often run
without the benefit of defragging.
50
Log Files & Data files

Once upon a time...
–
Physical segregation of indexes from data files was a best practice
• Storage abstraction rendered this largely unnecessary
–
We still often needed to segregate log files from data files
–
However, this may no longer be necessary—test!
• Bottom line: Look for consistent, low (<1ms) log file latency
51
OLTP vs. OLAP/DW

Once upon a time...
–
We didn’t intermingle workloads
–
Today? Flash may render this moot
52
Availability & RAID for SAS & PCIe

Not all flash is created equal!
–

Consumer vs. Enterprise
Contemporary Enterprise Flash is robust
–
–
Massively over-provisioned, internal error checking, etc.
Example warranty: 50PBW (petabytes written), i.e., 50 * 1024TB of writes
• 512GB each day for 6 years
• Doable, yet...

Is it necessary to RAID enterprise flash?
–
–
–
It depends
Be clear: I am not advocating an SPOF!
Perhaps a better use of resources to focus on server/system HA
53
CPU0 bottlenecks



Not a myth, but antiquated
Windows Software RAID leverages CPU0
Some flash drivers used to do so as well
–
–
–

For example: 16 core server, CPU0 at 99%, CPU1-15 at 10%
–

Result, high I/O could result in one core with little headroom
Even though other cores were relatively unused
Must examine core-by-core % Processor Time
Average CPU <16%
Contemporary flash drivers render this moot because they either:
–
–
On-board device processor, or
Balance CPU across all cores
54
Cost

Once upon a time...
–
Flash was expensive
–
Yet time is money
• Remember the latency slide?
–
Raw cost:
• Glacial SATA is ~50¢/GB
• Depending on format, vendor, etc.,
Enterprise Flash has recently been as economical as $3/GB
• As of March 2015: InfiniFlash is as low as $1/GB!
55
Upgrade for Nothing & the Flash is Free

Customer after customer: Hundreds of thousands, even millions in
savings

Licensing exercise / Consolidation story

EOL: Windows Server 2003 & SQL Server 2005
–
Upgrade h/w
–
Virtualize & Consolidate
56
THE CHALLENGE
• Reduce power, cooling, and footprint
• Near real time reporting for 15,000 users
running up to
1.2 million queries per month
• Meet failover requirements
RESULTS
• 3x more concurrent users
• 3x more queries
• 2x greater workload
• Eliminated maintenance overhead for 27
disk arrays and over 400 disks
• 16:1 footprint consolidation
• 1/16th power and cooling
Source: Case Study - TekSouth Fights US Air Force Datacenter Sprawl with ioMemory; http://www.fusionio.com/case-studies/teksouth
USAF Financial Data Warehouse
TEKSOUTH
US Air Force System Changes
Before and After
“A single server
with ioMemory
doubled the
workload
capability of a 3server, 21-disk
array-based
system”
Mike Rhodes
VP of Operations
v
Source: Case Study - TekSouth Fights US Air Force Datacenter Sprawl with ioMemory; http://www.fusionio.com/case-studies/teksouth
58
SanDisk PCIe Application Accelerators
59
Accelerate your time-to-insight
Leveraging Fusion ioMemory Flash

Microsoft certified SQL 2014 solutions

9.2GB/s of I/O Throughput

700% improvement in query execution
times when using Clustered Columnstore
Indexes

Database consolidation made easy.

20TB- 90TB size warehouses
60
Leading the Industry
Fusion ioMemory™ PX600
Fusion ioMemory™ SX300
Industry leading reliability
Strongest ECC using LDPC
Capacities up to 6.4TB
Unprecedented performance
61
Industry Leading Performance
4K Random
Read IOPS
4K Random
Write IOPS
Read
Bandwidth
Write Performance in the Industry
Previous Fusion ioMemory™
Read Performance
Write
Bandwidth
Write
Latency
62
Microsoft SQL Server 2014 Fast Track
(DWFTRA)
63
Lenovo 60TB SQL 2014 Data Warehouse - Ref Architecture
SanDisk-based 2400GB Lenovo io3 Flash Adapters
System x 3850 X6 Server
Breakthrough Performance – 11.4GB/s scan rate
64
Thank You!
68