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
