Re: Resetting spilled txn statistics in pg_stat_replication

On Tue, Jun 9, 2020 at 9:12 AM Masahiko Sawada <
masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:

> On Tue, 2 Jun 2020 at 18:34, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Tue, Jun 2, 2020 at 11:48 AM Masahiko Sawada
> > <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> > >
> > > Hi all,
> > >
> > > Tracking of spilled transactions has been introduced to PG13. These
> > > new statistics values, spill_txns, spill_count, and spill_bytes, are
> > > cumulative total values unlike other statistics values in
> > > pg_stat_replication. How can we reset these values? We can reset
> > > statistics values in other statistics views using by
> > > pg_stat_reset_shared(), pg_stat_reset() and so on. It seems to me that
> > > the only option to reset spilled transactions is to restart logical
> > > replication but it's surely high cost.
>

You just have to "bounce" the worker though, right? You don't have to
actually restart logical replication, just disconnect and reconnect?

> I see your point but I don't see a pressing need for such a function
> > for PG13. Basically, these counters will be populated when we have
> > large transactions in the system so not sure how much is the use case
> > for such a function. Note that we need to add additional column
> > stats_reset in pg_stat_replication view as well similar to what we
> > have in pg_stat_archiver and pg_stat_bgwriter. OTOH, I don't see any
> > big reason for not having such a function for PG14.
>
> Ok. I think the reset function is mostly for evaluations or rare
> cases. In either case, since it's not an urgent case we can postpone
> it to PG14 if necessary.
>

Reading through this thread, I agree that it's kind of weird to keep
cumulative stats mixed with non-cumulative stats. (it always irks me, for
example, that we have numbackends in pg_stat_database which behaves
different from every other column in it)

However, I don't see how they *are* cumulative. They are only cumulative
while the client is connected -- as soon as it disconnects they go away. In
that regard, they're more like the pg_stat_progress_xyz views for example.

Which makes it mostly useless for long-term tracking anyway. Because no
matter which way you snapshot it, you will lose data.

ISTM the logical places to keep cumulative stats would be
pg_replication_slots? (Or go create a pg_stat_replication_slots?) That is,
that the logical grouping of these statistics for long-term is the
replication slot, not the walsender?

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>