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)
Santa Clara, California | April 23th – 25th, 2018 MIchael Coburn, Product Manager
2
3
4
○ pmm-client - Client tools & agents you install on each server ○ PMM Server
i. Prometheus, Grafana, Consul, QAN
○ OVF - Open Virtualization Format (VMware, Microsoft Systems Center, Virtualbox) ○ docker ○ AWS Marketplace
5
○ We also support:
■ ProxySQL ■ Amazon RDS MySQL ■ Amazon Aurora MySQL
6
○ docker pull percona/pmm-server:latest
○ Supports VMware, RedHat Virtualization, Microsoft Systems Center ○ … and VirtualBox!
○ Production-ready AMI running in EC2 ○ Available since November 2017
7
Search for "pmm" or "Percona Monitoring and Management" https://aws.amazon.com/marketplace/pp/B077J7FYGX
8
○ mysqld_exporter ○ node_exporter ○ qan-agent
○ Query Analytics
■ QAN API ■ QAN App
○ Metrics Monitor
■ Prometheus ■ Grafana ■ Consul
9
○ Prometheus
■ Timeseries database ■ Powerful PromQL query language
○ Grafana
■ Visualization platform
○ Consul
■ Tracks which services are available to be scraped by Prometheus
○ 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
10
○ Command-line tool for client management
○ Agent that exports Linux metrics
○ Agent that exports MySQL server metrics
○ Agent that collects query metrics from MySQL Slow Log or PERFORMANCE_SCHEMA
11
○ by IP address and port ○ Example: curl https://192.168.56.3:42000/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
Installation and configuration
13
○ percona / percona
○ ssh percona@192.168.56.3
○ ssh admin@192.168.56.2
14
○ 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
15
○ File > Import Appliance…
○ VirtualBox > Preferences > Network > Host-only Networks
■ Create new host-only network via button on right
○ Adapter
■ Configure IPv4 Address: 192.168.56.1
○ Enable Server
■ Configure Server Address: 192.168.56.1 ■ Configure Lower Address Bound: 192.168.56.2 ■ Configure Upper Address Bound: 192.168.56.10
○ Per instance
16
17
18
19
20
○ Optional:
■ ssh-agent bash ■ ssh-add ~/.ssh/pmm
○ ssh -l percona 192.168.56.3 ○ [client $] ssh admin@192.168.56.2
○ [percona@client ~]$ ping pmm-server ○ [admin@pmm-server ~]$ ping client
○ [pmm-server $] date --set='TimeFromClient'
21
○ client$ ./sysbench.sh
22
○ sudo pmm-admin config --server 192.168.56.2 --server-user percona --server-password percona
○ 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
○ pmm-linux-metrics ○ pmm-mysql-metrics ○ pmm-mysql-queries
23
24
○ sudo pmm-admin list ○ sudo pmm-admin check-network
■ Time drift ?
○ sudo pmm-admin restart linux:metrics pmm-client ○ sudo pmm-admin restart --all
○ http://192.168.56.2/prometheus/targets
25
26
○ 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
○ ssh -i ~/.ssh/pmm ec2-user@<IP>
We'll resume in 15 minutes
Examining queries in depth
29
30
○ Query pattern with placeholders
○ Unique fingerprint, used for query group by
○ Grand Total Time - percentage of time that MySQL server spent executing the query
○ QPS, total count during window, % of total
○ Min, Med, Avg, P95, Max
31
32
33
34
35
○ pt-summary ○ pt-mysql-summary
Eye candy
37
○ Prometheus ○ CloudWatch ○ Graphite ○ Elasticsearch
○ Define your graph metrics, and let the hosts get filled in automatically ○ GREAT for large, dynamic environments where hosts are considered ephemeral
38
○ mysql_global_variables_innodb_buffer_pool_instances{instance= "ps57",job="mysql"} = 8 ○ mysql_slave_status_slave_io_running{instance="ps57r",job="mys ql",master_host="10.91.136.32",master_uuid="9809315d-4d97-11e 6-b85e-0007cb03dc86"} = 1
○ We're using consul in PMM for service discovery
39
○ Cross Server graphs
○ Trends Overview dashboard
○ MySQL Overview, InnoDB, InnoDB Advanced
○ Largest tables by rows and size, total DB size, tables by rows read and changed, auto_increment usage (about to hit the limit?)
○ Top users by connection count, network usage, rows read/changed
40
○ pmm-admin annotate "Application deployment v1.3"
41
○ Cannot use Templated Variables ○ Instead, replace with string constants for instance name
Almost the end Parting thoughts
43
○ We are looking at options to present a longer history
○ On high-latency links you might need to tune scrape_interval up
○ Prometheus in particular needs a lot of CPU cores and fast disks, in order to sequence scrape data before writing chunks to disk
○
○
44
○ faster, aggregation across all servers, new filtering and sorting options
45
46
47