PMM(Percona Monitoring and Management)监控MySQL数据库

最新推荐文章于 2022-09-17 07:30:41 发布

wholegale39

最新推荐文章于 2022-09-17 07:30:41 发布

阅读量1.5k

点赞数

分类专栏：数据库 pmm 文章标签： mysql

本文链接：https://blog.csdn.net/lurenyi333/article/details/117370247

版权

数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

pmm

1 篇文章 0 订阅

订阅专栏

前言

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

Database server	Versions	Recommended source
MySQL	5.1-5.5	Slow query log
MySQL	5.6+	Performance Schema
MariaDB	10.0+	Performance Schema
Percona Server for MySQL	5.7, 8.0	Slow query log
Percona XtraDB Cluster	5.6, 5.7, 8.0	Slow query log

将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

具体使用

Nodes OverView概览信息展示

Nodes OverView

Nodes分类详细信息

Nodes分类详细信息

MySQL Instances Overview概览信息

MySQL Instances Overview

MySQL Performance Schema Details

MySQL Performance Schema Details

参考文档

https://www.percona.com/software/pmm/quickstart

https://www.percona.com/doc/percona-monitoring-and-management/2.x/details/architecture.html

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/server/index.html

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/index.html

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/index.html#register

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/mysql.html

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/index.html#configure-add-services

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/server/docker.html

https://gist.github.com/paskal/48f10a0a584f4849be6b0889ede9262b

https://www.percona.com/doc/percona-monitoring-and-management/2.x/index.html

wholegale39

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
2
评论
PMM(Percona Monitoring and Management)监控MySQL数据库

前言Percona Monitoring and Management (PMM)是一款开源的专用于管理和监控MySQL、MongoDB、PostgreSQL、、ProxySQL、AWS RDS性能的开源平台，并且可以监控前述这些数据库所在的服务器资源，通过PMM客户端收集到的DB监控数据用第三方软件Grafana画图展示出来。通过PMM，可以获得以下信息广泛的可视化系统性能指标收集并分析复杂的多系统拓扑结构的数据深入发现效率低下原因，预测性能问题，或解决现有问题注意潜在的安全问题并补救
复制链接

扫一扫