Re: Resetting spilled txn statistics in pg_stat_replication

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

> On Tue, 9 Jun 2020 at 17:24, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> >
> >
> > 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?
>
> Right.
>
> >
> >
> >> > 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?
>
> I personally prefer to display these values in pg_replication_slots.
> If we create a new stats view, it's only for logical replication
> slots? Or displaying both types of slots as physical replication slots
> might have statistics in the future?
>

Yeah, I think it's kind of a weird situation. There's already some things
in pg_replication_slots that should probably be in a stat_ view, so if we
were to create one we would have to move those, and probably needlessly
break things for people.

i guess we could have separate views for logical and pysical slots since
there are things that only one of them will have. But there is that already
-- the database for example, and xmins. Splitting that apart now should be
a bigger thing, but I don't think it's worth it.

If we move these values to replication slots, I think we can change
> the code so that these statistics are managed by replication slots
> (e.g. ReplicationSlot struct). Once ReplicationSlot has these values,
> we can keep them beyond reconnections or multiple calls of SQL
> interface functions. Of course, these values don’t need to be
> persisted.
>

Eh, why should they not be persisted? The comparison would be temp_files
and temp_bytes in pg_stat_database, and those *are* persisted.

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