David Rowley writes:
> On Fri, 3 Apr 2020 at 04:46, Tom Lane wrote:
>> Concretely, I suggest the attached, which replaces the autovac disables
>> with adjusting partition boundaries so that the partitions contain
>> different numbers of rows.
> I've looked over this and I agree that it's a bette
On Fri, 3 Apr 2020 at 04:46, Tom Lane wrote:
>
> I wrote:
> > I'd be inclined to undo what you did in favor of initializing the
> > test tables to contain significantly different numbers of rows,
> > because that would (a) achieve plan stability more directly,
> > and (b) demonstrate that the plan
I wrote:
> I'd be inclined to undo what you did in favor of initializing the
> test tables to contain significantly different numbers of rows,
> because that would (a) achieve plan stability more directly,
> and (b) demonstrate that the planner is actually ordering the
> tables by cost correctly.
David Rowley writes:
> On Thu, 2 Apr 2020 at 16:13, Tom Lane wrote:
>> Quite :-(. While it's too early to declare victory, we've seen no
>> more failures of this ilk since 0936d1b6f, so it's sure looking like
>> autovacuum did have something to do with it.
> How about [1]? It seems related to m
On Wed, Apr 01, 2020 at 11:13:12PM -0400, Tom Lane wrote:
Dean Rasheed writes:
Yeah, that makes sense. I still can't see what might be causing those
failures. The tests that were doing an ALTER COLUMN and then expecting
to see the results of a non-analysed table ought to be fixed by
0936d1b6f,
On Thu, 2 Apr 2020 at 16:13, Tom Lane wrote:
>
> Dean Rasheed writes:
> > Yeah, that makes sense. I still can't see what might be causing those
> > failures. The tests that were doing an ALTER COLUMN and then expecting
> > to see the results of a non-analysed table ought to be fixed by
> > 0936d1
Dean Rasheed writes:
> Yeah, that makes sense. I still can't see what might be causing those
> failures. The tests that were doing an ALTER COLUMN and then expecting
> to see the results of a non-analysed table ought to be fixed by
> 0936d1b6f, but that doesn't match the buildfarm failures. Possib
On Tue, 31 Mar 2020 at 22:16, Tom Lane wrote:
>
> > Dean Rasheed writes:
> >> ...
> >> It looks to me as though the problem is that statext_store() needs to
> >> take its lock on pg_statistic_ext_data *before* searching for the
> >> stats tuple to update.
>
> > Hmm, yeah, that seems like clearly
I wrote:
> Dean Rasheed writes:
>> I had a go at reproducing this. I wasn't able to produce the reported
>> failure, but I can reliably produce an Assert failure that may be
>> related by doing a VACUUM FULL simultaneously with an ANALYZE that is
>> generating extended stats, which produces:
>> ..
Dean Rasheed writes:
> I had a go at reproducing this. I wasn't able to produce the reported
> failure, but I can reliably produce an Assert failure that may be
> related by doing a VACUUM FULL simultaneously with an ANALYZE that is
> generating extended stats, which produces:
> ...
> It looks to
On Tue, 31 Mar 2020 at 04:39, David Rowley wrote:
>
> On Sat, 28 Mar 2020 at 22:22, David Rowley wrote:
> > I'm unsure yet if this has caused an instability on lousyjack's run in
> > [1].
>
> pogona has just joined in on the fun [1], so, we're not out the woods
> on this yet. I'll start having a
On Sat, 28 Mar 2020 at 22:22, David Rowley wrote:
> I'm unsure yet if this has caused an instability on lousyjack's run in
> [1].
pogona has just joined in on the fun [1], so, we're not out the woods
on this yet. I'll start having a look at this in more detail.
[1]
https://buildfarm.postgresql.
On Mon, 30 Mar 2020 at 19:49, David Rowley wrote:
> I'll see if I can come up with some way to do this in a more
> deterministic way to determine which tables to add vacuums for, rather
> than waiting for and reacting post-failure.
I ended up running make installcheck on an instance with
autovacu
On Mon, 30 Mar 2020 at 17:57, Laurenz Albe wrote:
> How can it be that even after an explicit VACUUM, this patch can cause
> unstable regression test results?
I only added vacuums for mcv_lists. The problem with petalura [1] is
with the functional_dependencies table.
I'll see if I can come up wi
On Sat, 2020-03-28 at 19:21 +1300, David Rowley wrote:
> Thank you. Pushed.
Thanks for your efforts on this, and thanks for working on the fallout.
How can it be that even after an explicit VACUUM, this patch can cause
unstable regression test results?
Yours,
Laurenz Albe
Amit Kapila writes:
> On Mon, Mar 30, 2020 at 7:47 AM Tom Lane wrote:
>> But the ones that were seemingly due to that were intermittent,
>> so we'll have to watch for awhile.
> Today, stats_ext failed on petalura [1]. Can it be due to this? I
> have also committed a patch but immediately I don
On Mon, Mar 30, 2020 at 7:47 AM Tom Lane wrote:
>
> David Rowley writes:
> > I don't believe any of the current buildfarm failures can be
> > attributed to any of the recent changes to autovacuum, but I'll
> > continue to monitor the farm to see if anything is suspect.
>
> I agree none of the fai
David Rowley writes:
> I don't believe any of the current buildfarm failures can be
> attributed to any of the recent changes to autovacuum, but I'll
> continue to monitor the farm to see if anything is suspect.
I agree none of the failures I see right now are related to that
(there's some "No sp
On Sun, 29 Mar 2020 at 15:29, David Rowley wrote:
> I'm considering pushing the attached to try to get some confirmation
> that additional autovacuums are the issue. However, I'm not too sure
> it's a wise idea to as I can trigger an additional auto-vacuum and
> have these new tests fail with make
On Sun, 29 Mar 2020 at 10:30, David Rowley wrote:
>
> On Sun, 29 Mar 2020 at 06:26, Tom Lane wrote:
> >
> > David Rowley writes:
> > > On Sat, 28 Mar 2020 at 17:12, Laurenz Albe
> > > wrote:
> > >> In the light of that, I have no objections.
> >
> > > Thank you. Pushed.
> >
> > It seems like
On Sun, 29 Mar 2020 at 06:26, Tom Lane wrote:
>
> David Rowley writes:
> > On Sat, 28 Mar 2020 at 17:12, Laurenz Albe wrote:
> >> In the light of that, I have no objections.
>
> > Thank you. Pushed.
>
> It seems like this commit has resulted in some buildfarm instability:
>
> https://buildfarm.
David Rowley writes:
> On Sat, 28 Mar 2020 at 17:12, Laurenz Albe wrote:
>> In the light of that, I have no objections.
> Thank you. Pushed.
It seems like this commit has resulted in some buildfarm instability:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=lousyjack&dt=2020-03-28%20
On Sat, 28 Mar 2020 at 19:21, David Rowley wrote:
> Thank you. Pushed.
I'm unsure yet if this has caused an instability on lousyjack's run in
[1]. I see that table does have 30,000 rows inserted, so it does seem
probable that it may receive an autovacuum now when didn't before. I
did a quick loc
On Sat, 28 Mar 2020 at 17:12, Laurenz Albe wrote:
> In the light of that, I have no objections.
Thank you. Pushed.
David
On Sat, 2020-03-28 at 11:59 +1300, David Rowley wrote:
> On Fri, 27 Mar 2020 at 22:40, Laurenz Albe wrote:
> > The new meaning of -2 should be documented, other than that it looks
> > good to me.
>
> But the users don't need to know anything about -2. It's not possible
> to explicitly set the val
On Fri, 27 Mar 2020 at 22:40, Laurenz Albe wrote:
> The new meaning of -2 should be documented, other than that it looks
> good to me.
But the users don't need to know anything about -2. It's not possible
to explicitly set the value to -2. This is just the reset value of the
reloption which means
On Fri, 2020-03-27 at 10:18 +1300, David Rowley wrote:
> > > I believe there are enough options to disable insert-only vacuuming for
> > > an individual table:
> >
> > > - Set the threshold to 2147483647. True, that will not work for very
> > > large tables, but I think that there are few tables
On Fri, 27 Mar 2020 at 07:51, Andres Freund wrote:
>
> Hi,
>
> On 2020-03-26 10:12:39 +0100, Laurenz Albe wrote:
> > On Wed, 2020-03-25 at 23:19 +0300, Alexander Korotkov wrote:
> > I am reluctant to introduce new semantics like a reloption value of -2
> > to disable a feature in this patch right
Hi,
On 2020-03-26 10:12:39 +0100, Laurenz Albe wrote:
> On Wed, 2020-03-25 at 23:19 +0300, Alexander Korotkov wrote:
> I am reluctant to introduce new semantics like a reloption value of -2
> to disable a feature in this patch right before feature freeze.
>
> I believe there are enough options to
On Wed, 2020-03-25 at 23:19 +0300, Alexander Korotkov wrote:
> On Wed, Mar 25, 2020 at 10:26 PM Andres Freund wrote:
> > On 2020-03-25 11:05:21 -0500, Justin Pryzby wrote:
> > > Since we talked about how scale_factor can be used to effectively disable
> > > this
> > > new feature, I thought that
On Wed, Mar 25, 2020 at 10:26 PM Andres Freund wrote:
> On 2020-03-25 11:05:21 -0500, Justin Pryzby wrote:
> > Since we talked about how scale_factor can be used to effectively disable
> > this
> > new feature, I thought that scale=100 was too small and suggesed 1e10 (same
> > as
> > max for vac
Hi,
On 2020-03-25 11:05:21 -0500, Justin Pryzby wrote:
> Since we talked about how scale_factor can be used to effectively disable this
> new feature, I thought that scale=100 was too small and suggesed 1e10 (same as
> max for vacuum_cleanup_index_scale_factor since 4d54543ef). That should allow
On Wed, Mar 25, 2020 at 12:46:52PM -0300, Alvaro Herrera wrote:
> On 2020-Mar-25, Justin Pryzby wrote:
>
> > Maybe in the docs you can write this with thousands separators: 10,000,000
> >
> > It looks like the GUC uses scale factor max=1e10, but the relopt is still
> > max=100, which means it's l
On 2020-Mar-25, Justin Pryzby wrote:
> Maybe in the docs you can write this with thousands separators: 10,000,000
>
> It looks like the GUC uses scale factor max=1e10, but the relopt is still
> max=100, which means it's less possible to disable for a single rel.
I have paid no attention to this
On Mon, Mar 23, 2020 at 02:27:29PM +0100, Laurenz Albe wrote:
> Here is version 10 of the patch, which uses a scale factor of 0.2.
Thanks
> Any table that has received more inserts since it was
> last vacuumed (and that is not vacuumed for another
> reason) will be autovacuumed.
Since this vacuu
On Mon, 2020-03-23 at 14:27 +0100, Laurenz Albe wrote:
> Here is version 10 of the patch, which uses a scale factor of 0.2.
This patch should be what everybody can live with.
It would be good if we can get at least that committed before feature freeze.
Yours,
Laurenz Albe
On Fri, 2020-03-20 at 14:43 +0100, Laurenz Albe wrote:
> I.e. with the default settings we will perform a whole-index scan
> > (without visibility map or such) after every 10% growth of the
> > table. Which means that, even if the visibility map prevents repeated
> > tables accesses, increasing the
On Thu, 2020-03-19 at 23:20 -0700, Andres Freund wrote:
> I am not sure about b). In my mind, the objective is not to prevent
> > anti-wraparound vacuums, but to see that they have less work to do,
> > because previous autovacuum runs already have frozen anything older than
> > vacuum_freeze_min_a
On Fri, 20 Mar 2020 at 15:20, Andres Freund wrote:
>
> Hi,
>
> On 2020-03-19 06:45:48 +0100, Laurenz Albe wrote:
> > On Tue, 2020-03-17 at 18:02 -0700, Andres Freund wrote:
> > > I don't think a default scale factor of 0 is going to be ok. For
> > > large-ish tables this will basically cause perma
Hi,
On 2020-03-20 06:59:57 +0100, Laurenz Albe wrote:
> On Thu, 2020-03-19 at 15:17 -0700, Andres Freund wrote:
> > I am *VERY* doubtful that the attempt of using a large threshold, and a
> > tiny scale factor, is going to work out well. I'm not confident enough
> > in my gut feeling to full throa
Hi,
On 2020-03-19 06:45:48 +0100, Laurenz Albe wrote:
> On Tue, 2020-03-17 at 18:02 -0700, Andres Freund wrote:
> > I don't think a default scale factor of 0 is going to be ok. For
> > large-ish tables this will basically cause permanent vacuums. And it'll
> > sometimes trigger for tables that act
On Thu, 2020-03-19 at 14:38 -0700, Andres Freund wrote:
> > I am not sure about b). In my mind, the objective is not to prevent
> > anti-wraparound vacuums, but to see that they have less work to do,
> > because previous autovacuum runs already have frozen anything older than
> > vacuum_freeze_min
On Thu, 2020-03-19 at 15:17 -0700, Andres Freund wrote:
> I am doubtful it should be committed with the current settings. See below.
>
> > From 3ba4b572d82969bbb2af787d1bccc72f417ad3a0 Mon Sep 17 00:00:00 2001
> > From: Laurenz Albe
> > Date: Thu, 19 Mar 2020 20:26:43 +0100
> > Subject: [PATCH] A
Hi,
On 2020-03-20 15:05:03 +1300, David Rowley wrote:
> On Fri, 20 Mar 2020 at 11:17, Andres Freund wrote:
> > I think there's too much "reinventing" autovacuum scheduling in a
> > "local" insert-only manner happening in this thread. And as far as I can
> > tell additionally only looking at a som
On Fri, 20 Mar 2020 at 11:17, Andres Freund wrote:
> I think there's too much "reinventing" autovacuum scheduling in a
> "local" insert-only manner happening in this thread. And as far as I can
> tell additionally only looking at a somewhat narrow slice of insert only
> workloads.
I understand yo
Hi,
On 2020-03-20 01:11:23 +0300, Darafei "Komяpa" Praliaskouski wrote:
> > > According to my reckoning, that is the remaining objection to the patch
> > > as it is (with ordinary freezing behavior).
> > >
> > > How about a scale_factor od 0.005? That will be high enough for large
> > > tables, w
Hi,
On 2020-03-19 20:47:40 +0100, Laurenz Albe wrote:
> On Thu, 2020-03-19 at 21:39 +1300, David Rowley wrote:
> > I've attached a small fix which I'd like to apply to your v8 patch.
> > With that, and pending one final look, I'd like to push this during my
> > Monday (New Zealand time). So if an
> > According to my reckoning, that is the remaining objection to the patch
> > as it is (with ordinary freezing behavior).
> >
> > How about a scale_factor od 0.005? That will be high enough for large
> > tables, which seem to be the main concern here.
>
> Seems low on a first blush. On a large-i
Hi,
On 2020-03-19 06:45:48 +0100, Laurenz Albe wrote:
> On Tue, 2020-03-17 at 18:02 -0700, Andres Freund wrote:
> > I don't think a default scale factor of 0 is going to be ok. For
> > large-ish tables this will basically cause permanent vacuums. And it'll
> > sometimes trigger for tables that act
On Thu, 2020-03-19 at 21:39 +1300, David Rowley wrote:
> > According to my reckoning, that is the remaining objection to the patch
> > as it is (with ordinary freezing behavior).
> >
> > How about a scale_factor od 0.005? That will be high enough for large
> > tables, which seem to be the main con
On Thu, Mar 19, 2020 at 09:52:11PM +1300, David Rowley wrote:
> On Thu, 19 Mar 2020 at 19:07, Justin Pryzby wrote:
> >
> > On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> > > On 2020-03-13 13:44:42 -0500, Justin Pryzby wrote:
> > > > Having now played with the patch, I'll suggest
On Thu, 19 Mar 2020 at 19:07, Justin Pryzby wrote:
>
> On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> > On 2020-03-13 13:44:42 -0500, Justin Pryzby wrote:
> > > Having now played with the patch, I'll suggest that 1000 is too high a
> > > threshold. If autovacuum runs without
On Thu, 19 Mar 2020 at 18:45, Laurenz Albe wrote:
>
> On Tue, 2020-03-17 at 18:02 -0700, Andres Freund wrote:
> > I don't think a default scale factor of 0 is going to be ok. For
> > large-ish tables this will basically cause permanent vacuums. And it'll
> > sometimes trigger for tables that actua
On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> On 2020-03-13 13:44:42 -0500, Justin Pryzby wrote:
> > Having now played with the patch, I'll suggest that 1000 is too high a
> > threshold. If autovacuum runs without FREEZE, I don't see why it couldn't
> > be
> > much lower (1
On Tue, 2020-03-17 at 18:02 -0700, Andres Freund wrote:
> I don't think a default scale factor of 0 is going to be ok. For
> large-ish tables this will basically cause permanent vacuums. And it'll
> sometimes trigger for tables that actually coped well so far. 10 million
> rows could be a few secon
On Tue, 2020-03-17 at 17:26 -0700, Andres Freund wrote:
> On 2020-03-17 01:14:02 +0100, Laurenz Albe wrote:
> > lazy_check_needs_freeze() is only called for an aggressive vacuum, which
> > this isn't.
>
> Hm? I mean some of these will be aggressive vacuums, because it's older
> than vacuum_freeze_
On Wed, Mar 18, 2020 at 1:08 PM Andres Freund wrote:
>
> Hi,
>
> On 2020-03-17 21:58:53 -0400, James Coleman wrote:
> > On Tue, Mar 17, 2020 at 9:03 PM Andres Freund wrote:
> > >
> > > Hi,
> > >
> > > On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > > > > I think Andres was thinking this wou
On Tue, Mar 17, 2020 at 11:37 PM Justin Pryzby wrote:
>
> On Tue, Mar 17, 2020 at 09:58:53PM -0400, James Coleman wrote:
> > On Tue, Mar 17, 2020 at 9:03 PM Andres Freund wrote:
> > >
> > > On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > > > > I think Andres was thinking this would maybe be
Hi,
On 2020-03-17 21:58:53 -0400, James Coleman wrote:
> On Tue, Mar 17, 2020 at 9:03 PM Andres Freund wrote:
> >
> > Hi,
> >
> > On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > > > I think Andres was thinking this would maybe be an optimization
> > > > independent of
> > > > is_insert_onl
On Tue, Mar 17, 2020 at 09:58:53PM -0400, James Coleman wrote:
> On Tue, Mar 17, 2020 at 9:03 PM Andres Freund wrote:
> >
> > On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > > > I think Andres was thinking this would maybe be an optimization
> > > > independent of
> > > > is_insert_only (?)
On Tue, Mar 17, 2020 at 9:03 PM Andres Freund wrote:
>
> Hi,
>
> On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > > I think Andres was thinking this would maybe be an optimization
> > > independent of
> > > is_insert_only (?)
> >
> > I wasn't sure.
>
> I'm not sure myself - but I'm doubtful
Hi,
On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote:
> > I think Andres was thinking this would maybe be an optimization independent
> > of
> > is_insert_only (?)
>
> I wasn't sure.
I'm not sure myself - but I'm doubtful that using a 0 min age by default
will be ok.
I was trying to say (in a l
Hi,
On 2020-03-17 01:14:02 +0100, Laurenz Albe wrote:
> lazy_check_needs_freeze() is only called for an aggressive vacuum, which
> this isn't.
Hm? I mean some of these will be aggressive vacuums, because it's older
than vacuum_freeze_table_age? And the lower age limit would make that
potentially
On Mon, Mar 16, 2020 at 07:47:13AM -0500, Justin Pryzby wrote:
> Normally, when someone complains about bad plan related to no index-onlyscan,
> we tell them to run vacuum, and if that helps, then ALTER TABLE .. SET
> (autovacuum_vacuum_scale_factor=0.005).
>
> If there's two thresholds (4 GUCs an
On Tue, 2020-03-17 at 16:34 -0500, Justin Pryzby wrote:
> > > > Now we insert m number tuples (which are live).
>
> .. but not yet counted in reltuples.
Thanks for pointing out my mistake.
Here is another patch, no changes except setting the upper limit
for autovacuum_vacuum_insert_scale_factor
On Tue, Mar 17, 2020 at 10:22:44PM +0100, Laurenz Albe wrote:
> On Tue, 2020-03-17 at 16:07 -0500, Justin Pryzby wrote:
> > > Assume a scale factor >= 1, for example 2, and n live tuples.
> > > The table has just been vacuumed.
> > >
> > > Now we insert m number tuples (which are live).
.. but no
On Tue, 2020-03-17 at 16:07 -0500, Justin Pryzby wrote:
> > Assume a scale factor >= 1, for example 2, and n live tuples.
> > The table has just been vacuumed.
> >
> > Now we insert m number tuples (which are live).
> >
> > Then the condition
> >
> >threshold + scale_factor * live_tuples < n
On Tue, Mar 17, 2020 at 10:01:15PM +0100, Laurenz Albe wrote:
> On Tue, 2020-03-17 at 14:56 -0500, Justin Pryzby wrote:
> > I still suggest scale_factor maximum of 1e10, like
> > 4d54543efa5eb074ead4d0fadb2af4161c943044
> >
> > Which alows more effectively disabling it than a factor of 100, which
On Tue, 2020-03-17 at 14:56 -0500, Justin Pryzby wrote:
> I still suggest scale_factor maximum of 1e10, like
> 4d54543efa5eb074ead4d0fadb2af4161c943044
>
> Which alows more effectively disabling it than a factor of 100, which would
> progress like: ~1, 1e2, 1e4, 1e6, 1e8, 1e10, ..
>
> I don't thi
On Tue, Mar 17, 2020 at 08:42:07PM +0100, Laurenz Albe wrote:
> Also, since aggressive^H^H^H^H^H^H^H^H^H^Hproactive freezing seems to be a
> performance problem in some cases (pages with UPDATEs and DELETEs in otherwise
> INSERT-mostly tables), I have done away with the whole freezing thing,
> whic
On Tue, 2020-03-17 at 10:24 -0500, Justin Pryzby wrote:
> > --- a/src/backend/access/heap/vacuumlazy.c
> > +++ b/src/backend/access/heap/vacuumlazy.c
> > @@ -1388,17 +1388,26 @@ lazy_scan_heap(Relation onerel, VacuumParams
> > *params, LVRelStats *vacrelstats,
> >else
> >
On Tue, Mar 17, 2020 at 01:14:02AM +0100, Laurenz Albe wrote:
> lazy_check_needs_freeze() is only called for an aggressive vacuum, which
> this isn't.
> --- a/src/backend/access/heap/vacuumlazy.c
> +++ b/src/backend/access/heap/vacuumlazy.c
> @@ -1388,17 +1388,26 @@ lazy_scan_heap(Relation onerel,
On Mon, 2020-03-16 at 14:34 -0700, Andres Freund wrote:
> > > In particularl, I think it'd make sense to *not* have a lower freezing
> > > horizon for insert vacuums (because it *will* cause problems), but if
> > > the page is dirty anyway, then do the freezing even if freeze_min_age
> > > etc woul
Hi,
On 2020-03-16 22:25:11 +0100, Laurenz Albe wrote:
> On Mon, 2020-03-16 at 13:13 -0700, Andres Freund wrote:
> > > Freezing tuples is the point of this patch.
> >
> > Sure. But not hurting existing installation is also a goal of the
> > patch. Since this is introducing potentially significant
On Mon, 2020-03-16 at 16:07 -0500, Justin Pryzby wrote:
> Best practice is to vacuum following bulk load.
Yes.
> If it's a bulk load, then I think it's okay to assume it was vacuumed,
No. This patch is there precisely because too many people don't know
that they should vacuum their table after
On Mon, 2020-03-16 at 13:13 -0700, Andres Freund wrote:
> > Freezing tuples is the point of this patch.
>
> Sure. But not hurting existing installation is also a goal of the
> patch. Since this is introducing potentially significant performance
> downsides, I think it's good to be a bit conservati
On Mon, Mar 16, 2020 at 08:49:43PM +0100, Laurenz Albe wrote:
> On Mon, 2020-03-16 at 07:47 -0500, Justin Pryzby wrote:
> > It seems to me that the easy thing to do is to implement this initially
> > without
> > FREEZE (which is controlled by vacuum_freeze_table_age), and defer until
> > July/v14
Hi,
On 2020-03-16 20:49:43 +0100, Laurenz Albe wrote:
> On Mon, 2020-03-16 at 07:47 -0500, Justin Pryzby wrote:
> > It seems to me that the easy thing to do is to implement this initially
> > without
> > FREEZE (which is controlled by vacuum_freeze_table_age), and defer until
> > July/v14 further
On Mon, 2020-03-16 at 07:47 -0500, Justin Pryzby wrote:
> It seems to me that the easy thing to do is to implement this initially
> without
> FREEZE (which is controlled by vacuum_freeze_table_age), and defer until
> July/v14 further discussion and implementation of another GUC/relopt for
> autova
Hi,
On 2020-03-13 19:10:00 -0500, Justin Pryzby wrote:
> On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> > > |One disadvantage of decreasing vacuum_freeze_min_age is that it might
> > > cause
> > > |VACUUM to do useless work: freezing a row version is a waste of time if
> > > th
On Mon, Mar 16, 2020 at 12:53:43PM +0900, Masahiko Sawada wrote:
> There is already a consensus on introducing new 2 parameters, but as
> the second idea I'd like to add one (or two) GUC(s) to my suggestion,
> say autovacuum_vacuum_freeze_insert_ratio; this parameter is the ratio
> of the number o
On Mon, 2020-03-16 at 12:53 +0900, Masahiko Sawada wrote:
> There is already a consensus on introducing new 2 parameters, but as
> the second idea I'd like to add one (or two) GUC(s) to my suggestion,
> say autovacuum_vacuum_freeze_insert_ratio; this parameter is the ratio
> of the number of insert
On Fri, Mar 13, 2020 at 10:48:27PM +0100, Laurenz Albe wrote:
> On Fri, 2020-03-13 at 13:44 -0500, Justin Pryzby wrote:
> > Possible it would be better to run VACUUM *without* freeze_min_age=0 ? (I
> > get
> > confused and have to spend 20min re-reading the vacuum GUC docs every time I
> > deal w
On Fri, 13 Mar 2020 at 05:11, David Rowley wrote:
>
> On Fri, 13 Mar 2020 at 01:43, Masahiko Sawada
> wrote:
> >
> > On Thu, 12 Mar 2020 at 16:28, David Rowley wrote:
> > > Laurenz highlighted a seemingly very valid reason that the current
> > > GUCs cannot be reused. Namely, say the table has 1
On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> > Having now played with the patch, I'll suggest that 1000 is too high a
> > threshold. If autovacuum runs without FREEZE, I don't see why it couldn't
> > be
> > much lower (10?) or use (0.2 * n_ins + 50) like the other auto
On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> > |One disadvantage of decreasing vacuum_freeze_min_age is that it might cause
> > |VACUUM to do useless work: freezing a row version is a waste of time if
> > the row
> > |is modified soon thereafter (causing it to acquire a new XID
On Fri, 2020-03-13 at 13:44 -0500, Justin Pryzby wrote:
> Possible it would be better to run VACUUM *without* freeze_min_age=0 ? (I get
> confused and have to spend 20min re-reading the vacuum GUC docs every time I
> deal with this stuff, so maybe I'm off).
>
> As I understand, the initial motiva
Hi,
On 2020-03-13 13:44:42 -0500, Justin Pryzby wrote:
> As I understand, the initial motivation of this patch was to avoid disruptive
> anti-wraparound vacuums on insert-only table. But if vacuum were triggered at
> all, it would freeze the oldest tuples, which is all that's needed; especially
>
On Tue, Mar 10, 2020 at 01:53:42PM +1300, David Rowley wrote:
> 2. Perhaps the documentation in maintenance.sgml should mention that
> the table will be vacuumed with the equivalent of having
> vacuum_freeze_min_age = 0, instead of:
>
> "Such a vacuum will aggressively freeze tuples."
>
> aggress
On Fri, 2020-03-13 at 07:00 -0500, Justin Pryzby wrote:
> > 2. The new feature can be completely disabled. This might be very
> > useful for people who suffer from auto-vacuum starvation.
>
> > Yes, but in particular so it can be completely disabled easily.
>
> How is it disabled ? By setting sc
On Fri, 2020-03-13 at 12:05 +0300, Darafei "Komяpa" Praliaskouski wrote:
> 1. introduce no new parameters and trigger autovacuum if the number
> > of inserts exceeds the regular vacuum threshold.
> >
> > 2. introduce the new parameters with high base threshold and zero scale
> > factor.
>
>
On Wed, Mar 11, 2020 at 10:32:47AM +1300, David Rowley wrote:
> 2. The new feature can be completely disabled. This might be very
> useful for people who suffer from auto-vacuum starvation.
On Thu, Mar 12, 2020 at 08:28:05PM +1300, David Rowley wrote:
> Yes, but in particular so it can be complete
On Fri, Mar 13, 2020 at 3:19 AM Laurenz Albe wrote:
>
> On Fri, 2020-03-13 at 09:10 +1300, David Rowley wrote:
> > So you're suggesting we drive the insert-vacuums from existing
> > scale_factor and threshold? What about the 1 billion row table
> > example above?
>
> I am still not 100% certain i
On Fri, 2020-03-13 at 09:10 +1300, David Rowley wrote:
> So you're suggesting we drive the insert-vacuums from existing
> scale_factor and threshold? What about the 1 billion row table
> example above?
I am still not 100% certain if that is really realistic.
Transactions that insert only a single
On Fri, 13 Mar 2020 at 01:43, Masahiko Sawada
wrote:
>
> On Thu, 12 Mar 2020 at 16:28, David Rowley wrote:
> > Laurenz highlighted a seemingly very valid reason that the current
> > GUCs cannot be reused. Namely, say the table has 1 billion rows, if we
> > use the current scale factor of 0.2, the
On Thu, 12 Mar 2020 at 16:28, David Rowley wrote:
>
> On Thu, 12 Mar 2020 at 19:50, Masahiko Sawada
> wrote:
> > The reason why you want to add new GUC parameters is to use different
> > default values for insert-update table case and insert-only table
> > case?
>
> Yes, but in particular so it c
On Thu, 12 Mar 2020 at 19:50, Masahiko Sawada
wrote:
> The reason why you want to add new GUC parameters is to use different
> default values for insert-update table case and insert-only table
> case?
Yes, but in particular so it can be completely disabled easily.
> I think I understand the pros
On Thu, 12 Mar 2020 at 14:38, Laurenz Albe wrote:
>
> On Thu, 2020-03-12 at 17:47 +1300, David Rowley wrote:
> > I'm starting to think that we should set the scale_factor to something
> > like 0.3 and the threshold to 50. Is anyone strongly against that? Or
> > Laurenz, are you really set on the
On Thu, 12 Mar 2020 at 18:38, Laurenz Albe wrote:
>
> On Thu, 2020-03-12 at 17:47 +1300, David Rowley wrote:
> > Laurenz, are you really set on the 10 million threshold?
>
> These values are almost the same as "autovacuum_vacuum_scale_factor"
> and "autovacuum_vacuum_threshold", so you actually ag
On Thu, 2020-03-12 at 17:47 +1300, David Rowley wrote:
> I'm starting to think that we should set the scale_factor to something
> like 0.3 and the threshold to 50. Is anyone strongly against that? Or
> Laurenz, are you really set on the 10 million threshold?
These values are almost the same as "a
1 - 100 of 158 matches
Mail list logo
