Re: Tooling for per table autovacuum tuning

[Matt Pearson <mpearson2@xxxxxxxxxxx>]

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

On 12/03/2023 20:43, MichaelDBA wrote:

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@xxxxxxxx> 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@xxxxxxxxxxx> 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

Regards,

Michael Vitale

Michaeldba@xxxxxxxxxxx

703-600-9343

 

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

--