RE: Parallel Apply

Dear hackers,

> TODO - potential improvement to use shared hash table for tracking
> dependencies.

I measured the performance data for the shared hash table approach. Based on the result,
local hash table approach seems better.

Abstract
========
No good performance improvement was observed by the shared hash, it had 1-2% regression.
The trend was not changed by number of parallel apply workers.

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

Used patch
==========
0001 is same as Hou posted on -hackers [1], and 0002 is the patch for shared hash.

0002 introduces a shared hash table dependency_dshash. 0002 introduces a shared
hash table dependency_dshash. Since the length of shared hash key must be fixed
value, it is computed from the replica identity of tuples. When the parallel apply
worker receives changes, it computes the hash key again and remember it by the list.
At the commit time it iterates the list and remove hash entries based on the keys.
0001 has the mechanism to clean up the local hash but it was removed.

Workload
========
Setup:
---------
Pub --> Sub
 - Two nodes created in pub-sub synchronous logical replication setup.
 - Both nodes have same set of pgbench tables created with scale=100.
 - The Sub node is subscribed to all the changes from the Pub's pgbench tables

Workload Run:
--------------------
 - Run built-in pgbench(simple-update)[2] only on Pub with #clients=40 and run duration=5 minutes

Results:
--------------------
Number of worker is changed to 4, 8 or 16. In any cases 0001 has better performance.

#worker = 4:
------------
    0001    0001+0002    diff
TPS    14499.33387    14097.74469    3%
    14361.7166    14359.87781    0%
    14467.91344    14153.53934    2%
    14451.8596    14381.70987    0%
    14646.90346    14239.4712    3%
    14530.66788    14298.33845    2%
    14733.35987    14189.41794    4%
    14543.9252    14373.21266    1%
    14945.57568    14249.46787    5%
    14638.6342    14125.87626    4%
AVE    14581.988979    14246.865608    2%
MEDIAN    14537.296540    14244.469536    2%

#worker=8
---------
    0001    0001+0002    diff
TPS    21531.08712    21443.68765    0%
    22337.60439    21383.94778    4%
    21806.70504    21097.42874    3%
    22192.99695    21424.78921    4%
    21721.95472    21470.8714    1%
    21450.6779    21265.89539    1%
    21397.51433    21606.51486    -1%
    21551.09391    21306.97061    1%
    21455.89699    21351.38868    0%
    21849.52528    21304.42329    3%
AVE    21729.505662    21365.591761    2%
MEDIAN    21636.524316    21367.668229    1%


#worker=16
-----------
    0001    0001+0002    diff
TPS    28034.64652    28129.85068    0%
    27839.10942    27364.40725    2%
    27693.94576    27871.80199    -1%
    27717.83971    27129.96132    2%
    28453.25381    27439.77526    4%
    28083.73208    27201.0004    3%
    27842.19262    27226.43813    2%
    27729.44205    27459.01256    1%
    28103.76727    27385.80016    3%
    27688.52482    27485.67209    1%
AVE    27918.645405    27469.371982    2%
MEDIAN    27840.651020    27412.787708    2%

[1]:
https://www.postgresql.org/message-id/OS0PR01MB5716D43CB68DB8FFE73BF65D942AA%40OS0PR01MB5716.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/docs/current/pgbench.html#PGBENCH-OPTION-BUILTIN

Best regards,
Hayato Kuroda
FUJITSU LIMITED