Open Source Database Performance Optimization and Monitoring with PMM

这是一个实践教程,介绍了如何使用Percona监控和管理(PMM)平台为MySQL、MongoDB和PostgreSQL数据库服务器设置监控。
PMM是用于管理和监视MySQL、MongoDB和PosgreSQL性能的开放源码工具集。它为数据库服务器提供彻底的基于时间的分析,以确保它们尽可能高效地工作。
您将了解:
-MySQL、MongoDB和PostgreSQL监控最佳实践
-度量和时间序列
-数据收集、管理和可视化工具
-监控部署
-如何使用图表来发现性能问题
-mysql和mongodb的查询分析
-使用Grafana发出警报
-趋势和容量规划
-如何监控HA
请携带一台带有ssh客户端的笔记本电脑,因为您将连接到一个云配置实例,在该实例上部署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