Re: Resetting spilled txn statistics in pg_stat_replication

On Sat, 4 Jul 2020 at 22:13, Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
>
>
>
> On Thu, Jul 2, 2020 at 1:31 PM Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
>>
>>
>>
>> Thanks! Yes, I'm working on this patch while considering to send the
>> stats to stats collector.
>>
>> I've attached PoC patch that implements a simple approach. I'd like to
>> discuss how we collect the replication slot statistics in the stats
>> collector before I bring the patch to completion.
>>
>
> I understand the patch is only in the initial stage but I just tried testing it.

Thank you for testing the patch!

> Using the patch, I enabled logical replication and created two pub/subs (sub1,sub2) for two seperate tables (t1,t2). I inserted data into the second table (t2) such that it spills into disk.
> Then when I checked the stats using the new function pg_stat_get_replication_slots() , I see that the same stats are updated for both the slots, when ideally it should have reflected in the second slot alone.
>
> postgres=# SELECT s.name, s.spill_txns, s.spill_count, s.spill_bytes FROM pg_stat_get_replication_slots() s(name, spill_txns, spill_count, spill_bytes);
> name | spill_txns | spill_count | spill_bytes
> ------+------------+-------------+-------------
> sub1 | 1 | 20 | 1320000000
> sub2 | 1 | 20 | 1320000000
> (2 rows)
>

I think this is because logical decodings behind those two logical
replications decode all WAL records *before* filtering the specified
tables. In logical replication, we decode the whole WAL stream and
then pass it to a logical decoding output plugin such as pgoutput. And
then we filter tables according to the publication. Therefore, even if
subscription sub1 is not interested in changes related to table t2,
the replication slot sub1 needs to decode the whole WAL stream,
resulting in spilling into disk.

Regards,

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