Horton

Re: Conflict detection for update_deleted in logical replication

Nisha Moond <nisha.moond412@gmail.com>

View thread View on postgr.es
From: Nisha Moond <nisha.moond412@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, shveta malik <shveta.malik@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-01-21T05:57:28Z
Lists: pgsql-hackers

Attachments

On Wed, Jan 8, 2025 at 4:03 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jan 8, 2025 at 1:53 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > On Wed, Jan 8, 2025 at 3:02 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > On Thu, Dec 19, 2024 at 11:11 PM Nisha Moond <nisha.moond412@gmail.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

Commits

  1. Fix intermittent BF failures in 035_conflicts.

  2. Resume conflict-relevant data retention automatically.

  3. Fix intermittent test failure introduced in 6456c6e2c4.

  4. Fix Coverity issue reported in commit a850be2fe.

  5. Add test to prevent premature removal of conflict-relevant data.

  6. Post-commit review fixes for 228c370868.

  7. Add max_retention_duration option to subscriptions.

  8. Detect and report update_deleted conflicts.

  9. Preserve conflict-relevant data during logical replication.

  10. Integrate FullTransactionIds deeper into two-phase code

  11. Improve checks for GUC recovery_target_timeline

  12. Prevent excessive delays before launching new logrep workers.

  13. Keep WAL segments by slot's last saved restart LSN

  14. Rework some code handling pg_subscription data in psql and pg_dump

  15. Use generateClonedIndexStmt to propagate CREATE INDEX to partitions.