Postgresql Query Drill Down Dashboard metrics - pg_stat_statements_time milliseconds is not from postgres_exporter

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

In the mentioned dashboard there is prometheus metric called
pg_stat_statements _time_milliseconds

I have noticed that this is not collected by postgres_exporter. I am interested how this metric is being gathered and where? This is under query duration graph. I have tried to set up PMM Server but it also fails to show the mentioned metric and I want to understand how to troubleshoot it.

Thanks

2

That metric is likely only populated if you’ve enabled the pg_stat_statements PostgreSQL extension. Did you install it? If not, please see the upstream documentation on how to accomplish this.

By the way, we’re working on a PostgreSQL extension named pg_stat_monitor, which provides a lot of additional stats and metrics. It’s included in our PostgreSQL distribution.

3

Thank you for the answer.

Sure I have enabled pg_stat_statements extension for all databases.

I have installed PMM client , postgres_exporter is registered and works fine but still those pg_stat_statement_* metrics are not collected in exporter [host]:9187/metrics. I thought I have to install additional components or another exporter is being used, not the default postgres_exporter

For example this statement does not work

sum by (datname) ((rate(pg_stat_statements_calls{datname=~“$datname”,instance=~“$host”}[$interval]) or
irate(pg_stat_statements_calls{datname=~“$datname”,instance=~“$host”}[5m])))

Dashboards are very helpful but I can’t make them work

PS.I have checked pg_stat_monitor, it seems better, but my version is PG10 not supported by it so I use pg_stat_statements instead.strong text

4

Hi @Eleonora_Grigoryan,

The query drill-down dashboard is a custom dashboard implemented for PMM 1.x. It requires the presence of extra metrics that aren’t available by default. Please read this blogpost: Adding PostgreSQL Queries Overview Dashboards to the PMM Plugin - Percona Database Performance Blog.

In PMM 2.x the same functionality should be superseded by QAN. Technically, it is possible to add the same metrics and create an analogous dashboard, I’ve done that: scratch/pmm2-pg at master · arronax/scratch · GitHub. There, pg_query_drill-down.json and pg_query_overview.json are dashboards and pg_query_pg13.yaml is a custom query definition. You can find guidance on how to add custom queries in PMM 2 in the following blog post: PostgreSQL Custom Dashboards Ported to Percona Monitoring and Management 2 - Percona Database Performance Blog.

However, I strongly recommend against using these dashboards in PMM 2. The nature of query-related metrics makes them ill-suited for time series databases like the VictoriaMetrics used in PMM 2. Moreover, the same data is already being gathered by the QAN subsystem of PMM. If you feel that QAN cannot give you something that the “query drill-down” dashboard can, consider creating a feature request in the PMM Jira project: https://jira.percona.com/projects/PMM/issues. If the dashboard is still required in the meantime, the correct way would be to query the ClickHouse database which contains the QAN data. For an example of this, you can check out the following dashboard: RED Method for MySQL Queries - Designed for PMM2 | Grafana Labs.

P.S. My answer here is heavily based on the assumption that you’re using PMM 2. If you’re still using PMM 1, you should update soon, as it’s going EOL in May, 2022.

2 Likes