On Wed, Jan 8, 2025 at 4:03 PM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > On Wed, Jan 8, 2025 at 1:53 AM Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Wed, Jan 8, 2025 at 3:02 PM Masahiko Sawada <sawada.m...@gmail.com> > > wrote: > > > On Thu, Dec 19, 2024 at 11:11 PM Nisha Moond <nisha.moond...@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
otherDB_impact_setup.sh
Description: Bourne shell script
otherDB_impact_measure.sh
Description: Bourne shell script
