RE: Conflict detection for update_deleted in logical replication

On Wednesday, January 8, 2025 6:33 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:

Hi,

> On Wed, Jan 8, 2025 at 1:53 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > On Wed, Jan 8, 2025 at 3:02 PM Masahiko Sawada
> <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Dec 19, 2024 at 11:11 PM Nisha Moond
> <nisha(dot)moond412(at)gmail(dot)com> wrote:
> > > >
> > > >
> > > > [3] Test with pgbench run on both publisher and subscriber.
> > > >
> > > >
> > > >
> > > > Test setup:
> > > >
> > > > - Tests performed on pgHead + v16 patches
> > > >
> > > > - Created a pub-sub replication system.
> > > >
> > > > - Parameters for both instances were:
> > > >
> > > >
> > > >
> > > > share_buffers = 30GB
> > > >
> > > > min_wal_size = 10GB
> > > >
> > > > max_wal_size = 20GB
> > > >
> > > > autovacuum = false
> > >
> > > Since you disabled autovacuum on the subscriber, dead tuples created
> > > by non-hot updates are accumulated anyway regardless of
> > > detect_update_deleted setting, is that right?
> > >
> >
> > I think hot-pruning mechanism during the update operation will remove
> > dead tuples even when autovacuum is disabled.
>
> True, but why did it disable autovacuum? It seems that case1-2_setup.sh
> doesn't specify fillfactor, which makes hot-updates less likely to happen.

IIUC, we disable autovacuum as a general practice in read-write tests for
stable TPS numbers.

>
> I understand that a certain performance dip happens due to dead tuple
> retention, which is fine, but I'm surprised that the TPS decreased by 50% within
> 120 seconds. The TPS goes even worse for a longer test?

We will try to increase the time and run the test again.

> I did a quick
> benchmark where I completely disabled removing dead tuples (by
> autovacuum=off and a logical slot) and ran pgbench but I didn't see such a
> precipitous dip.

I think a logical slot only retain the dead tuples on system catalog,
so the TPS on user table would not be affected that much.

Best Regards,
Hou zj