public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 06/16] meson: prereq: add output path arg in generate-lwlocknames.pl
11+ messages / 6 participants
[nested] [flat]
* [PATCH v5 06/16] meson: prereq: add output path arg in generate-lwlocknames.pl
@ 2021-03-10 09:43 Andres Freund <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Andres Freund @ 2021-03-10 09:43 UTC (permalink / raw)
---
src/backend/storage/lmgr/generate-lwlocknames.pl | 14 ++++++++++----
1 file changed, 10 insertions(+), 4 deletions(-)
diff --git a/src/backend/storage/lmgr/generate-lwlocknames.pl b/src/backend/storage/lmgr/generate-lwlocknames.pl
index 8a44946594d..315156b29f1 100644
--- a/src/backend/storage/lmgr/generate-lwlocknames.pl
+++ b/src/backend/storage/lmgr/generate-lwlocknames.pl
@@ -5,15 +5,21 @@
use strict;
use warnings;
+use Getopt::Long;
+
+my $output_path = '.';
my $lastlockidx = -1;
my $continue = "\n";
+GetOptions(
+ 'output:s' => \$output_path);
+
open my $lwlocknames, '<', $ARGV[0] or die;
# Include PID in suffix in case parallel make runs this multiple times.
-my $htmp = "lwlocknames.h.tmp$$";
-my $ctmp = "lwlocknames.c.tmp$$";
+my $htmp = "$output_path/lwlocknames.h.tmp$$";
+my $ctmp = "$output_path/lwlocknames.c.tmp$$";
open my $h, '>', $htmp or die "Could not open $htmp: $!";
open my $c, '>', $ctmp or die "Could not open $ctmp: $!";
@@ -65,7 +71,7 @@ printf $h "#define NUM_INDIVIDUAL_LWLOCKS %s\n", $lastlockidx + 1;
close $h;
close $c;
-rename($htmp, 'lwlocknames.h') || die "rename: $htmp: $!";
-rename($ctmp, 'lwlocknames.c') || die "rename: $ctmp: $!";
+rename($htmp, "$output_path/lwlocknames.h") || die "rename: $htmp to $output_path/lwlocknames.h: $!";
+rename($ctmp, "$output_path/lwlocknames.c") || die "rename: $ctmp: $!";
close $lwlocknames;
--
2.23.0.385.gbc12974a89
--q7iu5jwpuq7sbl3z
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v5-0007-meson-prereq-add-src-tools-gen_versioning_script..patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v3 06/17] meson: prereq: add output path arg in generate-lwlocknames.pl
@ 2021-03-10 09:43 Andres Freund <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Andres Freund @ 2021-03-10 09:43 UTC (permalink / raw)
---
src/backend/storage/lmgr/generate-lwlocknames.pl | 14 ++++++++++----
1 file changed, 10 insertions(+), 4 deletions(-)
diff --git a/src/backend/storage/lmgr/generate-lwlocknames.pl b/src/backend/storage/lmgr/generate-lwlocknames.pl
index 8a44946594d..315156b29f1 100644
--- a/src/backend/storage/lmgr/generate-lwlocknames.pl
+++ b/src/backend/storage/lmgr/generate-lwlocknames.pl
@@ -5,15 +5,21 @@
use strict;
use warnings;
+use Getopt::Long;
+
+my $output_path = '.';
my $lastlockidx = -1;
my $continue = "\n";
+GetOptions(
+ 'output:s' => \$output_path);
+
open my $lwlocknames, '<', $ARGV[0] or die;
# Include PID in suffix in case parallel make runs this multiple times.
-my $htmp = "lwlocknames.h.tmp$$";
-my $ctmp = "lwlocknames.c.tmp$$";
+my $htmp = "$output_path/lwlocknames.h.tmp$$";
+my $ctmp = "$output_path/lwlocknames.c.tmp$$";
open my $h, '>', $htmp or die "Could not open $htmp: $!";
open my $c, '>', $ctmp or die "Could not open $ctmp: $!";
@@ -65,7 +71,7 @@ printf $h "#define NUM_INDIVIDUAL_LWLOCKS %s\n", $lastlockidx + 1;
close $h;
close $c;
-rename($htmp, 'lwlocknames.h') || die "rename: $htmp: $!";
-rename($ctmp, 'lwlocknames.c') || die "rename: $ctmp: $!";
+rename($htmp, "$output_path/lwlocknames.h") || die "rename: $htmp to $output_path/lwlocknames.h: $!";
+rename($ctmp, "$output_path/lwlocknames.c") || die "rename: $ctmp: $!";
close $lwlocknames;
--
2.23.0.385.gbc12974a89
--qozqt6hocm2wibt2
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v3-0007-meson-prereq-add-src-tools-gen_versioning_script..patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v10 05/16] meson: prereq: Add output path arg in generate-lwlocknames.pl
@ 2022-01-20 07:36 Andres Freund <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Andres Freund @ 2022-01-20 07:36 UTC (permalink / raw)
---
src/backend/storage/lmgr/generate-lwlocknames.pl | 14 ++++++++++----
src/tools/msvc/Solution.pm | 5 ++---
2 files changed, 12 insertions(+), 7 deletions(-)
diff --git a/src/backend/storage/lmgr/generate-lwlocknames.pl b/src/backend/storage/lmgr/generate-lwlocknames.pl
index 4565ff8760e..5fadc4503bf 100644
--- a/src/backend/storage/lmgr/generate-lwlocknames.pl
+++ b/src/backend/storage/lmgr/generate-lwlocknames.pl
@@ -5,15 +5,21 @@
use strict;
use warnings;
+use Getopt::Long;
+
+my $output_path = '.';
my $lastlockidx = -1;
my $continue = "\n";
+GetOptions(
+ 'outdir:s' => \$output_path);
+
open my $lwlocknames, '<', $ARGV[0] or die;
# Include PID in suffix in case parallel make runs this multiple times.
-my $htmp = "lwlocknames.h.tmp$$";
-my $ctmp = "lwlocknames.c.tmp$$";
+my $htmp = "$output_path/lwlocknames.h.tmp$$";
+my $ctmp = "$output_path/lwlocknames.c.tmp$$";
open my $h, '>', $htmp or die "Could not open $htmp: $!";
open my $c, '>', $ctmp or die "Could not open $ctmp: $!";
@@ -65,7 +71,7 @@ printf $h "#define NUM_INDIVIDUAL_LWLOCKS %s\n", $lastlockidx + 1;
close $h;
close $c;
-rename($htmp, 'lwlocknames.h') || die "rename: $htmp: $!";
-rename($ctmp, 'lwlocknames.c') || die "rename: $ctmp: $!";
+rename($htmp, "$output_path/lwlocknames.h") || die "rename: $htmp to $output_path/lwlocknames.h: $!";
+rename($ctmp, "$output_path/lwlocknames.c") || die "rename: $ctmp: $!";
close $lwlocknames;
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index fcbc1a20772..664fcea0d87 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -624,9 +624,8 @@ sub GenerateFiles
'src/backend/storage/lmgr/lwlocknames.txt'))
{
print "Generating lwlocknames.c and lwlocknames.h...\n";
- chdir('src/backend/storage/lmgr');
- system('perl generate-lwlocknames.pl lwlocknames.txt');
- chdir('../../../..');
+ my $lmgr = 'src/backend/storage/lmgr';
+ system("perl $lmgr/generate-lwlocknames.pl --outdir $lmgr $lmgr/lwlocknames.txt");
}
if (IsNewer(
'src/include/storage/lwlocknames.h',
--
2.37.0.3.g30cc8d0f14
--mqd6wwweo5bfrq2e
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v10-0006-meson-prereq-generate-errcodes.pl-Accept-output-.patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-04-26 02:24 Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-04-26 08:08 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
2024-04-26 13:27 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
0 siblings, 3 replies; 11+ messages in thread
From: Laurenz Albe @ 2024-04-26 02:24 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Frédéric Yhuel <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
> I believe that the underlying problem here can be summarized in this
> way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
> mean that I'm OK with 2TB of bloat in my 10TB table. One reason for
> this is simply that I can afford to waste 2MB much more easily than I
> can afford to waste 2TB -- and that applies both on disk and in
> memory.
I don't find that convincing. Why are 2TB of wasted space in a 10TB
table worse than 2TB of wasted space in 100 tables of 100GB each?
> Another reason, at least in existing releases, is that at some
> point index vacuuming hits a wall because we run out of space for dead
> tuples. We *most definitely* want to do index vacuuming before we get
> to the point where we're going to have to do multiple cycles of index
> vacuuming.
That is more convincing. But do we need a GUC for that? What about
making a table eligible for autovacuum as soon as the number of dead
tuples reaches 90% of what you can hold in "autovacuum_work_mem"?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
@ 2024-04-26 07:35 ` Frédéric Yhuel <[email protected]>
2024-04-26 08:18 ` Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2 siblings, 1 reply; 11+ messages in thread
From: Frédéric Yhuel @ 2024-04-26 07:35 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; +Cc: Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
Le 26/04/2024 à 04:24, Laurenz Albe a écrit :
> On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
>> I believe that the underlying problem here can be summarized in this
>> way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
>> mean that I'm OK with 2TB of bloat in my 10TB table. One reason for
>> this is simply that I can afford to waste 2MB much more easily than I
>> can afford to waste 2TB -- and that applies both on disk and in
>> memory.
>
> I don't find that convincing. Why are 2TB of wasted space in a 10TB
> table worse than 2TB of wasted space in 100 tables of 100GB each?
>
Good point, but another way of summarizing the problem would be that the
autovacuum_*_scale_factor parameters work well as long as we have a more
or less evenly distributed access pattern in the table.
Suppose my very large table gets updated only for its 1% most recent
rows. We probably want to decrease autovacuum_analyze_scale_factor and
autovacuum_vacuum_scale_factor for this one.
Partitioning would be a good solution, but IMHO postgres should be able
to handle this case anyway, ideally without per-table configuration.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
@ 2024-04-26 08:18 ` Laurenz Albe <[email protected]>
2024-04-26 08:43 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2024-04-26 08:18 UTC (permalink / raw)
To: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; +Cc: Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Fri, 2024-04-26 at 09:35 +0200, Frédéric Yhuel wrote:
>
> Le 26/04/2024 à 04:24, Laurenz Albe a écrit :
> > On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
> > > I believe that the underlying problem here can be summarized in this
> > > way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
> > > mean that I'm OK with 2TB of bloat in my 10TB table. One reason for
> > > this is simply that I can afford to waste 2MB much more easily than I
> > > can afford to waste 2TB -- and that applies both on disk and in
> > > memory.
> >
> > I don't find that convincing. Why are 2TB of wasted space in a 10TB
> > table worse than 2TB of wasted space in 100 tables of 100GB each?
>
> Good point, but another way of summarizing the problem would be that the
> autovacuum_*_scale_factor parameters work well as long as we have a more
> or less evenly distributed access pattern in the table.
>
> Suppose my very large table gets updated only for its 1% most recent
> rows. We probably want to decrease autovacuum_analyze_scale_factor and
> autovacuum_vacuum_scale_factor for this one.
>
> Partitioning would be a good solution, but IMHO postgres should be able
> to handle this case anyway, ideally without per-table configuration.
I agree that you may well want autovacuum and autoanalyze treat your large
table differently from your small tables.
But I am reluctant to accept even more autovacuum GUCs. It's not like
we don't have enough of them, rather the opposite. You can slap on more
GUCs to treat more special cases, but we will never reach the goal of
having a default that will make everybody happy.
I believe that the defaults should work well in moderately sized databases
with moderate usage characteristics. If you have large tables or a high
number of transactions per second, you can be expected to make the effort
and adjust the settings for your case. Adding more GUCs makes life *harder*
for the users who are trying to understand and configure how autovacuum works.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-04-26 08:18 ` Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
@ 2024-04-26 08:43 ` Michael Banck <[email protected]>
2024-04-26 13:22 ` Re: New GUC autovacuum_max_threshold ? Joe Conway <[email protected]>
2024-04-26 13:37 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Michael Banck @ 2024-04-26 08:43 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
Hi,
On Fri, Apr 26, 2024 at 10:18:00AM +0200, Laurenz Albe wrote:
> On Fri, 2024-04-26 at 09:35 +0200, Frédéric Yhuel wrote:
> > Le 26/04/2024 à 04:24, Laurenz Albe a écrit :
> > > On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
> > > > I believe that the underlying problem here can be summarized in this
> > > > way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
> > > > mean that I'm OK with 2TB of bloat in my 10TB table. One reason for
> > > > this is simply that I can afford to waste 2MB much more easily than I
> > > > can afford to waste 2TB -- and that applies both on disk and in
> > > > memory.
> > >
> > > I don't find that convincing. Why are 2TB of wasted space in a 10TB
> > > table worse than 2TB of wasted space in 100 tables of 100GB each?
> >
> > Good point, but another way of summarizing the problem would be that the
> > autovacuum_*_scale_factor parameters work well as long as we have a more
> > or less evenly distributed access pattern in the table.
> >
> > Suppose my very large table gets updated only for its 1% most recent
> > rows. We probably want to decrease autovacuum_analyze_scale_factor and
> > autovacuum_vacuum_scale_factor for this one.
> >
> > Partitioning would be a good solution, but IMHO postgres should be able
> > to handle this case anyway, ideally without per-table configuration.
>
> I agree that you may well want autovacuum and autoanalyze treat your large
> table differently from your small tables.
>
> But I am reluctant to accept even more autovacuum GUCs. It's not like
> we don't have enough of them, rather the opposite. You can slap on more
> GUCs to treat more special cases, but we will never reach the goal of
> having a default that will make everybody happy.
>
> I believe that the defaults should work well in moderately sized databases
> with moderate usage characteristics. If you have large tables or a high
> number of transactions per second, you can be expected to make the effort
> and adjust the settings for your case. Adding more GUCs makes life *harder*
> for the users who are trying to understand and configure how autovacuum works.
Well, I disagree to some degree. I agree that the defaults should work
well in moderately sized databases with moderate usage characteristics.
But I also think we can do better than telling DBAs to they have to
manually fine-tune autovacuum for large tables (and frequenlty
implementing by hand what this patch is proposed, namely setting
autovacuum_vacuum_scale_factor to 0 and autovacuum_vacuum_threshold to a
high number), as this is cumbersome and needs adult supervision that is
not always available. Of course, it would be great if we just slap some
AI into the autovacuum launcher that figures things out automagically,
but I don't think we are there, yet.
So this proposal (probably along with a higher default threshold than
500000, but IMO less than what Robert and Nathan suggested) sounds like
a stop forward to me. DBAs can set the threshold lower if they want, or
maybe we can just turn it off by default if we cannot agree on a sane
default, but I think this (using the simplified formula from Nathan) is
a good approach that takes some pain away from autovacuum tuning and
reserves that for the really difficult cases.
Michael
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-04-26 08:18 ` Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 08:43 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
@ 2024-04-26 13:22 ` Joe Conway <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Joe Conway @ 2024-04-26 13:22 UTC (permalink / raw)
To: Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On 4/26/24 04:43, Michael Banck wrote:
> So this proposal (probably along with a higher default threshold than
> 500000, but IMO less than what Robert and Nathan suggested) sounds like
> a stop forward to me. DBAs can set the threshold lower if they want, or
> maybe we can just turn it off by default if we cannot agree on a sane
> default, but I think this (using the simplified formula from Nathan) is
> a good approach that takes some pain away from autovacuum tuning and
> reserves that for the really difficult cases.
+1 to the above
Although I don't think 500000 is necessarily too small. In my view,
having autovac run very quickly, even if more frequently, provides an
overall better user experience.
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-04-26 08:18 ` Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 08:43 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
@ 2024-04-26 13:37 ` Robert Haas <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Robert Haas @ 2024-04-26 13:37 UTC (permalink / raw)
To: Michael Banck <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Fri, Apr 26, 2024 at 4:43 AM Michael Banck <[email protected]> wrote:
> > I believe that the defaults should work well in moderately sized databases
> > with moderate usage characteristics. If you have large tables or a high
> > number of transactions per second, you can be expected to make the effort
> > and adjust the settings for your case. Adding more GUCs makes life *harder*
> > for the users who are trying to understand and configure how autovacuum works.
>
> Well, I disagree to some degree. I agree that the defaults should work
> well in moderately sized databases with moderate usage characteristics.
> But I also think we can do better than telling DBAs to they have to
> manually fine-tune autovacuum for large tables (and frequenlty
> implementing by hand what this patch is proposed, namely setting
> autovacuum_vacuum_scale_factor to 0 and autovacuum_vacuum_threshold to a
> high number), as this is cumbersome and needs adult supervision that is
> not always available. Of course, it would be great if we just slap some
> AI into the autovacuum launcher that figures things out automagically,
> but I don't think we are there, yet.
>
> So this proposal (probably along with a higher default threshold than
> 500000, but IMO less than what Robert and Nathan suggested) sounds like
> a stop forward to me. DBAs can set the threshold lower if they want, or
> maybe we can just turn it off by default if we cannot agree on a sane
> default, but I think this (using the simplified formula from Nathan) is
> a good approach that takes some pain away from autovacuum tuning and
> reserves that for the really difficult cases.
I agree with this. If having an extra setting substantially reduces
the number of cases that require manual tuning, it's totally worth it.
And I think it will.
To be clear, I don't think this is the biggest problem with the
autovacuum algorithm, not by quite a bit. But it's a relatively easy
one to fix.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
@ 2024-04-26 08:08 ` Michael Banck <[email protected]>
2 siblings, 0 replies; 11+ messages in thread
From: Michael Banck @ 2024-04-26 08:08 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; Frédéric Yhuel <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
Hi,
On Fri, Apr 26, 2024 at 04:24:45AM +0200, Laurenz Albe wrote:
> On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
> > Another reason, at least in existing releases, is that at some
> > point index vacuuming hits a wall because we run out of space for dead
> > tuples. We *most definitely* want to do index vacuuming before we get
> > to the point where we're going to have to do multiple cycles of index
> > vacuuming.
>
> That is more convincing. But do we need a GUC for that? What about
> making a table eligible for autovacuum as soon as the number of dead
> tuples reaches 90% of what you can hold in "autovacuum_work_mem"?
Due to the improvements in v17, this would basically never trigger
accordings to my understanding, or at least only after an excessive
amount of bloat has been accumulated.
Michael
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
@ 2024-04-26 13:27 ` Robert Haas <[email protected]>
2 siblings, 0 replies; 11+ messages in thread
From: Robert Haas @ 2024-04-26 13:27 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; Frédéric Yhuel <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Thu, Apr 25, 2024 at 10:24 PM Laurenz Albe <[email protected]> wrote:
> I don't find that convincing. Why are 2TB of wasted space in a 10TB
> table worse than 2TB of wasted space in 100 tables of 100GB each?
It's not worse, but it's more avoidable. No matter what you do, any
table that suffers a reasonable number of updates and/or deletes is
going to have some wasted space. When a tuple is deleted or update,
the old one has to stick around until its xmax is all-visible, and
then after that until the page is HOT pruned which may not happen
immediately, and then even after that the line pointer sticks around
until the next vacuum which doesn't happen instantly either. No matter
how aggressive you make autovacuum, or even no matter how aggressively
you vacuum manually, non-insert-only tables are always going to end up
containing some bloat.
But how much? Well, it's basically given by
RATE_AT_WHICH_SPACE_IS_WASTED * AVERAGE_TIME_UNTIL_SPACE_IS_RECLAIMED.
Which, you'll note, does not really depend on the table size. It does
a little bit, because the time until a tuple is fully removed,
including the line pointer, depends on how long vacuum takes, and
vacuum takes larger on a big table than a small one. But the effect is
much less than linear, I believe, because you can HOT-prune as soon as
the xmax is all-visible, which reclaims most of the space instantly.
So in practice, the minimum feasible steady-state bloat for a table
depends a great deal on how fast updates and deletes are happening,
but only weakly on the size of the table.
Which, in plain English, means that you should be able to vacuum a
10TB table often enough that it doesn't accumulate 2TB of bloat, if
you want to. It's going to be harder to vacuum a 10GB table often
enough that it doesn't accumulate 2GB of bloat. And it's going to be
*really* hard to vacuum a 10MB table often enough that it doesn't
accumulate 2MB of bloat. The only way you're going to be able to do
that last one at all is if the update rate is very low.
> > Another reason, at least in existing releases, is that at some
> > point index vacuuming hits a wall because we run out of space for dead
> > tuples. We *most definitely* want to do index vacuuming before we get
> > to the point where we're going to have to do multiple cycles of index
> > vacuuming.
>
> That is more convincing. But do we need a GUC for that? What about
> making a table eligible for autovacuum as soon as the number of dead
> tuples reaches 90% of what you can hold in "autovacuum_work_mem"?
That would have been a good idea to do in existing releases, a long
time before now, but we didn't. However, the new dead TID store
changes the picture, because if I understand John Naylor's remarks
correctly, the new TID store can hold so many TIDs so efficiently that
you basically won't run out of memory. So now I think this wouldn't be
effective - yet I still think it's wrong to let the vacuum threshold
scale without bound as the table size increases.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-04-26 13:37 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 09:43 [PATCH v5 06/16] meson: prereq: add output path arg in generate-lwlocknames.pl Andres Freund <[email protected]>
2021-03-10 09:43 [PATCH v3 06/17] meson: prereq: add output path arg in generate-lwlocknames.pl Andres Freund <[email protected]>
2022-01-20 07:36 [PATCH v10 05/16] meson: prereq: Add output path arg in generate-lwlocknames.pl Andres Freund <[email protected]>
2024-04-26 02:24 Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 07:35 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-04-26 08:18 ` Re: New GUC autovacuum_max_threshold ? Laurenz Albe <[email protected]>
2024-04-26 08:43 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
2024-04-26 13:22 ` Re: New GUC autovacuum_max_threshold ? Joe Conway <[email protected]>
2024-04-26 13:37 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-04-26 08:08 ` Re: New GUC autovacuum_max_threshold ? Michael Banck <[email protected]>
2024-04-26 13:27 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox