Re: Conflict detection for update_deleted in logical replication

Here are the performance test results and analysis with the recent patches.

Test Setup:
- Created Pub and Sub nodes with logical replication and below configurations.
autovacuum_naptime = '30s'
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
track_commit_timestamp = on (only on Sub node).
- Pub and Sub had different pgbench tables with initial data of scale=100.

-------------------------------
Case-0: Collected data on pgHead
-------------------------------
- Ran pgbench(read-write) on both the publisher and the subscriber
with 30 clients for a duration of 15 minutes, collecting data over 3
runs.

Results:
Run# pub_TPS sub_TPS
1 30551.63471 29476.81709
2 30112.31203 28933.75013
3 29599.40383 28379.4977
Median 30112.31203 28933.75013

-------------------------------
Case-1: Long run(15-minutes) tests when retain_conflitc_info=ON
-------------------------------
- Code: pgHead + v19 patches.
- At Sub set autovacuum=false.
- Ran pgbench(read-write) on both the publisher and the subscriber
with 30 clients for a duration of 15 minutes, collecting data over 3
runs.

Results:
Run# pub_TPS sub_TPS
1 30326.57637 4890.410972
2 30412.85115 4787.192754
3 30860.13879 4864.549117
Median 30412.85115 4864.549117
regression 1% -83%

- A 15-minute pgbench run test showed higher reduction in the sub's
TPS. As the test run time increased the TPS reduced further at the Sub
node.

-------------------------------
Case-2 : Re-ran the case-1 with autovacuum enabled and running every 30 seconds.
-------------------------------
- Code: pgHead + v19 patches.
- At Sub set autovacuum=true.
- Also measured the frequency of slot.xmin and the worker's
oldest_nonremovable_xid updates.

Results:
Run# pub_TPS sub_TPS #slot.xmin_updates
#worker's_oldest_nonremovable_xid_updates
1 31080.30944 4573.547293 0 1
regression 3% -84%

- Autovacuum did not help in improving the Sub's TPS.
- The slot's xmin was not advanced.
~~~~

Observations and RCA for TPS reduction in above tests:
- The launcher was not able to advance slot.xmin during the 15-minute
pgbench run, leading to increased dead tuple accumulation on the
subscriber node.
- The launcher failed to advance slot.xmin because the apply worker
could not set the oldest_nonremovable_xid early and frequently enough
due to following two reasons -
1) For large pgbench tables (scale=100), the tablesync takes time
to complete, forcing the apply worker to wait before updating its
oldest_nonremovable_xid.
2) With 30 clients generating operations at a pace that a single
apply worker cannot match, the worker fails to catch up with the
rapidly increasing remote_lsn, lagging behind the Publisher's LSN
throughout the 15-minute run.

Considering the above reasons, for better performance measurements,
collected data when table_sync is off, with a varying number of
clients on the publisher node. Below test used the v21 patch set,
which also includes improvement patches (006 and 007) for more
frequent slot.xmin updates.
-------------------------------
Case-3: Create the subscription with option "copy_data=false", so, no
tablesync in the picture.
-------------------------------
Test setup:
- Code: pgHead + v21 patches.
- Created Pub and Sub nodes with logical replication and below configurations.
autovacuum_naptime = '30s'
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
track_commit_timestamp = on (only on Sub node).

- The Pub and Sub had different pgbench tables with initial data of scale=100.
- Ran pgbench(read-write) on both the pub and the sub for a duration
of 15 minutes, using 30 clients on the Subscriber while varying the
number of clients on the Publisher.
- In addition to TPS, the frequency of slot.xmin and the worker's
oldest_nonremovable_xid updates was also measured.

Observations:
- As the number of clients on the publisher increased, the
publisher's TPS improved, but the subscriber's TPS dropped
significantly.
- The frequency of slot.xmin updates also declined with more clients
on the publisher, indicating that the apply worker updated its
oldest_nonremovable_xid less frequently as the read-write operations
on the publisher increased.

Results:
#Pub-clients pubTPS pubTPS_increament subTPS pubTPS_reduction
#slot.xmin_updates #worker's_oldest_nonremovable_xid_updates
1 1364.487898 0 35000.06738 0 6976 6977
2 2706.100445 98% 32297.81408 -8% 5838 5839
4 5079.522778 272% 8581.034791 -75% 268 269
30 31308.18524 2195% 5324.328696 -85% 4 5

Note: In the above result table, the column -
- "PubTPS_increment" represents the % improvement in the Pub's
TPS compared to its TPS in the initial run with #Pub-clients=1 and
- "SubTPS_reduction" indicates the % decrease in the Sub's TPS
compared to its TPS in the initial run with #Pub-clients=1.
~~~~

Conclusion:
There is some improvement in slot.xmin update frequency with
table_sync off and the additional patches that updates slot's xmin
aggressively.
However, the key point is that with a large number of clients
generating write operations, apply worker LAGs with a large margin
leading to non-updation of slot's xmin as the test run time increases.
This is also visible [in case-3] that with only 1 client on publisher,
there is no degradation on the subscriber. As the number of clients
increases, the degradation also increases.

Based on this test analysis I can say that we need some way/option to
invalidate such slots that LAG by a threshold margin, as mentioned at
[1]. This should solve the performance degradation and bloat problem.
~~~~

(Attached the test scripts used for above tests)

[1] https://www.postgresql.org/message-id/CAA4eK1Jyo4odkVsnSeAWPh8Wgpw12EbS9q8s_eN14LtcFNXCSA%40mail.gmail.com

--
Thanks,
Nisha