Integrate pg_stat_monitor with test framework
General
Escalation
General
Escalation
Description
How to test
None
How to document
None
FB test
None
Attachments
2
compose.zip
10 Aug, 2021
Screenshot 2021-08-10 at 11.35.04.png
10 Aug, 2021
Activity
Nailya Kutlubaeva
August 10, 2021 at 8:33 AM
(edited)
Tested:
The only problem I found and it was fixed by @Jiří Čtvrtka: extension should be created for each database.
Jiří Čtvrtka
August 5, 2021 at 12:17 PM
@Nailya Kutlubaeva Looks like postgres was not ready yet and script already ran query for create extension, lets try increase waiting time.
Try this one on your env:
---
# docker-compose -f docker-compose-pg-load.yml up postgres-load
version: '3.7'services:
postgres-pgmonitor:
image: ${POSTGRES_IMAGE:-perconalab/percona-distribution-postgresql:13.3}
container_name: pmm-agent-postgres-pgmonitor
command: >
-c shared_preload_libraries=pg_stat_monitor,pg_stat_statements
-c track_activity_query_size=2048
-c pg_stat_statements.max=10000
-c pg_stat_monitor.pgsm_query_max_len=10000
-c pg_stat_monitor.pgsm_normalized_query=0
-c pg_stat_statements.track=all
-c pg_stat_statements.save=off
-c track_io_timing=on
ports:
- 127.0.0.1:5432:5432
environment:
- POSTGRES_USER=pmm-agent
- POSTGRES_PASSWORD=pmm-agent-password
volumes:
- test_db_postgres:/docker-entrypoint-initdb.d/ postgres-load:
image: ${POSTGRES_IMAGE:-perconalab/percona-distribution-postgresql:13.3}
container_name: pmm-agent-postgres-load
depends_on:
- postgres-pgmonitor
command: >
bash -c "
sleep 40
PGPASSWORD=pmm-agent-password psql -Upmm-agent --host=pmm-agent-postgres-pgmonitor -c 'CREATE DATABASE contrib_regression;'
PGPASSWORD=pmm-agent-password psql -Upmm-agent --host=pmm-agent-postgres-pgmonitor -d contrib_regression -c 'CREATE EXTENSION pg_stat_monitor;'
while true
do
PGPASSWORD=pmm-agent-password psql -Upmm-agent --host=pmm-agent-postgres-pgmonitor -d contrib_regression -f /testqueries/pg_stat_monitor_load.sql -o /dev/null
sleep 30
done
tail -f /dev/null
"
environment:
- POSTGRES_USER=pmm-agent
- POSTGRES_PASSWORD=pmm-agent-password
volumes:
- ./testqueries/postgres:/testqueries/volumes:
test_db_postgres:
Waiting increased from 20s to 40s.
Nailya Kutlubaeva
August 5, 2021 at 9:55 AM
there is a problem with this docker-compose file, I run it and added PG to PMM and see the next error in logs:
Failed to start Agent: pq: relation "pg_stat_monitor_settings" does not exist
I connected to this container :
[ec2-user@ip-10-178-0-47 ~]$ docker ps -a |grep postgres
18c6f8224871 perconalab/percona-distribution-postgresql:13.3 "/entrypoint.sh bash…" 14 minutes ago Up 14 minutes 5432/tcp pmm-agent-postgres-load
2a2c709e2a02 perconalab/percona-distribution-postgresql:13.3 "/entrypoint.sh -c s…" 14 minutes ago Up 14 minutes 127.0.0.1:5432->5432/tcp
[ec2-user@ip-10-178-0-47 ~]$ docker exec -it pmm-agent-postgres-pgmonitor psql --user=pmm-agent -W
Password:
psql (13.3)
Type "help" for help.
pmm-agent=# SELECT * FROM pg_stat_monitor_settings;
ERROR: relation "pg_stat_monitor_settings" does not exist
LINE 1: SELECT * FROM pg_stat_monitor_settings;
^
pmm-agent=# SELECT * FROM pg_stat_monitor;
ERROR: relation "pg_stat_monitor" does not exist
LINE 1: SELECT * FROM pg_stat_monitor;
^
pmm-agent=# \dx;
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
Done
Created June 21, 2021 at 11:35 AM
Updated November 13, 2024 at 8:31 AM
Resolved August 10, 2021 at 9:28 AM
Goal:
Create a list of queries that will generate typical values for all fields (dimensions, metrics, labels, etc.) of pg_stat_monitor.
DOD:
We will have a tool (script) to create a load on PostgreSQL DB with a new pg_stat_monitor to populate all fields and receive a report to validate if we collect expected metrics.
docker-compose with PostreSQL (pg_stat_monitor) and sysbench-tcc script to create load with needed metrics.
LINKS:
https://github.com/percona/pg_stat_monitor/blob/master/docs/USER_GUIDE.md
https://confluence.percona.com/pages/viewpage.action?spaceKey=PMM&title=QAN+PostgreSQL+pg_stat_monitor+data+mapping
Queries to generate metrics: https://github.com/percona/pg_stat_monitor/tree/master/regression/sql
How to test (no fb needed, only docker compose file attached here, use latest PMM):
1. Run PMM server
2. Unzip attached docker compose and testqueries folder and run in extracted folder:
docker-compose -f docker-compose-pg-load.yml up postgres-load
3. Add postgres to PMM monitoring
4. Check metrics here
5. Connect to postgres and check new metrics are filled by query:
SELECT * FROM pg_stat_monitor;