1.Practical MySQL Performance Optimization Peter Zaitsev, CEO, Percona 10 November, 2016 

2. In This Presentation We’ll Look at how to approach Performance Optimization Discuss Practical Performance Optimization Tips Look at the Tools which can help us 2 www.percona.com 

3. First Things First MySQL Performance does not Matter! 3 www.percona.com 

4. What DOES Matter? Application Performance! 4 www.percona.com 

5. Even More so Application Performance Always Matters! 5 www.percona.com 

6. Take Away Performance Performance Problems might Solutions might not not be MySQL be with MySQL 6 www.percona.com 

7. Many “Tools” Use • MongoDB the • Cassandra • Hadoop best • Spark Tool for • Elastic the job • Redis 7 www.percona.com 

8. Say Performance Think about Response Time 8 www.percona.com 

9. Related Issues Stability Scalability Efficiency 9 www.percona.com 

10. Scalability? •Load Scalability with: •Data Size •Infrastructure 10 www.percona.com 

11. Performance “Sandbags” Security Manageability Compatibility Compliance Ease of use by Developers 11 www.percona.com 

12. “Good Enough” You can always improve your system. Know when to stop. 12 www.percona.com 

13. What MySQL Does •Selects Processes •Inserts Queries •Deletes •Updates 13 www.percona.com 

14. What to focus on? • Making queries Performance run faster Optimization • Using less focused resources • Scaling better 14 www.percona.com 

15. Transaction Optimization Specific •Find out which Application queries it runs Transaction •Optimize them 15 www.percona.com 

16. General Optimization Look at •Prioritize what them queries Server •Optimize Runs them 16 www.percona.com 

17. Queries Are those • Get rid of them? the • Can we change right them to be doing queries less work? ? 17 www.percona.com 

18. Things to Consider Do not look at • But avoid focusing only the average case only on outliers Look at trends • Consider daily, weekly, over time monthly cycles Think about • Data size change? future performance Cardinality? 18 www.percona.com 

19. Query Tips Do less queries – latency and overhead Read less data; Modify less data Less data processing on the fly How much data is traversed vs sent How much data is sent vs used by app 19 www.percona.com 

20. Schema Look at •Minor Schema Schema changes and Queries •Data together Architecture 20 www.percona.com 

21. Schema Tips Learn Indexing • http://bit.ly/1rAtamE Design • Starting with Text book schema is OK Schema for ending is likely not data access • Partitioning & Sharding Technics • Normalization and Denormalization • Covering Indexes 21 www.percona.com 

22. The Following Also Matter Infrastructure Operating System and Configuration MySQL Version MySQL Configuration 22 www.percona.com 

23. Optimization Process Low Hanging Fruit Medium Level Hard Changes • MySQL • Infrastructure • Major Configuration • OS schema • OS Settings • MySQL changes • Indexes Version • Application • Caching architecture change 23 www.percona.com 

24. Infrastructure Scaling Up or Type Out • One MySQL • Public Cloud Node • Private Cloud • Many MySQL • Bare Metal Nodes 24 www.percona.com 

25. Hardware CPU Memory Disk Network 25 www.percona.com 

26. CPU Intel owns the market 2 sockets commonly used Faster cores or more cores Turboboost 26 www.percona.com 

27. Memory Main purpose – Cache Think Database size vs Memory Look together with Storage Optimization 27 www.percona.com 

28. Invest in Memory or Storage 28 www.percona.com 

29. Storage Types Flash RAID • Directly • PCI-E • Hardware Attached • SATA • Software • SAN “Disks” • Filesystem • NAS • TLC, MLC, • Virtualized eMLC, • Cloud SLC 29 www.percona.com