From: | wenhui qiu <qiuwenhuifx(at)gmail(dot)com> |
---|---|
To: | Melanie Plageman <melanieplageman(at)gmail(dot)com> |
Cc: | Greg Sabino Mullane <htamfids(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Peter Geoghegan <pg(at)bowt(dot)ie>, David Rowley <dgrowley(at)gmail(dot)com> |
Subject: | Re: Trigger more frequent autovacuums of heavy insert tables |
Date: | 2025-02-07 06:40:22 |
Message-ID: | CAGjGUALD-zus_GW7Ls43_Gm_+Cgq6wiDN6c1d7XhwX0eH94tKA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
> We could add autovacuum_vacuum_insert_max_threshold, but with an
> insert-only workload, we can expect that the cold data is being
> frozen. By calculating the threshold based on unfrozen data, we are
> effectively capping the threshold for inserted data without adding
> another guc. If any of that data is being unfrozen via updates or
> deletes, then the autovacuum_vacuum_max_threshold would apply.
> Perhaps I'm missing a case where calculating the insert threshold on
> unfrozen data would not act as a cap, in which case I could get on
> board with a guc.
Actually ,I like your solution. Even I think this formula could use that
pcnt_unfrozen parameter
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples *
pcnt_unfrozen;
Thanks
On Thu, Feb 6, 2025 at 11:42 PM Melanie Plageman <melanieplageman(at)gmail(dot)com>
wrote:
> Attached v6 is rebased over 306dc520b9dfd60
>
> On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu <qiuwenhuifx(at)gmail(dot)com> wrote:
> >
> > Hi Melanie Plageman
> > Thank you for working on this ,Actually, there were two patches aimed
> at optimizing vacuum-triggered processes, and one of them reached a
> consensus and has been committed:
> https://commitfest.postgresql.org/52/5046/ ,
> https://commitfest.postgresql.org/51/5395/, Maybe referring to the
> already committed patch and setting a maximum value for
> vacuum_max_ins_threshold would be more acceptable.
>
> We could add autovacuum_vacuum_insert_max_threshold, but with an
> insert-only workload, we can expect that the cold data is being
> frozen. By calculating the threshold based on unfrozen data, we are
> effectively capping the threshold for inserted data without adding
> another guc. If any of that data is being unfrozen via updates or
> deletes, then the autovacuum_vacuum_max_threshold would apply.
>
> Perhaps I'm missing a case where calculating the insert threshold on
> unfrozen data would not act as a cap, in which case I could get on
> board with a guc.
>
>
> - Melanie
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2025-02-07 06:47:02 | Re: Fix for a crash caused by triggers in cross-partition updates |
Previous Message | Peter Smith | 2025-02-07 06:34:45 | DOCS - inactive_since field readability |