Thread: Tooling for per table autovacuum tuning

T-shirts?

On 3/11/23 12:49, Joseph Hammerman wrote:
> Hi pgsql-admins,
>
> I would like to define t-shirt sizes and have an autovacuuming profile 
> associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? 
> Or are all of you rolling your own?
>
> Thanks in advance for any assistance anyone can provide,
> Joe

-- 
Born in Arizona, moved to Babylonia.

On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?

Isn't that question 21 days early?

Yours,
Laurenz Albe

Hi all,

Apologies for any confusion I may have caused. What I am imagining is per table tuning that buckets the tables based on their relative sizes.

Something like:

Up to 1Gb - Small

Up to 4Gb - Medium

Up to 8Gb - L

Bigger - XL

And an accordant autovacuum_scale_factor associated with each size.

The motivation for this is to make sure large tables get regularly vacuumed.

I hope that clears thing up!

Joe

On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm <wolfgang20121964@yahoo.de> wrote:

I think Mr Hammerman is referring to T-shirts for user stories.

But even when I'm right I don't get what is meant with that. I don't get what Joe means with "autovacuuming profiles per table". Joe, can you elaborate on that?

Yours

Wolfgang

Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe <laurenz.albe@cybertec.at> Folgendes geschrieben:

On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:

> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?

Isn't that question 21 days early?

Yours,
Laurenz Albe

Hi all,

Apologies for any confusion I may have caused. What I am imagining is per table tuning that buckets the tables based on their relative sizes.

Something like:

Up to 1Gb - Small

Up to 4Gb - Medium

Up to 8Gb - L

Bigger - XL

And an accordant autovacuum_scale_factor associated with each size.

The motivation for this is to make sure large tables get regularly vacuumed.

I hope that clears thing up!

Joe

On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm <wolfgang20121964@yahoo.de> wrote:

I think Mr Hammerman is referring to T-shirts for user stories.

But even when I'm right I don't get what is meant with that. I don't get what Joe means with "autovacuuming profiles per table". Joe, can you elaborate on that?

Yours

Wolfgang

Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe <laurenz.albe@cybertec.at> Folgendes geschrieben:

On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:

> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?

Isn't that question 21 days early?

Yours,
Laurenz Albe

Hi,

Following on from Michael's idea, you could write a function to split up the tables based upon size.  As an example, you could do this (using the pg_stat_all_tables):

DO
$$
DECLARE
        tab_rec RECORD;
BEGIN

        FOR tab_rec IN SELECT   schemaname,
                                relname tablename,
                                pg_catalog.pg_table_size(relid) bytes,
                                pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(relid)) size
                        FROM    pg_stat_all_tables
                        WHERE   schemaname NOT IN ('pg_catalog','information_schema')
                        AND     schemaname = 'public'
                        ORDER BY bytes
        LOOP
                RAISE NOTICE 'schemaname % tablename % bytes: % Table_size: %', tab_rec.schemaname, tab_rec.tablename, tab_rec.bytes, tab_rec.size;

                IF tab_rec.bytes < 10000 THEN
                        RAISE NOTICE 'ALTER TABLE %.% SET (autovacuum_vacuum_cost_limit = 1000);', tab_rec.schemaname, tab_rec.tablename;
                ELSE
                        RAISE NOTICE 'ALTER TABLE %.% SET (autovacuum_vacuum_cost_limit = 10000);', tab_rec.schemaname, tab_rec.tablename;
                END IF;

        END LOOP;
END;
$$

NOTICE:  schemaname public tablename event_check3 bytes: 0 Table_size: 0 bytes
NOTICE:  ALTER TABLE public.event_check3 SET (autovacuum_vacuum_cost_limit = 1000);
NOTICE:  schemaname public tablename event_check bytes: 0 Table_size: 0 bytes
NOTICE:  ALTER TABLE public.track_ddl SET (autovacuum_vacuum_cost_limit = 10000);
NOTICE:  schemaname public tablename audit_ddl_cmds bytes: 16384 Table_size: 16 kB
NOTICE:  ALTER TABLE public.audit_ddl_cmds SET (autovacuum_vacuum_cost_limit = 10000);
NOTICE:  schemaname public tablename c1 bytes: 16384 Table_size: 16 kB

You'd have to define the parameters in the IF statements but it could be split up into the "t-shirt" sizes that you want.  The pg_catalog table could also be changed to something else but this is the general idea.

KR,

Matt

Why don't you just monitor pg_stat_user_tables.n_dead_tup on a  regular basis and increase autovacuum aggressiveness based on that at the global level (postgresql.conf - thresholds) or set autovacuum parms at the table level for customized cases.

Regards,
Michael Vitale


Joseph Hammerman wrote on 3/12/2023 4:34 PM:

Hi all,

Apologies for any confusion I may have caused. What I am imagining is per table tuning that buckets the tables based on their relative sizes.

Something like:

Up to 1Gb - Small

Up to 4Gb - Medium

Up to 8Gb - L

Bigger - XL

And an accordant autovacuum_scale_factor associated with each size.

The motivation for this is to make sure large tables get regularly vacuumed.

I hope that clears thing up!

Joe

On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm <wolfgang20121964@yahoo.de> wrote:

I think Mr Hammerman is referring to T-shirts for user stories.

But even when I'm right I don't get what is meant with that. I don't get what Joe means with "autovacuuming profiles per table". Joe, can you elaborate on that?

Yours

Wolfgang

Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe <laurenz.albe@cybertec.at> Folgendes geschrieben:

On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:

> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?

Isn't that question 21 days early?

Yours,
Laurenz Albe

-- 
Pythian				
Matt Pearson | Database Consultant - PostgreSQL & Oracle | LinkedIn
mpearson2@pythian.com
www.pythian.com
Pythian

On Sun, 2023-03-12 at 13:34 -0700, Joseph Hammerman wrote:
> What I am imagining is per table tuning that buckets the tables based on their relative sizes.
>
> Something like:
>
> Up to 1Gb - Small
> Up to 4Gb - Medium
> Up to 8Gb - L
> Bigger - XL
>
> And an accordant autovacuum_scale_factor associated with each size.
>
> The motivation for this is to make sure large tables get regularly vacuumed.

I understand.

There is no such option currently.  Perhaps you can use "autovacuum_vacuum_threshold"
for something like that: if you set it to a certain (high) value and set "autovacuum_vacuum_scale_factor"
to 0, autovacuum is triggered based on the absolute number of dead tuples.

But I would say that the standard configuration makes sense in this case: normally,
large tables don't need to be vacuumed that often, and vacuum on large tables is
nore expensive too.

Yours,
Laurenz Albe

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@postgresql.org> []
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/

An excellent write up.

And seems to be for the same reason why one should VACUUM after

On 3/13/23 05:09, Alvaro Herrera wrote:
> 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@postgresql.org> []
> 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
>
>

-- 
Born in Arizona, moved to Babylonia.