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 1sEklk-003PPw-Bk for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 07:09:13 +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 1sEklk-005wJV-02 for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 07:09:12 +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 1sEklj-005wJN-H7 for pgsql-general@lists.postgresql.org; Wed, 05 Jun 2024 07:09:11 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sEklg-003XiZ-W9 for pgsql-general@lists.postgresql.org; Wed, 05 Jun 2024 07:09:10 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-dfa7fafa0c0so539173276.0 for ; Wed, 05 Jun 2024 00:09:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717571347; x=1718176147; 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=UYf4+BXIqYGJEXt6+VPJ/g15/pvWoJ7hxlB6jBqVxsQ=; b=nAbiIt2oFg62tXyRhWC3UDm7oHr4haAVV3z9vYvKYX6vcYEVO3pfS6hU52jLWHAl1w BQHJAql5HoThMhRxEA+AplyqI/j4NWBDv+q8GlvkJmQKeA/FzgRZS6I3evohFZPJqj3m Gdqmmt7LrOn+IQsZnkpPyc8176jsjulQmvRkBEia7QizoCbDwe3IffMjJGnfS2/bDEZP GHmGHyGX9pbhPMzK/idUOvW6/XJAQNW1aNdihCW1tDu2aYeBu6FZabYtwXgttKGf3vbK W8iPJM+kbQU3U05kDYkjW4IJQPtGW47MuS9rpfq09BsnQGqa/YrZTvEN2361qXhfECd5 RuBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717571347; x=1718176147; 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=UYf4+BXIqYGJEXt6+VPJ/g15/pvWoJ7hxlB6jBqVxsQ=; b=toBDXRmgVRmuE4OoAtOiBTHBFl4hQp5BVIExXAOkq69g9xRQECP1mpUAFAZiS3F1a+ JD6rxLAa07SAI0tAd0zLJDRzdrIjdfXwJak+f2eLlSYBG5aQwA6S1RTikqDWQrJic74+ Bmb0fWGoujiIBw5ngJR7rM0Q0pB7rz/gphHgFHw9dUMCuih43IffvugvOT9BADGh7n1M vwYtGFq6foyyXdsL+6jZcycjo7xdg/epTh1PdXLhKyFLbjzlBbdh1CLtqOoH5b/vo7xt SNHRhTyeWtOzeBBjKB/Q7S/pLRniUNvuPWaqI34rtd6mr3V76S56PcXAYVGhyn/MnAOj a7yQ== X-Forwarded-Encrypted: i=1; AJvYcCVudpnPUTlLAReuxMpWQNNLR4kzmxXXLRLAm7gSP260uaqtQqXfk4qt05B2voKmyoPShnSap6JKmPoPHaiM0WKUDwDMprX8d9MJNTRXUJslYpNN X-Gm-Message-State: AOJu0YxhYoTOZvazV7nGqinjHTqGXOEme9HEnnMBWB8PAwPmbZTy7Cgq rASmBbQtCUKLWhHDzkmAbSU7Ftd/qvMW0ayeBnSaKXgSeugB9SQdkGnnMEwdM0u+/Qlczeckmdr AYyHQsFNZLt2ivTCXdS2qdhU/4Bc= X-Google-Smtp-Source: AGHT+IE811GneRW6Xn53q5baRkptJe9atEBAPHJ2Qll1OtOhfOaWaMvcsHuk6hxmZNrmJRxbyOnQi1Vm+kGmWWIS7O4= X-Received: by 2002:a25:b294:0:b0:dfa:5b25:48 with SMTP id 3f1490d57ef6-dfac974643cmr1645454276.15.1717571347505; Wed, 05 Jun 2024 00:09:07 -0700 (PDT) MIME-Version: 1.0 References: <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at> <56ad97911d83f721dd872e8ee68cd77d50d3eef6.camel@cybertec.at> In-Reply-To: From: Simon Elbaz Date: Wed, 5 Jun 2024 09:08:56 +0200 Message-ID: Subject: Re: Long running query causing XID limit breach To: sud Cc: Laurenz Albe , yudhi s , pgsql-general Content-Type: multipart/alternative; boundary="0000000000000b6e6d061a1f3e5d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000b6e6d061a1f3e5d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, I am following this very interesting thread. From the documentation https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE= -IN-TRANSACTION-SESSION-TIMEOUT, the 0 value will disable the timeout (not -1). On Wed, Jun 5, 2024 at 8:25=E2=80=AFAM sud wrote: > Hello Laurenz, > > Thank you so much.This information was really helpful for us > understanding the working of these parameters. > > One follow up question i have , as we are setting one of the > standby/replica with value idle_in_transaction_session_timeout=3D-1 which= can > cause the WAL's to be heavily backlogged in a scenario where we have a > query running for very long time on that instance. So in that case will > there be chances of instance restart and if that can be avoided anyway? > > And the plan is to set these system parameters with different values in > writer/read replica , so in that case if we apply the "alter system" > command on the primary , won't the WAL going to apply those same commands > forcibly on reader instance making those same as the writer instance > configuration( but we want the reader replica configuration to be differe= nt > from writer)? > > Appreciate your guidance. > > On Wed, May 29, 2024 at 1:38=E2=80=AFPM Laurenz Albe > wrote: > >> On Wed, 2024-05-29 at 01:34 +0530, yudhi s wrote: >> > > 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 for >> > > your long-running queries. >> > >> > When you suggest having different max_standby_streaming_delay for firs= t >> 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? >> >> The parameter is ignored on the master. >> It needs to be off on the standby that is running long queries. >> For the other standby it probably doesn't matter if you are not running >> any >> queries on it. I would leave "hot_standby_feedback =3D off" there as we= ll. >> >> Actually, I would set "hot_standby =3D off" on the standby that is only = used >> for HA. >> >> >> - I would leave "hot_standby_feedback" off everywhere. >> - "max_standby_streaming_delay" should be -1 on the reporting standby an= d >> very >> low or 0 on the HA standby. It doesn't matter on the primary. >> - "statement_timeout" should be way lower on the first two nodes. >> - "idle_in_transaction_session_timeout" is good. >> - I would leave "autovacuum_freeze_max_age" at the default setting but >> 100 million >> is ok too. >> >> Yours, >> Laurenz Albe >> > --0000000000000b6e6d061a1f3e5d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

