MIME-Version: 1.0
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
 <CAKD7CDk=mB3Z2m9hLK=bX1=KThUwOE+yudmOEaBb6Grqg8HXaQ@mail.gmail.com>
 <CAD=mzVXm2wQD707KrwsQUH=8xE3Zknr8hWuTV_qz2XcTzFzYXg@mail.gmail.com> <CAKD7CDn8NpNhrsyqPucjHOxcDEjCuspmhG6Q0ej_wp4wM4jnOQ@mail.gmail.com>
In-Reply-To: <CAKD7CDn8NpNhrsyqPucjHOxcDEjCuspmhG6Q0ej_wp4wM4jnOQ@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Thu, 23 May 2024 11:41:58 +0530
Message-ID: <CAD=mzVU8CzBR8v-GcYDFgFjV5nOWMhmPnk8_peXXyjtX__PL1A@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000660cd8061918eeb0"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVU8CzBR8v-GcYDFgFjV5nOWMhmPnk8_peXXyjtX__PL1A%40mail.gmail.com>
Precedence: bulk

--000000000000660cd8061918eeb0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, May 23, 2024 at 10:42=E2=80=AFAM Muhammad Salahuddin Manzoor <
salahuddin.m@bitnine.net> wrote:

> 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 syste=
m
> consumes 200 million XIDs per hour.
>
>
>
Thank you so much. That helps.
So apart from setting these alerts on "Maximumusedtxnids" and making the
vacuum optimized by tweaking above parameters, should we also need to have
monitoring in place to ensure the Vacuum is not taking longer as compared
to its normal runtime and also if it's getting blocked/failed by something?
Like for example in our case where the select query was running longer , so
the vacuum must not be able to succeed every time it attempts, so is it
really worth having that level of alerting?  and also how can we get an
idea regarding if the vacuum is not succeeding or getting failed etc to
avoid such upcoming issues?

--000000000000660cd8061918eeb0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, May 23, 2024 at 10:42=E2=80=AFAM =
Muhammad Salahuddin Manzoor &lt;<a href=3D"mailto:salahuddin.m@bitnine.net"=
>salahuddin.m@bitnine.net</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr">Greetings,<div><br></div><div>Run=
ning `VACUUM table_name;` on a partitioned table will vacuum each partition=
 individually, not the whole table as a single unit.<br><br>Yes, running `V=
ACUUM table_name;` frequently on tables or partitions with heavy DML is rec=
ommended.<br><br>Regular `VACUUM` does not lock the table for reads or writ=
es, so it won&#39;t disrupt ongoing 24/7 data operations.<br><br>&quot;opti=
mize autovacuum&quot;<br>Yes. Adjust following parameters as per your syste=
m/environment requirement <br>autovacuum_max_workers,=C2=A0</div><div>autov=
acuum_freeze_max_age ,=C2=A0</div><div>autovacuum_vacuum_cost_delay<br><br>=
Following need to be first tested thoroughly in a test environment.<br>Reco=
mmended Alert Threshold<br>Alert at 50% Usage: Set the alert threshold at 1=
 billion used XIDs. This provides a significant buffer, giving you ample ti=
me to take corrective action before reaching the critical limit.<br><br>Cal=
culation Rationale<br>Daily XID Usage: Approximately 4 billion rows per day=
 implies high XID consumption.<br>Buffer Time: At 1 billion XIDs, you would=
 still have 1 billion XIDs remaining, giving you roughly 12 hours to addres=
s the issue if your system consumes 200 million XIDs per hour.<br><br><div>=
<div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><br></div></div=
></div></div></div></blockquote><div><br></div>Thank you so much. That help=
s.<div>So apart from setting these alerts on &quot;Maximumusedtxnids&quot; =
and making the vacuum optimized by tweaking above parameters, should we als=
o need to have monitoring in place to ensure=C2=A0the Vacuum is not taking =
longer as compared to its normal runtime and also if it&#39;s getting block=
ed/failed by something? Like for example in our case where the select query=
 was running longer , so the vacuum=C2=A0must not be able to succeed every =
time it attempts, so is it really worth having=C2=A0that level of alerting?=
=C2=A0 and also how can we get an idea regarding if the vacuum is not succe=
eding or getting failed etc to avoid such upcoming issues?=C2=A0</div><div>=
<br></div></div></div>

--000000000000660cd8061918eeb0--