MySQL Performance Optimization and Troubleshooting with PMM

1 .MySQL Performance Optimization and Troubleshooting with PMM Peter Zaitsev, CEO, Percona Percona University Kiev 11 November 2017 

2 . Few words about Percona Monitoring and Management (PMM) Free, Open Source database troubleshooting and performance optimization platform for MySQL and MongoDB 100% Free and Open Source 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 15 • http://bit.ly/InstallPMM minutes or less Would like to • https://pmmdemo.percona.co follow along in the demo ? m 3 

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

5 . 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) 5 

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

7 . Primary Resources CPU Disk IO Memory Network 7 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

22 .Innodb in Depth 

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

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

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

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

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

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

29 . Innodb IO • Will often roughly match disk IO • Allows to see the writes vs fsyncs 29