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 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