Search Postgresql Archives

Re: vacuum an all frozen table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux