PMM配置监控PG数据库

1. 架构图

2.网址

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

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

查看版本

https://hub.docker.com/r/percona/pmm-server/tags

3. 安装

-- 拉取镜像

docker pull percona/pmm-server:2.15

-- 创建PMM数据容器

--删除之前的

docker rm pmm-datadocker create --name pmm-data \-v /pmmdata \percona/pmm-server:2.15 /bin/true

-- 创建PMM服务器容器

docker rm -f pmm-serverdocker run -d --name lhr-pmm-server -h lhr-pmm-server \-p 280:80 -p 2443:443 \--volumes-from pmm-data \-e SERVER_USER=lishiyan \-e SERVER_PASSWORD=lishiyan \-e ORCHESTRATOR_ENABLED=true \-e METRICS_RETENTION=48h \--restart always \percona/pmm-server:2.15

-- 访问web界面

http://10.10.10.188:280

https://10.10.10.188:2443

admin/admin

-- 进入PMM服务器

docker exec -it pmm-server bash

-- 连接到PMM服务器

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@127.0.0.1:443pmm-admin list

以下是执行步骤

--在监控机器上执行

[root@pmm yum.repos.d]# docker create --name  pmm-data \>    -v /pmmdata \>    percona/pmm-server:2.15 /bin/true1c05c838d6e2cf75bb77a952750b48569086d001cabe0c29762aa35157d2907c[root@pmm yum.repos.d]# docker run -d --name  pmm-server -h  pmm-server \>   -p 280:80 -p 2443:443 \>   --volumes-from  pmm-data \>   -e SERVER_USER=lishiyan \>   -e SERVER_PASSWORD=lishiyan \>   -e ORCHESTRATOR_ENABLED=true \>   -e METRICS_RETENTION=48h \>   --restart always \>   percona/pmm-server:2.15b27905de4e0cc169d836dfa15b33301d003bc57209142b6b0be61ca179900823

监控PG数据库

--需要在监控的数据库上开启如下功能：

ALTER SYSTEM SET track_io_timing=ON;SELECT pg_reload_conf();CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD 'pmm';

--配置监控的PG主机

[root@allsql01 ~]# pmm-admin config --server-insecure-tls --server-url=https://admin:admin@10.10.10.188:2443Checking local pmm-agent status...pmm-agent is running.Registering pmm-agent on PMM Server...Registered.Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.Reloading pmm-agent configuration...Configuration reloaded.Checking local pmm-agent status...pmm-agent is running.[root@allsql01 ~]# pmm-admin listService type        Service name        Address and port        Service IDAgent type           Status           Metrics Mode        Agent ID                                              Service IDpmm_agent            Connected                            /agent_id/e40485ef-9834-46e8-8749-b9bbbbbc1aacnode_exporter        Running          push                /agent_id/1b7aee5a-24e3-4e65-b131-d94b173e6e6evmagent              Running          push                /agent_id/2b40cc87-e805-496d-ac87-603ca998839b

--添加数据库，注意发现下边中出现waitting状态

[root@allsql01 ~]# pmm-admin add postgresql --username=pmm --password=pmm --host=10.10.10.11 --port=5432 --service-name=PG-10.10.10.11-5432PostgreSQL Service added.Service ID  : /service_id/1f06cfca-4d82-472e-9a00-28b610984716Service name: PG-10.10.10.11-5432[root@allsql01 ~]# pmm-admin listService type        Service name               Address and port        Service IDPostgreSQL          PG-10.10.10.11-5432        10.10.10.11:5432        /service_id/1f06cfca-4d82-472e-9a00-28b610984716Agent type                           Status           Metrics Mode        Agent ID                                              Service IDpmm_agent                            Connected                            /agent_id/e40485ef-9834-46e8-8749-b9bbbbbc1aacnode_exporter                        Running          push                /agent_id/1b7aee5a-24e3-4e65-b131-d94b173e6e6epostgres_exporter                    Running          push                /agent_id/640c5077-10ed-4e90-be7a-0b5d4fc2b53e        /service_id/1f06cfca-4d82-472e-9a00-28b610984716postgresql_pgstatements_agent        Waiting                              /agent_id/f5e55c7b-54d1-4efc-9b8c-801d3a1bcd52        /service_id/1f06cfca-4d82-472e-9a00-28b610984716vmagent                              Running          push                /agent_id/2b40cc87-e805-496d-ac87-603ca998839b

出现这个waitting状态需要配置pg优化组件，并重启数据库

shared_preload_libraries='pg_stat_statements'track_io_timing = ontrack_activity_query_size = 2048pg_stat_statements.max = 10000pg_stat_statements.track = allpg_stat_statements.track_utility = onpg_stat_statements.save = on

postgres@allsql01->pg_ctl -D /data/pgdata/11/data -l logfile restartwaiting for server to shut down.... doneserver stoppedwaiting for server to start.... doneserver startedpostgres@allsql01->psql -h localhostpsql (11.7)Type "help" for help.postgres=# create extension pg_stat_statements;CREATE EXTENSION[root@allsql01 ~]# pmm-admin listService type        Service name               Address and port        Service IDPostgreSQL          PG-10.10.10.11-5432        10.10.10.11:5432        /service_id/1f06cfca-4d82-472e-9a00-28b610984716Agent type                           Status           Metrics Mode        Agent ID                                              Service IDpmm_agent                            Connected                            /agent_id/e40485ef-9834-46e8-8749-b9bbbbbc1aacnode_exporter                        Running          push                /agent_id/1b7aee5a-24e3-4e65-b131-d94b173e6e6epostgres_exporter                    Running          push                /agent_id/640c5077-10ed-4e90-be7a-0b5d4fc2b53e        /service_id/1f06cfca-4d82-472e-9a00-28b610984716postgresql_pgstatements_agent        Running                              /agent_id/f5e55c7b-54d1-4efc-9b8c-801d3a1bcd52        /service_id/1f06cfca-4d82-472e-9a00-28b610984716vmagent                              Running          push                /agent_id/2b40cc87-e805-496d-ac87-603ca998839b