RE: Perform streaming logical transactions by background workers and parallel apply

On Thu, Sep 15, 2022 1:15 PM Wang, Wei/王 威 <wangw.fnst@fujitsu.com> wrote:
> 
> Attach the new patch set.
> 

Hi,

I did some performance tests for "rollback to savepoint" cases, based on v28
patch.

This test used synchronous logical replication, and compared SQL execution times
before and after applying the patch. It tested different percentage of changes
in the transaction are rolled back (use "rollback to savepoint"), when using
different logical_decoding_work_mem.

The test was performed ten times, and the average of the middle eight was taken.

The results are as follows. The bar charts and the scripts of the test are
attached. The steps to reproduce performance test are at the beginning of
`start_pub.sh`.

RESULT - rollback 10% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                        43.752      43.463      42.667
patched                     32.646      30.941      31.491
Compare with HEAD           -25.39%     -28.81%     -26.19%


RESULT - rollback 20% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                        40.974      40.214      39.930
patched                     28.114      28.055      27.550
Compare with HEAD           -31.39%     -30.23%     -31.00%


RESULT - rollback 30% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                        37.648      37.785      36.969
patched                     29.554      29.389      27.398
Compare with HEAD           -21.50%     -22.22%     -25.89%


RESULT - rollback 50% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                        32.312      32.201      32.533
patched                     30.238      30.244      27.903
Compare with HEAD           -6.42%      -6.08%      -14.23%

(If "Compare with HEAD" is a positive number, it means worse than HEAD; if it is
a negative number, it means better than HEAD.)

Summary:
In general, when using "rollback to savepoint", the more the amount of data we
need to rollback, the smaller the improvement compared to HEAD. But as such
cases won't be often, this should be okay.

Regards,
Shi yu