前言

Percona Monitoring and Management (PMM) 是一款开源的专用于管理和监控 MySQL、MongoDB、PostgreSQL、ProxySQL、AWS RDS 性能的开源平台，并且可以监控前述这些数据库所在的服务器资源，通过 PMM 客户端收集到的 DB 监控数据用第三方软件 Grafana 画图展示出来。
通过 PMM，可以获得以下信息

1、广泛的可视化系统性能指标
2、收集并分析复杂的多系统拓扑结构的数据
3、深入发现效率低下原因，预测性能问题，或解决现有问题
4、注意潜在的安全问题并补救他们

PMM 简介

PMM Server 架构

PMM Server

Query Analytics (QAN) 可以在时间段内分析 MySQL 查询性能，除了客户端 QAN 代理外，它还包括以下内容
- QAN API 是用于存储和访问由在 PMM 客户端上运行的 QAN 代理收集的查询数据的后端.
- QAN Web App 应用程序是一个用于可视化收集的查询分析数据的 Web 应用程序.
Metrics Monitor 提供对 MySQL 或 MongoDB Server 实例至关重要的指标的历史视图，它包括以下内容:
- VictoriaMetrics, 时序型数据库. (Replaced Prometheus in PMM 2.12.0.)
- ClickHouse 是第三方列式存储数据库，方便查询分析功能.
- Grafana 是一款用 Go 语言开发的开源数据可视化工具,可以做数据监控和数据统计,带有告警功能.
- Percona Dashboards 是我们开发的 Grafana 的一组仪表板.

PMM Client 架构

PMM Client

pmm-admin 是用于管理 PMM 客户端的命令行工具，例如，添加和删除要监视的数据库实例. (Read more.
pmm-agent 是一个客户端组件，是一个最小的命令行界面，它是带来客户端功能的中央入口点：它携带客户的身份验证，获取存储在 PMM 服务器上的客户端配置，管理性能数据收集器和其他代理.
node_exporter 服务器性能数据收集器.
mysqld_exporter MySQL 性能数据收集器.
mongodb_exporter MongoDB 性能数据收集器.
postgres_exporter PostgreSQL 性能数据收集器.
proxysql_exporter ProxySQL 性能数据收集器.
rds_exporter Amazon RDS 性能数据收集器.
azure_database_exporter Azure database 性能数据收集器.

安装部署

PMM Server

创建docker-compose_prometheus_grafana.yml配置文件

cd /opt/docker-compose
touch docker-compose_pmm-server.yml

编辑docker-compose_pmm-server.yml文件并键入

services:
  pmm-server:
    image: percona/pmm-server:2
    hostname: pmm-server
    container_name: pmm-server
    restart: always
    logging:
      driver: json-file
      options:
        max-size: "10m"
        max-file: "5"
    ports:
      - "443:443"
    volumes:
      - data:/srv
volumes:
  data:

With this approach, data is stored in a volume, not in a pmm-data container.

查看数据卷

docker volume ls

local               pmm-server_data

如果存在垃圾数据，可以尝试以下命令进行清除

docker stop pmm-server

docker rm pmm-server

docker inspect pmm-server_data
[
    {
        "CreatedAt": "2021-05-28T16:16:15+08:00",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "pmm-server",
            "com.docker.compose.version": "1.29.2",
            "com.docker.compose.volume": "data"
        },
        "Mountpoint": "/var/lib/docker/volumes/pmm-server_data/_data",
        "Name": "pmm-server_data",
        "Options": null,
        "Scope": "local"
    }
]

docker volume rm -f pmm-server_data

或者使用以下方式将数据存储在 pmm-data container 内

version: '2'
# version 2 of docker-compose is not "old" version, it's the actual version,
# see below for explanation:
# https://stackoverflow.com/a/53636006/961092
services:
    # Percona Monitoring and Management server
    pmm-data:
        image: percona/pmm-server:2
        container_name: pmm-data
        hostname: pmm-data
        volumes:
            - /srv
        entrypoint: /bin/true

    pmm-server:
        image: percona/pmm-server:2
        hostname: pmm-server
        container_name: pmm-server
        restart: always


        # logging settings limit used disk space
        logging:
            driver: json-file
            options:
                max-size: "10m"
                max-file: "5"

        ports:
            - "443:443"
        # uncomment expose section in order to proxy requests through another container instead of
        # accessing the container directly
        # expose:
        #     - "443"

        volumes_from:
            - pmm-data

docker-compose 运行 docker 容器

