"# of events" in "Wait events for all databases" or "Wait events for all queries" tables appears to be wrong on server restart

[mbanck-cd]

The aggregated number of events for a whole database or particular queries appears to be wrong when the server gets restarted (or the pg_wait_sampling data gets otherwise reset, e.g. via pg_wait_sampling_reset_profile(), see powa-team/powa#178).

The numbers appear to high for me, but it is a bit difficult to generate a reproducer. The problem is likely the max(count) - min(count) here:

LOG:  statement: SELECT srvid, queryid, ps.query, event_type, event, sum(count) AS counts
                    FROM (
                    SELECT srvid, queryid, dbid, datname, event_type, event,
            max(count) - min(count) AS count
            FROM
          public.powa_databases,
          LATERAL
[...]

[rjuju]

Hi,

Indeed it will likely give wrong results if the datasource was reset in the displayed interval. I think that the best we can do is check if the last value is lower than the new value, in this case return the new value and otherwise the difference. That will give better results but unfortunately highly underestimated results.

[mbanck-cd]

Well or maybe just "invalid"/"-1" or something? Too low is probably better than too high, but still not great.

[rjuju]

We could, but with a big enough interval on a server restarted frequently enough there is no guarantee that the first value will be higher than the last one. And it would be hard to detect without looking at every single snapshot, which we cannot do without having bad overhead. Maybe just add an extra column which can contain a flag, that we would set to "warning, data seems to have been reset in the interval, only a truncated view is available" or something like that.

[mbanck-cd]

Any update on this? I guess this isn't trivial to fix, right?

[rjuju]

nothing yet, I was hoping that @pgiraud wouid have some insight on the UI part.

that being said, the biggest issue right now is the prohibitive cost when you have a lot of remote servers (number seems to be 20+), with quite a lot of reports having been made recently. this one is unfortunately quite hard to do, at least if done without forcing users to fully dedicate an instance for that.

is this pg_wait_sampling issue very frequent? I was assuming that it would only happen once in a while, making it not so pressing. if for some reason it's happening very frequently, I'm wondering how much you can actually learn from the data source.

[mbanck-cd]

is this pg_wait_sampling issue very frequent? I was assuming that it would only happen once in a while, making it not so pressing. if for some reason it's happening very frequently, I'm wondering how much you can actually learn from the data source.

The pg_wait_sampling problem was amplified a lot due to postgrespro/pg_wait_sampling#85 - this forced them to run pg_wait_sampling_reset() infrequently, leading to noticing the off statistics.

Now that pg_wait_sampling was fixed and profile_pid consistently stays off, this issue is indeed much less critical, but I wanted to ask about the status anyway.