Hi Melanie Plageman
Thank you for your reply. My calculation logic is to calculate the
proportion of active tuples. What I really want to know is whether this
algorithm is correct and acceptable. The way I wrote it is mainly to
express that I want to calculate the percentage of active tuples
On Fri, Mar 7, 2025 at 6:19 AM wenhui qiu wrote:
>
> Sorry ,A wrong version of debug pcnt_visibletuples ，kindly please check the
> v3 attachment
I looked at v3. I think I need more than the logging message to
understand your goal here. Could you explain the algorithm and why you
think it makes s
Sorry ,A wrong version of debug pcnt_visibletuples ，kindly please check the
v3 attachment
On Fri, Mar 7, 2025 at 5:37 PM wenhui qiu wrote:
> Hi
>The more accurate data I've found is tabentry->live_tuples; provides
> the second version
>
> #Here's a simple test I did
>
> test=# select count(*
Hi
The more accurate data I've found is tabentry->live_tuples; provides the
second version
#Here's a simple test I did
test=# select count(*) from join1;
count
-
2289001
(1 row)
test=# update join1 set name=md5(now()::text) where id<100;
UPDATE 1938700
test=# select 1938700/22
HI Nathan Bossart Melanie Plageman
Firstly, congratulations on the submission of this path:
https://commitfest.postgresql.org/patch/5320/
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
vacinsthresh = (float
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
ata, we just use reltuples, which is the
> existing behavior and should trigger vacuums less aggressively than if we
> _did_ have the data. That seems like the correct choice to me.
Yep.
- Melanie
From 33bf7404fc21be611ab9cf8faf0ef593eb9750a0 Mon Sep 17 00:00:00 2001
From: Melanie Plagema
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
introduction of relallfrozen.
Makes sense. Thanks Robert and Nathan. Attached v11 changes the docs
wording and is rebased.
- Melanie
From 957551f8987a090f043587202265210c2406572c Mon Sep 17 00:00:00 2001
From: Melanie Plageman
Date: Thu, 16 Jan 2025 16:31:55 -0500
Subject: [PATCH v11 2/2] Trigger more
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
cbb895ceba5acc8433695ef1e29bf Mon Sep 17 00:00:00 2001
From: Melanie Plageman
Date: Thu, 16 Jan 2025 16:31:55 -0500
Subject: [PATCH v10 2/2] Trigger more frequent autovacuums with relallfrozen
Calculate the insert threshold for triggering an autovacuum of a
relation based on the number of unfroze
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
HERE oid = 'stats_import.test'::regclass;
@@ -643,7 +663,7 @@ SELECT pg_restore_relation_stats(
'version', 15::integer,
'reltuples', '500'::real);
-SELECT relpages, reltuples, relallvisible
+SELECT relpages, reltuples, relallvisible, relallfr
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
x27;::integer);
-SELECT relpages, reltuples, relallvisible
+SELECT relpages, reltuples, relallvisible, relallfrozen
FROM pg_class
WHERE oid = 'stats_import.test'::regclass;
@@ -666,7 +687,7 @@ SELECT pg_restore_relation_stats(
'relpages', '16'::intege
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
envisioning using a manually updated relallvisible.
- Melanie
From 26dac7d2d9768c072913c911776d4f679bb21126 Mon Sep 17 00:00:00 2001
From: Melanie Plageman
Date: Thu, 16 Jan 2025 16:31:55 -0500
Subject: [PATCH v7 2/2] Trigger more frequent autovacuums with relallfrozen
Calculate the insert
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
: Melanie Plageman
Date: Thu, 16 Jan 2025 16:31:55 -0500
Subject: [PATCH v6 2/2] Trigger more frequent autovacuums with relallfrozen
Calculate the insert threshold for triggering an autovacuum of a
relation based on the number of unfrozen pages. By only considering the
"active" (unfrozen
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
ched v5.
- Melanie
v5-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch
Description: Binary data
v5-0001-Add-relallfrozen-to-pg_class.patch
Description: Binary data
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-autov
ething similar to this in attached v2.
> Again, I'm not clear under what circumstances will relallvisible > relpages?
I think this is mostly if someone manually updated the relation stats,
so we clamp it for safety.
- Melanie
From c2e29150e923f9782ce24a7a4e7d6f2d7445b543 Mon Sep 17 00:00:00 20
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
+++ b/src/include/commands/vacuum.h
@@ -329,6 +329,7 @@ extern void vac_update_relstats(Relation relation,
BlockNumber num_pages,
double num_tuples,
BlockNumber num_all_visible_pages,
+BlockNumber num_all_frozen_pages,
bool hasindex,
TransactionId frozenxid,
MultiXactId min
EATE INDEX ON history(id);
COMMIT;
pgbench \
--random-seed=0 \
--no-vacuum \
-M prepared \
-c 4 \
-j 4 \
-t 100 \
-R 27000 \
-f- <
v1-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch
Description: Binary data
v1-0001-Add-relallfrozen-to-pg_class.patch
Description: Binary data
51 matches
Mail list logo
