MySQL · 监控 · PMM

发表于 | 更新于 | 分类于 | 阅读次数：

简介

Percona Monitoring and Management (PMM)：是一款 Percona 发布的用于管理和监控 Linux、MySQL、MongoDB、PostgreSQL 等数据库开源工具，通过 PMM 客户端收集到的监控数据，通过第三方软件 Grafana 展示出来。

PMM功能

  • 支持对主机的硬盘、网络、CPU、内存的监控。
  • 支持对 MyISAM、InnoDB、TokuDB 和 PXC/Glarera 的监控。
  • 支持 Query Analytics 的功能，可以检视执行了哪些 SQL 指令，并对执行性能较差的 SQL 进行优化。
  • 支持了 MySQL 复制拓扑图结构构造。

官网 Demo

架构

PMM架构

PMM Client

PMM Server

Query Analytics（QAN）：主要用来搜集指令并作性能分析。

  • QAN API：作为后端储存和读取 Query 资料用。
  • QAN APP：提供图形化分析界面。

Metrics Monitor（MM）：主要提供 MySQL 和 MongoDB 历史监控信息。组件说明如下：

  • Prometheus：一个开源的服务监控系统和时间序列数据库，它连接到 PMM Client 上的 exporter 聚集的监控数据。
  • Consul：提供 API 让 PMM Client 可以远端替 Prometheus 新增、移除 Hosts，同时它也储存了监控的metadata。
  • Grafana： 这是一个第三方 Dashboard 和图形构建器，用于可视化 Prometheus 中聚合的数据，以 Web 方式呈现。
  • Percona Dashboards：是由 Percona 开发的一组用于 Grafana 的仪表板。上述的 Web 页面都能从 PMM Landing Page直接连接。

安装

PMM Server

（1）安装 docker

1
2
3
4
$ yum -y install docker

# 卸载 docker
$ yum remove docker

（2）运行 docker

1
2
3
$ systemctl start docker
$ systemctl enable docker
$ systemctl stop docker

（3）下载容器镜像

1
$ docker pull percona/pmm-server:latest

（4）创建数据卷容器

1
2
3
4
5
6
7
$ docker create \
-v /opt/prometheus/data \
-v /opt/consul-data \
-v /var/lib/mysql \
-v /var/lib/grafana \
--name pmm-data \
percona/pmm-server:latest /bin/true

（5）运行 PMM Server

1
2
3
4
5
6
$ docker run -d \
-p 8090:80 \
--volumes-from pmm-data \
--name pmm-server \
--restart always \
percona/pmm-server:latest

（6）验证，通过 登录 查看是否正常显示，也可以通过如下方式：

1
2
$ curl http://10.133.0.53/ping
{'version': '1.8.0'}

其他命令：

1
2
3
$ docker exec -it 'xx' /bin/bash
$ docker ps -a
$ docker stop pmm-server && docker rm pmm-server (停止 pmm-server)

PMM Client

（1）确保 PMM Server 主机可访问

1
$ ping 10.133.0.52

（2）安装 Client 包

1
2
3
4
5
6
7
8
$ yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ yum install -y pmm-client

$ wget https://devops-files.oss-cn-qingdao.aliyuncs.com/pmm2-client-2.1.0-5.el7.x86_64.rpm
$ rpm -ivh pmm2-client-2.1.0-5.el7.x86_64.rpm

# 卸载 pmm-client
$ yum remove pmm-client

（3）连接 PMM Client 到 PMM Server

1
2
3
4
5
6
7
$ pmm-admin config --server 10.133.0.52
OK, PMM server is alive.
PMM Server      | 10.133.0.52 
Client Name     | cmp15
Client Address  | 10.138.228.236

$ pmm-admin config --server 10.133.0.52:8080 （适用于非80端口）

监控

主机

1
2
3
4
5
# 添加监控
$ pmm-admin add linux:metrics

# 取消监控
$ pmm-admin remove linux:metrics

MySQL

1
2
3
# 添加监控
$ pmm-admin add mysql --socket /data/mysql3306/mysql.sock --user root --password Haier@123
$ pmm-admin list
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
$ wget https://devops-files.oss-cn-qingdao.aliyuncs.com/pmm2-client-2.1.0-5.el7.x86_64.rpm
$ rpm -ivh pmm2-client-2.1.0-5.el7.x86_64.rpm

