Thread: vacuum an all frozen table

vacuum an all frozen table

From
Senor Cervesa
Date:
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




Re: vacuum an all frozen table

From
Laurenz Albe
Date:
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