From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Joseph Hammerman <joe.hammerman@datadoghq.com>
Cc: Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Tooling for per table autovacuum tuning
Date: Sun, 12 Mar 2023 16:43:09 -0400
Message-ID: <23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com> (raw)
In-Reply-To: <CAHs7QM_dDN3M=G0vEEi7V_wZaWmtJO5OK6ruh7bBfQ4BNo4QxQ@mail.gmail.com>
References: <CAHs7QM_scGEh8uhsCUoc_BEimyYsUTVZxpE10rUiqnShd8nTkQ@mail.gmail.com>
<5c0a6ab9ba801e0fa7c2217e49ce804837c64cf1.camel@cybertec.at>
<14072753.2216234.1678640212317@mail.yahoo.com>
<CAHs7QM_dDN3M=G0vEEi7V_wZaWmtJO5OK6ruh7bBfQ4BNo4QxQ@mail.gmail.com>
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 <mailto: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 <mailto: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
>
>
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com/3-pgadvanced3.jpg)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, joe.hammerman@datadoghq.com, wolfgang20121964@yahoo.de, pgsql-admin@lists.postgresql.org, laurenz.albe@cybertec.at
Subject: Re: Tooling for per table autovacuum tuning
In-Reply-To: <23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox