Horton
Menu

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

Yu Shi (Fujitsu) <shiy.fnst@fujitsu.com>

View thread
From: "shiy.fnst@fujitsu.com" <shiy.fnst@fujitsu.com>
To: "wangw.fnst@fujitsu.com" <wangw.fnst@fujitsu.com>, Amit Kapila <amit.kapila16@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Cc: Peter Smith <smithpb2250@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-09-15T06:03:13Z
Lists: pgsql-hackers

Attachments

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix invalid memory access during the shutdown of the parallel apply worker.

  2. Fix assertion failure in apply worker.

  3. Use elog to report unexpected action in handle_streamed_transaction().

  4. Use appropriate wait event when sending data in the apply worker.

  5. Allow the logical_replication_mode to be used on the subscriber.

  6. Rename GUC logical_decoding_mode to logical_replication_mode.

  7. Display the leader apply worker's PID for parallel apply workers.

  8. Improve the code to decide and process the apply action.

  9. Document the newly added wait events added by commit 216a784829.

  10. Perform apply of large transactions by parallel workers.

  11. Wake up a subscription's replication worker processes after DDL.

  12. Add copyright notices to meson files

  13. Better document logical replication parameters

  14. Add a common function to generate the origin name.

  15. Harmonize parameter names in storage and AM code.

  16. Avoid using list_length() to test for empty list.

  17. Improve two comments related to a boolean DefElem's value

  18. Fix partition table's REPLICA IDENTITY checking on the subscriber.

  19. Fix data inconsistency between publisher and subscriber.

  20. Fix cache look-up failures while applying changes in logical replication.