Monitoring MySQL Performance with Percona Monitoring and - PowerPoint PPT Presentation

Monitoring MySQL Performance with Percona Monitoring and Management Santa Clara, California | April 23th – 25th, 2018 MIchael Coburn, Product Manager

Your Presenter • Product Manager for PMM (also Percona Toolkit and Percona XtraBackup) • With Percona for 6 years through 6 different roles • Consultant, Managing Consultant, Principal Architect, Technical Account Manager, Principal Support Engineer • http://bit.ly/JoinPerconaLiveSlack #monitoring-mysql-perf Percona Live App 2

Agenda • Introductions - 1:30pm • Part 1 - Installation and Configuration - 1:45pm • Break ~3:00pm • Part 2 - Query Analytics - 3:15pm • Part 3 - Metrics Monitor - 3:45pm • Questions - 4:15pm 3

Goals of Today's Tutorial 1. Understand the components of PMM ○ pmm-client - Client tools & agents you install on each server ○ PMM Server i. Prometheus, Grafana, Consul, QAN 2. Able to install PMM Server at your site ○ OVF - Open Virtualization Format (VMware, Microsoft Systems Center, Virtualbox) ○ docker ○ AWS Marketplace 3. Review MySQL queries using Query Analytics 4. Analyze MySQL performance using Metrics Monitor 4

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

PMM Distribution Methods ● docker ○ docker pull percona/pmm-server:latest ● Virtual Appliance ○ Supports VMware, RedHat Virtualization, Microsoft Systems Center ○ … and VirtualBox! ● AWS Marketplace ○ Production-ready AMI running in EC2 ○ Available since November 2017 6

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

PMM Architecture ● pmm-client ○ mysqld_exporter ○ node_exporter ○ qan-agent ● PMM Server ○ Query Analytics ■ QAN API ■ QAN App ○ Metrics Monitor ■ Prometheus ■ Grafana ■ Consul 8

PMM Server Components ● Metrics Monitor ○ Prometheus Timeseries database ■ Powerful PromQL query language ■ ○ Grafana Visualization platform ■ ○ Consul Tracks which services are available to be scraped by Prometheus ■ ● Query Analytics ○ View query performance in real-time ○ Aggregated for queries consuming most amount of time in MySQL ○ Query drill-down for individual query performance Rows read, Rows scanned, Query time, Query count ■ InnoDB statistics (Percona Server for MySQL only ■ 9

pmm-client Components ● pmm-admin ○ Command-line tool for client management ● node_exporter ○ Agent that exports Linux metrics ● mysqld_exporter ○ Agent that exports MySQL server metrics ● qan-agent ○ Agent that collects query metrics from MySQL Slow Log or PERFORMANCE_SCHEMA 10

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 11

Part 1 Installation and configuration

Environment Notes ● Authentication: ~/.ssh/config ○ percona / percona ● client 192.168.56.3 Host client ○ ssh percona@192.168.56.3 ● server 192.168.56.2 User percona ○ ssh admin@192.168.56.2 HostName 192.168.56.3 IdentityFile ~/.ssh/pmm 13

PMM Installation Steps ● High level steps: ○ Import Appliance into VirtualBox ○ Configure VirtualBox Host-Only Networking ○ Validate client and server can communicate ○ Start up sysbench test (optional) ○ Configure pmm-admin to start: linux:metrics ■ mysql:metrics ■ mysql:queries ■ 14

Configuring VirtualBox ● Import Appliance ● DHCP Server ○ File > Import Appliance… ○ Enable Server ● Configure Network ■ Configure Server Address: ○ VirtualBox > Preferences > Network 192.168.56.1 > Host-only Networks ■ Configure Lower Address Bound: ■ Create new host-only network via 192.168.56.2 button on right ■ Configure Upper Address Bound: ○ Adapter 192.168.56.10 ■ Configure IPv4 Address: ● Consider setting Execution Cap 192.168.56.1 at 50%, 2 cores ○ Per instance 15

VirtualBox Host Only Network < v5 - OSX 16

VirtualBox > v5 - Windows 17

VirtualBox Network Interfaces - OSX 18

VirtualBox Network Interfaces - Windows 19

VirtualBox Network Interfaces - Windows ● Start the two instances via VirtualBox ~/.ssh/config console Host client ● Connect using ssh ○ Optional: User percona ■ ssh-agent bash ■ ssh-add ~/.ssh/pmm ○ ssh -l percona 192.168.56.3 HostName 192.168.56.3 ○ [client $] ssh admin@192.168.56.2 ● Ensure the nodes can see each other IdentityFile ○ [percona@client ~]$ ping pmm-server ~/.ssh/pmm ○ [admin@pmm-server ~]$ ping client ● Configure pmm-server for correct time ○ [pmm-server $] date --set='TimeFromClient' 20

Starting Sysbench (optional) ● We use sysbench to generate load in MySQL ● Start the script: ○ client$ ./sysbench.sh ● Configuration is for 1 trx/sec of 20 events on a 10k row InnoDB table 21

Configuring PMM ● FINALLY! ● Connect client to pmm-server ○ sudo pmm-admin config --server 192.168.56.2 --server-user percona --server-password percona ● Start data collection ○ sudo pmm-admin add mysql --create-user --socket /var/lib/mysql/mysql.sock --user percona --password percona ○ Generates a MySQL user account with bare minimum privileges ● Creates three services ○ pmm-linux-metrics ○ pmm-mysql-metrics ○ pmm-mysql-queries 22

Confirming it all Works ● PMM Server: http://192.168.56.2/ ● Prometheus http://192.168.56.2/prometheus ● Do they work? ● Great - take a break! ● No? Let's Troubleshoot (next slide…) 23

Troubleshooting PMM ● Check for any red fields: ○ sudo pmm-admin list ○ sudo pmm-admin check-network Time drift ? ■ ● pmm-server$ sudo date --set='<TimeFromClient>' ● 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://192.168.56.2/prometheus/targets 24

Confirming IP address of PMM Server ● Confirm the IP address the server obtained from DHCP 25

If all else fails... ● I have a few PMM Server instances available in EC2: ○ http://13.58.238.204 ○ http://52.14.41.0 ○ http://18.221.240.156 ○ http://18.221.171.225 ○ http://13.58.244.142 ● To obtain a shell: ○ ssh -i ~/.ssh/pmm ec2-user@<IP> 26

Break! We'll resume in 15 minutes

Using Query Analytics (QAN) Examining queries in depth

Query Analytics Dashboard 29

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 30

PERFORMANCE_SCHEMA 31

Slow Log - Percona Server Enhanced 32

EXPLAIN - Table and JSON 33

CREATE TABLE, TABLE STATUS, and INDEXES 34

Server Summary Information ● Collects and displays per Server: ○ pt-summary ○ pt-mysql-summary ● _PMM System Summary ● Summary can be downloaded from the UI 35

Part 3 - Using Metrics Monitor Eye candy

Grafana in a Nutshell ● 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 37