On Tue, Jan 3, 2023 at 11:10 AM wangw.f...@fujitsu.com <wangw.f...@fujitsu.com> wrote: > > On Mon, Jan 2, 2023 at 18:54 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Fri, Dec 30, 2022 at 3:55 PM wangw.f...@fujitsu.com > > <wangw.f...@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
<<attachment: scripts.zip>>