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 1sC33s-002rgs-QQ for pgsql-general@arkaria.postgresql.org; Tue, 28 May 2024 20:04:46 +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 1sC33r-00BfOo-QK for pgsql-general@arkaria.postgresql.org; Tue, 28 May 2024 20:04:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sC33r-00BfOf-EK for pgsql-general@lists.postgresql.org; Tue, 28 May 2024 20:04:43 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sC33o-001E75-SX for pgsql-general@lists.postgresql.org; Tue, 28 May 2024 20:04:42 +0000 Received: by mail-qt1-x832.google.com with SMTP id d75a77b69052e-43fb909d45cso5719551cf.3 for ; Tue, 28 May 2024 13:04:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716926679; x=1717531479; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ljplIjy2zj6j+nOr4GbHV41hxk3U2sGKs6bhb0sR6V8=; b=EDW8OYi/LUaAM95Ne3QSS4lINvPk9ijjWmge0j6fM/4O9fo25qO5eyO/1dcEKzsqZk 2z8bFe4rjkOiViQbE/m6XGdN3nrXU+o7ynHh164ab1VRP6QN30fEAhRJWBUWkod907Ec LGhsZnn0fUZgliwSOYX+r4JxhwSs2NBSUZbtgryKjcJ2IgiJQPSHX/h335tOQ3k1gjt6 8p/Ki9FiJ+4aXpHMljoaKYsDRZddj9KiYBBomR3BC2NyUiq6abXA3evST73ER00hpxbm bVhEJsT5jC8IurfMevwHVLpIiVmxBhY43d3/pC08H7L++uisKS4yrxmK0tmNtvngXHbq polg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716926679; x=1717531479; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ljplIjy2zj6j+nOr4GbHV41hxk3U2sGKs6bhb0sR6V8=; b=OXjQppzikcLAaIM0eHvIRgNLeHjwrLJHPiogy8DAu/ZFRUNlx1qDKBHcwv86SqxwHC KSoB2DtYpyfD8nX81pGVG6z8PXIkYVHKTCtpTZAQFFU5UHPTs0epGtfO19npEkZVpglb T5/gqkR2xHzw6O8aYeaZd67lS+US4Az2kIZSmonidXtZORo17SrMKtHvtVAQsl1RQG2s TLyzGz+oAe7VTT0udxekTE5vzSY6Gch8CMlWHx4SH1mfKXRPtiPUjTK2qcIAfEks/hdC SP+P78Zpp/QKKZN+JsS4Plg1jZiO/EdLaxW9tdG5aIyuESnELgoTb1SdqMiBo1BR5a3b y/1w== X-Forwarded-Encrypted: i=1; AJvYcCWXeLorJ8Rb56ETP1hzKGuWirJENzOxNxMdkYyXRdgET4czZHe1EmJoiX937Iwe6/jrt8+GBG8Sc9GSmU18ir7Ggbqw0bSWVbUjZClwoZcgLpt7 X-Gm-Message-State: AOJu0YyxGGhcDhqFrfkupC48L9ZafwkOg2f1x4Nf8yFKIcxRUFd/vS6E ecyprDVAv0lpRBL+PYMzR+3XpQs+F/48C4uA2j+TFsQqs2e45oZ8+OEjhfmFUCgKySmvhJ99+bY cxo/upwAgY+BdkOTFC81bm6+zDhA= X-Google-Smtp-Source: AGHT+IGMpS/6bWfapz4X7RvM0TPbEujSo38QPl7O0aJmHcI5KI/6WI03MhfTd/n1rw0YYcK7kRh2ocYKmAq98HJBfVs= X-Received: by 2002:ad4:5946:0:b0:6ad:7742:4898 with SMTP id 6a1803df08f44-6ad7742496fmr100963076d6.56.1716926679331; Tue, 28 May 2024 13:04:39 -0700 (PDT) MIME-Version: 1.0 References: <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at> In-Reply-To: From: yudhi s Date: Wed, 29 May 2024 01:34:28 +0530 Message-ID: Subject: Re: Long running query causing XID limit breach To: Laurenz Albe Cc: sud , pgsql-general Content-Type: multipart/alternative; boundary="000000000000d3b8f00619892430" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d3b8f00619892430 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, May 27, 2024 at 2:50=E2=80=AFPM Laurenz Albe wrote: > > > > But again for HA , in case primary down we should not be in big lag > for the standby > > > and thus we want the standby also with minimal lag. And as you > mentioned there will > > > never be incorrect results but at amx it will be query cancellation, > so I was thinking, > > > if it's fine to just keep the "hot_feedback_standby" as OFF and let t= he > > > max_standby_streaming_delay set as it is like 14 sec. Let me know you= r > thoughts. > > > You cannot have it. > Let me repeat: you cannot have it. > > The only way you can have no delay in replication AND no canceled queries > is > if you use two different standby servers with different settings for > "max_standby_streaming_delay". One of the server is for HA, the other fo= r > your long-running queries. > > When you suggest having different max_standby_streaming_delay for first replica (say 10 sec for High availability) and second replica(say -1 for long running queries). Do you also suggest keeping "hot_feedback_standby" as "OFF" for all the three instances i.e. master and both the replicas? Also OP has added a few other parameters as below, do you think these should be needed? I think the master and first replica should have the same set up because in case of any disaster to master the first replica should be able to take the place of master. Master/Primary First Replica/Standby for High Availability Second Replica for Reporting hot_standby_feedback=3DON hot_standby_feedback=3DON hot_standby_feedback=3D= OFF max_standby_streaming_delay=3D10 sec max_standby_streaming_delay=3D10 sec max_standby_streaming_delay=3D-1 (Infinite) statement_timeout =3D "2hrs" statement_timeout=3D"2hrs" No statement_timeou= t i.e. infinite idle_in_transaction_session_timeout=3D10minutes idle_in_transaction_session_timeout=3D10minutes No idle_in_transaction_session_timeout i.e. infinite autovacuum_freeze_max_age=3D100M autovacuum_freeze_max_age=3D100M autovacuum_freeze_max_age=3D100M Log_autovacuum_min_duration=3D0 Log_autovacuum_min_duration=3D0 Log_autovacuum_min_duration=3D0 --000000000000d3b8f00619892430 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, May 27, 2024 at 2:50=E2=80=AFPM L= aurenz Albe <laurenz.albe@cy= bertec.at> wrote:

