Re: Resetting spilled txn statistics in pg_stat_replication - Mailing list pgsql-hackers

On Mon, Jun 22, 2020 at 8:22 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Sun, Jun 21, 2020 at 3:27 AM Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
> >
> > On Thu, Jun 18, 2020 at 12:21:17PM +0530, Amit Kapila wrote:
> > >On Thu, Jun 18, 2020 at 8:01 AM Masahiko Sawada
> > ><masahiko.sawada@2ndquadrant.com> wrote:
> > >>
> > >> On Wed, 17 Jun 2020 at 20:14, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >> >
> > >> >
> > >> > I had written above in the context of persisting these stats.  I mean
> > >> > to say if the process has bounced or server has restarted then the
> > >> > previous stats might not make much sense because we were planning to
> > >> > use pid [1], so the stats from process that has exited might not make
> > >> > much sense or do you think that is okay?  If we don't want to persist
> > >> > and the lifetime of these stats is till the process is alive then we
> > >> > are fine.
> > >> >
> > >>
> > >> Sorry for confusing you. The above my idea is about having the stats
> > >> per slots. That is, we add spill_txns, spill_count and spill_bytes to
> > >> pg_replication_slots or a new view pg_stat_logical_replication_slots
> > >> with some columns: slot_name plus these stats columns and stats_reset.
> > >> The idea is that the stats values accumulate until either the slot is
> > >> dropped, the server crashed, the user executes the reset function, or
> > >> logical decoding is performed with different logical_decoding_work_mem
> > >> value than the previous time. In other words, the stats values are
> > >> reset in either case. That way, I think the stats values always
> > >> correspond to logical decoding using the same slot with the same
> > >> logical_decoding_work_mem value.
> > >>
> > >
> > >What if the decoding has been performed by multiple backends using the
> > >same slot?  In that case, it will be difficult to make the judgment
> > >for the value of logical_decoding_work_mem based on stats.  It would
> > >make sense if we provide a way to set logical_decoding_work_mem for a
> > >slot but not sure if that is better than what we have now.
> > >
> > >What problems do we see in displaying these for each process?  I think
> > >users might want to see the stats for the exited processes or after
> > >server restart but I think both of those are not even possible today.
> > >I think the stats are available till the corresponding WALSender
> > >process is active.
> > >
> >
> > I don't quite see what the problem is. We're in this exact position with
> > many other stats we track and various GUCs. If you decide to tune the
> > setting for a particular slot, you simply need to be careful which
> > backends decode the slot and what GUC values they used.
> >
>
> What problem do you if we allow it to display per-process (WALSender
> or backend)?  They are incurred by the WALSender or by backends so
> displaying them accordingly seems more straightforward and logical to
> me.
>
> As of now, we don't allow it to be set for a slot, so it won't be
> convenient for the user to tune it per slot.  I think we can allow to
> set it per-slot but not sure if there is any benefit for the same.
>

If we display stats as discussed in email [1] (pid, slot_name,
spill_txns, spill_count, etc.), then we can even find the stats w.r.t
each slot.


[1] - https://www.postgresql.org/message-id/CA%2Bfd4k5nqeFdhpnCULpTh9TR%2B15rHZSbz0SDC6sZhr_v99SeKA%40mail.gmail.com

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com