$ pmm-admin config --server 10.133.0.52:8080
OK, PMM server is alive.
PMM Server      | 10.133.0.52:8080 
Client Name     | GPCMS-DB-P
Client Address  | 10.133.7.19

$ pmm-admin add linux:metrics
OK, now monitoring this system.

$ pmm-admin add mysql --user hdm --password Hdm@123!
[linux:metrics] OK, already monitoring this system.
[mysql:metrics] OK, now monitoring MySQL metrics using DSN hdm:***@unix(/data/mysql3306/mysql.sock)
[mysql:queries] OK, now monitoring MySQL queries from slowlog using DSN hdm:***@unix(/data/mysql3306/mysql.sock)

$ pmm-admin check-network
PMM Network Status

Server Address | 10.133.0.52:8080
Client Address | 10.133.7.19 

* System Time
NTP Server (0.pool.ntp.org)         | 2019-07-26 10:18:20 +0000 UTC
PMM Server                          | 2019-07-26 10:18:20 +0000 GMT
PMM Client                          | 2019-07-26 18:18:20 +0800 CST
PMM Server Time Drift               | OK
PMM Client Time Drift               | OK
PMM Client to PMM Server Time Drift | OK

* Connection: Client --> Server
-------------------- -------      
SERVER SERVICE       STATUS       
-------------------- -------      
Consul API           OK
Prometheus API       OK
Query Analytics API  OK

Connection duration | 702.217µs
Request duration    | 4.66389ms
Full round trip     | 5.366107ms

* Connection: Client <-- Server
-------------- ----------- ------------------ ------- ---------- ---------
SERVICE TYPE   NAME        REMOTE ENDPOINT    STATUS  HTTPS/TLS  PASSWORD 
-------------- ----------- ------------------ ------- ---------- ---------
linux:metrics  GPCMS-DB-P  10.133.7.19:42000  OK      YES        -        
mysql:metrics  GPCMS-DB-P  10.133.7.19:42002  OK      YES        -
1
2
3
4
# 开启防火墙端口
firewall-cmd --zone=public --add-port=42000/tcp --permanent
firewall-cmd --zone=public --add-port=42002/tcp --permanent
firewall-cmd --reload
1
2
3
4
5
6
7
8
9
10
# 更改存储类型为MySQL
docker exec -i -t pmm-server /bin/bash
vi /etc/grafana/grafana.ini

查/sqlite改为MYSQL存储
type=mysql
host=127.0.0.1:3306
name=grafana
user=root
password =

在容器进入 mysql 客户端,创建数据库 grafana

1
2
$ create database grafana;
$ docker restart pmm-server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$ pmm-admin --help
Usage:
pmm-admin [flags]
pmm-admin [command]
Available Commands:
config         Configure PMM Client. # 配置PMM客户端
add            Add service to monitoring. # 增加监控
remove         Remove service from monitoring. # 删除监控服务
list           List monitoring services for this system. # 列出监控服务
info           Display PMM Client information (works offline). # 打印PMM客户端信息
check-network  Check network connectivity between client and server. # 检查网络
ping           Check if PMM server is alive. 
start          Start monitoring service. # 开启服务
stop           Stop monitoring service. # 停止服务
restart        Restart monitoring service. # 重启服务
show-passwords Show PMM Client password information (works offline). # 显示客户端密码
purge          Purge metrics data on PMM server. # 删除PMM server端监控信息
repair         Repair installation. # 重新安装
uninstall      Removes all monitoring services with the best effort. # 删除所有服务
help           Help about any command
Flags:
-c, --config-file string   PMM config file (default "/usr/local/percona/pmm-client/pmm.yml")
-h, --help                 help for pmm-admin
    --verbose              verbose output
-v, --version              show version
Use "pmm-admin [command] --help" for more information about a command.

实践

  • 安装 pmm-client
1
2
$ wget https://devops-files.oss-cn-qingdao.aliyuncs.com/pmm2-client-2.1.0-5.el7.x86_64.rpm
$ rpm -ivh pmm2-client-2.1.0-5.el7.x86_64.rpm
  • 监控主机
1
2
3
4
5
6
7
8
# 将client_name修改为服务器ip
$ vi /usr/local/percona/pmm-client/pmm.yml

