Re: Conflict detection for update_deleted in logical replication

On Wed, Jan 8, 2025 at 4:03 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 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:
> > > > Here is further performance test analysis with v16 patch-set.
> > > >
> > > > - The performance is reduced on the subscriber side (TPS reduction (~50%) [3] ) due
> > > > to dead tuple retention for the conflict detection when detect_update_deleted=on.
> > > > - Performance reduction happens only on the subscriber side, as workload on the publisher is
> > > > pretty high and the apply workers must wait for the amount of transactions with earlier timestamps to
> > > > be applied and flushed before advancing the non-removable XID to remove dead tuples.
> > >
> > > Assuming that the performance dip happened due to dead tuple retention
> > > for the conflict detection, would TPS on other databases also be
> > > affected?
> > >
> >
> > As we use slot->xmin to retain dead tuples, shouldn't the impact be
> > global (means on all databases)?
>
> I think so too.

I can also confirm this via performance tests showing a similar TPS
impact on a database when the update_deleted conflict detection is
enabled for a different database on the subscriber node.

~~~~
Setup:
Code : pgHead+v24
Pub-Sub with logical replication and below configurations-
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB

## configurations only on Sub:
track_commit_timestamp = on
autovacuum_naptime = '30s'

Data on both nodes:
- Pub has pgbench tables created in 'postgres' DB with scale=100. The
tables are with a different name "pgbench_pub_xxx".
- Sub's 'postgres' DB has Pub's tables and is subscribed for all
table changes from the Pub.
- Sub has another DB - 'db1' which has pgbench tables with initial
data of scale=100 and has no subscriptions from Pub.

Test Runs:
- Ran pgbench with 30 clients and 15 minutes each on Pub and Sub's 'db1' DB.
- Collected data for three runs.
(Attached the scripts used for the test.)

Observation:
- At Sub, the TPS on 'db1' impacted despite no subscription created on
this database.
- The TPS is reduced by -84% during 15-minute pgbench runs.

Results:
Run# pubTPS subTPS_db1
1 31760.23258 4865.019909
2 32147.53313 4847.092329
3 31441.95633 4998.91639
Median 31760.23258 4865.019909
pgHead median 30252.22365 30332.64547
regression 5% -84%
~~~~

--
Thanks,
Nisha