Open Source Database Performance Optimization and Monitoring with PMM

1 . Open Source Database Performance Optimization and Monitoring with PMM Fernando Laudares, Vinicius Grippa, Michael Coburn Percona 

2 .Fernando Laudares 2 

3 .Vinicius Grippa 3 

4 .Michael Coburn ● Product Manager for PMM (as well as for Percona Toolkit) ● At Percona for 6 years across multiple MySQL roles ○ Principal Architect, Managing Consultant, Technical Account Manager 4 

5 .Goals of Today's Tutorial 1. Understand the components of PMM ○ pmm-client - Client tools & agents you install on each server ○ PMM Server ■ Prometheus, Grafana, Query Analytics, Metrics Monitor 2. Install PMM Server at your site ○ Docker (today's method) ○ OVA (Open Virtualization Format) - VMware, VirtualBox, etc ○ Amazon AMI from the AWS Marketplace 3. Review queries using Query Analytics 4. Analyze performance using Metrics Monitor 5 

6 .Agenda • Fundamentals • Part 1 - Installation and Configuration • Part 2 - Query Analytics • Part 3 - Metrics Monitor • Questions 6 

7 .Fundamentals 

8 .What is PMM ● Free, Open Source database troubleshooting and performance optimization platform for MySQL, MongoDB, and PostgreSQL ○ We also support: ■ ProxySQL ■ Amazon RDS MySQL & Aurora MySQL ■ Remote MySQL & PostgreSQL instances ● Runs in your secure environment (this is not a SaaS product!) and on your equipment ● Secured with SSL between client and server 8 

9 .PMM Distribution Methods 1. docker ○ docker pull percona/pmm-server:latest 2. Virtual Appliance ○ Supports VMware, RedHat Virtualization, Microsoft Systems Center ○ … and VirtualBox! 3. AWS Marketplace ○ Production-ready AMI running in EC2 9 

10 .AWS Marketplace ● Deploy directly to EC2 ● Running CentOS 7 Search for "pmm" or "Percona Monitoring and Management" https://aws.amazon.com/marketplace/pp/B077J7FYGX 10 

11 .PMM Architecture ● pmm-client (eg. MySQL host) ○ mysqld_exporter - MySQL metrics ○ node_exporter - Linux/OS metrics ○ qan-agent - Query Analytics ● PMM Server ○ Query Analytics ■ QAN API & QAN Application ○ Metrics Monitor ■ Prometheus ■ Grafana 11 

12 .PMM Server Components ● Metrics Monitor ○ Prometheus ■ Timeseries database ■ Powerful PromQL query language ○ Grafana ■ Visualization platform ● Query Analytics ○ View query performance in real-time ○ Aggregated by queries consuming most amount of time in the database ○ Query drill-down for individual query performance (MySQL & MongoDB) ■ MySQL: Rows read & scanned, Query time & count, InnoDB statistics (w/ Percona Server) ■ MongoDB: Query time & count, Docs returned, Response length, Docs scanned 12 

13 .pmm-client Components ● pmm-admin ○ Command-line tool for client management ● node_exporter ○ Agent that exports Linux metrics ● mysqld_exporter, mongodb_exporter, postgres_exporter, proxysql_exporter ○ Agents that export server metrics ● qan-agent ○ Agent that collects query metrics from MySQL Slow Log or PERFORMANCE_SCHEMA, MongoDB profile collection (system.profile) 13 

14 .Prometheus Data Collection ● Prometheus server asks Consul for which services & instances to query ○ by IP address and port ○ Example: curl https://192.168.56.3:42000/metrics ● Prometheus exporter performs data collection upon curl request ● Exporter generates text exposed via web server at :42002/metrics [root@ps57r ~]# curl -s -k https://10.91.136.33:42002/metrics-hr |grep mysql | head -8 # HELP mysql_exporter_collector_duration_seconds Collector time duration. # TYPE mysql_exporter_collector_duration_seconds gauge mysql_exporter_collector_duration_seconds{collector="collect.global_status"} 0.019977679 mysql_exporter_collector_duration_seconds{collector="collect.info_schema.innodb_metric s"} 0.006224816 mysql_exporter_collector_duration_seconds{collector="connection"} 2.1584e-05 # HELP mysql_exporter_hr_last_scrape_error Whether the last scrape of metrics from MySQL resulted in an error (1 for error, 0 for success). # TYPE mysql_exporter_hr_last_scrape_error gauge mysql_exporter_hr_last_scrape_error 0 14 

15 . Part 1 Installation and configuration 

16 .Environment Notes ● Authentication ● What is deployed ○ centos / percona ○ 16 cores, 32GB RAM ● ssh percona@<pmm-server> ○ 9 virtual machines (VirtualBox) ○ See handout for your IP address ■ 3 x PXC ■ 1 x MySQL ● Assumptions ■ 3 x MongoDB ○ Someone ELSE set up the OS, ■ 2 x PostgreSQL configured the database, and sends load (i.e. Application exists) ○ Someone else installed dependencies (docker daemon) ● If you get stuck, just grab our attention!! 16 

17 .Server Configuration - Docker method ● Create docker storage container ○ sudo docker create \ -v /opt/prometheus/data \ -v /opt/consul-data \ -v /var/lib/mysql \ -v /var/lib/grafana \ --name pmm-data \ percona/pmm-server:latest /bin/true 17 

18 .Server Configuration - Docker method ● Start docker container ○ sudo docker run -d \ -p 80:80 \ --volumes-from pmm-data \ --name pmm-server \ --restart always \ percona/pmm-server:latest ● Confirm Server is running ○ http://<pmm-server> 18 

19 .Client Configuration ● Install pmm-client ○ yum -y install pmm-client ● Connect client to PMM Server ○ pmm-admin config --server 10.0.0.13 19 

20 .Adding MySQL services ● pmm-admin add mysql:metrics --user root --password percona18live ● This will set up the following three services: ○ linux:metrics ○ mysql:metrics ○ mysql:queries 20 

21 .Adding MongoDB services ● pmm-admin add mongodb --uri mongodb://mongoadmin:mongoadmin@localhost:2700/admin --cluster MongoCluster mongo1-2700 ○ linux:metrics ○ mongodb:metrics ○ mongodb:queries 21 

22 .Adding PostgreSQL services ● pmm-admin add postgresql ○ linux:metrics ○ postgresql:metrics 22 

23 .Confirming it all Works ● PMM Server: http://<pmm-server>/ ● Prometheus http://<pmm-server>/prometheus ● Do they work? Great - take a break! Stretch your legs ● No? Let's Troubleshoot (next slide…) 23 

24 .Troubleshooting PMM ● Check for any red fields: ○ sudo pmm-admin list ○ sudo pmm-admin check-network ● Restarting one or all components ○ sudo pmm-admin restart linux:metrics pmm-client ○ sudo pmm-admin restart --all ● Logs are in /var/log/pmm-*.log ● Check targets status in Prometheus ○ http://<pmm-server>/prometheus/targets 24 

25 .Query Analytics Examining queries in depth 

26 .Query Analytics Dashboard 26 

27 .Query Analytics Overview ● Query Abstract ○ Query pattern with placeholders ● ID ○ Unique fingerprint, used for query group by ● Load ○ Grand Total Time - percentage of time that MySQL server spent executing the query ● Count ○ QPS, total count during window, % of total ● Latency ○ Min, Med, Avg, P95, Max 27 

28 .MySQL PERFORMANCE_SCHEMA 28 

29 .MySQL Slow Log - *Percona Server only 29