[go: up one dir, main page]
Scribd
Upload
26 views63 pages

Monitoring

Uploaded by

j5f9c5hysp
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views63 pages

Monitoring

Uploaded by

j5f9c5hysp
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 63

Monitoring MySQL with

Prometheus & Grafana


Julien Pivotto (@roidelapluie)
Percona University Belgium

June 22nd, 2017


SELECT USER();
Julien "roidelapluie" Pivotto
@roidelapluie
Sysadmin at inuits
Automation, monitoring, HA
MySQL/MariaDB user/admin/contributor
Grafana and Prometheus user/contributor
inuits
Monitoring
Creative Commons Attribution 2.0 https://www.flickr.com/photos/kevandotorg/13829348104/
DevOps
4 keys principles
Culture
Automation
Metrics
Sharing
Damon Edwards and John Willis, 2010
Metrics matters
Find when a service is unavailable (before your
users)
Understand Failures (post-mortem)
Learn from your infrastructure
Anticipate
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)
How to collect metrics?
Many many solutions, including:
Graphite
Elasticsearch beats
Zabbix
and .. Prometheus
Out of scope today
Alerting
Prometheus and Grafana installation
Prometheus Fine Tuning
But I'm happy to discuss that after the talks!
Prometheus
https://prometheus.io/
Prometheus
Prometheus is a Cloud-Native Data-Centric Open-
Source Performant Simple metrics collection,
analysis and alerting tool.
Nothing more.
Cloud Native
Easy to configure, deploy, maintain
Designed in multiple services
Container ready
Orchestration ready (dynamic config)
Fuzziness
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.
Open Source
Apache 2.0
Go
Support for multiple OS
Many "exporters":
https://github.com/prometheus/prometheus/wiki/Defau
lt-port-allocations
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
How does it work?
How does it work?
How does it work?
How does it work?
How does it work?
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
Exploring Metrics
Exploring Metrics
Exploring Metrics
Exploring Metrics
PromQL
mysql_global_status_commands_total
PromQL
mysql_global_status_commands_total{command="select"}
PromQL
mysql_global_status_commands_total
{command=~"select|set_options"}
PromQL
mysql_global_status_commands_total{command=~"select|set
_options"}
PromQL
deriv(mysql_global_status_connections[5m])
PromQL
{__name__=~".+innodb.+cache.*"}

predict_linear(mysql_heartbeat_lag_seconds[5m], 60*2)

sum(rate(mysql_global_status_commands_total{command=~"
(commit|rollback)"}[5m])) without (command)
Prometheus + mysqld_exporter
Collect a LOT of data
(e.g. global variables & status)
Embeds basic visualisation console
By default scraps every 15s
A word about
Prometheus vs Graphite
Prometheus does not see a metric as an "event".
Metrics are current value until they are replaced.
You can not see when a metric has been included
in Prometheus.
For Events, Prometheus refers to Elasticsearch.
One tool does one job...
Prometheus will collect data
Exporters will expose data
Grafana will graph data
Grafana
Open Source (Apache 2.0)
Web app
Specialized in visualization
Pluggable
Multiple datasources: prometheus, graphite,
influxdb...
Has an API!
History of Grafana
Grafana is a fork of Kibana 3 ; used to be JS-
Driven.
Now fully featured, requires a database, multi-
projects/users support, etc...
Grafana and Prometheus
Prometheus shipped its own consoles
Now it recommends Grafana and deprecated
its own consoles
Grafana Dashboards
Grafana Dashboards
Time Picker
Configure Prometheus in
Grafana
Configure Prometheus in
Grafana
Prometheus Dashboard
Creating Grafana Dashboards
Takes time
Requires deep knowledge of the tools
Improved over time
Easy to share (json + online library)
Percona Grafana Dashboard
Percona Open Sourced Grafana Dashboards
Covering MySQL, Mongo and Linux monitoring
Part of a bigger picture, PMM, but usable
standalone
Open Source (AGPL!)
https://github.com/percona/grafana-
dashboards
Installing Percona Graphes
Method 1 (RO dashboards)
Enable File dashboards in Grafana
Clone grafana-dashboards to the configured
location (or make a package)

Method 2 (RW dashboards)


Use the Grafana API to upload the JSON's.
MySQL Setup
You'll need mysqld_exporter, with a user
MySQL 5.1+
Performance Schema for full set of metrics
mysqld_exporter
-collect.binlog_size=true
-collect.info_schema.processlist=true`
node_exporter setup
node_exporter
-collectors.enabled=
"diskstats,filefd,filesystem,loadavg,
meminfo,netdev,stat,time,uname,vmstat"
Prometheus (static file)
scrape_configs:
­ job_name: prometheus
static_configs:
­ targets: ['localhost:9090']
labels:
instance: prometheus

­ job_name: linux
static_configs:
­ targets: ['10.0.98.43:9100']
labels:
instance: db1

­ job_name: mysql
static_configs:
­ targets: ['10.0.98.43:9104']
labels:
instance: db1
Dashboards
Dashboards
Dashboards
We don't need all of them?
Because Grafana is just viz, you can import
only the one you want (e.g. exclude Mongo)
You can import later any extra dashboard you
need
MySQL Overview
MySQL Overview
MySQL Overview
InnoDB
InnoDB
InnoDB
Replication
Conclusions
Prometheus and Grafana are first-class
monitoring tools
Totally different approach than other tools
Embeddable into your apps
Percona Dashboards gets your graphes ready
in no-time with minimal efforts
Contact

Julien Pivotto Inuits


roidelapluie https://inuits.eu
roidelapluie@inuits.eu info@inuits.eu

You might also like