剑客
关注科技互联网

使用Prometheus和Grafana监控Mysql服务器性能

这是一篇快速入门文章,介绍了如何使用 PrometheusGrafana 对Mysql服务器性能进行监控。内容基于 这篇 文章,结合了自己的实际实践并根据最新版本的应用进行了调整。下面是两张效果图:

使用Prometheus和Grafana监控Mysql服务器性能

使用Prometheus和Grafana监控Mysql服务器性能

概述

Prometheus 是一个开源的服务监控系统,它通过HTTP协议从远程的机器收集数据并存储在本地的时序数据库上。它提供了一个简单的网页界面、一个功能强大的查询语言以及HTTP接口等等。Prometheus通过安装在远程机器上的 exporter 来收集监控数据,我们用到了以下两个exporter:

  • node_exporter – 用于机器系统数据

  • mysqld_exporter – 用于Mysql服务器数据

Grafana 是一个开源的功能丰富的数据可视化平台,通常用于时序数据的可视化。它内置了以下数据源的支持:

使用Prometheus和Grafana监控Mysql服务器性能

并可以通过插件扩展支持的数据源。

架构图

下面是我们安装时用到的架构图:

使用Prometheus和Grafana监控Mysql服务器性能

安装和运行Prometheus

首先我们安装Prometheus:

$ wget https://github.com/prometheus/prometheus/releases/download/v1.1.3/prometheus-1.1.3.linux-amd64.tar.gz -O prometheus-1.1.3.linux-amd64.tar.gz
$ mkdir /usr/local/services/prometheus
$ tar zxf prometheus-1.1.3.linux-amd64.tar.gz -C /usr/local/services/prometheus --strip-components=1

然后在安装目下编辑配置文件 prometheus.yml

global:
  scrape_interval:     15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: linux
    static_configs:
      - targets: ['192.168.204.63:9100']
        labels:
          instance: db1

  - job_name: mysql
    static_configs:
      - targets: ['192.168.204.63:9104']
        labels:
          instance: db1

192.168.204.63 是我们数据库主机的IP,端口则是对应的 exporter 的监听端口。

然后我们启动Prometheus:

$ ./prometheus       
INFO[0000] Starting prometheus (version=1.1.3, branch=master, revision=ac374aa6748e1382dbeb72a00abf47d982ee8fff)  source=main.go:73
INFO[0000] Build context (go=go1.6.3, user=root@3e392b8b8b44, date=20160916-11:36:30)  source=main.go:74
INFO[0000] Loading configuration file prometheus.yml     source=main.go:221
INFO[0000] Loading series map and head chunks...         source=storage.go:358
INFO[0000] 4 series loaded.                              source=storage.go:363
INFO[0000] Starting target manager...                    source=targetmanager.go:76
WARN[0000] No AlertManagers configured, not dispatching any alerts  source=notifier.go:176
INFO[0000] Listening on :9090                            source=web.go:235

Prometheus内置了一个web界面,我们可通过 http://monitor_host:9090 进行访问:

使用Prometheus和Grafana监控Mysql服务器性能

Status -> Targets 页面下,我们可以看到我们配置的两个Target,它们的 StateDOWN

使用Prometheus和Grafana监控Mysql服务器性能

下一步我们需要安装并运行exporter。下载exporters并解压:

$ wget https://github.com/prometheus/node_exporter/releases/download/0.12.0/node_exporter-0.12.0.linux-amd64.tar.gz -O node_exporter-0.12.0.linux-amd64.tar.gz
$ wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.9.0/mysqld_exporter-0.9.0.linux-amd64.tar.gz -O mysqld_exporter-0.9.0.linux-amd64.tar.gz
$ mkdir /usr/local/services/prometheus_exporters
$ tar zxf node_exporter-0.12.0.linux-amd64.tar.gz -C /usr/local/services/prometheus_exporters --strip-components=1
$ tar zxf mysqld_exporter-0.9.0.linux-amd64.tar.gz -C /usr/local/services/prometheus_exporters --strip-components=1

运行node_exporter :

$ cd /usr/local/services/prometheus_exporters
$ ./node_exporter 
INFO[0000] Starting node_exporter (version=0.12.0, branch=master, revision=df8dcd2)  source=node_exporter.go:135
INFO[0000] Build context (go=go1.6.2, user=root@ff68505a5469, date=20160505-22:14:18)  source=node_exporter.go:136
INFO[0000] No directory specified, see --collector.textfile.directory  source=textfile.go:57
INFO[0000] Enabled collectors:                           source=node_exporter.go:155
INFO[0000]  - vmstat                                     source=node_exporter.go:157
INFO[0000]  - conntrack                                  source=node_exporter.go:157
INFO[0000]  - filesystem                                 source=node_exporter.go:157
INFO[0000]  - meminfo                                    source=node_exporter.go:157
INFO[0000]  - netdev                                     source=node_exporter.go:157
INFO[0000]  - stat                                       source=node_exporter.go:157
INFO[0000]  - entropy                                    source=node_exporter.go:157
INFO[0000]  - mdadm                                      source=node_exporter.go:157
INFO[0000]  - sockstat                                   source=node_exporter.go:157
INFO[0000]  - time                                       source=node_exporter.go:157
INFO[0000]  - uname                                      source=node_exporter.go:157
INFO[0000]  - diskstats                                  source=node_exporter.go:157
INFO[0000]  - filefd                                     source=node_exporter.go:157
INFO[0000]  - loadavg                                    source=node_exporter.go:157
INFO[0000]  - netstat                                    source=node_exporter.go:157
INFO[0000]  - textfile                                   source=node_exporter.go:157
INFO[0000] Listening on :9100                            source=node_exporter.go:176

