Re: Resetting spilled txn statistics in pg_stat_replication

On Thu, 11 Jun 2020 at 12:30, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Jun 11, 2020 at 12:35 AM Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> > On Wed, Jun 10, 2020 at 9:01 AM Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >>
> >
> >> 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?
> >
>
> Because these stats are corresponding to a ReoderBuffer (logical
> decoding) which will be allocated fresh after restart and have no
> relation with what has happened before restart.

I thought the same. But I now think there is no difference between
reconnecting replication and server restart in terms of the logical
decoding context. Even if we persist these values, we might want to
reset these values after crash recovery like stats collector.

>
> 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 think one of the main use-case of these statistics is the tuning of
logical_decoding_work_mem. This seems similar to a combination of
pg_stat_database.temp_files/temp_bytes and work_mem. From this
perspective, I guess it’s useful for users if these values remain
until or the slots are removed or server crashes. Given that the kinds
of logical decoding statistics might grow, having a separate view
dedicated to replication slots makes sense to me.

For updating these statistics, if we correspond these statistics to
logical decoding or replication slot, we can change the strategy of
updating statistics so that it doesn’t depend on logical decoding
plugin implementation. If updating statistics doesn’t affect
performance much, it’s better to track the statistics regardless of
plugins.

Regards,

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