Practical MySQL Performance Optimization

Practical MySQL Performance Optimization
实用MySQL性能优化

展开查看详情

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