PMM Demonstration

如果看不到发生的情况,如何优化数据库性能?Percona监控和管理(PMM)是一个免费的开源数据库故障排除和性能优化平台,适用于MySQL和MongoDB。PMM使用度量监视器(grafana+prometheus)来可视化数据点,以及查询分析(qan),以帮助识别和量化未执行的查询,并提供彻底的基于时间的分析,以确保您的数据尽可能高效地工作。
Michael Coburn将提供PMM的简短演示。他还将介绍PMM中最新发布的功能,如QAN for MongoDB、新的MyLocks仪表盘和用于度量监控的工具提示。

展开查看详情

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