Re: Conflict detection for update_deleted in logical replication

Hi,

As per the test results in [1], the TPS drop observed on the
subscriber with update_deleted enabled was mainly because only a
single apply worker was handling the replication workload from
multiple concurrent publisher clients.
The following performance benchmarks were conducted to evaluate the
improvements using parallel apply when update_deleted
(retain_dead_tuples) is enabled, under heavy workloads, without
leveraging row filters or multiple subscriptions to distribute the
load.

Note: The earlier tests from[1] are repeated with few workload
modifications to see the improvements using parallel-apply.

Highlights
===============
- No regression was observed when running pgbench individually on
either Pub or Sub nodes.
- When pgbench was run on both Pub and Sub, performance improved
significantly with the parallel apply patch. With just 4 workers, Sub
was able to catch up with Pub without regression.
- With max_conflict_retention_duration=60s, retention on Sub was not
stopped when using 4 or more parallel workers.

Machine details
===============
Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz CPU(s) :88 cores, - 503 GiB RAM

Source code:
===============
- pgHead(e9a31c0cc60) and parallel-apply v1 patch[2]
- additionally used v64-0001 of update_deleted for
max_conflict_retention_duration related tests.

Test-01: pgbench on publisher
============================
Setup:
---------
Pub --> Sub
- Two nodes created in pub-sub logical replication setup.
- Both nodes have the same set of pgbench tables created with scale=60.
- The Sub node is subscribed to all the changes from the Pub's
pgbench tables and the subscription has retain_dead_tuples = on

Workload:
--------------
- Run default pgbench(read-write) only on Pub with #clients=40 and
run duration=10 minutes

Results:
------------
#run pgHead_TPS pgHead+v1_patch_TPS
1 41135.71241 39922.7163
2 40466.23865 39980.29034
3 40578.16374 39867.44929
median 40578.16374 39922.7163
- No regression.
~~~

Test-02: pgbench on subscriber
========================
Setup: same as Test-01

Workload:
--------------
- Run default pgbench(read-write) only on Sub node with #clients=40
and run duration=10 minutes

Results:
-----------
#run pgHead_TPS pgHead+v1_patch_TPS
1 42173.90504 42071.18058
2 41836.10027 41839.84837
3 41921.81233 41494.9918
median 41921.81233 41839.84837
- No regression.
~~~

Test-03: pgbench on both sides
========================
Setup:
------
Pub --> Sub
- Two nodes created in a pub-sub logical replication setup.
- Both nodes have different sets of pgbench tables created with scale=60.
- The sub node also has Pub's pgbench tables and is subscribed to all
the changes.

Workload:
--------------
- Run default pgbench(read-write) on both Pub and Sub nodes for their
respective pgbench tables
- Both pgbench runs are with #clients=15 and duration=10 minutes

Observations:
--------------
- On pgHead when retain_dead_tuples=ON, the Sub's TPS reduced by ~76%
- With the parallel apply patch, performance improves significantly
as parallel workers increase, since conflict_slot.xmin advances more
quickly.
- With just 4 parallel workers, subscription TPS matches the baseline
(no regression).
- Performance remains consistent at 8 and 16 workers.

Detailed results:
------------------
case-1:
- The base case pgHead(e9a31c0cc60) and retain_dead_tuples=OFF

#run Pub_tps Sub_tps
1 17140.08647 16994.63269
2 17421.28513 17445.62828
3 17167.57801 17070.86979
median 17167.57801 17070.86979

case-2:
- pgHead(e9a31c0cc60) and retain_dead_tuples=ON

#run Pub_tps Sub_tps
1 18667.29343 4135.884924
2 18200.90297 4178.713784
3 18309.87093 4227.330234
median 18309.87093 4178.713784
- The Sub sees a ~76% of TPS reduction by default on head.

case-3:
- pgHead(e9a31c0cc60)+ v1_parallel_apply_patch and retain_dead_tuples=ON
- number of parallel apply workers varied as 2,4,8,16

3a) #workers=2
#run Pub_tps Sub_tps
1 18336.98565 4244.072357
2 18629.96658 4231.887288
3 18152.92036 4253.293648
median 18336.98565 4244.072357
- There is no significant TPS improvement with 2 parallel workers,
~76% TPS reduction

3b) #workers=4
#run Pub_tps Sub_tps
1 16796.49468 16850.05362
2 16834.06057 16757.73115
3 16647.78486 16762.9107
median 16796.49468 16762.9107
- No regression

3c) #workers=8
#run Pub_tps Sub_tps
1 17105.38794 16778.38209
2 16783.5085 16780.20492
3 16806.97569 16642.87521
median 16806.97569 16778.38209
- No regression

3d) #workers=16
#run Pub_tps Sub_tps
1 16827.20615 16770.92293
2 16860.10188 16745.2968
3 16808.2148 16668.47974
median 16827.20615 16745.2968
- No regression.
~~~

Test-04. pgbench on both side, and max_conflict_retention_duration was tuned
========================================================================
Setup:
-------
Pub --> Sub
- setup is same as Test-03(above)
- Additionally, subscription option max_conflict_retention_duration=60s

Workload:
-------------
- Run default pgbench(read-write) on both Pub and Sub nodes for their
respective pgbench tables
- Started with 15 clients on both sides.
- When conflict_slot.xmin becomes NULL on Sub, pgbench was paused to
let the subscription catch up. Then reduced publisher clients by half
and resumed pgbench. Here, slot.xmin becomes NULL to indicate conflict
retention is stopped under high publisher load but stays non-NULL when
Sub is able to catchup with Pub's load.
- Total duration of pgbench run is 10 minutes (600s).

Observations:
------------------
- Without the parallel apply patch, publisher clients reduced from
15->7->3,and finally the retention was not stopped at 3 clients and
slot.xmin remained non-NULL.
- With the parallel apply patch, using 2 workers the subscription
handled up to 7 publisher clients without stopping the conflict
retention.
- With 4+ workers, retention continued for the full 10 minutes and
Sub TPS showed no regression.

Detailed results:
-----------------
case-1:
- pgHead(e9a31c0cc60) + v64-001 and retain_dead_tuples=ON

On publisher:
#cleints durations[s] TPS
15 73 17953.52
7 100 9141.9
3 426 4286.381132
On Subscriber:
#cleints durations[s] TPS
15 73 10626.67
15 99 10271.35
15 431 19467.07612
~~~

case-2:
- pgHead(e9a31c0cc60) + v64-001 + v1_parallel-apply patch[2] and
retain_dead_tuples=ON
- number of parallel apply workers varied as 2,4,8

2a) #workers=2
On publisher:
#cleints durations[s] TPS
15 87 17318.3
7 512 9063.506025

On Subscriber:
#cleints durations[s] TPS
15 87 10299.66
15 512 18426.44818

2b) #workers=4
On publisher:
#cleints durations[s] TPS
15 600 16953.40302

On Subscriber:
#cleints durations[s] TPS
15 600 16812.15289

2c) #workers=8
On publisher:
#cleints durations[s] TPS
15 600 16946.91636

On Subscriber:
#cleints durations[s] TPS
15 600 16708.12774

~~~~
The scripts used for all the tests are attached.

[1] https://www.postgresql.org/message-id/OSCPR01MB1496663AED8EEC566074DFBC9F54CA%40OSCPR01MB14966.jpnprd01.prod.outlook.com
[2] https://www.postgresql.org/message-id/OS0PR01MB5716D43CB68DB8FFE73BF65D942AA%40OS0PR01MB5716.jpnprd01.prod.outlook.com

--
Thanks,
Nisha