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 1nRkp3-0000n8-5W for pgsql-hackers@arkaria.postgresql.org; Wed, 09 Mar 2022 01:09:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRkp1-0002Gh-Sh for pgsql-hackers@arkaria.postgresql.org; Wed, 09 Mar 2022 01:08:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRkp1-0002GY-2g for pgsql-hackers@lists.postgresql.org; Wed, 09 Mar 2022 01:08:59 +0000 Received: from smtp-fw-33001.amazon.com ([207.171.190.10]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRkox-0005SX-Mw for pgsql-hackers@lists.postgresql.org; Wed, 09 Mar 2022 01:08:58 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=amazon.com; i=@amazon.com; q=dns/txt; s=amazon201209; t=1646788136; x=1678324136; h=message-id:date:mime-version:to:cc:references:from: in-reply-to:subject; bh=4avbpX6+gssQJqkdZtNt1bZ6wiR4g/zR+t/WG1Hn1eQ=; b=BlB4qM51dL38GuCVFval8wG+Frk9h29jUrzgXjn9rEiBcYWGsjW6w6L+ zd4nqq26cumUHAw2vrcrhvzgtBnInmICOSeYQ0ocGqdjt67LKvHk4iSoA BW3RZPhLFKAS7H/1YKAd8DbnS1FduqvrFWZnogXoWCPIWfYvtGaYEtLpW 4=; X-IronPort-AV: E=Sophos;i="5.90,165,1643673600"; d="scan'208,217";a="180678793" Subject: Re: Allow async standbys wait for sync replication Received: from iad12-co-svc-p1-lb1-vlan2.amazon.com (HELO email-inbound-relay-pdx-2c-51ba86d8.us-west-2.amazon.com) ([10.43.8.2]) by smtp-border-fw-33001.sea14.amazon.com with ESMTP; 09 Mar 2022 01:08:52 +0000 Received: from EX13MTAUWC001.ant.amazon.com (pdx1-ws-svc-p6-lb9-vlan2.pdx.amazon.com [10.236.137.194]) by email-inbound-relay-pdx-2c-51ba86d8.us-west-2.amazon.com (Postfix) with ESMTPS id 4D5B293394; Wed, 9 Mar 2022 01:08:51 +0000 (UTC) Received: from EX13D11UWC001.ant.amazon.com (10.43.162.151) by EX13MTAUWC001.ant.amazon.com (10.43.162.135) with Microsoft SMTP Server (TLS) id 15.0.1497.28; Wed, 9 Mar 2022 01:08:51 +0000 Received: from [10.142.205.96] (10.43.160.100) by EX13D11UWC001.ant.amazon.com (10.43.162.151) with Microsoft SMTP Server (TLS) id 15.0.1497.28; Wed, 9 Mar 2022 01:08:50 +0000 Content-Type: multipart/alternative; boundary="------------nP0BGgP3ypHFmPVpFc5oqB6D" Message-ID: <8fc4fb4c-d429-4c17-b239-13a560bcd966@amazon.com> Date: Tue, 8 Mar 2022 17:08:49 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0) Gecko/20100101 Thunderbird/91.6.1 Content-Language: en-US To: Bharath Rupireddy , Andres Freund CC: Nathan Bossart , Kyotaro Horiguchi , SATYANARAYANA NARLAPURAM , PostgreSQL Hackers References: <20220228185732.GB944837@nathanxps13> <20220301060528.GA1026683@nathanxps13> <20220301.163431.1826638724406024793.horikyota.ntt@gmail.com> <20220301170537.GA1031413@nathanxps13> <20220301212700.GB1033258@nathanxps13> <20220304195602.GA1184024@nathanxps13> <20220305202752.4oll4hgztlgdfykl@alap3.anarazel.de> From: "Hsu, John" In-Reply-To: X-Originating-IP: [10.43.160.100] X-ClientProxiedBy: EX13D43UWC003.ant.amazon.com (10.43.162.16) To EX13D11UWC001.ant.amazon.com (10.43.162.151) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------nP0BGgP3ypHFmPVpFc5oqB6D Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 7bit Hi, On 3/5/22 10:57 PM, Bharath Rupireddy wrote: > On Sun, Mar 6, 2022 at 1:57 AM Andres Freund wrote: >> Hi, >> >> On 2022-03-05 14:14:54 +0530, Bharath Rupireddy wrote: >>> I understand. Even if we use the SyncRepWaitForLSN approach, the async >>> walsenders will have to do nothing in WalSndLoop() until the sync >>> walsender wakes them up via SyncRepWakeQueue. >> I still think we should flat out reject this approach. The proper way to >> implement this feature is to change the protocol so that WAL can be sent to >> replicas with an additional LSN informing them up to where WAL can be >> flushed. That way WAL is already sent when the sync replicas have acknowledged >> receipt and just an updated "flush/apply up to here" LSN has to be sent. > I was having this thought back of my mind. Please help me understand these: > 1) How will the async standbys ignore the WAL received but > not-yet-flushed by them in case the sync standbys don't acknowledge > flush LSN back to the primary for whatever reasons? > 2) When we say the async standbys will receive the WAL, will they just > keep the received WAL in the shared memory but not apply or will they > just write but not apply the WAL and flush the WAL to the pg_wal > directory on the disk or will they write to some other temp wal > directory until they receive go-ahead LSN from the primary? > 3) Won't the network transfer cost be wasted in case the sync standbys > don't acknowledge flush LSN back to the primary for whatever reasons? > > The proposed idea in this thread (async standbys waiting for flush LSN > from sync standbys before sending the WAL), although it makes async > standby slower in receiving the WAL, it doesn't have the above > problems and is simpler to implement IMO. Since this feature is going > to be optional with a GUC, users can enable it based on the needs. > I think another downside of the approach would be if the async-replica had a lot of changes that were unacknowledged and it were to be restarted for whatever reason we might need to recreate the replica, or run pg_rewind from it again which seems to be what we're trying to avoid. It also pushes the complexity to the client side for consumers who stream changes from logical slots which the current proposal seems to prevent. --------------nP0BGgP3ypHFmPVpFc5oqB6D Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: 8bit

