public inbox for [email protected]
help / color / mirror / Atom feedvacuum an all frozen table
2+ messages / 2 participants
[nested] [flat]
* vacuum an all frozen table
@ 2024-05-21 21:46 Senor Cervesa <[email protected]>
2024-05-22 07:34 ` Re: vacuum an all frozen table Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Senor Cervesa @ 2024-05-21 21:46 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hi All,
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
2024-05-18 23:20:37.900 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: 3445717402
buffer usage: 107105521 hits, 123171316 misses, 3 dirtied
avg read rate: 839.611 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 166.46 s, system: 611.40 s, elapsed:
1146.09 s
2024-05-20 19:11:29.519 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: 3645738812
buffer usage: 107095847 hits, 123180990 misses, 2 dirtied
avg read rate: 744.513 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 195.13 s, system: 694.13 s, elapsed:
1292.59 s
workdb1=# SELECT * from pg_visibility('log_entry_20240510'::regclass) where
all_visible IS NOT true
OR all_frozen IS NOT true
OR pd_all_visible IS NOT true;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
(0 rows)
Thank you for any insights,
Senor
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: vacuum an all frozen table
2024-05-21 21:46 vacuum an all frozen table Senor Cervesa <[email protected]>
@ 2024-05-22 07:34 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-05-22 07:34 UTC (permalink / raw)
To: Senor Cervesa <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-22 07:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-21 21:46 vacuum an all frozen table Senor Cervesa <[email protected]>
2024-05-22 07:34 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox