Re: Tooling for per table autovacuum tuning

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

 



On 2023-Mar-12, Ron wrote:

> A giant table which is never updated (or deleted from) doesn't need to be
> vacuumed.

Actually, this bit is wrong, because vacuuming is also used for things
such as setting up the visibility map, which feeds index-only scans.  So
insert-only tables also benefit from vacuuming, and indeed Postgres 13
introduced this as an autovacuum feature, in this commit:


commit b07642dbcd8d5de05f0ee1dbb72dd6760dd30436
Author:     David Rowley <drowley@xxxxxxxxxxxxxx> []
AuthorDate: Sat Mar 28 19:20:12 2020 +1300
CommitDate: Sat Mar 28 19:20:12 2020 +1300

    Trigger autovacuum based on number of INSERTs
    
    Traditionally autovacuum has only ever invoked a worker based on the
    estimated number of dead tuples in a table and for anti-wraparound
    purposes. For the latter, with certain classes of tables such as
    insert-only tables, anti-wraparound vacuums could be the first vacuum that
    the table ever receives. This could often lead to autovacuum workers being
    busy for extended periods of time due to having to potentially freeze
    every page in the table. This could be particularly bad for very large
    tables. New clusters, or recently pg_restored clusters could suffer even
    more as many large tables may have the same relfrozenxid, which could
    result in large numbers of tables requiring an anti-wraparound vacuum all
    at once.
    
    Here we aim to reduce the work required by anti-wraparound and aggressive
    vacuums in general, by triggering autovacuum when the table has received
    enough INSERTs. This is controlled by adding two new GUCs and reloptions;
    autovacuum_vacuum_insert_threshold and
    autovacuum_vacuum_insert_scale_factor. These work exactly the same as the
    existing scale factor and threshold controls, only base themselves off the
    number of inserts since the last vacuum, rather than the number of dead
    tuples. New controls were added rather than reusing the existing
    controls, to allow these new vacuums to be tuned independently and perhaps
    even completely disabled altogether, which can be done by setting
    autovacuum_vacuum_insert_threshold to -1.
    
    We make no attempt to skip index cleanup operations on these vacuums as
    they may trigger for an insert-mostly table which continually doesn't have
    enough dead tuples to trigger an autovacuum for the purpose of removing
    those dead tuples. If we were to skip cleaning the indexes in this case,
    then it is possible for the index(es) to become bloated over time.
    
    There are additional benefits to triggering autovacuums based on inserts,
    as tables which never contain enough dead tuples to trigger an autovacuum
    are now more likely to receive a vacuum, which can mark more of the table
    as "allvisible" and encourage the query planner to make use of Index Only
    Scans.
    
    Currently, we still obey vacuum_freeze_min_age when triggering these new
    autovacuums based on INSERTs. For large insert-only tables, it may be
    beneficial to lower the table's autovacuum_freeze_min_age so that tuples
    are eligible to be frozen sooner. Here we've opted not to zero that for
    these types of vacuums, since the table may just be insert-mostly and we
    may otherwise freeze tuples that are still destined to be updated or
    removed in the near future.
    
    There was some debate to what exactly the new scale factor and threshold
    should default to. For now, these are set to 0.2 and 1000, respectively.
    There may be some motivation to adjust these before the release.
    
    Author: Laurenz Albe, Darafei Praliaskouski
    Reviewed-by: Alvaro Herrera, Masahiko Sawada, Chris Travers, Andres Freund, Justin Pryzby
    Discussion: https://postgr.es/m/CAC8Q8t%2Bj36G_bLF%3D%2B0iMo6jGNWnLnWb1tujXuJr-%2Bx8ZCCTqoQ%40mail.gmail.com


-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/





[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux