Practical MySQL Performance Optimization

Practical MySQL Performance Optimization


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

3. First Things First MySQL Performance does not Matter! 3

4. What DOES Matter? Application Performance! 4

5. Even More so Application Performance Always Matters! 5

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

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

8. Say Performance Think about Response Time 8

9. Related Issues Stability Scalability Efficiency 9

10. Scalability? •Load Scalability with: •Data Size •Infrastructure 10

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

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

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

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

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

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

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

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

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

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

21. Schema Tips Learn Indexing • 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

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

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

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

25. Hardware CPU Memory Disk Network 25

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

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

28. Invest in Memory or Storage 28

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