Using PMM to Troubleshoot MySQL Performance Issues

成功的应用程序常常受到MySQL性能的限制。Michael将向您展示如何使用Percona监视和管理(PMM)获得出色的MySQL性能。在解决MySQL性能问题时,将演示如何利用查询分析和度量监视器的组合。我们将回顾PMM的基本组件,并使用一些最常见的数据库慢度案例作为查找位置和执行操作的示例。
网络研讨会结束后,您将更好地了解:
1)查询度量,包括发送的字节、锁定时间、发送的行等
2)指标监测
3)如何识别MySQL性能问题
4)数据库性能的时间点可见性和历史趋势

展开查看详情

1. Using Percona Monitoring and Management to Troubleshoot MySQL Performance Issues Michael Coburn, Product Manager PMM September 7th, 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 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 ▪ 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.Query fingerprint + EXPLAIN 10 © 2017 Percona

11.PERFORMANCE_SCHEMA 11 © 2017 Percona

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

13.CREATE TABLE 13 © 2017 Percona

14.TABLE STATUS 14 © 2017 Percona

15.INDEXES 15 © 2017 Percona

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

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

18.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 18 © 2017 Percona

19.How can I... ▪ Compare servers' to each other • Cross Server graphs ▪ Show behaviour now() compared to past period • Trends Overview dashboard ▪ At a glance MySQL + indepth • MySQL Overview, InnoDB, InnoDB Advanced ▪ View Prometheus performance data • Prometheus dashboard ▪ MariaDB - Aria, Online DDL, ICP, and Defragmentation • MariaDB dashboard 19 © 2017 Percona

20.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 20 © 2017 Percona

21.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 21 © 2017 Percona

22.Get Your Tickets for Percona Live Europe! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland Last Year’s Conference Sold Out! Reserve your spot ASAP. 22 © 2017 Percona

23.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 23 © 2017 Percona

24. DATABASE PERFORMANCE Database Performance Matters MATTERS