Re: Resetting spilled txn statistics in pg_stat_replication

On Fri, 12 Jun 2020 at 12:21, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Jun 11, 2020 at 7:39 PM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > On Thu, 11 Jun 2020 at 20:02, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Jun 11, 2020 at 3:07 PM Masahiko Sawada
> > > <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> > > >
> > > > On Thu, 11 Jun 2020 at 18:11, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Thu, Jun 11, 2020 at 1:46 PM Masahiko Sawada
> > > > > <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> > > > > >
> > > > > > On Thu, 11 Jun 2020 at 12:30, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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.
> >
>
> What I wanted to know is how will it help users to tune
> logical_decoding_work_mem? Different backends can process from the
> same slot, so it is not clear how user will be able to make any
> meaning out of those stats.

I thought that the user needs to constantly monitor them during one
process is executing logical decoding and to see the increments. I
might not fully understand but I guess the same is true for displaying
them w.r.t. process. Since a process can do logical decoding several
times using the same slot with a different setting, the user will need
to monitor them several times.

> OTOH, it is easier to see how to make
> meaning of these stats if we display them w.r.t process. Basically,
> we have spill_count and spill_size which can be used to tune
> logical_decoding_work_mem and also the activity of spilling happens at
> process level, so it sounds like one-to-one mapping.

Displaying them w.r.t process also seems a good idea but I'm still
unclear what to display and how long these values are valid. The view
will have the following columns for example?

* pid
* slot_name
* spill_txns
* spill_count
* spill_bytes
* exec_count

Regards,

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