docker-compose -p pmm-server -f docker-compose_pmm-server.yml up -d

docker volume ls
DRIVER              VOLUME NAME
local               f9ed9b5237aecec9ee6c3afce336161582d4d5f79e3dbcc09dca80d78c18ac22

通过以下命令访问 pmm-server 命令查看 pmm-server 状态信息

docker exec pmm-server pmm-admin status

成功后会出现以下信息

Agent ID: pmm-server
Node ID : pmm-server

PMM Server:
        URL    : https://127.0.0.1:443/
        Version: 2.17.0

PMM Client:
        Connected        : true
        Time drift       : 56.117µs
        Latency          : 428.232µs
        pmm-admin version: 2.17.0
        pmm-agent version: 2.17.0
Agents:
        /agent_id/6a7e8dcb-8aa8-4b65-8447-ff8284904348 postgresql_pgstatements_agent Running
        /agent_id/92688359-7668-47f5-9a93-eaf46ba9cf22 node_exporter Running
        /agent_id/a223620e-7a16-43ee-bf63-bd1dbe33ba01 postgres_exporter Running

启动成功通过浏览器 443 端口访问 pmm-server 服务

pmm-server

修改 admin 密码为 pmm-reporter

PMM Client

创建docker-compose.yml配置文件

cd /opt/docker-compose/pmm-client
touch docker-compose.yml

编辑docker-compose.yml文件并键入，Use unique hostnames across all PMM Clients

version: '2'
services:
  pmm-client:
    image: percona/pmm-client:2
    hostname: pmm-client-myhost
    container_name: pmm-client
    restart: always
    ports:
      - "42000:42000"
      - "42001:42001"
    logging:
      driver: json-file
      options:
        max-size: "10m"
        max-file: "5"
    volumes:
      - ./pmm-agent.yaml:/etc/pmm-agent.yaml
      - pmm-client-data:/srv
    environment:
      - PMM_AGENT_CONFIG_FILE=/etc/pmm-agent.yaml
     entrypoint: pmm-agent setup --server-insecure-tls --server-address=192.168.9.1xx:443 --server-username=admin --server-password=pmm-reporter
volumes:
  pmm-client-data:

生成配置文件

cd /opt/docker-compose/pmm-client
touch pmm-agent.yaml && chmod 0666 pmm-agent.yaml

with mode 0666 so container user will be able to write to it

使用以下命令启动后会立即停下，并自动映射当前目录下 pmm-agent.yaml

docker-compose up pmm-client

Creating volume "pmm-client_pmm-client-data" with default driver
Creating pmm-client ... done
Attaching to pmm-client
pmm-client    | INFO[2021-05-28T11:51:35.526+00:00] Loading configuration file /etc/pmm-agent.yaml.  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/node_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/mysqld_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/mongodb_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/postgres_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/proxysql_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/rds_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/azure_exporter  component=setup
pmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/vmagent  component=setup
pmm-client    | Checking local pmm-agent status...
pmm-client    | pmm-agent is not running.
pmm-client    | Registering pmm-agent on PMM Server...
pmm-client    | Registered.
pmm-client    | Configuration file /etc/pmm-agent.yaml updated.
pmm-client    | Please start pmm-agent: `pmm-agent --config-file=/etc/pmm-agent.yaml`.

查看pmm-agent.yml配置信息

# Updated by `pmm-agent setup`.
---
id: /agent_id/0c514bcf-41ac-4de8-9d2c-57cb4b8523bf
listen-address: 127.0.0.1
listen-port: 7777
server:
    address: 192.168.9.1xx:443
    username: admin
    password: pmm-reporter
    insecure-tls: true
paths:
    exporters_base: /usr/local/percona/pmm2/exporters
    node_exporter: /usr/local/percona/pmm2/exporters/node_exporter
    mysqld_exporter: /usr/local/percona/pmm2/exporters/mysqld_exporter
    mongodb_exporter: /usr/local/percona/pmm2/exporters/mongodb_exporter
    postgres_exporter: /usr/local/percona/pmm2/exporters/postgres_exporter
    proxysql_exporter: /usr/local/percona/pmm2/exporters/proxysql_exporter
    rds_exporter: /usr/local/percona/pmm2/exporters/rds_exporter
    azure_exporter: /usr/local/percona/pmm2/exporters/azure_exporter
    vmagent: /usr/local/percona/pmm2/exporters/vmagent
    tempdir: /tmp
    pt_summary: /usr/local/percona/pmm2/tools/pt-summary
    pt_pg_summary: /usr/local/percona/pmm2/tools/pt-pg-summary
    pt_mysql_summary: /usr/local/percona/pmm2/tools/pt-mysql-summary
    pt_mongodb_summary: /usr/local/percona/pmm2/tools/pt-mongodb-summary
