multi node stat collection

[tedyu]

Suppose there are 3 PG backends (running on 3 nodes) and same data has been loaded into each backend.

On each backend, the stat is collected by pg_qualstats via shared_preload_libraries properly configured. There are several clients. Each client would issue read queries against any of the 3 backends (each backend would only have partial view of the total workload).

This issue is to develop tool which can make index advice based on the aggregate stat from all the backends.

[rjuju]

Hi,

Are you talking about using pg_qualstats in the "single backend" mode, ie. without configuring shared_preload_libraries?

This mode is only intended for some specific and lightweight usage if you can't afford to restart your instance to have the "real" pg_qualstats usage. Therefore, it's up to you to send all the representative workload on a single connection if you want the index advisor to give better answer in that mode.

Otherwise, just you the normal mode configuring shared_preload_libraires and you will get the behavior you want. With the default sampling (1/max_connections), the overhead should be in the noise level. If that's still too much you can still keep pg_qualstats loaded yet disabled all the time, and enable it for a short period of time to analyze the predicates.

[tedyu]

This is a feature request. In the title, I mentioned node. I modified the description to mention that the backends run on different nodes.

The background: backends use storage which is implemented through RAFT, hence they access the same physical data (including pg_catalog.pg_database, pg_catalog.pg_operator, etc).

[rjuju]

Ah I see. I'm still a bit confused about your description, because a backend is only a server process, and pg_qualstats stores the data in shared memory, which is totally irrelevant with either backends or the storage. I think the way you use backend means some modified postgres server that can share its data with postgres modified postgres server.

If that's the case, I'm still not sure that you have a problem. Seeing only part of the workload isn't a issue, as you would want to sample the queries anyway to avoid a huge overhead. Does each node somehow only see a subset of the different queries (as in queryid), and thus doesn't get a representative overview of the normalized queries?

Also, don't you have the exact same problem with pg_stat_statements and similar extensions?

[tedyu]

backend means some modified postgres server

Yes.

Does each node somehow only see a subset of the different queries

Yes. That is possible.

have the exact same problem with pg_stat_statements 

pg_stat_statements has the same issue.

[rjuju]

I see. There are still a lot of unknown on the architecture, but I think that it would be better to find a way to have the shared memory shared between all nodes rather than being local (as I'm assuming that this architecture wouldn't perform on high latency network), or at least reconcile it frequently like the rest of the data so that you get a global and consistent view of hash tables and other things in shared memory. This way the problem will be fixed for everything.

I agree it's hard to do but I don't see other alternative really working. It might be possible to find some workarounds for pg_qualstats (although I don't think we could easily merge all the counters), but that won't be possible for each extension (at least pg_stat_statements will also be problematic), and also you won't need some specific changes in each and every extension of the ecosystem.

For the index advisor only, you might be able to merge the data from all nodes as you don't really need to be exact for that. Only getting quals that are executed on sequential scans and are selective enough are interesting, but the selectivity itself doesn't need to be very precise, and most of the other fields are ignored for that purpose. But you will still have to build something to retrieve the data from all nodes, merge it and modify the function to get the data from that source rather than pg_qualstats() directly.