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 1sA0kf-006wke-4f for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 05:12:30 +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 1sA0ke-0086PW-4b for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 05:12:28 +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 1sA0kd-0086PO-IM for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 05:12:27 +0000 Received: from mail-lj1-x231.google.com ([2a00:1450:4864:20::231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sA0kX-000KBd-Fu for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 05:12:26 +0000 Received: by mail-lj1-x231.google.com with SMTP id 38308e7fff4ca-2e95125e257so4713421fa.0 for ; Wed, 22 May 2024 22:12:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1716441140; x=1717045940; 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=9z2C2hiAmPHGjn5JIQy+Uzieg1a7f54mgQaS0Z6lPjs=; b=zdDq+G4ETfzAxCzvNALamU0RmbRuHK3dInJXBWNvj6Frx8nH+TdBY/xws+Voaq+EBw j+Sz0HC+H5mUfHJz8XwvNNfGpjE1tZOLPL0Kxv/RIcQRZJ2upqiTldyoToTdjj8tSn1m dTJxZByEfcxNBNcNEeov4BPCxcpMjCDNjNZcfrRFbYgPLTKOHvf2ygdi7V5XwBSPPTG1 e7BSiVdewZpLmr3s/eoeg1IXGQc4NTTyMninDmNpdeNIAb8LVZ9ORdbYcxsuvBA2Ee4I UwxGVi0QIBTw/BZhe7FZqSVO2ad1WTIlD+8qKUyUsAoevMRZadqc6L1n3ivuMnuQ8WEr YNkw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716441140; x=1717045940; 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=9z2C2hiAmPHGjn5JIQy+Uzieg1a7f54mgQaS0Z6lPjs=; b=jsEQ/XT5imzPTERrvMGrAnBpXAze/uEZOwDFbaL1KWNpuOIcKvNK9WWwgnkrLzEM8r JS6x13zubABY5XY74CfDN2YQsCPPhK3/czYYZsP0lGS8Ubr1cmZxBGE3mCPmRcKmLSpu +Q+w+flsK8cGCzlAvZJDe+8aBYWDPOT/hO5kXSnzSnaa+Rj8l8Udq06n5JMacd71C9MT QqfRIXDktk9lteQawPpIsCAkdf3zmIApzE3f/FjM1BxUIfzYMXVPauJJqfZu/I5X84k1 zYSvLBwwD0ozoOrHb58+LAVdS0gSkN65Dk4hAG7ENiot64HxLlAChD36kYxd4WSXolaN Moow== X-Gm-Message-State: AOJu0Yy4XlIqC940DvWPy9fgwkbe4/tKbEerV2KKBLd/p3tvVoQSqRyu 2ljbLFZAe5h9AvCnqJq37cuPUoXvnlScDtHF6RXxm9jVDwJfodbIjg82zGS+k+77Eua5V2zTzer UhJys9NSjy0+5IjkTPR3bwruNQMkcvL0X93/VUQ== X-Google-Smtp-Source: AGHT+IH9Gag+varyEhFmuHvDX7OYa6nSOwT45MHnzTk1J/47pp1ozjKKAzmda4ybkI7SKFlH/IqAO2SZP1WwcUd864E= X-Received: by 2002:a2e:8797:0:b0:2e2:2623:36a with SMTP id 38308e7fff4ca-2e951b4f1f9mr3296821fa.3.1716441140165; Wed, 22 May 2024 22:12:20 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Salahuddin Manzoor Date: Thu, 23 May 2024 10:12:08 +0500 Message-ID: Subject: Re: Long running query causing XID limit breach To: sud Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000070190606191818d1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000070190606191818d1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greetings, Running `VACUUM table_name;` on a partitioned table will vacuum each partition individually, not the whole table as a single unit. Yes, running `VACUUM table_name;` frequently on tables or partitions with heavy DML is recommended. Regular `VACUUM` does not lock the table for reads or writes, so it won't disrupt ongoing 24/7 data operations. "optimize autovacuum" Yes. Adjust following parameters as per your system/environment requirement autovacuum_max_workers, autovacuum_freeze_max_age , autovacuum_vacuum_cost_delay Following need to be first tested thoroughly in a test environment. Recommended Alert Threshold Alert at 50% Usage: Set the alert threshold at 1 billion used XIDs. This provides a significant buffer, giving you ample time to take corrective action before reaching the critical limit. Calculation Rationale Daily XID Usage: Approximately 4 billion rows per day implies high XID consumption. Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs remaining, giving you roughly 12 hours to address the issue if your system consumes 200 million XIDs per hour. *Salahuddin (=EC=82=B4=EB=9D=BC=ED=9B=84=EB=94=98**)* On Thu, 23 May 2024 at 09:48, sud wrote: > On Thu, May 23, 2024 at 9:00=E2=80=AFAM Muhammad Salahuddin Manzoor < > salahuddin.m@bitnine.net> wrote: > >> Greetings, >> >> In high-transaction environments like yours, it may be necessary to >> supplement this with manual vacuuming. >> >> Few Recommendations >> >> Monitor Long-Running Queries try to optimize. >> Optimize Autovacuum. >> Partitioning. >> Adopt Vacuum Strategy after peak hours. >> >> We have these big tables already partitioned. So does "vacuum table_name= " > will endup scanning whole table or just the latest/live partition which i= s > getting loaded currently? and do you mean to say running command "vacuum > table_name;" frequently on selective tables that are experiencing heavy D= ML > ? Hope this won't lock the table anyway because the data will be > written/read from these tables 24/7. > > When you say, "optimize autovacuum" does it mean to set a higher value of= "autovacuum_max_workers" > and "autovacuum_freeze_max_age"? > > Considering we have ~4 billion rows inserted daily into the table and > there is limit of ~2billion to the "Maximumusedtxnids", what threshold > should we set for the alerting and to have enough time at hand to fix thi= s > issue? > > --00000000000070190606191818d1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Greetings,

Running `VACUUM table_name;`= on a partitioned table will vacuum each partition individually, not the wh= ole table as a single unit.

Yes, running `VACUUM table_name;` freque= ntly on tables or partitions with heavy DML is recommended.

Regular = `VACUUM` does not lock the table for reads or writes, so it won't disru= pt ongoing 24/7 data operations.

"optimize autovacuum"
= Yes. Adjust following parameters as per your system/environment requirement=
autovacuum_max_workers,=C2=A0
autovacuum_freeze_max_age ,=C2= =A0
autovacuum_vacuum_cost_delay

Following need to be firs= t tested thoroughly in a test environment.
Recommended Alert ThresholdAlert at 50% Usage: Set the alert threshold at 1 billion used XIDs. This = provides a significant buffer, giving you ample time to take corrective act= ion before reaching the critical limit.

Calculation Rationale
Dai= ly XID Usage: Approximately 4 billion rows per day implies high XID consump= tion.
Buffer Time: At 1 billion XIDs, you would still have 1 billion XID= s remaining, giving you roughly 12 hours to address the issue if your syste= m consumes 200 million XIDs per hour.

=

Salahuddin (=EC=82=B4=EB=9D=BC=ED=9B=84=EB=94= =98)


=

On Thu, 23 May 2024 at 09:48, sud <suds1434@gmail.com> wrote:
On Thu, May 23, = 2024 at 9:00=E2=80=AFAM Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net>= wrote:
Greetings,

In hi= gh-transaction environments like yours, it may be necessary to supplement t= his with manual vacuuming.

Few Recommendations

Monitor Long-R= unning Queries try to optimize.
Optimize Autovacuum.
Partitioning.Adopt Vacuum Strategy after peak hours.

=
We have these big tables already = partitioned. So does=C2=A0"vacuum table_name" will endup scanning= whole=C2=A0table or just the latest/live partition which is getting loaded= currently? and do you mean to say running command "vacuum table_name;= " frequently=C2=A0on selective tables that=C2=A0are=C2=A0experiencing= =C2=A0heavy DML ? Hope=C2=A0this=C2=A0won't lock the table anyway becau= se the data will be written/read from these tables=C2=A024/7.
When you say, "optimize autovacuum" does it mean to s= et a higher value of "autovacuum_max_workers" and "autovacuum_freeze_max_age"?

=
Considering we have ~4 billion rows inserted daily into t= he table and there is limit of ~2billion to the "Maximumusedtxnids&quo= t;, what threshold should we set for the alerting and to have enough time a= t hand to fix this issue?

--00000000000070190606191818d1--