Adding PostgreSQL Databases/Tables Size Dashboard to the Percona Monitoring and Management Plugin

PostgreSQL Table Size PMMThis blog post is about how you can gain detailed information in Percona Monitoring and Management (PMM2) of PostgreSQL disk usage. We take a look at using a catalog called pg_class, which catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes, sequences, views, materialized views, composite types, and toast tables.

It will use some custom queries in order to fetch such information, so we need to create a file with a custom query. Postgresql exporter is processing such files by default, so you should place your file into one of the paths referred to below, to the desired scraping interval and restart the exporter.

/usr/local/percona/pmm2/collectors/custom-queries/postgresql/low-resolution
/usr/local/percona/pmm2/collectors/custom-queries/postgresql/medium-resolution
/usr/local/percona/pmm2/collectors/custom-queries/postgresql/high-resolution

FYI, the metrics scraping interval can be set on the tab “PMM Settings”:

Setting up Query Monitoring in PMM for Postgres

First of all, we have to enable the extension dblink. It’s a PostgreSQL contrib extension that allows users to connect to other databases and run arbitrary queries in them.

The next step is to create a query for collecting data from tables. We propose using our bash script that generates a query and forms other fields for a custom query file. You have to specify the database name, or names, that will be monitored. Pg_class catalog is generated for each database so it’s not possible to create a general query. You can store the result of the script in a separate file or can extend an existing file with queries.

Now it’s time to restart exporter and check the newly collected metrics.

  • pg_class_table_rows
  • pg_class_disk_usage_table_bytes
  • pg_class_disk_usage_index_bytes
  • pg_class_disk_usage_toast_bytes

Also, you can upload our dashboard in order to checks queries through the Grafana interface. It has got all PMM2 elements such as variables, menu, annotations.

Here is a screenshot from our test installation.

Share this post

Leave a Reply