ports:
    min: 42000
    max: 51999
debug: false
trace: false

将docker-compose.yml配置文件 entrypoint 信息注释掉，如下

#     entrypoint: pmm-agent setup --server-insecure-tls --server-address=192.168.9.1xx:443 --server-username=admin --server-password=pmm-reporter

docker-compose 运行 docker 容器

docker-compose -p pmm-client -f docker-compose.yml up -d
Recreating pmm-client ... done

通过以下命令访问 pmm-client 命令查看 pmm-client 状态信息

docker exec pmm-client pmm-admin status

成功后会出现以下信息

Agent ID: /agent_id/0c514bcf-41ac-4de8-9d2c-57cb4b8523bf
Node ID : /node_id/8d7cb9d5-c407-4c87-ba0f-ee28e68d2725

PMM Server:
        URL    : https://192.168.9.1xx:443/
        Version: 2.17.0

PMM Client:
        Connected        : true
        Time drift       : 81.61µs
        Latency          : 585.095µs
        pmm-admin version: 2.16.0-release-2.16-ebcf4316
        pmm-agent version: 2.16.0-release-2.16-ebcf4316
Agents:
        /agent_id/50269d19-7663-4650-8c66-48356ce2ccde vmagent Running
        /agent_id/5a86413f-0c1b-4312-aac0-99f6f1a07ecc node_exporter Running

服务启动成功后会自动注册 node 节点

docker exec pmm-client pmm-admin list

Service type   Service name   Address and port  Service ID
Agent type           Status           Metrics Mode        Agent ID                                                                                   Service ID
pmm_agent         Connected                                    /agent_id/0c514bcf-41ac-4de8-9d2c-57cb4b8523bf 
node_exporter     Running        push                       /agent_id/5a86413f-0c1b-4312-aac0-99f6f1a07ecc 
vmagent               Running        push                       /agent_id/50269d19-7663-4650-8c66-48356ce2ccde

如图所示

Home-Dashboard

pmm-server、pmm-client 在同一台服务器上，目前 pmm-client 还未添加数据库 service

pmm-client-myhost

注册 Client

当 pmm-client 与 pmm-server 服务在同一台机器上，pmm-client 服务启动成功后，会自动执行注册 Client。

如果非同一台机器，则需要手动执行注册 Client。

向 pmm-server 注册 pmm-client 节点

docker exec pmm-client pmm-admin config --server-insecure-tls --server-url=https://admin:pmm-exporter@192.168.9.1xx:443 192.168.9.1xx generic pmm-client-myhost

注册成功提示以下信息

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /etc/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

监控数据库

首次使用先检查一下，确认是没有 mysql 类型的 service

docker exec pmm-client pmm-admin inventory list services --service-type=mysql

Services list.

Service type           Service name         Address and Port  Service ID

创建并授权数据库用户

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pmm-client' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';
FLUSH PRIVILEGES;

确保 mysql 数据库中 PERFORMANCE_SCHEMA 参数值处于开启状态

MySQL 5.5 开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数，5.5 版本默认关闭，5.6 版本默认开启。

root@localhost 14:43:  [(none)]>SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

Data source recommendations

将 pmm-client 节点中的 mysql 数据库 service 添加至 pmm-server

docker exec pmm-client pmm-admin add mysql --query-source=perfschema --username=pmm --password=pmm-client pmm-client-myhost 192.168.9.1xx:3306

MySQL Service added.
Service ID  : /service_id/5baab9a7-1e3a-441e-8d9e-27ff7b5b5da8
Service name: pmm-client-myhost

Table statistics collection enabled (the limit is 1000, the actual table count is 476).

浏览器页面开始展示 mysql 相关信息

mysql

再次查询已经存在 mysql 类型的 service

docker exec pmm-client pmm-admin inventory list services --service-type=mysql
Services list.

Service type    Service name         Address and Port  Service ID
MySQL           pmm-client-myhost    192.168.9.1xx:3306 /service_id/5baab9a7-1e3a-441e-8d9e-27ff7b5b5da8

当然也可以移除数据库 services

docker exec pmm-client pmm-admin remove <service-type> <service-name>

<service-type>：mysql,mongodb,postgresql,proxysql,haproxy,external

docker exec pmm-client pmm-admin remove mysql pmm-client-myhost