Clarification on setup

[tarkhil]

Hello

Maybe my questions are dumb, but I need some clarifications on remote setup

What extensions are sever-wide and what needs to be installed in every monitored base? Only hypopg or am I wrong?

Do I need to install powa extension on all servers or only on repository?

Why Predicates shows "No quals found for this query" and why it shows "N/A" in the parameters field of an example plan?

[rjuju]

What extensions are sever-wide and what needs to be installed in every monitored base? Only hypopg or am I wrong?

Yes, only hypopg has to be installed on specific databases. This is documented in the quickstart at https://powa.readthedocs.io/en/latest/quickstart.html:

All extensions except hypopg only need to be installed once, in the dedicated powa database (or another database name that you want to use).

hypopg must be installed in every database on which you want to be able to get automatic index suggestion, including the powa database if needed.

also also reminded in the hypopg documentation at https://powa.readthedocs.io/en/latest/components/hypopg.html:

Connect as a superuser on each database of each server you want to be able to use hypopg on, and type:

Do I need to install powa extension on all servers or only on repository?

on all servers.

Why Predicates shows "No quals found for this query"

Because no predicate was sampled for this query in the selected timeframe. Either there were no activity, or not enough activity given the pg_qualstats.sample_rate configuration (see https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_qualstats.html)

and why it shows "N/A" in the parameters field of an example plan?

same reason, and also because the actual query has been normalised by pg_stat_statements, so we need the actual constant sampled by pg_qualstats to generate a usable query string. Note that since very recently postgres can actually do an explain of a normalised query string, but this isn't something that we support for now (especially since the explain plans are supposed to be tied to specific predicates, like most or least filtering and so on).

[tarkhil]

Thanks a lot; I guess that should be stated explicitly, not spreaded over the documentation.

Activity should be enough, but I'll increase sample rate since this is a test server.

[rjuju]

yeah, I totally agree. I actually started a major overhaul of the documentation some time ago, which is still in progress. At this point this is the main thing blocking the release of the v5, which has a lot of features that need to be documented on top of the needed refactoring.

[tarkhil]

Can't wait for it. Excellent tool!

It shows graphs for what DBA needs, not graphs simplest to build.

[rjuju]

Thanks a lot!

[rjuju]

I tried to improve the situation with the addition of a new "presentation" section in the architecture page that state all of that explicitly plus a bit of additional general stuff: https://powa.readthedocs.io/en/latest/architecture.html#presentation

This should be the most relevant page where I image people would look for this information.

If there are information you think I should add there, or if you think it's best to have it somewhere else please let me know I will be happy to improve it.

[tarkhil]

Regarding remote setup:

Add (if it's true) something like

"So all overhead of metrics storage and request goes to the dedicated Powa repository"

And, well, some numbers helping to set up repository. Like "on 10000 RPS and 0.05 pg_qualstats.sample_rate, repository requires NN Mbytes of RAM" and some estimates on storage.

[rjuju]

"So all overhead of metrics storage and request goes to the dedicated Powa repository"

it's true, the only exception is when the UI runs some EXPLAIN queries (which can be entirely disabled), but those will barely consume any resource. I will add something along those lines, thanks.

And, well, some numbers helping to set up repository. Like "on 10000 RPS and 0.05 pg_qualstats.sample_rate, repository requires NN Mbytes of RAM" and some estimates on storage.

The disk usage isn't related to TPS or even the qualstats sample rate (it has a configurable fixed amount of predicate it can store). The main factor for the disk usage is the number of entries in pg_stat_statements, the snapshot frequency, the "coalesce" setting and the retention. @frost242 has a lot of remote instances (or at least used to), maybe he could share some number?

For the RAM it mostly depends on the dataset size, so also the number of entries in pg_stat_statements but also the time internal used in the UI. I will try to do some benchmarking to give some estimate for general use cases.