Re: Tooling for per table autovacuum tuning

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