Re: Resetting spilled txn statistics in pg_stat_replication

On Tue, 9 Jun 2020 at 17:24, Magnus Hagander <magnus@hagander.net> wrote:
>
>
>
> On Tue, Jun 9, 2020 at 9:12 AM Masahiko Sawada <masahiko.sawada@2ndquadrant.com> wrote:
>>
>> On Tue, 2 Jun 2020 at 18:34, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> >
>> > On Tue, Jun 2, 2020 at 11:48 AM Masahiko Sawada
>> > <masahiko.sawada@2ndquadrant.com> wrote:
>> > >
>> > > Hi all,
>> > >
>> > > Tracking of spilled transactions has been introduced to PG13. These
>> > > new statistics values, spill_txns, spill_count, and spill_bytes, are
>> > > cumulative total values unlike other statistics values in
>> > > pg_stat_replication. How can we reset these values? We can reset
>> > > statistics values in other statistics views using by
>> > > pg_stat_reset_shared(), pg_stat_reset() and so on. It seems to me that
>> > > the only option to reset spilled transactions is to restart logical
>> > > replication but it's surely high cost.
>
>
> You just have to "bounce" the worker though, right? You don't have to actually restart logical replication, just
disconnectand reconnect? 

Right.

>
>
>> > I see your point but I don't see a pressing need for such a function
>> > for PG13.  Basically, these counters will be populated when we have
>> > large transactions in the system so not sure how much is the use case
>> > for such a function. Note that we need to add additional column
>> > stats_reset in pg_stat_replication view as well similar to what we
>> > have in pg_stat_archiver and pg_stat_bgwriter.  OTOH, I don't see any
>> > big reason for not having such a function for PG14.
>>
>> Ok. I think the reset function is mostly for evaluations or rare
>> cases. In either case, since it's not an urgent case we can postpone
>> it to PG14 if necessary.
>
>
> Reading through this thread, I agree that it's kind of weird to keep cumulative stats mixed with non-cumulative
stats.(it always irks me, for example, that we have numbackends in pg_stat_database which behaves different from every
othercolumn in it) 
>
> However, I don't see how they *are* cumulative. They are only cumulative while the client is connected -- as soon as
itdisconnects they go away. In that regard, they're more like the pg_stat_progress_xyz views for example. 
>
> Which makes it mostly useless for long-term tracking anyway. Because no matter which way you snapshot it, you will
losedata. 
>
> ISTM the logical places to keep cumulative stats would be pg_replication_slots? (Or go create a
pg_stat_replication_slots?)That is, that the logical grouping of these statistics for long-term is the replication
slot,not the walsender? 

I personally prefer to display these values in pg_replication_slots.
If we create a new stats view, it's only for logical replication
slots? Or displaying both types of slots as physical replication slots
might have statistics in the future?

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.

Regards,

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