Hi,

On 3/5/22 10:57 PM, Bharath Rupireddy wrote: 
On Sun, Mar 6, 2022 at 1:57 AM Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2022-03-05 14:14:54 +0530, Bharath Rupireddy wrote:

I understand. Even if we use the SyncRepWaitForLSN approach, the async
walsenders will have to do nothing in WalSndLoop() until the sync
walsender wakes them up via SyncRepWakeQueue.

I still think we should flat out reject this approach. The proper way to
implement this feature is to change the protocol so that WAL can be sent to
replicas with an additional LSN informing them up to where WAL can be
flushed. That way WAL is already sent when the sync replicas have acknowledged
receipt and just an updated "flush/apply up to here" LSN has to be sent.

I was having this thought back of my mind. Please help me understand these:
1) How will the async standbys ignore the WAL received but
not-yet-flushed by them in case the sync standbys don't acknowledge
flush LSN back to the primary for whatever reasons?
2) When we say the async standbys will receive the WAL, will they just
keep the received WAL in the shared memory but not apply or will they
just write but not apply the WAL and flush the WAL to the pg_wal
directory on the disk or will they write to some other temp wal
directory until they receive go-ahead LSN from the primary?
3) Won't the network transfer cost be wasted in case the sync standbys
don't acknowledge flush LSN back to the primary for whatever reasons?

The proposed idea in this thread (async standbys waiting for flush LSN
from sync standbys before sending the WAL), although it makes async
standby slower in receiving the WAL, it doesn't have the above
problems and is simpler to implement IMO. Since this feature is going
to be optional with a GUC, users can enable it based on the needs.
I think another downside of the approach would be if the async-replica 
had a lot of changes that were unacknowledged and it were to be 
restarted for whatever reason we might need to recreate the replica, or 
run pg_rewind from it again which seems to be what we're trying to avoid.

It also pushes the complexity to the client side for consumers who stream
changes from logical slots which the current proposal seems to prevent.
--------------nP0BGgP3ypHFmPVpFc5oqB6D--