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

On Tue, Jan 3, 2023 at 11:10 AM wangw.fnst@fujitsu.com
<wangw.fnst@fujitsu.com> wrote:
>
> On Mon, Jan 2, 2023 at 18:54 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > On Fri, Dec 30, 2022 at 3:55 PM wangw.fnst@fujitsu.com
> > <wangw.fnst@fujitsu.com> wrote:
> > >
> > > I've checked it and it looks good to me.
> > > Rebased the other patches and ran the pgident for the patch set.
> > >
> > > Attach the new patch set.
> > >
> >
> > I have added a few DEBUG messages and changed a few comments in the
> > 0001 patch. With that v71-0001* looks good to me and I'll commit it
> > later this week (by Thursday or Friday) unless there are any major
> > comments or objections.
>
> Thanks for your improvement.
>
> Rebased the patch set because the new change in HEAD (c8e1ba7).
> Attach the new patch set.
>
> Regards,
> Wang wei

Hi,
In continuation with [1] and [2], I did some performance testing on
v70-0001 patch.

This test used synchronous logical replication and compared SQL
execution times before and after applying the patch.

The following cases are tested by varying logical_decoding_work_mem:
a) Bulk insert.
b) Bulk delete
c) Bulk update
b) Rollback to savepoint. (different percentages of changes in the
transaction are rolled back).

The tests are performed ten times, and the average of the middle eight is taken.

The scripts are the same as before [1]. The scripts for additional
update and delete testing are attached.

The results are as follows:

RESULT - bulk insert (5kk)
---------------------------------------------------------------
logical_decoding_work_mem     64kB        256kB       64MB
HEAD                                         34.475      34.222      34.400
patched                                      20.168      20.181      20.510
Compare with HEAD                -41.49%     -41.029%    -40.377%


RESULT - bulk delete (5kk)
---------------------------------------------------------------
logical_decoding_work_mem     64kB        256kB       64MB
HEAD                                         40.286      41.312      41.312
patched                                      23.749      23.759      23.480
Compare with HEAD                 -41.04%     -42.48%    -43.16%


RESULT - bulk update (5kk)
---------------------------------------------------------------
logical_decoding_work_mem     64kB        256kB       64MB
HEAD                                         63.650      65.260      65.459
patched                                      46.692      46.275      48.281
Compare with HEAD                -26.64%     -29.09%    -26.24%


RESULT - rollback 10% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem     64kB        256kB       64MB
HEAD                                        33.386      33.213      31.990
patched                                      20.540      19.295      18.139
Compare with HEAD                 -38.47%     -41.90%    -43.29%


RESULT - rollback 20% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem     64kB        256kB       64MB
HEAD                                         32.150      31.871      30.825
patched                                      19.331      19.366      18.285
Compare with HEAD                -39.87%     -39.23%     -40.68%


RESULT - rollback 30% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                                      28.611      30.139      29.433
patched                                   19.632      19.838      18.374
Compare with HEAD               -31.38%     -34.17%      -37.57%


RESULT - rollback 50% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem   64kB        256kB       64MB
HEAD                                       27.410      27.167     25.990
patched                                    19.982      18.749     18.048
Compare with HEAD               -27.099%    -30.98%      -30.55%

(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:
Update shows 26-29% improvement, while insert and delete shows ~40% improvement.
In the case of rollback, the improvement is somewhat between 27-42%.
The improvement slightly decreases with larger amounts of data being
rolled back.


[1] https://www.postgresql.org/message-id/OSZPR01MB63103AA97349BBB858E27DEAFD499%40OSZPR01MB6310.jpnprd01.prod.outlook.com
[2] https://www.postgresql.org/message-id/OSZPR01MB6310174063C9144D2081F657FDE09%40OSZPR01MB6310.jpnprd01.prod.outlook.com

thanks
Shveta