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

On Tue, Jan 3, 2023 at 11:10 AM wangw(dot)fnst(at)fujitsu(dot)com
<wangw(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Mon, Jan 2, 2023 at 18:54 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > On Fri, Dec 30, 2022 at 3:55 PM wangw(dot)fnst(at)fujitsu(dot)com
> > <wangw(dot)fnst(at)fujitsu(dot)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