I am following this very= interesting thread.

From the documentation https://www.postgresql.org/docs/curre= nt/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT, = the 0 value will disable the timeout (not -1).


On Wed, Jun 5, 2024 at 8:25=E2=80=AFAM sud <suds1434@gmail.com> wrote:
Hello Laurenz,

Thank you so much.This information was really helpful= =C2=A0for us understanding=C2=A0the working of these parameters.
=
One follow up question i have , as we are setting one of the= standby/replica with value idle_in_transaction_session_timeout=3D-1 which = can cause=C2=A0the WAL's to be heavily backlogged in a scenario=C2=A0wh= ere we have a query running for very long time on that instance. So in that= case will there be chances of instance restart and if that can be avoided = anyway?

And the plan is to set these system=C2=A0p= arameters=C2=A0with different values in writer/read replica , so in that ca= se if we apply the "alter system" command on the primary , won= 9;t the WAL=C2=A0going to apply those same commands forcibly on reader inst= ance making those same as the writer instance configuration( but we want th= e=C2=A0reader replica configuration to be different from writer)?=C2=A0

Appreciate=C2=A0your guidance.

On Wed, May 29, 2024 at= 1:38=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-05-29 at 01:34 +0= 530, yudhi s wrote:
> > 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".=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 firs= t replica
> (say 10 sec for High availability) and second replica(say -1 for long = running queries).
> Do you also suggest=C2=A0 keeping "hot_feedback_standby" as = "OFF" for all the three
> instances i.e. master and both the replicas?

The parameter is ignored on the master.
It needs to be off on the standby that is running long queries.
For the other standby it probably doesn't matter if you are not running= any
queries on it.=C2=A0 I would leave "hot_standby_feedback =3D off"= there as well.

Actually, I would set "hot_standby =3D off" on the standby that i= s only used
for HA.


- I would leave "hot_standby_feedback" off everywhere.
- "max_standby_streaming_delay" should be -1 on the reporting sta= ndby and very
=C2=A0 low or 0 on the HA standby. It doesn't matter on the primary. - "statement_timeout" should be way lower on the first two nodes.=
- "idle_in_transaction_session_timeout" is good.
- I would leave "autovacuum_freeze_max_age" at the default settin= g but 100 million
=C2=A0 is ok too.

Yours,
Laurenz Albe
--0000000000000b6e6d061a1f3e5d--