![Page 1: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/1.jpg)
© 2017 Percona1
Michael Coburn, Product Manager PMM
Percona Monitoring and Management Demonstration
October 11th, 2017
![Page 2: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/2.jpg)
© 2017 Percona2
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
![Page 3: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/3.jpg)
© 2017 Percona3
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.
![Page 4: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/4.jpg)
© 2017 Percona4
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
![Page 5: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/5.jpg)
© 2017 Percona5
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
![Page 6: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/6.jpg)
© 2017 Percona6
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?
![Page 7: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/7.jpg)
© 2017 Percona7
Query Analytics - QANMySQL query optimisation
![Page 8: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/8.jpg)
© 2017 Percona8
QAN - Query Analytics - MySQL
![Page 9: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/9.jpg)
© 2017 Percona9
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
![Page 10: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/10.jpg)
© 2017 Percona10
PERFORMANCE_SCHEMA
![Page 11: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/11.jpg)
© 2017 Percona11
Slow log - Percona Server enhanced
![Page 12: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/12.jpg)
© 2017 Percona12
Query fingerprint + EXPLAIN
![Page 13: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/13.jpg)
© 2017 Percona13
JSON EXPLAIN
![Page 14: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/14.jpg)
© 2017 Percona14
CREATE TABLE
![Page 15: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/15.jpg)
© 2017 Percona15
TABLE STATUS
![Page 16: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/16.jpg)
© 2017 Percona16
INDEXES
![Page 17: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/17.jpg)
© 2017 Percona17
QAN - Query Analytics - MongoDB
![Page 18: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/18.jpg)
© 2017 Percona18
Query fingerprint + Example
![Page 19: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/19.jpg)
© 2017 Percona19
JSON EXPLAIN
![Page 20: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/20.jpg)
© 2017 Percona20
Server summary info
▪Click the icon:
▪pt-summary• Instance-specific information
▪pt-mysql-summary• Facts about the running MySQL instance
![Page 21: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/21.jpg)
© 2017 Percona21
Metrics MonitorPrometheus + Grafana + Beautiful dashboards from Percona!
![Page 22: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/22.jpg)
© 2017 Percona22
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
![Page 23: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/23.jpg)
© 2017 Percona23
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
![Page 24: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/24.jpg)
© 2017 Percona24
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
![Page 25: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/25.jpg)
© 2017 Percona25
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
![Page 26: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/26.jpg)
© 2017 Percona26
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
![Page 27: Percona Monitoring and Management Demonstration · •MySQL/MariaDB/MongoDB •Reduce costs and optimize performance Percona Monitoring and Management Demos •Point-in-time visibility](https://reader030.vdocuments.us/reader030/viewer/2022040611/5ed8766754dcf351405ed4db/html5/thumbnails/27.jpg)
DATABASE PERFORMANCEMATTERS
Database Performance MattersDatabase Performance MattersDatabase Performance MattersDatabase Performance MattersDatabase Performance Matters