MySQL Performance Optimization with PMM

1 .MySQL Performance Optimization and Troubleshooting with PMM Peter Zaitsev, CEO, Percona Percona Technical Webinars 9 May 2018 

2 .Few words about Percona Monitoring and Management (PMM) 100% Free, Open Source database troubleshooting and performance optimization platform for MySQL and MongoDB Based on Industry Leading Technology Roll your own in and out of the Cloud 2 

3 .Exploring Percona Monitoring and Management You should be able to install PMM in • http://bit.ly/InstallPMM 15 minutes or less Would like to follow along in the • https://pmmdemo.percona.com demo ? 3 

4 .In the Presentation Practical approach to deal with some of the common MySQL Issues 4 

5 .PMM is not just for MySQL Supports MongoDB as well Other databases can be added via External Exporters This Presentation is MySQL Focused 5 

6 .Assumptions You’re looking to Have your MySQL Queries Run Faster You want to troubleshoot sudden MySQL Performance Problem You want to find way to run more efficiently (use less Resources) 6 

7 .How to Look at MySQL Performance Resource Based Query Based Approach Approach • All the users • Queries use resources. (developers) care is Slow Performance how quickly their often caused by queries perform resource constraints 7 

8 .Primary Resources CPU Disk IO Memory Network 8 

9 .Low Resource Usage + Poor Performance Contention Mixed Resource Usage • Table Locks/Row Level Locks • Single worker spending 33% • Locking/Latching in MySQL on CPU and Kernel • 33% Waiting on Disk • 33% on Network • Will not be seen as directly constrained by any resource 9 

10 .Load Average • What can you tell me about server load ? 10 

11 .Problems with Load Average Mixes CPU and IO resource usage (on Linux) Is not normalized for number of CPU cores available Does not keep into account Queue Depth Needed for optimal storage performance 11 

12 .CPU Usage • Can observe overall or per core • Matching Load Average in the previous screen 12 

13 .Saturation Metrics • Good to understand where waits are happening • IO Load is not normalized 13 

14 .Looking at CPU Saturation Separately • Can normalize CPU Saturation based on number of threads 14 

15 .Row Locks – Logical Contention • Row Locks are often declared by transaction semantics • But more transactions underway also mean more locks 15 

16 .Zooming in on Row Locks Wait Load • How many MySQL Connections are Blocked because or Row Level Lock Waits 16 

17 .“Load at MySQL Side” • “threads_running” - MySQL is busy handling query • CPU ? Disk ? Row Level Locks ? Need to dig deeper 17 

18 .MySQL Questions – Inflow of Queries • Are we serving more queries or less queries ? • Any spikes or dips ? 18 

19 .Innodb Rows – Actual Work Being Done • Better number to think re system capacity • Not all rows are created equal, but more equal than queries 19 

20 .Commands – What kind of operations • Note if prepared statements are used MySQL is “double counting” 20 

21 .MySQL “Handlers” low lever row access • Works for all storage engines • Gives more details on access type • Mixes Temporary Tables and Non-Temporary tables together 21 

22 .Memory usage by MySQL Leave some memory available for OS Cache and other needs 22 

23 .Innodb in Depth 

24 .Innodb Checkpointing • The log file size is good enough as Uncheckpointed bytes are fraction of log file size 24 

25 .Innodb Checkpointing • Very Close – Innodb Log File Size too small for optimal performance 25 

26 .Innodb Transaction History - not yet Purged Transactions • Short term spikes are normal if some longer transactions are ran on the system 26 

27 .Innodb Transaction History • Growth over long period of time without long queries in the processlist • Often identifies orphaned transactions (left open) 27 

28 .Transaction History Recovery • If Backlog is resolved quickly it is great • If not you may be close to the limit of purge subsystem 28 

29 .Is your Innodb Log Buffer Large Enough? • You will be surprised to see how little log buffer space Innodb needs 29