1. technology and computing
2. software
3. databases

White Paper | October 2014
Optimize MySQL Cluster
Environments with
HGST Flash Pools
Transform the way you operate, support and innovate MySQL
Master/Slave clusters and significantly reduce TCO
Optimize MySQL Cluster Environments with HGST Flash Pools
Table of Contents
Introduction
Purpose of the MySQL Slave.....................................................................................................1
Why Consider an Alternative Strategy...............................................................................1
The Solution
How it Works..................................................................................................................................2
Snapshots, ETL, Backups, Analytics...................................................................................... 3
Flash Pools vs. MySQL Replication.......................................................................................4
Device Affinity...............................................................................................................................4
Performance Metrics
HGST Flash Pools Compared to HDD Array..................................................................... 5
HGST Flash Pools Replication Compared to Identical Master/Slave SSD
Configuration................................................................................................................................. 5
HGST FlashMAX SSD Options
HGST FlashMAX II Model Options.......................................................................................6
Flash Pools TCO
Traditional Approach vs. HGST Flash Pools - Capex and Opex Comparison.. 7
Summary
Optimize MySQL Cluster Environments with HGST Flash Pools
Introduction
HGST Flash Pools for MySQL will transform the way you operate, support and innovate Master/Slave cluster
environments by eliminating the barriers that substantially increase administrative complexity and
operating costs associated with scaling MySQL applications.
Purpose of the MySQL Slave
1. Standby server – in case of a Master failure, the Slave preserves availability
2. Offload read queries from Master – often uses multiple Slaves to accelerate reads
3. ETL – load data into NoSQL, Hadoop without affecting Master server
4. Backup – does not slow down Master with backup jobs
5. Database development – changing schema, indexes, testing, etc.
6. Analytics – offloading work from primary application for analytical processing
Why Consider an Alternative Strategy
1. Waste – PCIe server Flash eliminates the need for Slaves to provide read acceleration. Reads and writes
can be served from the same host at much higher levels of performance than HDD-based options.
2. Capex – Purchasing a server (or servers), to identically configure as a peer to your Master is a significant
expense. These unsustainable expenses are compound exponentially in lock step with growth in
business.
3. Opex – Management, power and cooling to keep the Slave servers running 24/7. The four-year cost of a
server’s electricity is typically the same as the cost of the server itself. Many data centers are using less
than 15% of supplied electricity to power their servers. Growth-to-power ratios are front and center in
modern data center energy best practices. Customers need to ensure that power is directed to its most
profitable business initiatives and limit idle or underutilized equipment.
4. Replica latency – Window of vulnerability, application delays (Master and Slave may never actually be
in sync under high write loads).
5. Operational complexity – The administrative overhead associated with managing Slaves requires
additional personnel to maintain, lowering the management ratio of terabytes-to-storage
administrator.
1
Optimize MySQL Cluster Environments with HGST Flash Pools
The Solution
PCIe solid-state drives (SSDs) reduce shards and Slaves, but traditional architectures still require a 1:1
relationship between Masters and Slaves. Slaves are still needed for HA, ETL, analytics, testing and development access. With HGST Flash Pools, a new approach allows for software with “device affinity” to create a
block-level mirrored pool of highly performant Flash capacity that allows you to consolidate operationally
redundant Slaves in a group with a single server while still maintaining data replication capabilities, availability and development access. With HGST Flash Pools, you may experience consolidation of 38% or more.
After
Before
Multi-Function
Server
- Shared, cluser multi-function server
- 8 servers to 5, 38% consolidation
- Fully mirrored Pool of Flash
- Any server to any volume
- Dedicated replication pairs
- Inefficient server utilization
How it Works
HGST Virident Space is software that creates a shared Flash cluster from FlashMAX® SSDs. Simply install
HGST FlashMAX SSDs on each Master server, then install HGST Virident Space software on each Master and
the Multi-Function Server. HGST Virident Space software clusters all servers with Flash into a Flash Pool that
is fully mirrored. This can be done through the familiar command line interface (CLI) or through the graphical user interface (GUI).
A cluster-aware LVM (CLVM) volume is created on the Flash Pool. Each Master gets its own LVM volume to
use as its EXT4 or XFS filesystem for all MySQL data (ibdata, iblog, etc.).
Master
Shard 1
Master
Shard 3
Master
Shard 2
HGST
Flash
HGST
Flash
Master
Shard 4
HGST
Flash
HGST
Flash
/dev/master1 (ext4)
/dev/master2 (ext4)
/dev/master3 (ext4)
/dev/master4 (ext4)
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
Redundant
Server
Each Master has its own private CLVM volume on the space Redundant Server idle, running vgc_mysqlfailover daemon.
2
Optimize MySQL Cluster Environments with HGST Flash Pools
Add the Redundant Server onto the network with Space software installed. The Redundant Server does not
require a FlashMAX SSD. It can be on-line or even powered off saving operational expense. The Redundant
Server runs a MySQL monitoring daemon, and when it detects a Master failure it takes over the shared
volume and starts MySQL up exactly where the failed host left off.
Master
Shard 1
Master
Shard 3
Master
Shard 2
HGST
Flash
HGST
Flash
Master
Shard 4
HGST
Flash
HGST
Flash
/dev/master1 (ext4)
/dev/master2 (ext4)
/dev/master3 (ext4)
/dev/master4 (ext4)
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
Redundant
Server
MySQL starts up, replays log, and resumes service at the Redundant Server
Snapshots, ETL, Backups, Analytics
Snapshots can be taken of running Masters. HGST provides scripts to automate this task. Snapshots are
available for use on the Redundant Server once completed. It is also possible to have more than one Redundant Server in the cluster to handle backup, ETL and development work without adding load to the Masters.
For analytics, simply snap the Flash Pool volume and mount. Now you have a Flash Pool for mysqlslap and
OLAP in a single instance.
(1) Snapshot initiated on a Master to a new VLVM volume
Master
Shard 1
Master
Shard 3
Master
Shard 2
HGST
Flash
HGST
Flash
Master
Shard 4
HGST
Flash
HGST
Flash
/dev/master1 (ext4)
/dev/master2 (ext4)
/dev/master3 (ext4)
/dev/master4 (ext4)
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
sn
ap
Master1_snap
IBDATA
IBLOG ...
Multi-Function
Server
(dev/test/analytics)
Redundant
Server
Master continues operating during the snapshot process
3
Optimize MySQL Cluster Environments with HGST Flash Pools
(2) Snapshot mounted on Development or Redundant Server
Master
Shard 1
Master
Shard 3
Master
Shard 2
HGST
Flash
HGST
Flash
Master
Shard 4
HGST
Flash
HGST
Flash
/dev/master1 (ext4)
/dev/master2 (ext4)
/dev/master3 (ext4)
/dev/master4 (ext4)
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
IBDATA
IBLOG ...
Master1_snap
IBDATA
IBLOG ...
Multi-Function
Server
(dev/test/analytics)
Redundant
Server
Could also use Redundant Server if desired
Flash Pools vs. MySQL Replication
In addition to the consolidation benefits, Flash Pools provide other capabilities for MySQL that compare
favorably to conventional MySQL Replication methods. HGST Flash Pools enable synchronous mirrors that
ensure guaranteed replication. Since they are synchronous, they are also faster with virtually no replication
delay at the application level. However, Flash Pools do not enable Master/Master replication or WAN replication. The chart below highlights the primary differences between Flash Pools and MySQL Replication.
MySQL Replication
HGST Flash Pool
Synchronous
No
Yes
Replication Guaranteed
No
Yes
Slower
Faster
No
Yes
Servers Needed
2*N
N+1
Replication Delay
10ms...1000ms+
0
Master/Master
Yes
No
WAN Replication
Yes
No
Allowable Failures
N masters
1 master
Speed
Device Affinity
Device Affinity
HGST optimizes Flash performance and endurance with its unique ‘device affinity’ technology. Through a
deep understanding of NAND Flash and the die itself, we optimize our SSDs, drivers and software layers ‘up
the stack’ as part of a vertically integrated strategy to ensure the best performance. The benefits of device
affinity vary significantly from commercial software and other industry SSDs that are not optimized in this
manner. Some of those benefits include:
4
Optimize MySQL Cluster Environments with HGST Flash Pools
1. Higher performance – the ability to cut-through many different layers of overhead, often resulting in 2x
or greater performance
2. Greater endurance – fewer writes to the Flash for the same amount of work
3. Native DMA support – kernel-level interfacing with OFED/Infiniband stack. Note, while Flash Pools will
work with either Infiniband or Ethernet, there is optimization for Infiniband.
Performance Metrics
HGST Flash Pools Compared to HDD Array
HGST Flash Pools deliver dramatically superior performance compared to an HDD-based array. In a
mysqlslap-type workload, we are able to demonstrate 40x higher number of transactions per minute.
Transactions/Minute
40.5x
40x
30x
20x
10x
0x
1x
MySQL
Master/Slave
HGST Flash Pool
Master/Slave
HGST Flash Pools Replication Compared to Identical Master/Slave SSD
Configuration
HGST Flash Pools achieve 62% higher performance with nearly half the number of servers when considering
mysqlslap workloads using the same FlashMAX SSD. In addition, since the replication is synchronous and at
the block-level, we can assure much more granular recovery in the event of a Master failure.
Transactions/Second
2.0x
1.5x
1.0x
.5x
.0x
1.6x
1x
MySQL Flash Master/Slave
Replication
HGST Flash Pool
Replication
5
Optimize MySQL Cluster Environments with HGST Flash Pools
HGST FlashMAX SSD Options
Flash Pools are delivered on HGST FlashMAX II PCIe SSDs today and FlashMAX III coming soon. The HGST
architecture has been designed to tightly integrate different types of Flash media, hardware and software
to deliver memory-class performance with storage-class capacity and persistence. HGST’s FlashMAX II SSDs
and associated software deliver performance without compromise, along with HDD-like capacity in a very
compact, universal form factor. Unlike competing solutions, 100% of the capacity available on a FlashMAX
II card is available as a single host volume on the server without having to leverage 3rd party software RAID
products to stripe across multiple drives. With FlashMAX II you can have a single volume presented to the
operating system up to the formatted capacity, which can be as large as 4.8TB when using the FlashMAX II
Capacity model.
HGST FlashMAX II Model Options
Performance1
STANDARD MODELS
PERFORMANCE MODELS
CAPACITY MODEL
Capacities (GB2)
550, 1100
1100, 2200
4800
Read throughput (max MB/s, sequential 64k)
1,600
2,700
2,600
Write throughput (max MB/s, sequential 64k)
550
1,000
900
Read IOPS (max IOPS, random 4k)
174,000
345,000
269,000
Write IOPS (max IOPS, random 4k)
27,000
57,000
51,000
Peak write IOPS (max IOPS, random 4k)
109,000
245,000
213,000
Mixed IOPS (70/30 R/W, random 4k)
72,000
138,000
128,000
Peak mixed IOPS (70/30 R/W, random 4k)
161,000
315,000
264,000
Read IOPS (max IOPS, random 8k)
125,000
250,000
214,000
Write IOPS (max IOPS, random 8k)
13,000
28,000
27,000
Latency 512B (μs)
21
22
19
1
All performance measurements are in full sustained mode except where noted as “Peak.”
2
One gigabyte (GB) is equal to one billion bytes, one terabyte (TB) is equal to 1,000GB (one trillion bytes), and one petabyte (PB) is equal to 1,000TB
(one quadrillion bytes) when referring to solid-state drive or hard drive capacity. Accessible capacity will vary from the stated capacity due to
formatting and partitioning of the drive, the computer’s operating system, and other factors.
For more information on FlashMAX II PCIe SSDs, go to http://www.hgst.com/solid-state-storage/enterprisessd/pcie-ssd/FlashMax-II
Flash Pools TCO
HGST Flash Pools not only deliver a significant performance boost, but an extraordinary TCO as well. Based
upon Dell’s MySQL Reference architecture for MySQL Server hardware http://en.community.dell.com/
techcenter/os-applications/w/wiki/3752.mysql-reference-architectures-sample-data-on-dell-poweredgesystems we built our hardware reference architecture around the Dell PowerEdge R620.
HGST Virident Space Software runs on Linux today and Windows in the next release. So our reference
architecture is slightly different than the Dell model as we substitute Red Hat Enterprise Linux for Microsoft
Windows 2008 R2 Data Center edition.
6
Optimize MySQL Cluster Environments with HGST Flash Pools
Dell PowerEdge R620 Configuration Summary
Module
Description
PowerEdge R620
PowerEdge R620, Intel® Xeon® E-26XX Processors
Warranty & Service
3Yr Basic Hardware Warranty Repair: 5x10 HW-Only,
5x10 NBD Onsite
PCIe Riser
Additional Riser with x16 PCIe Slot for x8, 2 PCIe
Chassis with 2 Processors
Embedded Systems Management
iDRAC7 Express
Select Network Adapter
Broadcom 5720 QP 1Gb Network Daughter Card
Chassis Configuration
Chassis with up to 8 Hard Drives and 2 PCIe Slots
(Requires Additional Riser)
Power Management BIOS Settings
Power Saving Dell Active Power Controller
RAID Controller
PERC H310 Integrated RAID Controller
Processor
Intel® Xeon® E5-2660 2.20GHz, 20M Cache, 8.0GT/s
QPI, Turbo, 8C, 95W
Additional Processor
Intel® Xeon® E5-2660 2.20GHz, 20M Cache, 8.0GT/s
QPI, Turbo, 8C, 95W
Memory Capacity
(2) 8GB RDIMM, 1600MT/s, Low Volt, Single Rank, x4
Data Width
Memory DIMM Type and Speed
1600MT/s RDIMMS
Memory Configuration Type
Performance Optimized
System Documentation
Electronic System Documentation and OpenManage
DVD Kit
Power Supply
Single, Hot-plug Power Supply (1+0), 495W
Power Cords
NEMA 5-15P to C13 Wall Plug, 125 Volt, 15 AMP, 10 Feet
(3m), Power Cord
Traditional Approach vs. HGST Flash Pools - Capex and Opex Comparison
While it is possible to do consolidations of 16 Master/Slaves to 8 Masters and 1 Multi-Function Server, we
focus on a simpler design for this paper. Since our architecture assumes a consolidation from 8 MySQL
Master/Slave pairs to a Flash Pool of 4 Masters and a Multi-Function Server, we highlight the capital
expenses for the traditional approach as follows:
Traditional Approach
Dell R620 Server
Unit Price
Units
Total Capex
$4,909.97
8
$39,279.76
$16,250.00
8
$130,000.00
RHEL 5 yr License
$4,276.61
8
$34,212.88
8 @ 128GB 15K RPM HDD (Dell.com)
$1,866.64
8
$14,933.12
ProSupport Plus (5-year next business day)
$1,399.26
8
$11,194.08
MySQL Enterprise (5 yrs) @ 35% discount
Capex Total
$229,619.84
7
Optimize MySQL Cluster Environments with HGST Flash Pools
In the traditional approach, we use 8@128GB 15K HDDs 1 HDD for the OS, 2 HDDs for DBLog RAID-1, and 5
HDDs in RAID5 (=4*140=560GB) for DB files. Collectively, the 8 HDDs can deliver 1,600 IOPS.
In the HGST Flash Pools configuration, we substitute a 1.1TB FlashMAX II SSD with 174,000 IOPS for each
server, which is more than enough performance to power Masters performing both read and write operations. All of the logs and database files are stored on these SSDs directly, with no special segregation needed
like in the hard drive case. The other cost in the Flash Pools model is HGST Virident Space software that is
loaded on each machine to enable the Flash Pools capability.
HGST Flash Pools
Unit Price
Dell R620 Server
MySQL Enterprise (5 yrs) @ 35% discount
RHEL 5 yr License
Units
Total Capex
$4,909.97
5
$24,549.85
$16,250.00
5
$81,250.00
$4,276.61
5
$21,383.05
HGST Virident Space Software @25% discount
$3,562.50
5
$17,812.50
1 @ 1.1TB PCIe SSD @ 25% discount
$5,000.00
4
$14,250.00
$1,399.26
5
$6,996.30
ProSupport Plus (5-year next business day)
Capex Total
$159,245.40
Comparing the configurations on a Capex basis, Flash Pools delivers a $70,374 savings or 31% better TCO over
5 years than the traditional approach.
Considering 0peration expense, Flash Pools reduce server count and therefore clearly will reduce
operational TCO. Highlighted below is a side-by-side comparison of the two approaches:
Traditional
Approach
Server Watts
HGST
Flash Pool
800
800
Storage Watts
72
25
Total Watts
872
825
Total Units
8
5
Total Platform Watts
6976
4125
Power Cost/kWH
$0.10
$0.10
Hours/Year
8760
8760
Power Cost/Year
$876
$876
Cooling Cost/Year
$876
$876
Platform Cost/Year
$12,222
$7,227
Platform Cost 5 Years
$61,110
$36,135
Besides the number of servers, the most significant difference in the two approaches is the power consumed
by the HDDs (72 watts/server) verses 25 watts for the FlashMAX SSD.
8
Optimize MySQL Cluster Environments with HGST Flash Pools
Overall, there is a 41% difference in Opex between HGST Flash Pools and the traditional approach, a savings
of $24,975 over 5 years.
The chart below summarizes the 5-year TCO for both approaches.
$350,000
$300,000
Traditional Approach
HGST Flash Pools
$250,000
$200,000
$150,000
$100,000
$50,000
$-
Capex
Opex
Total
Overall, total savings for HGST Flash Pools is $95,349 over 5 years or a 33% TCO compared to the traditional
approach.
Summary
Putting PCIe Flash to work into your MySQL environment will yield immediate performance benefits, but
there’s more. Since Flash is so much faster and lower latency, you can consolidate servers by up to 38%,
shifting Capex and Opex expenses to profitable business initiatives. With HGST Flash Pools, you can
eliminate the barriers that have persisted with conventional MySQL Master/Slave architecture with a
multi-function server. As a result you’ll enhance data availability and achieve a significant reduction in
capital expense for hardware, software and maintenance. The impact on data center energy costs will be
enormous, setting the stage for future growth initiatives. Finally, because HGST Flash Pools use synchronous mirroring, you guarantee replication transactions integrity as soon as the Master COMMITs, resulting
in a High Availability MySQL cluster with no replication lag.
We hope this white paper has provided a valuable education for you and provided insights into the transformative capabilities of HGST Flash Pools for MySQL. HGST provides our solutions through direct sales to
enterprises, cloud service providers and through channel partners worldwide. We look forward to working
with you.
9
Optimize MySQL Cluster Environments with HGST Flash Pools
© 2014 HGST, Inc., 3403 Yerba Buena Road, San Jose, CA 95135 USA, Produced in the United States 10/14, All rights reserved.
FlashMAX is a registered trademark of HGST, Inc. and its affiliates in the United States and/or other countries.
HGST trademarks are intended and authorized for use only in countries and jurisdictions in which HGST has obtained the rights to use,market and advertise
the brand. Contact HGST for additional information. HGST shall not be liable to third parties for unauthorized use of this document or unauthorized use
of its trademarks. References in this publication to HGST’s products, programs, or services do not imply that HGST intends to make these available in all
countries in which it operates.
The information provided does not constitute a warranty. Information is true as of the date of publication and is subject to change. Actual specifications for
unique part numbers may vary.
Please visit the Support section of our website, www.hgst.com/support, for additional information on product specifications. Photographs may show design
models.
WP18-EN-US-1014-01