From: | Melanie Plageman <melanieplageman(at)gmail(dot)com> |
---|---|
To: | Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com> |
Subject: | Re: New GUC autovacuum_max_threshold ? |
Date: | 2024-04-24 19:10:27 |
Message-ID: | CAAKRu_bevP35okEsQuT6dr5jbvD79Gva_-YO_ciBSchT4GDHSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Apr 24, 2024 at 8:08 AM Frédéric Yhuel
<frederic(dot)yhuel(at)dalibo(dot)com> wrote:
>
> Hello,
>
> I would like to suggest a new parameter, autovacuum_max_threshold, which
> would set an upper limit on the number of tuples to delete/update/insert
> prior to vacuum/analyze.
Hi Frédéric, thanks for the proposal! You are tackling a very tough
problem. I would also find it useful to know more about what led you
to suggest this particular solution. I am very interested in user
stories around difficulties with what tables are autovacuumed and
when.
Am I correct in thinking that one of the major goals here is for a
very large table to be more likely to be vacuumed?
> The idea would be to replace the following calculation :
>
> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
>
> with this one :
>
> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples / (1
> + vac_scale_factor * reltuples / autovacuum_max_threshold)
>
> (and the same for the others, vacinsthresh and anlthresh).
My first thought when reviewing the GUC and how it is used is
wondering if its description is a bit misleading.
autovacuum_vacuum_threshold is the "minimum number of updated or
deleted tuples needed to trigger a vacuum". That is, if this many
tuples are modified, it *may* trigger a vacuum, but we also may skip
vacuuming the table for other reasons or due to other factors.
autovacuum_max_threshold's proposed definition is the upper
limit/maximum number of tuples to insert/update/delete prior to
vacuum/analyze. This implies that if that many tuples have been
modified or inserted, the table will definitely be vacuumed -- which
isn't true. Maybe that is okay, but I thought I would bring it up.
> The attached (draft) patch further illustrates the idea.
Thanks for including a patch!
> My guess is that a similar proposal has already been submitted... and
> rejected 🙂 If so, I'm very sorry for the useless noise.
I rooted around in the hackers archive and couldn't find any threads
on this specific proposal. I copied some other hackers I knew of who
have worked on this problem and thought about it in the past, in case
they know of some existing threads or prior work on this specific
topic.
- Melanie
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2024-04-24 19:19:57 | Re: some additional (small) problems with pg_combinebackup and tablespaces |
Previous Message | Daniel Gustafsson | 2024-04-24 19:08:27 | Re: Add notes to pg_combinebackup docs |