Hi,
I did some performance tests for this patch, based on v59-0001 and v59-0002
patch.
This test used synchronous logical replication, and compared SQL execution times
before and after applying the patch.
Two cases are tested by varying logical_decoding_work_mem:
a) Bulk insert.
b) Rollback to savepoint. (Different percentage of changes in the transaction
are rolled back).
The test was performed ten times, and the average of the middle eight was taken.
The results are as follows. The bar charts are attached.
(The steps are the same as before.[1])
RESULT - bulk insert (5kk)
---------------------------------------------------------------
logical_decoding_work_mem 64kB 256kB 64MB
HEAD 51.655 51.694 51.262
patched 31.104 31.234 31.711
Compare with HEAD -39.79% -39.58% -38.14%
RESULT - rollback 10% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem 64kB 256kB 64MB
HEAD 43.908 43.358 42.874
patched 31.924 31.343 29.102
Compare with HEAD -27.29% -27.71% -32.12%
RESULT - rollback 20% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem 64kB 256kB 64MB
HEAD 40.561 40.599 40.015
patched 31.562 32.116 29.680
Compare with HEAD -22.19% -20.89% -25.83%
RESULT - rollback 30% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem 64kB 256kB 64MB
HEAD 38.092 37.756 37.142
patched 31.631 31.236 28.783
Compare with HEAD -16.96% -17.27% -22.50%
RESULT - rollback 50% (5kk)
---------------------------------------------------------------
logical_decoding_work_mem 64kB 256kB 64MB
HEAD 33.387 33.056 32.638
patched 31.272 31.279 29.876
Compare with HEAD -6.34% -5.38% -8.46%
(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 the case of bulk insert, it takes about 30% ~ 40% less time, which looks good
to me.
In the case of rollback to savepoint, the larger the amount of data rolled back,
the smaller the improvement compared to HEAD. But as such cases won't be often,
this should be okay.
[1] https://www.postgresql.org/message-id/OSZPR01MB63103AA97349BBB858E27DEAFD499%40OSZPR01MB6310.jpnprd01.prod.outlook.com
Regards,
Shi yu