> > But again for HA , in case primary down we should not be in big l= ag for the standby
> > and thus we want the standby also with minimal lag. And as you me= ntioned there will
> > never be incorrect results but at amx it will be query cancellati= on, so I was thinking,
> > if it's=C2=A0fine to just keep the "hot_feedback_standby= " as OFF and let the
> > max_standby_streaming_delay set as it is like 14 sec. Let me know= your thoughts.
>
You cannot have it.
Let me repeat: you cannot have it.

The only way you can have no delay in replication AND no canceled queries i= s
if you use two different standby servers with different settings for
"max_standby_streaming_delay".=C2=A0 One of the server is for HA,= the other for
your long-running queries.

When you suggest having= different max_standby_streaming_delay for first replica (say 10 sec for Hi= gh availability) and second replica(say -1 for long running queries). Do yo= u also suggest=C2=A0 keeping "hot_feedback_standby" as "OFF&= quot; for all the three instances i.e. master and both the replicas?=C2=A0<= /div>

Also OP has added a few other parameters as below,= do you think these should be needed?=C2=A0
=C2=A0I think the mas= ter and first replica should have the same set up because in=C2=A0case of a= ny disaster to master the first replica should be able to take the place of= master.
=C2=A0
<= /tr>
Master/PrimaryFirst Rep= lica/Standby for High AvailabilitySecond Replica for Report= ing
hot_standby_feedback=3D= ONhot_standby_feedback=3DONhot_standby_feedback=3DOFF
max_standby_streaming_delay=3D10 secmax_standby_streaming_delay=3D10 sec= max_standby_streaming_delay=3D-1 (Infinite)
statement_timeout =3D "2hrs"statement_timeout=3D"2hrs"No statement_timeout i.e. infinite
idle_in_transaction_session_timeout=3D10minutesidle_in_transaction_session_timeout=3D10minutesNo idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=3D100M= autovacuum_freeze_max_age=3D100Mautovacuum_freeze_max_age=3D100M
Log_autovacuum_min_duration=3D0Log_autovacuum_min_duration=3D0L= og_autovacuum_min_duration=3D0
=C2=A0

--000000000000d3b8f00619892430--