RE: Conflict detection for update_deleted in logical replication

Dear hackers,

As a confirmation purpose, I did performance testing with four workloads
we did before.

Highlights
==========
The retests on the latest patch set v46 show results consistent with previous
observations:
- There is no performance impact on the publisher side
- There is no performance impact on the subscriber side, if the workload is
running only on subscriber.
- The performance is reduced on the subscriber side (TPS reduction (~50%) [Test-03])
when retain_conflict_info=on and pgbench is running on both side. Because dead
tuple retention for conflict detection. If high workloads on the publisher,
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.
- Subscriber-side TPS improves when the workload on the publisher is reduced.
- Performance on the subscriber can also be improved by tuning the
max_conflict_retention_duration GUC properly.

Used source
===========
pgHead commit fd7d7b7191 + v46 patchset

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

01. pgbench on publisher
========================
The workload is mostly same as [1].

Workload:
- Ran pgbench with 40 clients for the publisher.
- The duration was 120s, and the measurement was repeated 10 times.

(pubtest.tar.gz can run the same workload)

Test Scenarios & Results:
- pgHead : Median TPS = 39809.84925
- pgHead + patch : Median TPS = 40102.88108

Observation:
- No performance regression observed with the patch applied.
- The results were consistent across runs.

Detailed Results Table:
- each cell shows the TPS in each case.
- patch(ON) means patched and retain_conflict_info=ON is set.

run# pgHEAD pgHead+patch(ON)
1 40106.88834 40356.60039
2 39854.17244 40087.18077
3 39516.26983 40063.34688
4 39746.45715 40389.40549
5 40014.83857 40537.24
6 39819.26374 40016.78705
7 39800.43476 38774.9827
8 39884.2691 40163.35257
9 39753.11246 39902.02755
10 39427.2353 40118.58138
median 39809.84925 40102.88108

02. pgbench on subscriber
========================
The workload is mostly same as [2].

Workload:
- Ran pgbench with 40 clients for the *subscriber*.
- The duration was 120s, and the measurement was repeated 10 times.

(subtest.tar.gz can run the same workload)

Test Scenarios & Results:
- pgHead : Median TPS = 41564.64591
- pgHead + patch : Median TPS = 41083.09555

Observation:
- No performance regression observed with the patch applied.
- The results were consistent across runs.

Detailed Results Table:

run# pgHEAD pgHead+patch(ON)
1 41605.88999 41106.93126
2 41555.76448 40975.9575
3 41505.76161 41223.92841
4 41722.50373 41049.52787
5 41400.48427 41262.15085
6 41386.47969 41059.25985
7 41679.7485 40916.93053
8 41563.60036 41178.82461
9 41565.69145 41672.41773
10 41765.11049 40958.73512
median 41564.64591 41083.09555

03. pgbench on both sides
========================
The workload is mostly same as [3].

Workload:
- Ran pgbench with 40 clients for the *both side*.
- The duration was 120s, and the measurement was repeated 10 times.

(bothtest.tar.gz can run the same workload)

Test Scenarios & Results:
Publisher:
- pgHead : Median TPS = 16799.67659
- pgHead + patch : Median TPS = 17338.38423
Subscriber:
- pgHead : Median TPS = 16552.60515
- pgHead + patch : Median TPS = 8367.133693

Observation:
- No performance regression observed on the publisher with the patch applied.
- The performance is reduced on the subscriber side (TPS reduction (~50%)) due
to dead tuple retention for the conflict detection

Detailed Results Table:

On publisher:
run# pgHEAD pgHead+patch(ON)
1 16735.53391 17369.89325
2 16957.01458 17077.96864
3 16838.07008 17480.08206
4 16743.67772 17531.00493
5 16776.74723 17511.4314
6 16784.73354 17235.76573
7 16871.63841 17255.04538
8 16814.61964 17460.33946
9 16903.14424 17024.77703
10 16556.05636 17306.87522
median 16799.67659 17338.38423

On subscriber:
run# pgHEAD pgHead+patch(ON)
1 16505.27302 8381.200661
2 16765.38292 8353.310973
3 16899.41055 8396.901652
4 16305.05353 8413.058805
5 16722.90536 8320.833085
6 16587.64864 8327.217432
7 16508.45076 8369.205438
8 16357.05337 8394.34603
9 16724.90296 8351.718212
10 16517.56167 8365.061948
median 16552.60515 8367.133693

04. pgbench on both side, and max_conflict_retention_duration was tuned
========================================================================
The workload is mostly same as [4].

Workload:
- Initially ran pgbench with 40 clients for the *both side*.
- Set max_conflict_retention_duration = {60, 120}
- When the slot is invalidated on the subscriber side, stop the benchmark and
wait until the subscriber would be caught up. Then the number of clients on
the publisher would be half.
In this test the conflict slot could be invalidated as expected when the workload
on the publisher was high, and it would not get invalidated anymore after
reducing the workload. This shows even if the slot has been invalidated once,
users can continue to detect the update_deleted conflict by reduce the
workload on the publisher.
- Total period of the test was 900s for each cases.

(max_conflixt.tar.gz can run the same workload)

Observation:
-
- Parallelism of the publisher side is reduced till 15->7->3 and finally the
conflict slot is not invalidated.
- TPS on the subscriber side is improved when the concurrency was reduced.
This is because the dead tuple accumulation is reduced on subscriber due to
the reduced workload on the publisher.
- when publisher has Nclients=3, no regression in subscriber's TPS

Detailed Results Table:
For max_conflict_retention_duration = 60s
On publisher:
Nclients duration [s] TPS
15 72 14079.1
7 82 9307
3 446 4133.2

On subscriber:
Nclients duration [s] TPS
15 72 6827
15 81 7200
15 446 19129.4

For max_conflict_retention_duration = 120s
On publisher:
Nclients duration [s] TPS
15 162 17835.3
7 152 9503.8
3 283 4243.9

On subscriber:
Nclients duration [s] TPS
15 162 4571.8
15 152 4707
15 283 19568.4

Thanks Nisha-san and Hou-san for helping the work.

[1]: https://www.postgresql.org/message-id/CABdArM5SpMyGvQTsX0-d%3Db%2BJAh0VQjuoyf9jFqcrQ3JLws5eOw%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/TYAPR01MB5692B0182356F041DC9DE3B5F53E2%40TYAPR01MB5692.jpnprd01.prod.outlook.com
[3]: https://www.postgresql.org/message-id/CABdArM4OEwmh_31dQ8_F__VmHwk2ag_M%3DYDD4H%2ByYQBG%2BbHGzg%40mail.gmail.com
[4]: https://www.postgresql.org/message-id/OSCPR01MB14966F39BE1732B9E433023BFF5E72%40OSCPR01MB14966.jpnprd01.prod.outlook.com

Best regards,
Hayato Kuroda
FUJITSU LIMITED