Open Source Database Performance Optimization and Monitoring with PMM

1 . Open Source Database Performance Optimization and Monitoring with PMM Vinnie Grippa, Charly Batista, Michael Coburn Percona 

2 .Vinicius Grippa ● Support Engineer at Percona since 2017 ● Working with MySQL for over six years ● Working with databases for over nine years ● Speaker at PL 2018 and meetups about MySQL/MongoDB 2 

3 .Charly Batista ● Senior Support Engineer at Percona ● MySQL and PostgreSQL expert ● Working with databases for over 15 years ● Speaker at Percona Live and meetups about Database and Development 3 

4 .Michael Coburn ● Product Manager for PMM (and Percona Toolkit) ● At Percona for six 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 and 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. Deploy PMM 2 (Optional) 5 

6 .Tutorial Requirements • Laptop • ssh client • web browser 6 

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

8 .Fundamentals 

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

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

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

12 .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 and QAN Application ○ Metrics Monitor ■ Prometheus ■ Grafana 12 

13 .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 and MongoDB) ■ MySQL: Rows read and scanned, Query time and count, InnoDB statistics (w/ Percona Server) ■ MongoDB: Query time and count, Docs returned, Response length, Docs scanned 13 

14 .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) 14 

15 .Prometheus Data Collection ● Prometheus server asks Consul for which services and 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_metrics" } 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 15 

16 . Part One Installation and configuration 

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

18 .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 18 

19 .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> 19 

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

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

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

23 .Adding PostgreSQL Services ● pmm-admin add postgresql --host=localhost --user pmm --password 'pmm' ○ linux:metrics ○ postgresql:metrics 23 

24 .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…) 24 

25 .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 25 

26 .Query Analytics Examining queries in depth 

27 .Query Analytics Dashboard 27 

28 .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 28 

29 .MySQL PERFORMANCE_SCHEMA 29