pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Euler Taveira <euler@eulerto.com>
To: Amit Kapila <amit.kapila16@gmail.com>
To: kuroda.hayato@fujitsu.com <kuroda.hayato@fujitsu.com>
Cc: Alexander Lakhin <exclusion@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com>
Subject: Re: speed up a logical replica setup
Date: Mon, 29 Jul 2024 18:11:32 -0300
Message-ID: <c3ea9757-50bf-43d9-8767-3ee2f8db82d6@app.fastmail.com> (raw )
In-Reply-To: <CAA4eK1LSqM3tF_TgEr-PsztGL6hLJTcojwBTycTnpa7ucjfKjQ@mail.gmail.com >
References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@app.fastmail.com >
<CAA4eK1JJq_ER6Kq_H=jKHR75QPRd8y9_D=RtYw=aPYKMfqLi9A@mail.gmail.com >
<CAA4eK1KdCb+5sjYu6qCMXXdCX1y_ihr8kFzMozq0=P=auYxgog@mail.gmail.com >
<CANhcyEV6q1Vhd37i1axUeScLi0UAGVxta1LDa0BV0Eh--TcPMg@mail.gmail.com >
<CAA4eK1JaQcAZ=n513-4VDW4ujE9T9f007fczNCpVDbg1smhT4g@mail.gmail.com >
<5da4b286-e570-4341-8262-92d763f6b1a4@app.fastmail.com >
<776c5cac-5ef5-4001-b1bc-5b698bc0c62a@app.fastmail.com >
<3c024250-3f13-4f4d-b8e9-55f176db4abf@eisentraut.org >
<0dffca12-bf17-4a7a-334d-225569de5e6e@gmail.com >
<OSBPR01MB25528300C71FDD83EA1DCA12F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com >
<CAA4eK1+Qmc34cooSNm2=U6YsySSjZTn2_eD_deDFEAZv+aj-AA@mail.gmail.com >
<OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com >
<CAA4eK1J9Vn1HB7sXNx-cH4COBd_moGZtrZd82Z160mK6u0HspQ@mail.gmail.com >
<bde6ac67-69cc-c104-5ab6-dd4f5deadf24@gmail.com >
<CAA4eK1KG7+jUdONhsELY2poyBrmesNnkt-m5QSgPhgyyM6cFHQ@mail.gmail.com >
<68de6498-0449-a113-dd03-e198dded0bac@gmail.com >
<b1f0f8c7-8f01-4950-af77-339df3dc4684@app.fastmail.com >
<TYAPR01MB5692D87C0E1FD86713525F60F5A32@TYAPR01MB5692.jpnprd01.prod.outlook.com >
<CAA4eK1J5t48z2mfpNdCT8OunECR7qJZmHUiDnrdunU+z83u_oA@mail.gmail.com >
<TYCPR01MB5693BC8F357F243196E8DE41F5A32@TYCPR01MB5693.jpnprd01.prod.outlook.com >
<CAA4eK1LSqM3tF_TgEr-PsztGL6hLJTcojwBTycTnpa7ucjfKjQ@mail.gmail.com >
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.jpnprd0...
--
Euler Taveira
EDB https://www.enterprisedb.com/
view thread (320+ messages) latest in thread
Message-ID: <c3ea9757-50bf-43d9-8767-3ee2f8db82d6@app.fastmail.com>
Permalink: ../c3ea9757-50bf-43d9-8767-3ee2f8db82d6@app.fastmail.com/
Also on: postgresql.org/message-id/c3ea9757-50bf-43d9-8767-3ee2f8db82d6@app.fastmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: euler@eulerto.com, amit.kapila16@gmail.com, kuroda.hayato@fujitsu.com, exclusion@gmail.com, pgsql-hackers@lists.postgresql.org, houzj.fnst@fujitsu.com
Subject: Re: speed up a logical replica setup
In-Reply-To: <c3ea9757-50bf-43d9-8767-3ee2f8db82d6@app.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox