Turbo-charge MySQL Performance With Flash Storage

Panelists: Vadim Tkachenko, Percona

Baron Schwartz, Percona

Shirish Jamthe, Virident

Host: Shridar Subramanian, Virident

• Approaches to fully leverage flash-based storage solutions for MySQL

• MySQL configuration and optimization techniques

• Evaluation criteria and techniques for selecting the suitable flash-storage technology

• Price/performance advantages (ROI) when flash storage is used appropriately for MySQL workloads

Agenda

• How does MySQL suffer?

– When does MySQL struggle on spindle-based disks?

– What is the typical progression over time?

– What types of workloads suffer a lot?

MySQL Performance Challenges

• How does MySQL suffer? – When does MySQL struggle on spindle-based disks?

– What is the typical progression over time?

– What types of workloads suffer a lot?

• What workloads suffer? – Joins (normalized schema)

– Very large tables

– Mixed workloads

– Replication

– When low latency is required?

MySQL Performance Challenges

• More memory?

• Invest in SAN?

• Deploy “Sharding”?

• Get faster I/O?

What Are Available Options?

Flash Storage for Performance Gap

100’s

100,000’s

100,000,000’s

1,000,000,000’s

Picoseconds Nanoseconds Microseconds Milliseconds Seconds

HDD

DRAM

CPU

Flash Storage

Ops / Second

Latency

Types of Flash Storage

100’s

100,000’s

100,000,000’s

1,000,000,000’s

Picoseconds Nanoseconds Microseconds Milliseconds Seconds

HDD

DRAM

CPU

Ops / Second

Latency

SATA/SAS SSDs :

Disk form factor (Intel X25)

• Coexists with legacy RAID storage sub-systems

• Cost and packaging focused

SATA/SAS SSDs :

PCIe-Attach (LSI, OCZ)

• Coexists with legacy RAID storage sub-systems

• Time-to-market focused

Native PCIe SSD :

PCIe-Attach (Virident, Fusion-io)

• Storage plus application-specific memory interfaces

• Application value focused

• Where Flash Storage helps MySQL?

– Low-latency

– Joins, large tables, mixed workloads, replication

– High throughput workloads

– High concurrency workloads

• Key Benefits of Flash with MySQL

– Avoid/Reduce Sharding – App/Code complexity)

– Avoid denormalization – Database complexity

– Avoid replication lag – Infrastructure complexity

Where Flash Storage Benefits MySQL?

• Use a flash-optimized version of MySQL

• Shard and consolidate / multi-tenant

How can MySQL Better Leverage Flash?

Example of Optimized MySQL

• MySQL 5.1 with default InnoDB – not a good choice

• Better: MySQL 5.5

• Best: Percona Server 5.5

• If you still need 5.1 version: Percona Server 5.1

MySQL version is important

• Put all data ( ibdata1, ib_log, tables.ibd) on SSD

• Use several SSDs in server for larger data size

• 5-7x Improvement

MySQL Use case

Memory vs. Flash

• Put single-threaded sequential data on RAID – InnoDB transactional logs with fsyncs

– Binary logs

– Doublewrite buffer ( with whole ibdata)

• Configuration – XFS, better with 4k blocks

– Mkfs.xfs -s size=4096

– Mount –o nobarrier

– Multiple threads

– Percona Server or InnoDB-plugin or MySQL 5.5

Some Recommendations

• innodb_flush_neighbor_pages= ON | OFF

• innodb_log_block_size = 512 | 4096

• innodb_page_size = 4K | 8K | 16K (Use carefully)

• innodb_doublewrite_file

• Innodb_adaptive_checkpoint=keep_average

• innodb_log_file_size > 4GB

Percona Server Configs

Impact of TRX Log on MySQL Performance

• Single MySQL instance is unable to utilize all I/O provided by SSD card – multiple instances per server

• How about several instances in a server?

Scaling MySQL on Fewer Servers

• Write performance degradation over time

• Sizing for worst case IOPS

• Flash Storage Lifetime

• Power loss protection

Concerns with Flash Storage

Example of Performance Variability

• Fundamental characteristics of NAND Flash has order of magnitude impact

• Read/Write Asymmetry

– Difference between read and write performance

– Operation latencies

• Lack of update-in-place capability

– Needs constant balancing between application traffic and flash management activity

What Causes the Performance Variability

Performance Variability of Flash Storage

Mixed read-write (Databases, Caching, Metadata serving, …)

“Seasoned” Device Specified Performance Device filled to capacity, Continuous GC

128 KB, 100% read

128 KB, 100% write

4KB, 100% read

4KB, 100% write

4 KB, 100% read

4 KB, 100% write 4 KB, 70:30

4 KB, 70:30

Performance

Workload Conditions

ssdbench2

Sustained Multi-thread Random 4K Mixed IOPS

Sustained Multi-thread Random Read IOPS

Sustained 4K Write Latency

• Test for broad set of application workloads

• Analytics: Large block IO (128K), 90/10 read & write

• Bigblock IO: Large block dump (512K random writes)

• Checkpointing: 4K random writes at full capacity

• Database Simulation: 8K random IO 60/40 mix

• High Frequency Trading: Small block (512b) sequential IO

• Meta Data File Ops: IOZone on multiple XFS filesystems

• OLTP: Innosim benchmark for MySQL, Low writes & DB writes

ssdbench3 – Application Benchmarks

ssdbench-3 Sample Results

TARGET AREA

ssdbench2 & 3 Benchmark Availability

http://www.virident.com/benchmarks

Social Blog Hosting – 4:1 Server Consolidation

Before After

• 4:1 server consolidation

• 4x increase in performance

• Accommodates higher user growth and increased revenue

• Lower management overhead and lower data center costs

4x Performance Improvement

QUESTIONS?

Turbo-charge MySQL Performance With Flash Storage

Panelists: Vadim Tkachenko, Percona

Baron Schwartz, Percona

Shirish Jamthe, Virident

Host: Shridar Subramanian, Virident