Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mSGMq-0000e5-1f for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Sep 2021 10:17:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mSGMn-0001kr-Sa for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Sep 2021 10:17:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mSGMn-0001k9-Gp for pgsql-hackers@lists.postgresql.org; Mon, 20 Sep 2021 10:17:41 +0000 Received: from smtp-fw-9102.amazon.com ([207.171.184.29]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mSGMg-0006hS-35 for pgsql-hackers@postgresql.org; Mon, 20 Sep 2021 10:17:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=amazon.com; i=@amazon.com; q=dns/txt; s=amazon201209; t=1632133055; x=1663669055; h=subject:to:cc:references:from:message-id:date: mime-version:in-reply-to; bh=OB7yn1ngPS8ImbMeu+q/Bo488AUs3iAxDTVa6Voc9vg=; b=Sxf2X5ZEi61MeQsMdQ3fjtXYw6H1AZruvBIbmckFizQJ4YqAULNGIczK F44NBtRr8Ytl+LlDXn+R6Wcj1kZupFaJ/SSAh4S03CldAIbbpuXCPQsz5 ul9B8Yk7VhcYA3OnBX4OF+fDRlQ3yKKNfZHYkerqF/JyHHriaC5yxMIm7 I=; X-IronPort-AV: E=Sophos;i="5.85,308,1624320000"; d="scan'208,217";a="161030961" Received: from pdx4-co-svc-p1-lb2-vlan3.amazon.com (HELO email-inbound-relay-iad-1e-ccb3efe0.us-east-1.amazon.com) ([10.25.36.214]) by smtp-border-fw-9102.sea19.amazon.com with ESMTP; 20 Sep 2021 10:17:23 +0000 Received: from EX13D03EUC003.ant.amazon.com (iad12-ws-svc-p26-lb9-vlan3.iad.amazon.com [10.40.163.38]) by email-inbound-relay-iad-1e-ccb3efe0.us-east-1.amazon.com (Postfix) with ESMTPS id 0AF7FC08AF; Mon, 20 Sep 2021 10:17:20 +0000 (UTC) Received: from 38f9d36c4abb.ant.amazon.com (10.43.160.106) by EX13D03EUC003.ant.amazon.com (10.43.164.192) with Microsoft SMTP Server (TLS) id 15.0.1497.23; Mon, 20 Sep 2021 10:17:16 +0000 Subject: Re: Minimal logical decoding on standbys To: CC: Alvaro Herrera , Andres Freund , Ibrar Ahmed , Amit Khandekar , tushar , "[pgdg] Robert Haas" , Rahila Syed , pgsql-hackers References: <202107281526.o74ieuj6sj7z@alvherre.pgsql> <2b14d595-1e8b-e5b6-6098-6a11d19fa1c7@amazon.com> From: "Drouvot, Bertrand" Message-ID: Date: Mon, 20 Sep 2021 12:17:11 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6316386C04B892D7211598FD" Content-Language: en-US X-Originating-IP: [10.43.160.106] X-ClientProxiedBy: EX13D48UWB004.ant.amazon.com (10.43.163.74) To EX13D03EUC003.ant.amazon.com (10.43.164.192) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------6316386C04B892D7211598FD Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit Hi, On 9/17/21 10:32 PM, Fabrízio de Royes Mello wrote: > > On Wed, Sep 15, 2021 at 8:36 AM Drouvot, Bertrand > wrote: > > > > Another rebase attached. > > > > The patch proposal to address Andre's walsender corner cases is > still a dedicated commit (as i think it may be easier to discuss). > > > > Did one more battery of tests and everything went well... Thanks for looking at it! > > But doing some manually tests: > > 1. Setup master/replica (wal_level=logical, hot_standby_feedback=on, etc) > 2. Initialize the master instance: "pgbench -i -s10 on master" > 3. Terminal1: execute "pgbench -c20 -T 2000" > 4. Terminal2: create the logical replication slot: > > 271480 (replica) fabrizio=# select * from > pg_create_logical_replication_slot('test_logical', 'test_decoding'); > -[ RECORD 1 ]----------- > slot_name | test_logical > lsn       | 1/C7C59E0 > > Time: 37658.725 ms (00:37.659) > > > Even with activity on primary the creation of the logical replication > slot took ~38s. Can we do something related to it or should we need to > clarify even more the documentation? > For the logical slot creation on the standby, as we can not do WAL writes, we have to wait for xl_running_xact to be logged on the primary and be replayed on the standby. So we are somehow dependent on the checkpoints on the primary and LOG_SNAPSHOT_INTERVAL_MS. If we want to get rid of this, what i could think of is the standby having to ask the primary to log a standby snapshot (until we get one we are happy with). Or, we may just want to mention in the doc: +     For a logical slot to be created, it builds a historic snapshot, for which +     information of all the currently running transactions is essential. On +     primary, this information is available, but on standby, this information +     has to be obtained from primary. So, creating a logical slot on standby +     may take a noticeable time. Instead of: +     For a logical slot to be created, it builds a historic snapshot, for which +     information of all the currently running transactions is essential. On +     primary, this information is available, but on standby, this information +     has to be obtained from primary. So, slot creation may wait for some +     activity to happen on the primary. If the primary is idle, creating a +     logical slot on standby may take a noticeable time. What do you think? Thanks Bertrand --------------6316386C04B892D7211598FD Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 8bit

Hi,

On 9/17/21 10:32 PM, Fabrízio de Royes Mello wrote:

On Wed, Sep 15, 2021 at 8:36 AM Drouvot, Bertrand <bdrouvot@amazon.com> wrote:
>
> Another rebase attached.
>
> The patch proposal to address Andre's walsender corner cases is still a dedicated commit (as i think it may be easier to discuss).
>

Did one more battery of tests and everything went well...

Thanks for looking at it!


But doing some manually tests:

1. Setup master/replica (wal_level=logical, hot_standby_feedback=on, etc)
2. Initialize the master instance: "pgbench -i -s10 on master"
3. Terminal1: execute "pgbench -c20 -T 2000"
4. Terminal2: create the logical replication slot:

271480 (replica) fabrizio=# select * from pg_create_logical_replication_slot('test_logical', 'test_decoding');
-[ RECORD 1 ]-----------
slot_name | test_logical
lsn       | 1/C7C59E0

Time: 37658.725 ms (00:37.659)


Even with activity on primary the creation of the logical replication slot took ~38s. Can we do something related to it or should we need to clarify even more the documentation?

For the logical slot creation on the standby, as we can not do WAL writes, we have to wait for xl_running_xact to be logged on the primary and be replayed on the standby.

So we are somehow dependent on the checkpoints on the primary and LOG_SNAPSHOT_INTERVAL_MS.

If we want to get rid of this, what i could think of is the standby having to ask the primary to log a standby snapshot (until we get one we are happy with).

Or, we may just want to mention in the doc:

+     For a logical slot to be created, it builds a historic snapshot, for which
+     information of all the currently running transactions is essential. On
+     primary, this information is available, but on standby, this information
+     has to be obtained from primary. So, creating a logical slot on standby
+     may take a noticeable time.

Instead of:

+     For a logical slot to be created, it builds a historic snapshot, for which
+     information of all the currently running transactions is essential. On
+     primary, this information is available, but on standby, this information
+     has to be obtained from primary. So, slot creation may wait for some
+     activity to happen on the primary. If the primary is idle, creating a
+     logical slot on standby may take a noticeable time.

What do you think?

Thanks

Bertrand

--------------6316386C04B892D7211598FD--