Re: Resetting spilled txn statistics in pg_stat_replication

On Thu, Jun 11, 2020 at 3:07 PM Masahiko Sawada
<masahiko.sawada@2ndquadrant.com> wrote:
>
> On Thu, 11 Jun 2020 at 18:11, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Thu, Jun 11, 2020 at 1:46 PM Masahiko Sawada
> > <masahiko.sawada@2ndquadrant.com> wrote:
> > >
> > > On Thu, 11 Jun 2020 at 12:30, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > >
> > > > Now, thinking about this again, I am not sure if these stats are
> > > > directly related to slots. These are stats for logical decoding which
> > > > can be performed either via WALSender or decoding plugin (via APIs).
> > > > So, why not have them displayed in a new view like pg_stat_logical (or
> > > > pg_stat_logical_decoding/pg_stat_logical_replication)?   In future, we
> > > > will need to add similar stats for streaming of in-progress
> > > > transactions as well (see patch 0007-Track-statistics-for-streaming at
> > > > [1]), so having a separate view for these doesn't sound illogical.
> > > >
> > >
> > > I think we need to decide how long we want to remain these statistics
> > > values. That is, if we were to have such pg_stat_logical view, these
> > > values would remain until logical decoding finished since I think the
> > > view would display only running logical decoding. OTOH, if we were to
> > > correspond these stats to slots, these values would remain beyond
> > > multiple logical decoding SQL API calls.
> > >
> >
> > I thought of having these till the process that performs these
> > operations exist.  So for WALSender, the stats will be valid till it
> > is not restarted due to some reason or when performed via backend, the
> > stats will be valid till the corresponding backend exits.
> >
>
> The number of rows of that view could be up to (max_backends +
> max_wal_senders). Is that right? What if different backends used the
> same replication slot one after the other?
>

Yeah, it would be tricky if multiple slots are used by the same
backend.  We could probably track the number of times decoding has
happened by the session that will probably help us in averaging the
spill amount.  If we think that the aim is to help users to tune
logical_decoding_work_mem to avoid frequent spilling or streaming then
how would maintaining at slot level will help?  As you said previously
we could track it only for running logical decoding context but if we
do that then data will be temporary and the user needs to constantly
monitor the same to make sense of it but maybe that is fine.

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