On Mon, Mar 3, 2025 at 1:11 PM Nathan Bossart wrote:
>
> "fraction of unfrozen pages before insert vacuum"
>
> That more closely matches the other scale factor parameters. It's
> admittedly quite terse, but so are the vast majority of other descriptions
> in the sample file. I don't thin
On Mon, Mar 03, 2025 at 12:18:37PM -0500, Melanie Plageman wrote:
> I noticed the docs wording is kind of different than that in
> postgresql.conf.sample. The docs wording mentions that the scale
> factor gets added to the threshold and postgresql.conf.sample does not
> (in master as well). I just
On Fri, Feb 28, 2025 at 12:54 PM Nathan Bossart
wrote:
>
> On Wed, Feb 26, 2025 at 04:48:20PM -0500, Melanie Plageman wrote:
> > Makes sense. Thanks Robert and Nathan. Attached v11 changes the docs
> > wording and is rebased.
>
> 0001 LGTM.
Cool. Corey checked over the stats import tests off-list
On Sat, Mar 01, 2025 at 08:57:52AM +0800, wenhui qiu wrote:
> Based on the comments, the pcnt_unfrozen value could potentially be 0,
> which would indicate that everything is frozen. Therefore, is it necessary
> to handle the case where the value is 0.？
How so? If it's 0, then the insert thre
On Wed, Feb 26, 2025 at 04:48:20PM -0500, Melanie Plageman wrote:
> Makes sense. Thanks Robert and Nathan. Attached v11 changes the docs
> wording and is rebased.
0001 LGTM.
>
> - Specifies a fraction of the table size to add to
> - autovacuum_vacuum_insert_threshold
> -
Hi
> + * It could be the stats were updated manually and relallfrozen >
> + * relpages. Clamp relallfrozen to relpages to avoid nonsensical
> + * calculations.
> + */
> + relallfrozen = Min(relallfrozen, relpages);
> + pcnt_unfrozen = 1 - ((float4) relallfrozen / relpages);
> + }
> +
Based on the c
On Wed, Feb 26, 2025 at 12:25 PM Nathan Bossart
wrote:
>
> On Wed, Feb 26, 2025 at 11:17:06AM -0500, Robert Treat wrote:
> > It strikes me as a bit odd to have this extra wording in the pg_class
> > documentation:
> >
> > + Every all-frozen page must also be marked
> > + all-visible in the v
On Wed, Feb 26, 2025 at 11:17:06AM -0500, Robert Treat wrote:
> It strikes me as a bit odd to have this extra wording in the pg_class
> documentation:
>
> + Every all-frozen page must also be marked
> + all-visible in the visibility map, so
> + relallfrozen should be less than or equal
On Tue, Feb 25, 2025 at 4:29 PM Melanie Plageman
wrote:
>
> On Tue, Feb 25, 2025 at 11:36 AM Greg Sabino Mullane
> wrote:
> >
> > On Tue, Feb 25, 2025 at 11:03 AM Melanie Plageman
> > wrote:
> >>
> >> Because users can now manually update these values in pg_class, there
> >> wouldn't be a way
On Tue, Feb 25, 2025 at 05:19:30PM -0500, Melanie Plageman wrote:
> Yes, so one thing you haven't said yet is if you are +1 on going
> forward with these patches in general.
Sorry, yes, I'm +1 in general. It conceptually makes sense to me that we
should disregard frozen pages when deciding whethe
On Tue, Feb 25, 2025 at 1:52 PM Robert Haas wrote:
>
> On Tue, Feb 25, 2025 at 11:03 AM Melanie Plageman
> wrote:
> > This does however leave me with the question of how to handle the
> > original question of whether or not to cap the proposed relallfrozen
> > to the value of relallvisible when u
On Tue, Feb 25, 2025 at 3:05 PM Nathan Bossart wrote:
>
> On Tue, Feb 25, 2025 at 01:52:28PM -0500, Robert Haas wrote:
> > Given that users could manually update the catalog, we have to be able
> > to tolerate bad data in the catalogs without the world ending. If that
> > code has to exist anyway,
On Tue, Feb 25, 2025 at 11:36 AM Greg Sabino Mullane wrote:
>
> On Tue, Feb 25, 2025 at 11:03 AM Melanie Plageman
> wrote:
>>
>> Because users can now manually update these values in pg_class, there
>> wouldn't be a way to detect the difference
>> between a bogus relallfrozen value due to VM co
On Tue, Feb 25, 2025 at 01:52:28PM -0500, Robert Haas wrote:
> Given that users could manually update the catalog, we have to be able
> to tolerate bad data in the catalogs without the world ending. If that
> code has to exist anyway, then it's not mandatory to cap. On the other
> hand, there's no
On Tue, Feb 25, 2025 at 11:03 AM Melanie Plageman
wrote:
> This does however leave me with the question of how to handle the
> original question of whether or not to cap the proposed relallfrozen
> to the value of relallvisible when updating stats at the end of
> vacuum. The current code in heap_v
On Tue, Feb 25, 2025 at 12:36:40PM -0500, Robert Treat wrote:
> On Tue, Feb 25, 2025 at 11:32 AM Nathan Bossart
> wrote:
>> I'm currently leaning towards the "remove all caps" idea. But I'm not sure
>> I totally understand the need for a WARNING. What do we expect users to do
>> with that inform
On Tue, Feb 25, 2025 at 11:32 AM Nathan Bossart
wrote:
> On Tue, Feb 25, 2025 at 11:02:40AM -0500, Melanie Plageman wrote:
> > This does however leave me with the question of how to handle the
> > original question of whether or not to cap the proposed relallfrozen
> > to the value of relallvisibl
On Tue, Feb 25, 2025 at 11:03 AM Melanie Plageman
wrote:
> Because users can now manually update these values in pg_class, there
> wouldn't be a way to detect the difference
> between a bogus relallfrozen value due to VM corruption or a bogus value
> due to manual statistics intervention.
Er..y
On Tue, Feb 25, 2025 at 11:02:40AM -0500, Melanie Plageman wrote:
> This does however leave me with the question of how to handle the
> original question of whether or not to cap the proposed relallfrozen
> to the value of relallvisible when updating stats at the end of
> vacuum. The current code i
On Tue, Feb 25, 2025 at 10:38:48AM -0500, Robert Haas wrote:
> On Mon, Feb 24, 2025 at 6:35 PM Melanie Plageman
> wrote:
>> I'm on the fence about adding a PANIC. We do PANIC in other places
>> where we notice corruption (like PageAddItemExtended()). But, in most
>> of the cases, it seems like we
On Tue, Feb 25, 2025 at 10:39 AM Robert Haas wrote:
>
> On Mon, Feb 24, 2025 at 6:35 PM Melanie Plageman
> wrote:
> > I'm on the fence about adding a PANIC. We do PANIC in other places
> > where we notice corruption (like PageAddItemExtended()). But, in most
> > of the cases, it seems like we ar
On Mon, Feb 24, 2025 at 6:35 PM Melanie Plageman
wrote:
> I'm on the fence about adding a PANIC. We do PANIC in other places
> where we notice corruption (like PageAddItemExtended()). But, in most
> of the cases, it seems like we are PANICing because there isn't a
> reasonable way to accomplish t
On Mon, Feb 24, 2025 at 10:30 PM wenhui qiu wrote:
>
> Hi Melanie
> > relallvisible. It seems like we should make it consistent. Perhaps we
> > should just remove it from heap_vacuum_rel(). Then add an assert in
> > all these places to at least protect development mistakes.
> I think there's some
Hi Melanie
> relallvisible. It seems like we should make it consistent. Perhaps we
> should just remove it from heap_vacuum_rel(). Then add an assert in
> all these places to at least protect development mistakes.
I think there's some objection to that.
Thanks
On Tue, Feb 25, 2025 at 7:35 AM Mel
On Mon, Feb 24, 2025 at 4:53 PM Nathan Bossart wrote:
>
> On Thu, Feb 20, 2025 at 07:35:32PM -0500, Melanie Plageman wrote:
> > Attache v7 doesn't cap the result for manual stats updating done with
> > relation_statistics_update().
>
> Unfortunately, this already needs a rebase...
Thanks! Attache
On Thu, Feb 20, 2025 at 07:35:32PM -0500, Melanie Plageman wrote:
> Attache v7 doesn't cap the result for manual stats updating done with
> relation_statistics_update().
Unfortunately, this already needs a rebase...
> I did, however, keep the cap for the
> places where vacuum/analyze/create index
On Wed, Feb 19, 2025 at 4:59 PM Nathan Bossart wrote:
>
> On Wed, Feb 19, 2025 at 04:36:05PM -0500, Melanie Plageman wrote:
> > This makes me think I should also not cap relallfrozen when using it
> > in relation_needs_vacanalyze(). There I cap it to relallvisible and
> > relallvisible is capped t
On Wed, Feb 19, 2025 at 04:36:05PM -0500, Melanie Plageman wrote:
> This makes me think I should also not cap relallfrozen when using it
> in relation_needs_vacanalyze(). There I cap it to relallvisible and
> relallvisible is capped to relpages. One of the ideas behind letting
> people modify these
On Mon, Feb 17, 2025 at 11:11 AM Nathan Bossart
wrote:
>
> On Fri, Feb 07, 2025 at 03:05:09PM -0600, Nathan Bossart wrote:
> > Okay, I'll actually look at the patches next...
Thanks for taking a look!
> I'm not sure I understand the reason for capping relallfrozen to
> relallvisible. From upthr
On Fri, Feb 07, 2025 at 03:05:09PM -0600, Nathan Bossart wrote:
> Okay, I'll actually look at the patches next...
Ugh, it's already been 10 days since I said that. A couple of thoughts on
0001:
I'm not sure I understand the reason for capping relallfrozen to
relallvisible. From upthread, I gath
On Fri, Feb 07, 2025 at 03:57:49PM -0500, Melanie Plageman wrote:
> As for relallfrozen, one of the justifications for adding it to
> pg_class is actually for the visibility it would provide. We have no
> way of knowing how many all-visible but not all-frozen pages there are
> on users' systems wit
On Fri, Feb 7, 2025 at 3:38 PM Nathan Bossart wrote:
>
> On Fri, Feb 07, 2025 at 02:21:07PM -0500, Melanie Plageman wrote:
> > On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart
> > wrote:
> >>
> >> Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
> >> unless this is an anti-
On Fri, Feb 07, 2025 at 02:21:07PM -0500, Melanie Plageman wrote:
> On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart
> wrote:
>> My first reaction is to question whether
>> it makes send to have two strategies for this sort of thing:
>> autovacuum_vacuum_max_threshold for updates/deletes and this f
On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart wrote:
>
> On Tue, Oct 22, 2024 at 03:12:53PM -0400, Melanie Plageman wrote:
> > By considering only the unfrozen portion of the table when calculating
> > the vacuum insert threshold, we can trigger vacuums more proactively
> > on insert-heavy tables
On Tue, Oct 22, 2024 at 03:12:53PM -0400, Melanie Plageman wrote:
> By considering only the unfrozen portion of the table when calculating
> the vacuum insert threshold, we can trigger vacuums more proactively
> on insert-heavy tables. This changes the definition of
> insert_scale_factor to a perce
On Thu, 06 Feb 2025 at 10:42, Melanie Plageman
wrote:
> Attached v6 is rebased over 306dc520b9dfd60
>
> On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu wrote:
>>
>> Hi Melanie Plageman
>>Thank you for working on this ,Actually, there were two patches
>> aimed at optimizing vacuum-triggered process
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 an
Attached v6 is rebased over 306dc520b9dfd60
On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu 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 committe
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
On Thu, Jan 16, 2025 at 5:50 PM Melanie Plageman
wrote:
>
> On Thu, Jan 16, 2025 at 4:43 PM Melanie Plageman
> wrote:
> >
> > On Fri, Oct 25, 2024 at 11:14 AM Melanie Plageman
> > wrote:
> > >
> > > I've done something similar to this in attached v2.
> >
> > This needed a rebase. See attached v4
On Thu, Jan 16, 2025 at 4:43 PM Melanie Plageman
wrote:
>
> On Fri, Oct 25, 2024 at 11:14 AM Melanie Plageman
> wrote:
> >
> > I've done something similar to this in attached v2.
>
> This needed a rebase. See attached v4.
Whoops -- docs didn't build. Attached v5.
- Melanie
v5-0002-Trigger-mor
On Fri, Oct 25, 2024 at 11:14 AM Melanie Plageman
wrote:
>
> I've done something similar to this in attached v2.
This needed a rebase. See attached v4.
- Melanie
v4-0001-Add-relallfrozen-to-pg_class.patch
Description: Binary data
v4-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patc
Thanks for the review!
On Thu, Oct 24, 2024 at 3:51 PM Greg Sabino Mullane wrote:
>
> I really appreciate all the work to make vacuum better. Anything that helps
> our problem of autovacuum not scaling well for large tables is a win.
>
> I'm not overly familiar with this part of the code base, b
I really appreciate all the work to make vacuum better. Anything that helps
our problem of autovacuum not scaling well for large tables is a win.
I'm not overly familiar with this part of the code base, but here are some
questions/ideas:
+ /*
+* Every block marked all-frozen in the
On Tue, Oct 22, 2024 at 3:12 PM Melanie Plageman
wrote:
>
> The attached patch does this.
I realized that I broke relation_statistics_update(). Attached v2 is fixed.
> I've estimated the unfrozen percentage of the table by adding a new
> field to pg_class, relallfrozen, which is updated in the s
Hi,
Because of the way autovacuum_vacuum_[insert]_scale_factor works,
autovacuums trigger less frequently as the relation gets larger.
See this math in relation_needs_vacanalyze:
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
For an insert-only table, nearly all
46 matches
Mail list logo
