When using a non-default pg_stat_statements.max value, the calculated QPS displayed in QAN may be wrong.
General
Escalation
General
Escalation
Description
If pg_stat_statements.max is changed, the current PMM client-side cache for queries may not be enough for the query churn, and the results of calculating the per-minute query call deltas may be wrong.
From ClickHouse pmm.metrics table, for the problematic query, we see num_queries in the millions per minute, which is wrong. After 00:46, pg_stat_statements.max was changed from 20000 back to its default of 5000:
This can be easily reproduced by increasing pg_stat_statements.max:
and having a workload that generates unique queries, like:
Check statements stored:
And run an intensive workload for a SELECT count query in another session. If you let that query call reach the millions, it will be much more visible.
I’m attaching two screenshots showing the good and bad examples from QAN itself.
Suggested implementation:
Set cache size by the value from pg_stat_statements.max
Check if it affects pg_stat_monitor, perfschema
How to test
STR as above
Test simplification - Set pg_stat_statements.max to 10 000 and check for QPS
If pg_stat_statements.max is changed, the current PMM client-side cache for queries may not be enough for the query churn, and the results of calculating the per-minute query call deltas may be wrong.
From ClickHouse pmm.metrics table, for the problematic query, we see num_queries in the millions per minute, which is wrong. After 00:46, pg_stat_statements.max was changed from 20000 back to its default of 5000:
This can be easily reproduced by increasing pg_stat_statements.max:
and having a workload that generates unique queries, like:
Check statements stored:
And run an intensive workload for a SELECT count query in another session. If you let that query call reach the millions, it will be much more visible.
I’m attaching two screenshots showing the good and bad examples from QAN itself.
Suggested implementation:
Set cache size by the value from
pg_stat_statements.maxCheck if it affects pg_stat_monitor, perfschema