public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Salahuddin Manzoor <[email protected]>
To: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Long running query causing XID limit breach
Date: Thu, 23 May 2024 10:12:08 +0500
Message-ID: <CAKD7CDn8NpNhrsyqPucjHOxcDEjCuspmhG6Q0ej_wp4wM4jnOQ@mail.gmail.com> (raw)
In-Reply-To: <CAD=mzVXm2wQD707KrwsQUH=8xE3Zknr8hWuTV_qz2XcTzFzYXg@mail.gmail.com>
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
<CAKD7CDk=mB3Z2m9hLK=bX1=KThUwOE+yudmOEaBb6Grqg8HXaQ@mail.gmail.com>
<CAD=mzVXm2wQD707KrwsQUH=8xE3Zknr8hWuTV_qz2XcTzFzYXg@mail.gmail.com>
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 (살라후딘**)*
On Thu, 23 May 2024 at 09:48, sud <[email protected]> wrote:
> On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor <
> [email protected]> 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 is
> getting loaded currently? and do you mean to say running command "vacuum
> table_name;" frequently on selective tables that are experiencing heavy DML
> ? 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 this
> issue?
>
>
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Long running query causing XID limit breach
In-Reply-To: <CAKD7CDn8NpNhrsyqPucjHOxcDEjCuspmhG6Q0ej_wp4wM4jnOQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox