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