关于percona-server:Percona-Monitoring-and-Management监控MySQL性能

30次阅读

共计 10294 个字符，预计需要花费 26 分钟才能阅读完成。

前言

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

1、宽泛的可视化零碎性能指标
2、收集并剖析简单的多零碎拓扑构造的数据
3、深刻发现效率低下起因，预测性能问题，或解决现有问题
4、留神潜在的平安问题并补救他们

PMM 简介

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-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 lsDRIVER              VOLUME NAMElocal               f9ed9b5237aecec9ee6c3afce336161582d4d5f79e3dbcc09dca80d78c18ac22

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

docker exec pmm-server pmm-admin status
  • 胜利后会呈现以下信息
Agent ID: pmm-serverNode ID : pmm-serverPMM Server:        URL    : https://127.0.0.1:443/        Version: 2.17.0PMM Client:        Connected        : true        Time drift       : 56.117µs        Latency          : 428.232µs        pmm-admin version: 2.17.0        pmm-agent version: 2.17.0Agents:        /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 服务

  • 批改 admin 明码为 pmm-reporter

PMM Client

  • 创立 docker-compose.yml 配置文件
cd /opt/docker-compose/pmm-clienttouch 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-reportervolumes:  pmm-client-data:
  • 生成配置文件
cd /opt/docker-compose/pmm-clienttouch pmm-agent.yaml && chmod 0666 pmm-agent.yamlwith 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 driverCreating pmm-client ... doneAttaching to pmm-clientpmm-client    | INFO[2021-05-28T11:51:35.526+00:00] Loading configuration file /etc/pmm-agent.yaml.  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/node_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/mysqld_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/mongodb_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/postgres_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/proxysql_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/rds_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/azure_exporter  component=setuppmm-client    | INFO[2021-05-28T11:51:35.542+00:00] Using /usr/local/percona/pmm2/exporters/vmagent  component=setuppmm-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-57cb4b8523bflisten-address: 127.0.0.1listen-port: 7777server:    address: 192.168.9.1xx:443    username: admin    password: pmm-reporter    insecure-tls: truepaths:    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-summaryports:    min: 42000    max: 51999debug: falsetrace: 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 -dRecreating pmm-client ... done
  • 通过以下命令拜访 pmm-client 命令查看 pmm-client 状态信息
docker exec pmm-client pmm-admin status
  • 胜利后会呈现以下信息
Agent ID: /agent_id/0c514bcf-41ac-4de8-9d2c-57cb4b8523bfNode ID : /node_id/8d7cb9d5-c407-4c87-ba0f-ee28e68d2725PMM Server:        URL    : https://192.168.9.140:443/        Version: 2.17.0PMM 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-ebcf4316Agents:        /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 IDAgent type     Status         Metrics Mode      Agent ID                                       Service IDpmm_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
  • 如图所示

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

注册 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-27ff7b5b5da8Service name: pmm-client-myhostTable statistics collection enabled (the limit is 1000, the actual table count is 476).
  • 浏览器页面开始展现 mysql 相干信息

  • 再次查问曾经存在 mysql 类型的 service
docker exec pmm-client pmm-admin inventory list services --service-type=mysqlServices list.Service type    Service name         Address and Port  Service IDMySQL           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,externaldocker exec pmm-client pmm-admin remove mysql pmm-client-myhost

具体应用

  • Nodes OverView 概览信息展现

  • Nodes 分类详细信息

  • MySQL Instances Overview 概览信息

  • MySQL Performance Schema Details

参考文档

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

https://www.percona.com/doc/p…

https://www.percona.com/doc/p…

https://www.percona.com/doc/p…

https://gist.github.com/paska…

正文完
percona-server
发表至： percona-server
2021-05-28
 0
关于percona-server:Percona-Monitoring-and-Management监控MySQL性能