This post explains how you can quickly start using such trending tools as Prometheus and Grafana for monitoring and graphing of MySQL and system performance.
Update, February 20, 2017: Since this blog post was published, we have released Percona Monitoring and Management (PMM), which is the easiest way to monitor MySQL and MongoDB using Grafana and Prometheus. You can check the PMM documentation for more detail. If you need help, PMM is fully supported under Percona Support. Alternatively, community support is available through the Percona Montoring and Management Forums.
I will try to keep this blog as short as possible, so you can quickly set things up before getting bored. I plan to cover the details in the next few posts. I am going to go through the installation process here in order to get some really useful and good-looking graphs in the end.
Overview
Prometheus is an open-source service monitoring system and time series database. In short, the quite efficient daemon scrapes metrics from remote machines using HTTP protocol and stores data in the local time-series database. Prometheus provides a simple web interface, a very powerful query language, HTTP API, etc. However, the storage is not designed to be durable for the time being.
The remote machines need to run exporters to expose metrics to Prometheus. We will be using the following two:
- node_exporter – for machine metrics.
- mysqld_exporter – for MySQL server metrics
Grafana is an open source, feature-rich metrics dashboard and graph editor for Graphite, Elasticsearch, OpenTSDB, Prometheus, and InfluxDB. It is a powerful tool for visualizing large-scale measurement data and designed to work with time-series. Grafana supports different types of graphs, allows for a custom representation of individual metrics on the graph and various methods of authentication including LDAP.
Diagram
Here is a diagram of the setup we are going to use:
Prometheus setup
To install on the monitor host.
Get the latest tarball from Github.
|
1 2 3 |
wget https://github.com/prometheus/prometheus/releases/download/0.17.0rc2/prometheus-0.17.0rc2.linux-amd64.tar.gz mkdir /opt/prometheus tar zxf prometheus-0.17.0rc2.linux-amd64.tar.gz -C /opt/prometheus --strip-components=1 |
Create a simple config:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
cat << EOF > /opt/prometheus/prometheus.yml global: scrape_interval: 5s evaluation_interval: 5s scrape_configs: - job_name: linux target_groups: - targets: ['192.168.56.107:9100'] labels: alias: db1 - job_name: mysql target_groups: - targets: ['192.168.56.107:9104'] labels: alias: db1 EOF |
where 192.168.56.107 is the IP address of the db host we are going to monitor and db1 is its short name. Note, the “alias” label is important here because we rely on it in the predefined dashboards below to get per host graphs.
Start Prometheus in the foreground:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[root@centos7 ~]# cd /opt/prometheus [root@centos7 prometheus]# ./prometheus prometheus, version 0.17.0rc2 (branch: release-0.17, revision: 667c221) build user: fabianreinartz@macpro build date: 20160205-13:35:53 go version: 1.5.3 INFO[0000] Loading configuration file prometheus.yml source=main.go:201 INFO[0000] Loading series map and head chunks... source=storage.go:297 INFO[0000] 0 series loaded. source=storage.go:302 WARN[0000] No AlertManager configured, not dispatching any alerts source=notification.go:165 INFO[0000] Starting target manager... source=targetmanager.go:114 INFO[0000] Target manager started. source=targetmanager.go:168 INFO[0000] Listening on :9090 source=web.go:239 |
Now we can access Prometheus’ built-in web interface by http://monitor_host:9090
If you look at the Status page from the top menu, you will see that our monitoring targets are down so far. Now let’s setup them – prometheus exporters.
Prometheus exporters setup
Install on the db host. Of course, you can use the same monitor host for the experiment. Obviously, this node must run MySQL.
Download exporters from here and there.
|
1 2 3 4 5 |
wget https://github.com/prometheus/node_exporter/releases/download/0.12.0rc3/node_exporter-0.12.0rc3.linux-amd64.tar.gz wget https://github.com/prometheus/mysqld_exporter/releases/download/0.7.1/mysqld_exporter-0.7.1.linux-amd64.tar.gz mkdir /opt/prometheus_exporters tar zxf node_exporter-0.12.0rc3.linux-amd64.tar.gz -C /opt/prometheus_exporters tar zxf mysqld_exporter-0.7.1.linux-amd64.tar.gz -C /opt/prometheus_exporters |
Start node_exporter in foreground:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@centos7 ~]# cd /opt/prometheus_exporters [root@centos7 prometheus_exporters]# ./node_exporter INFO[0000] No directory specified, see --collector.textfile.directory source=textfile.go:57 INFO[0000] Enabled collectors: source=node_exporter.go:146 INFO[0000] - filesystem source=node_exporter.go:148 INFO[0000] - loadavg source=node_exporter.go:148 INFO[0000] - time source=node_exporter.go:148 INFO[0000] - vmstat source=node_exporter.go:148 INFO[0000] - diskstats source=node_exporter.go:148 INFO[0000] - filefd source=node_exporter.go:148 INFO[0000] - mdadm source=node_exporter.go:148 INFO[0000] - meminfo source=node_exporter.go:148 INFO[0000] - netdev source=node_exporter.go:148 INFO[0000] - textfile source=node_exporter.go:148 INFO[0000] - entropy source=node_exporter.go:148 INFO[0000] - stat source=node_exporter.go:148 INFO[0000] - uname source=node_exporter.go:148 INFO[0000] - conntrack source=node_exporter.go:148 INFO[0000] - netstat source=node_exporter.go:148 INFO[0000] - sockstat source=node_exporter.go:148 INFO[0000] - version source=node_exporter.go:148 INFO[0000] Starting node_exporter v0.12.0rc3 at :9100 source=node_exporter.go:167 |
Unlike node_exporter, mysqld_exporter wants MySQL credentials. Those privileges should be sufficient:
|
1 2 |
mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'prom'@'localhost' identified by 'abc123'; mysql> GRANT SELECT ON performance_schema.* TO 'prom'@'localhost'; |
Create .my.cnf and start mysqld_exporter in foreground:
|
1 2 3 4 5 6 7 8 9 |
[root@centos7 |