Hipster MySQL Monitoring: Serving a deconstructed PMM Percona Live - PowerPoint PPT Presentation

Hipster MySQL Monitoring: Serving a deconstructed PMM Percona Live 2017 Santa Clara, California | April 24th – 27th, 2017 Ben Mildren, Senior Database Engineer

The deconstructed flat white digitalocean.com

Just give me coffee! digitalocean.com

What is PMM? ● Appliance ○ Abstracted setup ○ Batteries included ● Monitoring ○ Query Analytics (Log Analysis) ○ Metrics Monitor (Performance Monitoring) ● Management ○ MySQL Replication Topology Manager (Orchestrator) digitalocean.com

What is PMM? Query Analytics ● QAN ○ QAN App (https://github.com/percona/qan-app) ○ QAN API (https://github.com/percona/qan-api) ○ QAN Agent (https://github.com/percona/qan-agent) digitalocean.com

PMM Components - QAN ● QAN Agent consumes slow query log / performance schema ○ Fingerprints Queries for aggregation ● Sends data to QAN API ○ Stores data in MySQL DB local to PMM ● Displays data via QAN App (web gui) ● MySQL Query Analysis (Log Analysis) ○ pt-query-digest, Anemometer, ELK ○ VividCortex & Honeycomb digitalocean.com

What is PMM? Metrics Monitor ● Prometheus (https://prometheus.io/) ○ Exporters ○ https://prometheus.io/docs/instrumenting/exporters/ ● Grafana (https://grafana.com/) ○ Percona Dashboards ○ https://github.com/percona/grafana-dashboards digitalocean.com

PMM Components - Prometheus ● Originally developed at Soundcloud ● Based on Borgmon (Google) ● Whitebox monitoring ● Pull vs Push ● Storage / Retention ○ Graphite ○ InfluxDB ● Exporters digitalocean.com

PMM Components - Consul ● Service Discovery & K/V Data store ● Single node configuration ● Uses API (no local agents) ● Services for each exporter digitalocean.com

PMM Components - Grafana ● Dashboarding ● Plugins ○ Panels ○ Data sources ○ Apps digitalocean.com

What is PMM? MySQL Replication Topology Manager ● Orchestrator (https://github.com/github/orchestrator) ● Mature project under custodianship of Github ● Automates master failover / slave promotion digitalocean.com

PMM Overview ● PMM Server ○ Docker Container ○ Open Virtual Appliance (OVA) ○ Amazon Machine Images (AMI) ● PMM Client ○ DEB ○ RPM https://github.com/percona/pmm/blob/master/doc/source/images/pmm-diagram.png digitalocean.com

PMM Components - PMM Glue ● pmm landing page (PMM Server) ● pmm admin (PMM Client) digitalocean.com

Walking through the installation.. Install PMM Server (Docker Container) 1. < install docker > 2. Create data container docker create -v /opt/prometheus/data -v /opt/consul-data -v /var/lib/mysql \ -v /var/lib/grafana --name pmm-data percona/pmm-server:1.1.3 /bin/true 1. Create PMM server container docker run -d -p 80:80 --volumes-from pmm-data --name pmm-server --restart always \ percona/pmm-server:1.1.3 digitalocean.com

PMM Server under the hood.. ● https://github.com/percona/pmm-server ● https://github.com/percona/pmm-server/blob/master/Dockerfile ○ Centos based ○ Installed using Ansible playbooks (local connection) ○ Docker runs multiple processes using supervisord digitalocean.com

PMM Server under the hood.. ● https://github.com/percona/pmm-server ● https://github.com/percona/pmm-server/blob/master/playbook-install.yml ○ install / configuration ○ pmm yum repo ● https://github.com/percona/pmm-server/blob/master/playbook-init.yml ● https://github.com/percona/pmm-server/blob/master/entrypoint.sh ● https://github.com/percona/pmm-server/blob/master/supervisord.conf digitalocean.com

Walking through the installation.. Install PMM Client 1. Download and install repo ( yum or apt ) 2. Install pmm client sudo apt-get install pmm-client 1. Connect client to pmm server sudo pmm-admin config --server < insert pmm server address here > 1. Start collecting data sudo pmm-admin add mysql digitalocean.com

PMM Client under the hood.. ● https://github.com/percona/pmm-client sudo pmm-admin config --server < insert pmm server address here > ● https://github.com/percona/pmm-client/blob/master/pmm-admin.go#L558-L587 ○ https://github.com/percona/pmm-client/blob/master/pmm/config.go ○ Creates pmm config file (/usr/local/percona/pmm-client/pmm.yml) sudo pmm-admin add mysql ● https://github.com/percona/pmm-client/blob/master/pmm-admin.go#L142-L202 ○ AddLinuxMetrics ( https://github.com/percona/pmm-client/blob/master/pmm/linux_metrics.go ) ○ AddMySQLMetrics ( https://github.com/percona/pmm-client/blob/master/pmm/mysql_metrics.go ) ○ AddMySQLQueries ( https://github.com/percona/pmm-client/blob/master/pmm/mysql_queries.go ) digitalocean.com

PMM Limitations ● Docker anti-pattern - single container running multiple processes ● Scalability - always confined to a single appliance ● Flexibility - only a few variables can be adjusted ● Integration digitalocean.com

Questions? digitalocean.com

Independent Deployment Options ● Ansible (Chef, Puppet, Salt Stack) ● Docker Compose digitalocean.com

Ansible Best Practices ● Idempotence ● Keep playbooks simple ○ Cleanly separate Provisioning, Deployment, & Orchestration ● Use Ansible Vault digitalocean.com

Ansible Roles ● Use Roles! ○ Encapsulation ■ Reuse (Ansible Galaxy) ■ Defaults & Variables ○ Versioning ○ Testing digitalocean.com

Sample Ansible Project Layout - ansible (main project directory) - galaxy_roles (roles installed by ansible-galaxy) - inventories - development (environment specific inventories) - production - group_vars - development (include both vars & vault) - production - library (local modules) - playbooks - roles (local roles) digitalocean.com - vault_passwords

Sample Ansible Config [defaults] inventory = ./inventories gathering = smart fact_caching = jsonfile fact_caching_connection = /tmp fact_caching_timeout = 86400 ansible_managed = "PLEASE DO NOT EDIT BY HAND -- THIS FILE IS MANAGED BY ANSIBLE" library = /usr/share/ansible/modules/:./library roles_path = ./roles:./galaxy_roles hash_behaviour = merge [ssh_connection] ssh_args = -C -o ControlMaster=auto -o ControlPersist=60s control_path = %(directory)s/%%C digitalocean.com

Delphinus Project ● Sample PMM-like project ● Encapsulated Roles (Including MySQL, Consul, Prometheus, Grafana, QAN, Nginx) ● Utilises Ansible Vault & Ansible Galaxy digitalocean.com

Demo digitalocean.com

Further options ● HA ○ Prometheus & Grafana ● Terraform ○ Declarative vs Procedural ○ Terragrunt ● Packer ○ Reuse ansible code to build images ○ Faster deployment digitalocean.com

Final thoughts ● PMM is a great solution ○ Quick and easy to install ○ It can provide immediate value ● However, PMM is just a collection of components ○ If you need to more advanced options, be your own barista digitalocean.com

Thank you! Questions?