Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYXf2-00CBCo-R0 for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Jul 2024 21:12:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sYXf1-003DzL-9j for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Jul 2024 21:12:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYXez-003DzC-3e for pgsql-hackers@lists.postgresql.org; Mon, 29 Jul 2024 21:12:02 +0000 Received: from fout7-smtp.messagingengine.com ([103.168.172.150]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYXeu-0026Bc-EX for pgsql-hackers@lists.postgresql.org; Mon, 29 Jul 2024 21:11:59 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailfout.nyi.internal (Postfix) with ESMTP id A4A56138018C; Mon, 29 Jul 2024 17:11:54 -0400 (EDT) Received: from imap49 ([10.202.2.99]) by compute4.internal (MEProxy); Mon, 29 Jul 2024 17:11:54 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1722287514; x=1722373914; bh=pPH8J025To RPJM3zwt55TQwankwebiaJLC7cwsPnj6w=; b=TiMq2B0+K00snrgeihkaJavfhz +oAzUWxLh0e4bmuuu/L1FG7NGEbwQ2idO0fd0KvkrFWb7QJZIrenTbEwAO1EaIEe rhynSyRnsk+dF7Wt49IpI/SSt+cDRY9qCqwwbhGesGzBVYhciavqCy1U+TQak0re 6AVdecMxnl4PHIrSnGu7FTj2rS/VuTU049GrqM0tllY3aHQ6k3TYqbnVLZYoT3// EoImK3GgYPL4Qr/E8+o8XwqSxPyWTfoztVt1skHEuciZD/zLavVZQOd8jLw6+uYS L/RuSVU3lcSJS4rJeP4NxBy/QQrt7mCoQljh0Kd52Q1K083pLBnE3WKvyEiw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1722287514; x=1722373914; bh=pPH8J025ToRPJM3zwt55TQwankwe biaJLC7cwsPnj6w=; b=DQOXRAUpi1uZKAdohYf3pXkCZD7Kb4hBHdyLfFBEMo+1 P0q+pxVToVkXL5HoHV0GOPD9FDZ3hjDoPyrWZpLXWUW8vPqUC+6KHhvG9AYd+Fub EPJE5zrY1O/GzugRocuCR5pu1TVBlx1vZdzHhgI/YV/ZZVCnv6rcE6kcO2Nol5sM nKsxq/B/uWC8XmSqbRYbnSYpEDlpgvvObDI/X4Bqkfl3y9WE440CenDRYLnFZMpJ S3J+aJQfkvuHpCQrQtxZ1P7x6RJfNyL3MfyLC6MzcQiVIDhgmI49oT6DfxdHBQT4 fNLdA1xriFS/33CFa5XQjeyRD7xBw/LV3hDvSlXYlw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeftddrjedvgdduiedtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepofgfggfkjghffffhvfevufgtsegrtderreerredtnecuhfhrohhmpedfgfhu lhgvrhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecugg ftrfgrthhtvghrnhephedvveeuleejkeefleeihfeffefgffegueefhfdvueegtddtgedv heeuvdfgudfgnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghdpvghnthgvrh hprhhishgvuggsrdgtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehm rghilhhfrhhomhepvghulhgvrhesvghulhgvrhhtohdrtghomhdpnhgspghrtghpthhtoh eptd X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 3B83E15A0092; Mon, 29 Jul 2024 17:11:54 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-582-g5a02f8850-fm-20240719.002-g5a02f885 MIME-Version: 1.0 Message-Id: In-Reply-To: References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@app.fastmail.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> <68de6498-0449-a113-dd03-e198dded0bac@gmail.com> Date: Mon, 29 Jul 2024 18:11:32 -0300 From: "Euler Taveira" To: "Amit Kapila" , "kuroda.hayato@fujitsu.com" Cc: "Alexander Lakhin" , "pgsql-hackers@lists.postgresql.org" , "houzj.fnst@fujitsu.com" Subject: Re: speed up a logical replica setup Content-Type: multipart/alternative; boundary=95928cde6e6a44af926cc76da205a43b List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --95928cde6e6a44af926cc76da205a43b Content-Type: text/plain 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/ --95928cde6e6a44af926cc76da205a43b Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Jul 17,= 2024, at 11:37 PM, Amit Kapila wrote:
I am thinking of transactions between restar= t_lsn and "consistent
point lsn" (aka the point after whic= h all commits will be replicated).
You conclusion seems co= rrect to me that such transactions won't be
replicated by = streaming replication and would be skipped by logical
repl= ication. 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 ava= ilable solutions. The recovery step was slow
if the server= is idle (that's the case for the test). An idle server
us= ually 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 LS= N is ahead of the
current WAL position and the recovery wa= its until something writes a
WAL record to reach the targe= t and ends the recovery.

Hayato already men= tioned 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_createsu= bscriber', 'pgoutput', true);
select pg_logical_emit_messa= ge(false, 'pg_createsubscriber', 'dummy');
select pg_log_s= tandby_snapshot();
select pg_create_restore_point('pg_crea= tesubscriber');

that results in the followi= ng output:

      s= lot_name      |    lsn &nbs= p;  
---------------------+-----------
=
pg_createsubscriber | 0/942DD28
(1 row)

Time: 200.571 ms
pg_logical_emit_message&n= bsp;
-------------------------
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):  &n= bsp;  50/    50, tx:     &n= bsp;    0, lsn: 0/0942DCF0, prev 0/0942DCB8, desc: RUNNIN= G_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 3939
rmgr: LogicalMessage len (rec/tot):     75/=     75, tx:       &nbs= p;  0, lsn: 0/0942DD28, prev 0/0942DCF0, desc: MESSAGE non-transact= ional, prefix "pg_createsubscriber"; payload (5 bytes): 64 75 6D 6D 79
rmgr: Standby     len (rec/tot): &= nbsp;   50/    50, tx:    &= nbsp;     0, lsn: 0/0942DD78, prev 0/0942DD28, desc:= RUNNING_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 393= 9
rmgr: XLOG        len= (rec/tot):     98/    98, tx: &= nbsp;        0, lsn: 0/0942DDB0, prev= 0/0942DD78, desc: RESTORE_POINT pg_createsubscriber

<= /div>
The options are:

(a) temporary re= plication slot: requires an additional replication slot.
s= mall payload. it is extremely slow in comparison with the other
options.
(b) logical message: can be consumed by log= ical replication when/if it
is supported some day. big pay= load. fast.
(c) snapshot of running txn:  small paylo= ad. fast.
(d) named restore point: biggest payload. fast.<= br>

I don't have a strong preference but if I n= eed to pick one I would
choose option (c) or option (d). T= he option (a) is out of the question.

Opini= ons?



--
Euler Ta= veira
EDB   https://www.enterprisedb.com/

--95928cde6e6a44af926cc76da205a43b--