[MySQL][펌] Graphing MySQL performance with Prometheus and Grafana

Graphing MySQL performance with Prometheus and Grafana

출처 : https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/

Roman Vynar   | February 29, 2016 |  Posted In: Monitoring, MySQL, Prometheus

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.

First of all, let me mention that Percona Monitoring and Management beta has been released recently which is an easy way you can get all of this.

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 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.

Shell

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:

YAML

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 foreground:

Shell

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.

Shell

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:

Shell

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:

Shell

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:

Shell

1 2 3 4 5 6 7 8 9

[root@centos7 ~]# cd /opt/prometheus_exporters [root@centos7 prometheus_exporters]# cat << EOF > .my.cnf [client] user=prom password=abc123 EOF [root@centos7 prometheus_exporters]# [root@centos7 prometheus_exporters]# ./mysqld_exporter -config.my-cnf=".my.cnf" INFO[0000] Starting Server: :9104                        file=mysqld_exporter.go line=1997

At this point we should see our endpoints are up and running on the Prometheus Status page:

Grafana setup

Install on the monitor host.

Grafana has RPM and DEB packages. The installation is as simple as installing one package.
RPM-based system:

Shell

1	yum install https://grafanarel.s3.amazonaws.com/builds/grafana-2.6.0-1.x86_64.rpm

or APT-based one:

Shell

1 2 3

wget https://grafanarel.s3.amazonaws.com/builds/grafana_2.6.0_amd64.deb apt-get install -y adduser libfontconfig dpkg -i grafana_2.6.0_amd64.deb

Open and edit the last section of /etc/grafana/grafana.ini resulting in the following ending:

INI

1 2 3

[dashboards.json] enabled = true path = /var/lib/grafana/dashboards

Percona has built the predefined dashboards for Grafana with Prometheus for you.

Let’s get them deployed:

Shell

1 2	git clone https://github.com/percona/grafana-dashboards.git cp -r grafana-dashboards/dashboards /var/lib/grafana

It is important to apply the following minor patch on Grafana 2.6 in order to use the interval template variable to get the good zoomable graphs. The fix is simply to allow variable in Step field on Grafana graph editor page. For more information, take a look at PR#3757 and PR#4257. We hope the last one will be released with the next Grafana version.

Shell

1 2

sed -i 's/step_input:""/step_input:c.target.step/; s/ HH:MM/ HH:mm/; s/,function(c)/,"templateSrv",function(c,g)/; s/expr:c.target.expr/expr:g.replace(c.target.expr,c.panel.scopedVars)/' /usr/share/grafana/public/app/plugins/datasource/prometheus/query_ctrl.js sed -i 's/h=a.interval/h=g.replace(a.interval, c.scopedVars)/' /usr/share/grafana/public/app/plugins/datasource/prometheus/datasource.js

Those changes are idempotent.

Finally, start Grafana:

Shell

1	service grafana-server start

At this point, we are one step before being done. Login into Grafana web interface http://monitor_host:3000 (admin/admin).

Go to Data Sources and add one for Prometheus:

Now check out the dashboards and graphs. Say choose “System Overview” and period “Last 5 minutes” on top-right. You should see something similar:

If your graphs are not populating ensure the system time is correct on the monitor host.

Samples

Here are some real-world samples (images are clickable and scrollable):
 
 
 
 

Enjoy!

Conclusion

Prometheus and Grafana is a great tandem for enabling monitoring and graphing capabilities for MySQL. The tools are pretty easy to deploy, they are designed for time series with high efficiency in mind. In the next blog posts I will talk more about technical aspects, problems and related stuff.