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