# 重启pmm-client
$ pmm-admin restart --all

# 添加监控
$ pmm-admin add linux:metrics
  • 监控 MySQL
1
2
$ pmm-admin add mysql --user hdm --password Hdm@123! x.x.x.x:port
$ pmm-admin list
  • 配置告警
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 进入docker
$ docker exec -it pmm-server /bin/bash

# 编缉 grafana.ini
vim /etc/grafana/grafana.ini

[smtp]
enabled = true
host = [smtp.healthmall.cn](http://smtp.healthmall.cn/):25
user = liuqian@healthmall.cn
password = 123456
from_address = liuqian@healthmall.cn
from_name = Grafana

# 重启pmm-server容器
$ docker restart pmm-server

# 钉钉报警配置
http://docs.grafana.org/alerting/notifications/
  • 升级 pmm-server
1
2
3
$ docker pull percona/pmm-server:latest
$ docker stop pmm-server && docker rm pmm-server
$ docker run -d -p 80:80 -p 9090:9090 --volumes-from pmm-data --name pmm-server -e SERVER_USER=pmm -e SERVER_PASSWORD=ngiISI0Q4g9gfqWz89folKJSi --restart always --init percona/pmm-server:latest

FA&Q

（1）docker 默认目录 /var/lib/docker 修改为 /data/dockerfile

1
2
3
4
5
6
7
8
9
10
11
12
13
$ docker info
Kernel Version: 3.10.0-693.el7.x86_64
Operating System: CentOS Linux 7 (Core)
OSType: linux
Architecture: x86_64
Number of Docker Hooks: 3
CPUs: 6
Total Memory: 15.66 GiB
Name: console
ID: 7MYD:NQPC:4TNL:IT3W:6KZS:TY6L:CORW:L432:UE3M:4LBJ:OFUU:HSMB
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 停止docker服务
$ systemctl stop docker

# 将docker文件迁移到指定目录
$ mkdir /data/dockerfile
$ mv /var/lib/docker/* /data/dockerfile

# 删除原目录
$ rm -rf /var/lib/docker

# 创建软连接
$ ln -s/data/dockerfile/ /var/lib/docker

# 启动docker服务
$ systemctl start docker
1
2
3
4
5
6
7
8
9
10
11
12
13
$ docker info
Kernel Version: 3.10.0-693.el7.x86_64
Operating System: CentOS Linux 7 (Core)
OSType: linux
Architecture: x86_64
Number of Docker Hooks: 3
CPUs: 6
Total Memory: 15.66 GiB
Name: console
ID: 7MYD:NQPC:4TNL:IT3W:6KZS:TY6L:CORW:L432:UE3M:4LBJ:OFUU:HSMB
Docker Root Dir: /data/dockerfile
Debug Mode (client): false
Debug Mode (server): false

（2）PMM 修改主机名作为唯一标识，修改为 ip、port

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
$ pmm-admin check-network
PMM Network Status

Server Address | 10.133.0.53:8090
Client Address | 10.133.0.51 

*** System Time**
NTP Server (0.pool.ntp.org)         | 2019-09-02 08:59:28 +0000 UTC
PMM Server                          | 2019-09-02 08:59:28 +0000 GMT
PMM Client                          | 2019-09-02 16:59:28 +0800 CST
PMM Server Time Drift               | **OK**
PMM Client Time Drift               | **OK**
PMM Client to PMM Server Time Drift | **OK**

*** Connection: Client --> Server**
-------------------- -------      
SERVER SERVICE       STATUS       
-------------------- -------      
Consul API           **OK**
Prometheus API       **OK**
Query Analytics API  **OK**

Connection duration | 433.939µs
Request duration    | 3.335106ms
Full round trip     | 3.769045ms

*** Connection: Client <-- Server**
-------------- ----------------- ------------------ ------- ---------- ---------
SERVICE TYPE   NAME              REMOTE ENDPOINT    STATUS  HTTPS/TLS  PASSWORD 
-------------- ----------------- ------------------ ------- ---------- ---------
linux:metrics  10.133.0.51       10.133.0.51:42000  **OK**      **YES**        -        
mysql:metrics  10.133.0.51:3306  10.133.0.51:42002  **OK**      **YES**        -