Innodb Performance Optimization

InnoDB是MySQL和Percona Server for MySQL最常用的存储引擎之一。它平衡了高可靠性和高性能,是MySQL和Percona Server为MySQL团队开发的大多数存储引擎的重点。
本次网络研讨会关注的是InnoDB,包括MySQL5.7和Percona Server for MySQL的新开发。Peter将解释如何使用它,以及帮助您从应用程序获得最佳性能的许多配置选项。


1. Innodb Performance Optimization Most important practices Peter Zaitsev CEO Percona Technical Webinars December 20th, 2017 1 © 2017 Percona

2.About this Presentation Innodb Architecture and Performance Optimization 3h In-depth tutorial delivered at Percona Live Conferences This is the most important highlights + some new material 2 © 2017 Percona

3.Performance Optimizations What are the Goals of “Performance Optimization” ? 3 © 2017 Percona

4.Goals of Performance Optimization Make Queries • Performance Run Faster Improve • And Save Costs Efficiency Achieve • Run “Larger” applications on the same Scalability system 4 © 2017 Percona

5.This Presentation Focus Getting most of the Single Node Not touching Architecture Optimizations 5 © 2017 Percona

6.Optimization Areas Hardware/Infrastructure Operating Systems MySQL Version MySQL Settings Queries Schema 6 © 2017 Percona

7.Hardware and Infrastructure 7 © 2017 Percona

8.Hardware/Infrastructure Optimizations CPU Memory Storage Network 8 © 2017 Percona

9.General and Amazon Cloud Most Suggestions apply to Cloud and Non-Cloud Environments Specific Recommendations for AWS Cloud 9 © 2017 Percona

10.CPU for Innodb Faster CPUs are Better Mostly Single Threaded handling of a single Query Multi-Core for Highly Concurrent Workload MySQL now scales to 64+ Cores 10 © 2017 Percona

11.MySQL/Innodb Multi Core Scalability 11 © 2017 Percona

12.How much Concurrency do you Have ? Status Variable threads_running is a great indicator 12 © 2017 Percona

13.OK but what about CPU ? Number of “runnable” processes in VMSTAT good proxy 13 © 2017 Percona

14.CPU on AWS M5 Instance Type • General Purpose C5 Instance Type • Faster CPU but not as much memory R4 Instance Type • More Memory but less CPU X1/X1e Instance Type • Even More Memory per CPU T2 Instance Type • Burstable CPU for light workloads 14 © 2017 Percona

15.Memory Memory is mostly used for caching Try to fit “Working Set” in memory Caching Improves Reads and Writes Performance 15 © 2017 Percona

16.Memory and Storage Capacity Planning Think about Misses • Hit ratio is useless (Storage IO) Think about Reads and • Different Performance Profile Writes separately Think Throughput • Do not push more IO than device can handle Think Latency • How many IO query can do and still be fast ? 16 © 2017 Percona

17.Writes Delaying Writes Behind Many Can’t go on Writes there is forever a Read 17 © 2017 Percona

18.Flushing Impacts Performance Faster Performance at First, Until Flushing needs to happen 18 © 2017 Percona

19.Innodb Change Buffer – Long Warmup 19 © 2017 Percona

20.Storage Even Fastest Storage is a lot slower than Memory Storage IO has to be done in 16K pages (by default) Memory accesses can read/write few bytes 20 © 2017 Percona

21.Type of Storage Use SSD Whenever Directly Attached or not Local NVMe and Intel Optane are fastest 21 © 2017 Percona

22.Storage IO latency is critical What is optimal concurrency (queue depth) Reads vs Writes Writes and Flushes Write Cache, but Durable Write Cache 22 © 2017 Percona

23.Read and Write Latency 23 © 2017 Percona

24.Understanding IO Concurrency 24 © 2017 Percona

25.AWS Storage Chocies EBS io1 (Provisioned Local NVMe (i3 EBS gp2 IOPS) instance type) • Up to 500MB/sec • Burstable for small • Non Redundant and 32.000 IOPS volumes (less then • Performance and per volume 1TB) Space Depends on • Scale IO capacity • Performance scales Instance Size independently with size – 3 IOPS • Up to 3.3M IOPS from the size per GB and 16GB/sec • Expensive 25 © 2017 Percona

26.PXC Best Practices on AWS Extensive Benchmarks done 26 © 2017 Percona

27.Network “Front End” “Back End” Network •To the MySQL •For Network Client Attached Storage (Application) 27 © 2017 Percona

28.Network Performance Latency • The Most Critical Bandwith • Can be bottleneck for Large Data Transfer Variance • Network performance is not constant 28 © 2017 Percona

29.Network in AWS Single Availability Single Region, Multiple Regions Zone (AZ) Different AZ • Lowest Latency • Medium Latency • Highest Latency (Fastest) (Slowest) 29 © 2017 Percona