PMM Demonstration

1 . Percona Monitoring and Management Demonstration Michael Coburn, Product Manager PMM October 11th, 2017 1 © 2017 Percona 

2 .Your presenter ▪ Michael Coburn - Product Manager PMM • Working at Percona for almost 5 years • Consultant, Manager, TAM, now Product Manager • Canadian living in México 2 © 2017 Percona 

3 .Why does this talk exist? ▪ Troubleshooting performance issues can be a bit tricky, especially when you’re given a broad statement that the database is slow. ▪ Learn to direct your attention to the correct moving pieces and fix what needs your attention. ▪ Learn how all this is done at Percona, what we monitor and track, and the tools we use. 3 © 2017 Percona 

4 .Agenda ▪ What is PMM? ▪ Using Metrics Monitor to visualise events over time ▪ Using QAN to optimise MySQL queries ▪ What's new from our September 1.3 release 4 © 2017 Percona 

5 .What is PMM ▪ Free, Open Source database troubleshooting and performance optimisation platform for MySQL and MongoDB • We also support MariaDB and ProxySQL ▪ Runs in your secure environment (not a SaaS) and on your equipment ▪ Secured with SSL encryption between pmm-client and pmm-server 5 © 2017 Percona 

6 .My database is slow !?! ▪ What is ‘normal’ ? Establishing a baseline ▪ Are other factors impacting your database performance? • Hardware/virtualisation layer • Network • Application behaviour ▪ But I already have top and SHOW PROCESSLIST, isn't that sufficient? 6 © 2017 Percona 

7 .Query Analytics - QAN MySQL query optimisation 7 © 2017 Percona 

8 .QAN - Query Analytics - MySQL 8 © 2017 Percona 

9 .QAN overview ▪ Query Abstract • Query pattern with placeholders ▪ ID • Unique fingerprint, used for query group by ▪ Load • Grand Total Time - percentage of time that the MySQL server spent executing a specific query ▪ Count • QPS, total count during window, % of total ▪ Latency • Min, Med, Avg, P95, Max 9 © 2017 Percona 

10 .PERFORMANCE_SCHEMA 10 © 2017 Percona 

11 .Slow log - Percona Server enhanced 11 © 2017 Percona 

12 .Query fingerprint + EXPLAIN 12 © 2017 Percona 

13 .JSON EXPLAIN 13 © 2017 Percona 

14 .CREATE TABLE 14 © 2017 Percona 

15 .TABLE STATUS 15 © 2017 Percona 

16 .INDEXES 16 © 2017 Percona 

17 .QAN - Query Analytics - MongoDB 17 © 2017 Percona 

18 .Query fingerprint + Example 18 © 2017 Percona 

19 .JSON EXPLAIN 19 © 2017 Percona 

20 .Server summary info ▪ Click the icon: ▪ pt-summary • Instance-specific information ▪ pt-mysql-summary • Facts about the running MySQL instance 20 © 2017 Percona 

21 .Metrics Monitor Prometheus + Grafana + Beautiful dashboards from Percona! 21 © 2017 Percona 

22 .What is Grafana ▪ Open Source data visualisation tool ▪ Popular datasources • Prometheus • CloudWatch • Graphite • Elasticsearch ▪ Templated Variables • Define your graph metrics, and let the hosts get filled in automatically ▪ GREAT for large, dynamic environments where hosts are considered ephemeral 22 © 2017 Percona 

23 .How can I... ▪ At a glance MySQL + Storage Engine indepth • MySQL Overview, InnoDB, MyRocks ▪ MongoDB high level + ReplSet views • MongoDB Overview, MongoDB ReplSet ▪ Compare servers' to each other • Cross Server graphs ▪ Show behaviour now() compared to past period • Trends Overview dashboard 23 © 2017 Percona 

24 .How can I… - Percona Server only ▪ Table statistics • Largest tables by rows and size, total DB size, tables by rows read and changed, auto_increment usage (about to hit the limit?) ▪ User statistics • Top users by connection count, network usage, rows read/changed ▪ Query Response Time • Average, read vs write, distribution 24 © 2017 Percona 

25 .Distribution methods ▪ Docker • Most common deployment method for PMM today ▪ Virtual appliance • OVF/OVA file format, compatible with VMware, VirtualBox, Microsoft, Red Hat ▪ AWS AMI • Clone the image from any of the 14 regions • Deployment moving to AWS Marketplace 25 © 2017 Percona 

26 .Talk to Percona Experts at AWS Re:Invent! Database Performance for Cloud Deployments ▪Percona Support and Managed Services •Amazon RDS, Aurora, Roll Your Own •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance ▪Percona Monitoring and Management Demos •Point-in-time visibility and historical trending of database performance •Detailed query analytics ▪Booth #1138 26 © 2017 Percona 

27 . DATABASE PERFORMANCE Database Performance Matters MATTERS