postgres_exporter takes >5mins to generate metrics on large database

[arajkumar]

What happened?

$title

Did you expect to see something different?

postgres_exporter /metrics page must return data quickly in few seconds.

How to reproduce it (as minimally and precisely as possible):

Run against large postgres with quite bit of child tables/hyper tables.

Environment

I've been running postgres/timescale for 2 weeks with >5000 hyper tables and quite a bit of data(1.2TiB).

• tobs version:

  tobs version

• Kubernetes version information:

  kubectl version

• Kubernetes cluster kind:

  insert how you created your cluster: kops, bootkube, etc.

• postgres_exporter logs

  ts=2022-11-18T12:55:45.767Z caller=postgres_exporter.go:617 level=debug msg="Querying PostgreSQL version" server=/var/run/postgresql:5432
  ts=2022-11-18T12:55:45.776Z caller=pg_setting.go:35 level=debug msg="Querying pg_setting view" server=/var/run/postgresql:5432
  ts=2022-11-18T12:55:45.792Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_stat_replication
  ts=2022-11-18T12:55:45.794Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_replication_slots
  ts=2022-11-18T12:55:45.797Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_stat_archiver
  ts=2022-11-18T12:55:45.809Z caller=collector.go:200 level=debug msg="collector succeeded" name=bgwriter duration_seconds=0.045750953
  ts=2022-11-18T12:55:45.817Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_stat_activity
  ts=2022-11-18T12:55:45.830Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_stat_database
  ts=2022-11-18T12:55:45.851Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_stat_database_conflicts
  ts=2022-11-18T12:55:45.866Z caller=namespace.go:193 level=debug msg="Querying namespace" namespace=pg_locks
  ts=2022-11-18T13:00:20.934Z caller=collector.go:200 level=debug msg="collector succeeded" name=database duration_seconds=275.167551805

• tobs Logs:

insert tobs logs relevant to the issue here

Anything else we need to know?:

[arajkumar]

I will move this to upstream postgres_exporter once I find the root cause.

[arajkumar]

The query which lists database along with it's size takes the time.

SELECT pg_database.datname
        ,pg_database_size(pg_database.datname)
        FROM pg_database;

[arajkumar]

Looks like the large database is the culprit.

SELECT pg_database.datname
                ,pg_database_size(pg_database.datname)
                FROM pg_database;
  datname  | pg_database_size
-----------+------------------
 postgres  |    1284355875619
 template1 |         10265091
 template0 |          8602115

SELECT pg_database_size('postgres') takes a lot of time(~5mins) to complete. Probably we should have an option to disable db size population from the exporter?

[arajkumar]

I see that there is an option collector.database to disable it, but that doesn't seem to work!

[arajkumar]

--no-collector.database should fix the problem

[arajkumar]

@paulfantom May be we should consider bringing the change to cloud as well. CC: @cevian