mysqld_exporter需要连接到Mysql,所以需要Mysql的权限,我们先为它创建用户并赋予所需的权限:

mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'prom'@'localhost' identified by 'abc123';
mysql> GRANT SELECT ON performance_schema.* TO 'prom'@'localhost';

创建 .my.cnf 文件并运行mysqld_exporter:

$ cd /usr/local/services/prometheus_exporters
$ cat << EOF > .my.cnf
[client]
user=prom
password=abc123
EOF
$ ./mysqld_exporter -config.my-cnf=".my.cnf"  
INFO[0000] Starting mysqld_exporter (version=0.9.0, branch=master, revision=8400af20ccdbf6b5e0faa2c925c56c48cd78d70b)  source=mysqld_exporter.go:432
INFO[0000] Build context (go=go1.6.3, user=root@2c131c66ca20, date=20160926-18:28:09)  source=mysqld_exporter.go:433
INFO[0000] Listening on :9104                            source=mysqld_exporter.go:451

我们再次回到 Status -> Targets 页面,可以看到两个Target的状态已经变成 UP 了:

使用Prometheus和Grafana监控Mysql服务器性能

安装和运行Grafana

下载并解压Grafana:

$ wget https://grafanarel.s3.amazonaws.com/builds/grafana-3.1.1-1470047149.linux-x64.tar.gz
$ mkdir /usr/local/services/grafana
$ tar zxvf grafana-3.1.1-1470047149.linux-x64.tar.gz -C /usr/local/services/grafana  --strip-components=1

编辑配置文件 /usr/local/services/grafana/conf/defaults.ini ,修改 dashboards.json 段落下两个参数的值:

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

安装仪表盘:

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

运行以下命令为Grafana打个补丁,不然图表不能正常显示:

$ sed -i 's/expr=/(./)/.replace(/(./)/.expr,/(./)/.scopedVars/(.*/)var /(./)=/(./)/.interval/expr=/1.replace(/2.expr,/3.scopedVars/4var /5=/1.replace(/6.interval, /3.scopedVars)/' /usr/local/services/grafana/public/app/plugins/datasource/prometheus/datasource.js
$ sed -i 's/,range_input/.replace(//"{//g,"//"").replace(//}"//g,"//""),range_input/; s/step_input:""/step_input:this.target.step/' /usr/local/services/grafana/public/app/plugins/datasource/prometheus/query_ctrl.js

最后我们运行Grafana服务:

$ cd /usr/local/services/grafana/bin/
$ ./grafana-server
INFO[09-28|12:23:33] Starting Grafana                         logger=main version=3.1.1 commit=a4d2708 compiled=2016-08-01T18:20:16+0800
INFO[09-28|12:23:33] Config loaded from                       logger=settings file=/usr/local/services/grafana/conf/defaults.ini
INFO[09-28|12:23:33] Path Home                                logger=settings path=/usr/local/services/grafana
INFO[09-28|12:23:33] Path Data                                logger=settings path=/usr/local/services/grafana/data
INFO[09-28|12:23:33] Path Logs                                logger=settings path=/usr/local/services/grafana/data/log
INFO[09-28|12:23:33] Path Plugins                             logger=settings path=data/plugins
INFO[09-28|12:23:33] Initializing DB                          logger=sqlstore dbtype=mysql
INFO[09-28|12:23:33] Starting DB migration                    logger=migrator
INFO[09-28|12:23:33] Creating json dashboard index for path: [/var/lib/grafana/dashboards] 
INFO[09-28|12:23:33] Starting plugin search                   logger=plugins
INFO[09-28|12:23:33] Server Listening                         logger=server address=0.0.0.0:3000 protocol=http subUrl=

我们可通过 http://monitor_host:3000 访问Grafana网页界面(缺省的帐号/密码为admin/admin):

使用Prometheus和Grafana监控Mysql服务器性能

使用Prometheus和Grafana监控Mysql服务器性能

然后我们到 Data Sources 页面添加数据源:

使用Prometheus和Grafana监控Mysql服务器性能

最后我们就可以通过选择不同的仪表盘(左上角)和时间段(右上角)来呈现图表了:

使用Prometheus和Grafana监控Mysql服务器性能

使用Prometheus和Grafana监控Mysql服务器性能

使用Prometheus和Grafana监控Mysql服务器性能

参考资料

https://prometheus.io/

http://grafana.org/

https://github.com/percona/gr…

https://www.percona.com/blog/…

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址