Horton

Re: speed up a logical replica setup

Euler Taveira <euler@eulerto.com>

View thread
From: "Euler Taveira" <euler@eulerto.com>
To: "Amit Kapila" <amit.kapila16@gmail.com>, "kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com>
Cc: "Alexander Lakhin" <exclusion@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Date: 2024-07-29T21:11: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

On Wed, Jul 17, 2024, at 11:37 PM, Amit Kapila wrote:
> I am thinking of transactions between restart_lsn and "consistent
> point lsn" (aka the point after which all commits will be replicated).
> You conclusion seems correct to me that such transactions won't be
> replicated by streaming replication and would be skipped by logical
> replication. Now, if we can avoid that anyway, we can consider that.

Under reflection what I proposed [1] seems more complex and possibly
error prone than other available solutions. The recovery step was slow
if the server is idle (that's the case for the test). An idle server
usually doesn't have another WAL record after creating the replication
slots. Since pg_createsubscriber is using the LSN returned by the last
replication slot as recovery_target_lsn, this LSN is ahead of the
current WAL position and the recovery waits until something writes a
WAL record to reach the target and ends the recovery.

Hayato already mentioned one of the solution in a previous email [2].
AFAICS we can use any solution that creates a WAL record. I tested the
following options: 

\timing
select * from pg_create_logical_replication_slot('pg_createsubscriber', 'pgoutput', true);
select pg_logical_emit_message(false, 'pg_createsubscriber', 'dummy');
select pg_log_standby_snapshot();
select pg_create_restore_point('pg_createsubscriber');

that results in the following output:

      slot_name      |    lsn    
---------------------+-----------
pg_createsubscriber | 0/942DD28
(1 row)

Time: 200.571 ms
pg_logical_emit_message 
-------------------------
0/942DD78
(1 row)

Time: 0.938 ms
pg_log_standby_snapshot 
-------------------------
0/942DDB0
(1 row)

Time: 0.741 ms
pg_create_restore_point 
-------------------------
0/942DE18
(1 row)

Time: 0.870 ms

and generates the following WAL records:

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0942DCF0, prev 0/0942DCB8, desc: RUNNING_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 3939
rmgr: LogicalMessage len (rec/tot):     75/    75, tx:          0, lsn: 0/0942DD28, prev 0/0942DCF0, desc: MESSAGE non-transactional, prefix "pg_createsubscriber"; payload (5 bytes): 64 75 6D 6D 79
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0942DD78, prev 0/0942DD28, desc: RUNNING_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 3939
rmgr: XLOG        len (rec/tot):     98/    98, tx:          0, lsn: 0/0942DDB0, prev 0/0942DD78, desc: RESTORE_POINT pg_createsubscriber

The options are:

(a) temporary replication slot: requires an additional replication slot.
small payload. it is extremely slow in comparison with the other
options.
(b) logical message: can be consumed by logical replication when/if it
is supported some day. big payload. fast.
(c) snapshot of running txn:  small payload. fast.
(d) named restore point: biggest payload. fast.

I don't have a strong preference but if I need to pick one I would
choose option (c) or option (d). The option (a) is out of the question.

Opinions?

[1] https://www.postgresql.org/message-id/b1f0f8c7-8f01-4950-af77-339df3dc4684%40app.fastmail.com
[2] https://www.postgresql.org/message-id/OSBPR01MB25521B15BF950D2523BBE143F5D32%40OSBPR01MB2552.jpnprd01.prod.outlook.com


--
Euler Taveira
EDB   https://www.enterprisedb.com/