Turbo-charge MySQL Performance With Flash Storage Panelists: Vadim Tkachenko, Percona Baron Schwartz, Percona Shirish Jamthe, Virident Host: Shridar Subramanian, Virident Agenda • 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 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? 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? What Are Available Options? • More memory? • Invest in SAN? • Deploy “Sharding”? • Get faster I/O? Flash Storage for Performance Gap Ops / Second 1,000,000,000’s CPU 100,000,000’s DRAM 100,000’s Flash Storage 100’s HDD Picoseconds Nanoseconds Microseconds Milliseconds Seconds Latency Types of Flash Storage Native PCIe SSD : Ops / Second 1,000,000,000’s PCIe-Attach (Virident, Fusion-io) • Storage plus application-specific memory interfaces • Application value focused SATA/SAS SSDs : PCIe-Attach (LSI, OCZ) • Coexists with legacy RAID storage sub-systems • Time-to-market focused CPU SATA/SAS SSDs : 100,000,000’s Disk form factor (Intel X25) • Coexists with legacy RAID storage sub-systems • Cost and packaging focused DRAM 100,000’s 100’s HDD Picoseconds Nanoseconds Microseconds Milliseconds Seconds Latency Where Flash Storage Benefits MySQL? • 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 How can MySQL Better Leverage Flash? • Use a flash-optimized version of MySQL • Shard and consolidate / multi-tenant Example of Optimized MySQL MySQL version is important • 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 Use case • Put all data ( ibdata1, ib_log, tables.ibd) on SSD • Use several SSDs in server for larger data size • 5-7x Improvement Memory vs. Flash Some Recommendations • 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 Percona Server Configs • 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 Impact of TRX Log on MySQL Performance Scaling MySQL on Fewer Servers • 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? Concerns with Flash Storage • Write performance degradation over time • Sizing for worst case IOPS • Flash Storage Lifetime • Power loss protection Example of Performance Variability What Causes the 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 Performance Variability of Flash Storage Performance 128 KB, 100% read 128 KB, 100% write 4KB, 100% read 4KB, 100% write 4 KB, 100% read 4 KB, 70:30 4 KB, 100% write 4 KB, 70:30 ssdbench2 Specified Performance “Seasoned” Device Mixed read-write (Databases, Caching, Metadata serving, …) Workload Conditions Device filled to capacity, Continuous GC Sustained Multi-thread Random 4K Mixed IOPS Sustained Multi-thread Random Read IOPS Sustained 4K Write Latency ssdbench3 – Application Benchmarks • 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 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 4x Performance Improvement • Accommodates higher user growth and increased revenue • Lower management overhead and lower data center costs QUESTIONS? Turbo-charge MySQL Performance With Flash Storage Panelists: Vadim Tkachenko, Percona Baron Schwartz, Percona Shirish Jamthe, Virident Host: Shridar Subramanian, Virident