Why was the query not captured in PMM?

Percona Monitoring and Management (PMM) PMM 3.x
1

The server is running PostgreSQL version 14 with the pg_stat_statements and pg_profile extensions installed.
Pg_profile snapshots are created hourly at minute 00.
A query was started at 00:05 and completed at 04:23.
This query is not present in PMM but appears in the pg_profile report for 05:00.
PMM is configured in medium mode for metric collection.
What could be the reason the query was not captured in PMM?

PMM version 3.4.1

2

Disabled pg_profile snapshot. Waited for the long query to complete. Its execution took 3 hours and 40 minutes. After 30 minutes, checked PMM QAN — this query is not there. Maybe single queries are being filtered somewhere on your side?

I suspect the problem is that after the query execution is complete and the data is retrieved, the client closes the connection. This creates a situation where the query exists in pg_stat_statements, but since the corresponding PID is no longer in pg_stat_activity, this information does not make it into PMM.

Why does a request from session 69462779.3f3725 from a single IP address appear in the pg_stat_statement log and in PMM, while a request from session 6945c41b.2922ac is only present in pg_stat_statement?

session_id=69462779.3f3725, user=[unknown], db=[unknown], app=[unknown], client=[local]LOG: connection received: host=[local]
session_id=69462779.3f3725, user=postgres, db=database, app=[unknown], client=[local]LOG: connection authorized: user=postgres database=database application_name=psql
session_id=69462779.3f3725, user=postgres, db=database, app=psql, client=[local]LOG: duration: 3919303.234 ms statement: — query_text —

session_id=6945c41b.2922ac, user=[unknown], db=[unknown], app=[unknown], client=ip_clientLOG: connection received: host=ip_client port=44852
session_id=6945c41b.2922ac, user=id_client, db=database, app=[unknown], client=ip_clientLOG: connection authenticated: identity=“id_client” method=md5 (/pgsql/14/data/pg_hba.conf:122)
session_id=6945c41b.2922ac, user=id_client, db=database, app=[unknown], client=ip_clientLOG: connection authorized: user=id_client database=database
session_id=6945c41b.2922ac, user=id_client, db=database, app=PostgreSQL JDBC Driver, client=ip_clientLOG: duration: 11260136.968 ms execute : — query_text —
session_id=6945c41b.2922ac, user=id_client, db=database, app=PostgreSQL JDBC Driver, client=ip_clientDETAIL: parameters: $1 = ‘2025-10-01’, $2 = ‘2025-10-01’, $3 = ‘2025-10-01’, $4 = ‘2025-12-19 00:05:00.182’, $5 = ‘1000000’
session_id=6945c41b.2922ac, user=id_client, db=database, app=PostgreSQL JDBC Driver, client=ip_clientLOG: duration: 103180.453 ms execute : — query_text —
session_id=6945c41b.2922ac, user=id_client, db=database, app=PostgreSQL JDBC Driver, client=ip_clientDETAIL: parameters: $1 = ‘2025-10-01’, $2 = ‘2025-10-01’, $3 = ‘2025-10-01’, $4 = ‘2025-12-19 00:05:00.182’

3

I conducted testing. When using pg_stat_statements, we lose the query chain. We also lose queries for which the connection is terminated immediately after execution.

I created a file named 1.sql and wrote something similar to this in it:

sql

select * from schemas.tables limit 10000000;
SELECT pg_sleep(600);
\q

After executing the script via psql, only the query SELECT pg_sleep(600); with an execution time of 600 seconds appeared in PMM’s QAN. The first SELECT query is not present in PMM’s history.

Then, in the 1.sql file, I removed SELECT pg_sleep(600); and ran the execution via psql again.
This time, no queries appeared in QAN. All of them are present in pg_stat_statements, but pg_stat_activity is empty.

Perhaps we should give administrators the option to choose how necessary it is to bind queries to the hosts they originated from? When using pg_stat_statements…

4

Hi @ANDiv1976,

First of all, PMM using pg_stat_statements (PGSS) does not use pg_stat_activity in any way. So if something is captured by pg_stat_activity but not by PGSS, it will be missing in PMM, because PMM only consumes the output from PGSS.

I tried your example, but I’m seeing the opposite behavior. The query SELECT * FROM schemas.tables LIMIT 10000000; shows up in QAN, but SELECT pg_sleep(600); does not - because it was cancelled before it finished.

You wrote:

“After executing the script via psql, only the query SELECT pg_sleep(600); with an execution time of 600 seconds appeared in PMM’s QAN. The first SELECT query is not present in PMM’s history.”

I’m not sure how SELECT pg_sleep(600); could appear in PGSS output, because PGSS records only finished queries, not running or cancelled ones. So it shouldn’t be present at all, because you cancelled it. It may be left over from a previous execution if PGSS wasn’t reset in the meantime. Could you please verify (e.g., reset PGSS and rerun)?

I’ll also take another look at the long-running query case (where it’s missing in QAN even though you say it was present in PGSS). I haven’t been able to reproduce that yet - maybe it requires running the query for hours. I’ll follow up once I have an update.

Thank you.

5

which versions have you tested on?
I’m losing queries for PostgreSQL version 14.17 and PMM 3.4.1
I’ll run similar tests on version 16 of PostgreSQL.

6

I’m using the same versions as you (PostgreSQL 14.17 and PMM 3.4.1). Regardless of the version, canceled queries are not being captured. That’s why I’m surprised that you had SELECT pg_sleep(600); appear in QAN.

Could you please run the same test you mentioned and share the results? Thank you.