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 1neyUD-00034M-5Q for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Apr 2022 12:22:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1neyUC-0000Z4-1o for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Apr 2022 12:22:08 +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 1neyU9-0000Xm-FR for pgsql-hackers@lists.postgresql.org; Thu, 14 Apr 2022 12:22:07 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1neyU6-0002VP-69 for pgsql-hackers@lists.postgresql.org; Thu, 14 Apr 2022 12:22:04 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id D9A635C0232; Thu, 14 Apr 2022 08:22:00 -0400 (EDT) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Thu, 14 Apr 2022 08:22:00 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm1; t=1649938920; x=1650025320; bh=y/nSXUmd1n 900by2Yc5Zn7qRf+bJJfEEkbHDW86EL/Y=; b=HyBKQG0e1JOfr82LsLv8MIP1oz EvzcXnwDuFEllPs9rPy0edwJxbgAmS3ZstyMqAK/HIgQtbbyZe3u1r4CxDvddLHi xn+NziQH9qKo488PeQhTq9j9Ipmr/5FgkdiJP8y2fs69uw467+TRI4xc92gLlgZL tgi80RiMIMQSiDBHprYR3o1JlDCzc1OwWrSO1NdQ/L/xtfVDeRRXh1sZaTE7H6qB fqDFyttzT8olcdqJufnzPui0nZcdQnt37OPJlRVK5xIafbNhRjM5GwyQ6TgAqLgH ZCgTfb4Ck9G66L5+fI9DFPz6Bc+KYgvXdk5hQNqFPJ8NlcPJQBEb7Tw7eclA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1649938920; x= 1650025320; bh=y/nSXUmd1n900by2Yc5Zn7qRf+bJJfEEkbHDW86EL/Y=; b=W aVq3wxHr829jD9fAVLlgsVedOckHvsEU1f5vAvuuw3wtkRk9t8Qb3KIyLt4wiXbG itnhlxQLF2OuSkMW/guRXYOgi2mma+ufHFWfFYMRbLnajaXEcKoLEycoCxkOxN92 9dPdoveOi8T1e1i+dsdPW9lO2JWIXQ+3RlMsE1j4S8DWWfEg33mD78RACWLw905K tlR41S2n9EgbBzQE6H0x0rqstE9rcG4cgaTO8NK1vjVRErksj37IQMV/pNVkXsRf 0QGolBLOw823/nVhTn+OV4xHkTDP8pMVlL8/KzrJfvCFzZojUA4DyTIGCl9Fr8UG LCL4MZ1c88lbpCBNsTDlw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvvddrudelfedgheduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepofgfggfkjghffffhvffutgesrgdtreerreertdenucfhrhhomhepfdfguhhl vghrucfvrghvvghirhgrfdcuoegvuhhlvghrsegvuhhlvghrthhordgtohhmqeenucggtf frrghtthgvrhhnpeejtefhuedvgffftdetvedufedtjeeuffekleevkefhveekgeelfefh jeehtdejgeenucffohhmrghinhepvghnthgvrhhprhhishgvuggsrdgtohhmnecuvehluh hsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomhepvghulhgvrhesvghu lhgvrhhtohdrtghomh X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id 3493215A0069; Thu, 14 Apr 2022 08:22:00 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.7.0-alpha0-387-g7ea99c4045-fm-20220413.002-g7ea99c40 Mime-Version: 1.0 Message-Id: In-Reply-To: References: <0271e197-2cfe-4627-9c11-47535cc7fdf3@www.fastmail.com> <8cc4f31f-240a-4590-94a8-a50bdf4f4d1c@www.fastmail.com> Date: Thu, 14 Apr 2022 09:21:27 -0300 From: "Euler Taveira" To: "Amit Kapila" , "wangw.fnst@fujitsu.com" Cc: "Masahiko Sawada" , "kuroda.hayato@fujitsu.com" , "Peter Smith" , "Fabrice Chapuis" , "Simon Riggs" , "Petr Jelinek" , "tanghy.fnst@fujitsu.com" , "PostgreSQL Hackers" , "Ajin Cherian" Subject: Re: Logical replication timeout problem Content-Type: multipart/alternative; boundary=91da60841d1c4a2aa7f345a272ef42d7 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --91da60841d1c4a2aa7f345a272ef42d7 Content-Type: text/plain On Wed, Apr 13, 2022, at 7:45 AM, Amit Kapila wrote: > On Mon, Apr 11, 2022 at 12:09 PM wangw.fnst@fujitsu.com > wrote: > > > > So I skip tracking lag during a transaction just like the current HEAD. > > Attach the new patch. > > > > Thanks, please find the updated patch where I have slightly modified > the comments. > > Sawada-San, Euler, do you have any opinion on this approach? I > personally still prefer the approach implemented in v10 [1] especially > due to the latest finding by Wang-San that we can't update the > lag-tracker apart from when it is invoked at the transaction end. > However, I am fine if we like this approach more. It seems v15 is simpler and less error prone than v10. v10 has a mix of OutputPluginUpdateProgress() and the new function update_progress(). The v10 also calls update_progress() for every change action in pgoutput_change(). It is not a good approach for maintainability -- new changes like sequences need extra calls. However, as you mentioned there should handle the track lag case. Both patches change the OutputPluginUpdateProgress() so it cannot be backpatched. Are you planning to backpatch it? If so, the boolean variable (last_write or end_xacts depending of which version you are considering) could be added to LogicalDecodingContext. (You should probably consider this approach for skipped_xact too) + * For a large transaction, if we don't send any change to the downstream for a + * long time then it can timeout. This can happen when all or most of the + * changes are either not published or got filtered out. We should probable mention that "long time" is wal_receiver_timeout on subscriber. + * change as that can have overhead. Testing reveals that there is no + * noticeable overhead in doing it after continuously processing 100 or so + * changes. Tests revealed that ... + * We don't have a mechanism to get the ack for any LSN other than end xact + * lsn from the downstream. So, we track lag only for end xact lsn's. s/lsn/LSN/ and s/lsn's/LSNs/ I would say "end of transaction LSN". + * If too many changes are processed then try to send a keepalive message to + * receiver to avoid timeouts. In logical replication, if too many changes are processed then try to send a keepalive message. It might avoid a timeout in the subscriber. Does this same issue occur for long transactions? I mean keep a long transaction open and execute thousands of transactions. BEGIN; INSERT INTO foo (a) VALUES(1); -- wait a few hours while executing 10^x transactions INSERT INTO foo (a) VALUES(2); COMMIT; -- Euler Taveira EDB https://www.enterprisedb.com/ --91da60841d1c4a2aa7f345a272ef42d7 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Ap= r 13, 2022, at 7:45 AM, Amit Kapila wrote:
On Mon, Apr 11, 2022 at 12:09 PM wangw.fnst@fujitsu.com
>
> So I skip tra= cking lag during a transaction just like the current HEAD.
> Attach the new patch.
>

