RE: Conflict detection for update_deleted in logical replication - Mailing list pgsql-hackers

Dear hackers,

All the tests in [1] are done with autovacuum=off, so I checked how would be in
autovacuum=on case.

Highlights
==========
* Regression on subscriber-side became bit larger than autovacuum=on case
  when pgbench was run on both side
* Other than that, they were mostly same.

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], but autovacuum was enabled.

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

Test Scenarios & Results:
 - pgHead : Median TPS = 39757.24404
 - pgHead + patch : Median TPS = 40871.37782

Observation:
 - same trend as autovacuum=off
 - 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    40020.19703        40871.37782
2    39650.27381        40299.31806
3    39757.24404        41017.21089
median    39757.24404        40871.37782

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

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


Test Scenarios & Results:
 - pgHead : Median TPS = 41552.27857
 - pgHead + patch : Median TPS = 41677.02942

Observation:
 - same trend as autovacuum=off
 - No performance regression observed with the patch applied.
 - The results were consistent across runs.

Detailed Results Table:

run#    pgHEAD         pgHead+patch(ON) 
1    41656.71589        41673.42577
2    41552.27857        41677.02942
3    41504.98347        42114.66964
median    41552.27857        41677.02942


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

Workload:
 - Ran pgbench with 15 clients for the *both side*.
 - The duration was 300s, and the measurement was repeated 3 times.

Test Scenarios & Results:
Publisher:
 - pgHead : Median TPS = 17355.08998
 - pgHead + patch : Median TPS = 18382.41213

Subscriber:
 - pgHead : Median TPS = 16998.14496
 - pgHead + patch : Median TPS = 5804.129821


Observation:
 - Regression became larger than autovacuum = off (-50%->-60%)
 - No performance regression observed on the publisher with the patch applied.
 - The performance is reduced on the subscriber side (TPS reduction (~60%)) due
   to dead tuple retention for the conflict detection

Detailed Results Table:

On publisher:
run#    pgHEAD     pgHead+patch(ON) 
1    17537.52375    18382.41213
2    17355.08998    18408.0712
3    17286.78467    18119.77276
median    17355.08998    18382.41213

On subscriber:
run#    pgHEAD     pgHead+patch(ON) 
1    17130.63876    5886.375748
2    16998.14496    5737.799408
3    16891.2713    5804.129821
median    16998.14496    5804.129821

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

Best regards,
Hayato Kuroda
FUJITSU LIMITED