Re: Resetting spilled txn statistics in pg_stat_replication

On Thu, 11 Jun 2020 at 20:02, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> 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?

Since the logical decoding intermediate files are written at per slots
directory, I thought that corresponding these statistics to
replication slots is also understandable for users. I was thinking
something like pg_stat_logical_replication_slot view which shows
slot_name and statistics of only logical replication slots. The view
always shows rows as many as existing replication slots regardless of
logical decoding being running. I think there is no big difference in
how users use these statistics values between maintaining at slot
level and at logical decoding level.

In logical replication case, since we generally don’t support setting
different logical_decoding_work_mem per wal senders, every wal sender
will decode the same WAL stream with the same setting, meaning they
will similarly spill intermediate files. Maybe the same is true
statistics of streaming. So having these statistics per logical
replication might not help as of now.

> 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.

Agreed, in general, it's better not to frequently reset cumulative
values. I personally would like these statistics to be valid even
after the process executing logical decoding exits (or even after
server restart), and to do reset them as needed.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services