<= div>Thanks, please find the updated patch where I have slightly modified=
the comments.

Sawada-San, Eu= ler, do you have any opinion on this approach? I
personall= y still prefer the approach implemented in v10 [1] especially
<= div>due to the latest finding by Wang-San that we can't update the
lag-tracker apart from when it is invoked at the transaction en= d.
However, I am fine if we like this approach more.
It seems v15 is simpler and less error prone than = v10. v10 has a mix of
OutputPluginUpdateProgress() and the= new function update_progress(). The v10
also calls update= _progress() for every change action in pgoutput_change(). It
is not a good approach for maintainability -- new changes like sequen= ces need
extra calls. However, as you mentioned there shou= ld handle the track lag case.

Both patches = change the OutputPluginUpdateProgress() so it cannot be
ba= ckpatched. Are you planning to backpatch it? If so, the boolean variable=
(last_write or end_xacts depending of which version you a= re considering) could
be added to LogicalDecodingContext. = (You should probably consider this approach
for skipped_xa= ct too)

+ * For a large transaction, if we = don't send any change to the downstream for a
+ * long tim= e then it can timeout. This can happen when all or most of the
=
+ * changes are either not published or got filtered out.
=

We should probable mention that "long time" is wal_r= eceiver_timeout on
subscriber.

+    * change as that can have overhead. Testing reveal= s that there is no
+    * noticeable overhe= ad in doing it after continuously processing 100 or so
+&n= bsp;   * changes.

Tests revealed = that ...

+    * We don't hav= e a mechanism to get the ack for any LSN other than end xact
+    * lsn from the downstream. So, we track lag only = for end xact lsn's.

s/lsn/LSN/ and s/lsn's/= LSNs/

I would say "end of transaction LSN".=

+ * If too many changes are processed then= try to send a keepalive message to
+ * receiver to avoid = timeouts.

In logical replication, if too ma= ny changes are processed then try to send a
keepalive mess= age. It might avoid a timeout in the subscriber.

Does this same issue occur for long transactions? I mean keep a lo= ng
transaction open and execute thousands of transactions.=

BEGIN;
INSERT INTO foo (a) V= ALUES(1);
-- wait a few hours while executing 10^x transac= tions
INSERT INTO foo (a) VALUES(2);
COMMIT;=


--
Euler Taveira
=
EDB   https://www.enterprisedb.com/
<= br>
--91da60841d1c4a2aa7f345a272ef42d7--