RE: Conflict detection for update_deleted in logical replication

Dear hackers,

I did some benchmarks with the patch. More detail, a pub-sub replication system
was built and TPS was measured on the subscriber. Results were shown that the
performance can be degraded if the wal_receiver_status_interval is long.
This is expected behavior because the patch retains more dead tuples on the
subscriber side.
Also, we've considered a new mechanism which dynamically tunes the period of status
request, and confirmed that it reduced the regression. This is described the latter
part.

Below part contained the detailed report.

## Motivation - why the benchmark is needed

V15 patch set introduces a new replication slot on the subscriber side to retain
needed tuples for the update_deleted detection.
However, this may affect the performance of query executions on the subscriber because
1) tuples to be scaned will be increased and 2) HOT update cannot be worked.
The second issue comes from the fact that HOT update can work only when both tuples
can be located on the same page.

Based on above reasons I ran benchmark tests for the subscriber. The variable of the
measurement is the wal_receiver_status_interval, which controls the duration of status request.

## Used source code

HEAD was 962da900, and applied v15 patch set atop it.

## Environment

RHEL 7 machine which has 755GB memory, 4 physical CPUs and 120 logical processors.

## Workload

1. Constructed a pub-sub replication system.
Parameters for both instances were:

share_buffers = 30GB
min_wal_size = 10GB
max_wal_size = 20GB
autovacuum = false
track_commit_timestamp = on (only for subscriber)

2. Ran pgbench with initialize mode. The scale factor was set to 100.
3. Ran pgbench with 60 clients for the subscriber. The duration was 120s,
and the measurement was repeated 5 times.

Attached script can automate above steps. You can specify the source type in the
measure.sh and run it.

## Comparison

The performance testing was done for HEAD and patched source code.
In case of patched, "detect_update_deleted" parameter was set to on. Also, the
parameter "wal_receiver_status_interval" was varied to 1s, 10s, and 100s to
check the effect.

Appendix table shows results [1]. The regression becomes larger based on the wal_receiver_status_interval.
TPS regressions are almost 5%(interval=1s) -> 25%(intervals=10s) -> 55% (intervals=55%).

Attached png file visualize the result: each bar shows median.

## Analysis

I attached to the backend via perf and found that heapam_index_fetch_tuple()
consumed much CPU time ony in case of patched [2]. Also, I checked pg_stat_all_tables
view and found that HOT update rarely happened only in the patched case [3].
This means that whether backend could do HOT update is the dominant.

When the detect_update_deleted = on, the additional slot is defined on the subscriber
ide and it is updated based on the activity; The frequency is determined by the
wal_receiver_status_intervals. In intervals=100s case, it is relatively larger
for the workload so that some dead tuples remained, this makes query processing slower.

This result means that users may have to tune consider the interval period based
on their workload. However, it is difficult to predict the appropriate value.

## Experiment - dynamic period tuning

Based on above, I and Hou discussed off-list and implemented new mechanism which
tunes the duration between status request dynamically. The basic idea is similar
with what slotsync worker does. The interval of requests is initially 100ms,
and becomes twice when if there are no XID assignment since the last advancement.
The maxium value is either of wal_receiver_status_interval or 3min.

Benchmark results with this are shown in [4]. Here wal_receiver_status_interval
is not changed, so we can compare with the HEAD and interval=10s case in [1] - 59536 vs 59597.
The regression is less than 1%.

The idea has already been included in v16-0002, please refer it.

## Experiment - shorter interval

Just in case - I did an extreme case that wal_receiver_status_interval is quite short - 10ms.
To make interval shorter I implemented an attached patch for both cases. Results are shown [5].
The regression is not observed or even better (I think this is caused by the randomness).

This experiment also shows the result that the regression is happened due to the dead tuple.

## Appendix [1] - result table

Each cells show transaction per seconds of the run.

patched
# run interval=1s intervals=10s intervals=100s
1 55876 45288 26956
2 56086 45336 26799
3 56121 45129 26753
4 56310 45169 26542
5 55389 45071 26735
median 56086 45169 26753

HEAD
# run interval=1s intervals=10s intervals=100s
1 59096 59343 59341
2 59671 59413 59281
3 59131 59597 58966
4 59239 59693 59518
5 59165 59631 59487
median 59165 59597 59341

## Appendix [2] - perf analysis

patched:

```
- 58.29% heapam_index_fetch_tuple
+ 38.28% heap_hot_search_buffer
+ 13.88% ReleaseAndReadBuffer
5.34% heap_page_prune_opt
+ 13.88% ReleaseAndReadBuffer
```

head:

```
- 2.13% heapam_index_fetch_tuple
1.06% heap_hot_search_buffer
0.62% heap_page_prune_opt
```

## Appendix [3] - pg_stat

patched
```
postgres=# SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd, n_tup_upd - n_tup_hot_upd AS n_tup_non_hot FROM pg_stat_all_tables where relname like 'pgbench%';
relname | n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd | n_tup_non_hot
------------------+-----------+---------------+-------------------+---------------
pgbench_history | 0 | 0 | 0 | 0
pgbench_tellers | 453161 | 37996 | 415165 | 415165
pgbench_accounts | 453161 | 0 | 453161 | 453161
pgbench_branches | 453161 | 272853 | 180308 | 180308
(4 rows)
```

head
```
postgres=# SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd, n_tup_upd - n_tup_hot_upd AS n_tup_non_hot FROM
pg_stat_all_tables where relname like 'pgbench%';
relname | n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd | n_tup_non_hot
------------------+-----------+---------------+-------------------+---------------
pgbench_history | 0 | 0 | 0 | 0
pgbench_tellers | 2078197 | 2077583 | 614 | 614
pgbench_accounts | 2078197 | 1911535 | 166662 | 166662
pgbench_branches | 2078197 | 2078197 | 0 | 0
(4 rows)
```

## Appendix [4] - dynamic status request

# run dynamic (v15 + PoC)
1 59627
2 59536
3 59359
4 59443
5 59541
median 59536

## Apendix [5] - shorter wal_receiver_status_interval

pached
# run interval=10ms
1 58081
2 57876
3 58083
4 57915
5 57933
median 57933

head
# run interval=10ms
1 57595
2 57322
3 57271
4 57421
5 57590
median 57421

Best regards,
Hayato Kuroda
FUJITSU LIMITED