Horton

RE: speed up a logical replica setup

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread
From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'Shubham Khanna' <khannashubham1197@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, Peter Eisentraut <peter@eisentraut.org>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Andres Freund <andres@anarazel.de>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: 2024-01-25T05:54:32Z
Lists: pgsql-hackers

Commits

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

  1. pg_createsubscriber: Remove obsolete comment

  2. pg_createsubscriber: Fix an unpredictable recovery wait time.

  3. Fix unstable test in 040_pg_createsubscriber.

  4. Fix the testcase introduced in commit 81d20fbf7a.

  5. Further weaken new pg_createsubscriber test on Windows.

  6. Temporarily(?) weaken new pg_createsubscriber test on Windows.

  7. Make pg_createsubscriber warn if publisher has two-phase commit enabled.

  8. Make pg_createsubscriber more wary about quoting connection parameters.

  9. pg_createsubscriber: Remove failover replication slots on subscriber

  10. pg_createsubscriber: Remove replication slot check on primary

  11. pg_createsubscriber: Only --recovery-timeout controls the end of recovery process

  12. pg_createsubscriber: creates a new logical replica from a standby server

  13. Add some const decorations

  14. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  15. Remove MSVC scripts

Attachments

Dear hackers,

Based on the requirement, I have profiled the performance test. It showed bottlenecks
are in small-data case are mainly two - starting a server and waiting until the
recovery is done.

# Tested source code

V7 patch set was applied atop HEAD(0eb23285). No configure options were specified
when it was built.

# Tested workload

I focused on only 100MB/1GB cases because bigger ones have already had good performance.
(Number of inserted tuples were same as previous tests)
I used bash script instead of tap test framework. See attached. Executed SQLs and
operations were almost the same.

As you can see, I tested only one-db case. Results may be changed if the number
of databases were changed.

# Measurement
Some debug logs which output current time were added (please see diff file).
I picked up some events and done at before/after them. Below bullets showed the measured ones:

* Starting a server
* Stopping a server
* Creating replication slots
* Creating publications
* Waiting until the recovery ended
* Creating subscriptions

# Result 
Below table shows the elapsed time for these events. Raw data is also available
by the attached excel file.

|Event category                  |100MB case [sec]|1GB [sec]|
|Starting a server               |1.414           |1.417    |
|Stoping a server                |0.506           |0.506    |
|Creating replication slots      |0.005           |0.007    |
|Creating publications           |0.001           |0.002    |
|Waiting until the recovery ended|1.603           |14.529   |
|Creating subscriptions          |0.012           |0.012    |
|Total                           |3.541           |16.473   |
|actual time                     |4.37            |17.271   |


As you can see, starting servers and waiting seem slow. We cannot omit these,
but setting smaller shared_buffers will reduce the start time. One approach is
to overwrite the GUC to smaller value, but I think we cannot determine the
appropriate value.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED