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