Monitoring MySQL with Prometheus and Grafana

1 .Monitoring MySQL with Prometheus & Grafana Julien Pivotto (@roidelapluie) Percona University Belgium June 22nd, 2017 

2 . SELECT USER(); Julien "roidelapluie" Pivotto @roidelapluie Sysadmin at inuits Automation, monitoring, HA MySQL/MariaDB user/admin/contributor Grafana and Prometheus user/contributor 

3 .inuits 

4 . Monitoring Creative Commons Attribution 2.0 https://www.flickr.com/photos/kevandotorg/13829348104/ 

5 . DevOps 4 keys principles Culture Automation Metrics Sharing Damon Edwards and John Willis, 2010 

6 . Metrics matters Find when a service is unavailable (before your users) Understand Failures (post-mortem) Learn from your infrastructure Anticipate 

7 . Collecting Metrics In general, you should collect as many metrics as you can Frequently (5 min collection are not enough) Store the metrics in a sane way (avoid averaging old metrics, etc) 

8 . How to collect metrics? Many many solutions, including: Graphite Elasticsearch beats Zabbix and .. Prometheus 

9 . Out of scope today Alerting Prometheus and Grafana installation Prometheus Fine Tuning But I'm happy to discuss that after the talks! 

10 .Prometheus https://prometheus.io/ 

11 . Prometheus Prometheus is a Cloud-Native Data-Centric Open- Source Performant Simple metrics collection, analysis and alerting tool. Nothing more. 

12 . Cloud Native Easy to configure, deploy, maintain Designed in multiple services Container ready Orchestration ready (dynamic config) Fuzziness 

13 . Data Centric A Metric in Prometheus has metadata: myql_global_status_handlers_total{handler="tmp_write"} 1122 And lots of function to filter, change, remove... those metadata while fetching them. 

14 . Open Source Apache 2.0 Go Support for multiple OS Many "exporters": https://github.com/prometheus/prometheus/wiki/Defau lt-port-allocations 

15 . Performance Prometheus is designed to fetch data in an interval measured in SECONDS You can fine tune its memory usage and when it flushes to disk It can also adapt its scraping frequency dynamically 

16 .How does it work? 

17 .How does it work? 

18 .How does it work? 

19 .How does it work? 

20 .How does it work? 

21 . Exporters Exporters expose metrics with an HTTP API Bindings available for many languages Exporters do not save data ; they are not "proxies" and don't "cache" anything 

22 .Exploring Metrics 

23 .Exploring Metrics 

24 .Exploring Metrics 

25 .Exploring Metrics 

26 . PromQL mysql_global_status_commands_total 

27 . PromQL mysql_global_status_commands_total{command="select"} 

28 . PromQL mysql_global_status_commands_total {command=~"select|set_options"} 

29 . PromQL mysql_global_status_commands_total{command=~"select|set _options"}