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