public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Senor Cervesa <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: vacuum an all frozen table
Date: Wed, 22 May 2024 09:34:47 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <SN4P221MB0683A34E144BD9A66BBCB566F7EA2@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
References: <SN4P221MB0683A34E144BD9A66BBCB566F7EA2@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
On Tue, 2024-05-21 at 14:46 -0700, Senor Cervesa wrote:
> I'd like to understand what's happening here and whether there is
> anything I can do to improve the situation.
>
> PostgreSQL v11.22 (yeah, I know. Needs upgrade)
>
> The following 3 autovacuum log entries show a vacuum of an append only
> table that has not had any changes since the end of 5/10/2024. There is
> only 1 page not skipped in each instance yet it takes over 1100 seconds
> to complete. Visibility map shows all frozen. The associated TOAST table
> is similar in numbers except that it completes in sub-second times.
>
> I understand that the vacuum is occurring due to age of
> pg_class.relfrozenxid for the table but what exactly is it referring to
> in these cases? Can that also be frozen or similar? Should I add
> autovacuum_freeze_max_age=400000000 or higher to relopts do reduce
> vacuum frequency.
>
>
> 2024-05-17 09:56:57.167 GMT "" "" LOG: automatic aggressive vacuum of
> table "workdb1.public.log_entry_20240510": index scans: 0
> pages: 0 removed, 53551748 remain, 0 skipped due to pins,
> 53551747 skipped frozen
> tuples: 0 removed, 242384013 remain, 0 are dead but not yet
> removable, oldest xmin: 3245896267
> buffer usage: 107117593 hits, 123159244 misses, 3 dirtied
> avg read rate: 856.853 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU: user: 151.07 s, system: 638.29 s, elapsed:
> 1122.92 s
Strange. Could you run
VACUUM (VERBOSE, FREEZE) public.log_entry_20240510;
and show the result? Perhaps that contains some clues.
Yours,
Laurenz Albe
view thread (2+ messages)
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: vacuum an all frozen table
In-Reply-To: <[email protected]>
* 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