PostgresSQL Database/Table size monitoring

Percona Monitoring and Management (PMM) Database Monitoring and Management
1

Hello All!

I’m trying to monitor PostgreSQL tables/database sizes. I run >>>this<<< tutorial from Percona. It looks like it’s working but PMM Agent is spamming errors in syslog.

Here’s my setup:

PMM Server:

  • OS/kernel: Linux pmm-test 4.19.0-17-amd64 #1 SMP Debian 4.19.194-3 (2021-07-18) x86_64 GNU/Linux
  • Docker: Docker version 18.09.1, build 4c52b90 (installed from Debian repository)
  • Docker compose version: docker-compose version 1.21.0, build unknown (also from Debian repository)
  • PMM server version: 2.22.0 (September 21, 2021)

PostgreSQL server:

  • OS/kernel: Linux ntms-test 4.19.0-17-amd64 #1 SMP Debian 4.19.194-3 (2021-07-18) x86_64 GNU/Linux
  • PostgreSQL version: psql (11.12 (Debian 11.12-0+deb10u1))
  • PMM Agent: 2.21.0 (FullCommit: 22d3dec79e93b92de77a9fb8ff2b74b3d8d5d47d)
  • Installed extensions on PostgreSQL database:
postgres=# \dx
                                       List of installed extensions
        Name        | Version |   Schema   |                         Description
--------------------+---------+------------+--------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
  • Full YAML generated by Percona script to monitor table size
## Custom query
pg_class:
  query: "SELECT datname, COALESCE(ntms.relname) as relname, COALESCE(ntms.table_rows) as table_rows, COALESCE(ntms.disk_usage_table_bytes) as disk_usage_table_bytes, COALESCE(ntms.disk_usage_index_bytes) as disk_usage_index_bytes, COALESCE(ntms.disk_usage_toast_bytes,0) as disk_usage_toast_bytes FROM pg_database LEFT OUTER JOIN dblink('dbname=ntms','SELECT current_database(), relname,  CAST(reltuples as BIGINT), pg_total_relation_size(oid), pg_indexes_size(oid), pg_total_relation_size(reltoastrelid) FROM pg_class') as ntms(datname name, relname name, table_rows BIGINT, disk_usage_table_bytes BIGINT, disk_usage_index_bytes BIGINT, disk_usage_toast_bytes BIGINT) USING (datname) WHERE datname NOT LIKE 'template_' AND datname NOT LIKE 'postgres'"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of the database that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of the table, index, view, etc."
    - table_rows:
        usage: "GAUGE"
        decription: "Number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX"
    - disk_usage_table_bytes:
        usage: "GAUGE"
        description: "Total disk space used by the specified table, including all indexes and TOAST data"
    - disk_usage_index_bytes:
        usage: "GAUGE"
        description: "Total disk space used by indexes attached to the specified table"
    - disk_usage_toast_bytes:
        usage: "GAUGE"
        description: "Total disk space used by TOAST data attached to the specified table"

I can see charts in grafana. If I run this query from pmm postgres user it’s returning all data without any errors. There is no other schemata than public.
Errors in syslogs looks like this:

Oct  4 13:08:16 ntms-test pmm-agent[5065]: #033[36mINFO#033[0m[2021-10-04T13:08:16.955+02:00] time="2021-10-04T13:08:16+02:00" level=info msg="Error running query on database \"127.0.0.1:5432\": pg_class pq: function dblink(unknown, unknown) does not exist" source="postgres_exporter.go:1388"  #033[36magentID#033[0m=/agent_id/2e6857f8-8f5c-4912-a65c-f8f7298dd641 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter

This error is occuring every second/two seconds (despite it’s placed in low-resolution folder which should collect data every 60 seconds).

Regards
Przemek

2

Hi, Przemek. Please try to enable extension dblink from pmm user.

3

Hi, Anton

Thank you for reply. Unfortunately this didn’t help (just like I suppose). I’m still getting same errors. I have even updated pmm to newest version (2.23.0 from October 21st).

Oct 26 14:50:16 ntms-debug pmm-agent[27106]: #033[36mINFO#033[0m[2021-10-26T14:50:16.552+02:00] time="2021-10-26T14:50:16+02:00" level=info msg="Error running query on database \"127.0.0.1:5432\": pg_class pq: function dblink(unknown, unknown) does not exist" source="postgres_exporter.go:1426"  #033[36magentID#033[0m=/agent_id/011189e9-a10a-4aa4-bfc4-1066a78077e4 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter
Oct 26 14:50:16 ntms-debug pmm-agent[27106]: #033[36mINFO#033[0m[2021-10-26T14:50:16.552+02:00] time="2021-10-26T14:50:16+02:00" level=error msg="queryNamespaceMappings returned 1 errors" source="postgres_exporter.go:1605"  #033[36magentID#033[0m=/agent_id/011189e9-a10a-4aa4-bfc4-1066a78077e4 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter

[14:56:59]root@ntms-debug:~# psql -h127.0.0.1 -U pmm postgres
Password for user pmm:
psql (11.12 (Debian 11.12-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \dx
                                       List of installed extensions
        Name        | Version |   Schema   |                         Description
--------------------+---------+------------+--------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# SELECT datname, COALESCE(ntms.relname) as relname, COALESCE(ntms.table_rows) as table_rows, COALESCE(ntms.disk_usage_table_bytes) as disk_usage_table_bytes, COALESCE(ntms.disk_usage_index_bytes) as disk_usage_index_bytes, COALESCE(ntms.disk_usage_toast_bytes,0) as disk_usage_toast_bytes FROM pg_database LEFT OUTER JOIN dblink('dbname=ntms','SELECT current_database(), relname,  CAST(reltuples as BIGINT), pg_total_relation_size(oid), pg_indexes_size(oid), pg_total_relation_size(reltoastrelid) FROM pg_class') as ntms(datname name, relname name, table_rows BIGINT, disk_usage_table_bytes BIGINT, disk_usage_index_bytes BIGINT, disk_usage_toast_bytes BIGINT) USING (datname) WHERE datname NOT LIKE 'template_' AND datname NOT LIKE 'postgres';

 datname |                    relname                    | table_rows | disk_usage_table_bytes | disk_usage_index_bytes | disk_usage_toast_bytes
---------+-----------------------------------------------+------------+------------------------+------------------------+------------------------
 ntms    | pg_toast_16393                                |          0 |                   8192 |                   8192 |                      0
...
(655 rows)
postgres=#

Query generated by script works flawlessly but for some reason pmm-client/agent exporters are throwing errors.

4

Hi, Przemek!

I repeat this problem. It is true when you haven’t user/database “postgres” and extension dblink at postgres db. Can you check it at your environment?

5

User and database postgres exists. Dblink extension is installed on postgres database.

I have no more ideas what’s wrong, so probably will try to make own dashboard and query. Especially that names “bloat_1234” or “index_3412” are annoying :wink: