Innodb Performance Optimization

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 https://www.mysql.com/why-mysql/benchmarks/ 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 http://bit.ly/2zeRpSZ 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