Per-tablespace autovacuum settings
Hello,
Is there any interest in making autovacuum parameters available on a
tablespace level in order to apply those to all vacuumable objects in the
tablespace?
We have a set of tables running on ZFS, where autovacuum does almost no good
to us (except for preventing anti-wraparound) due to the nature of ZFS (FS
fragmentation caused by copy-on-write leads to sequential scans doing random
access) and the fact that our tables there are append-only. Initially, the
team in charge of the application just disabled autovacuum globally, but
that lead to a huge system catalog bloat.
At present, we have to re-enable autovacuum globally and then disable it
per-table using table storage parameters, but that is inelegant and requires
doing it once for existing tables and modifying the script that periodically
creates new ones (the whole system is a Postgres-based replacement of an
ElasticSearch cluster and we have to create new partitions regularly).
Grouping tables by tablespaces for the purpose of autovacuum configuration
seems natural, as tablespaces are often placed on another filesystems/device
that may require changing how often does autovacuum run, make it less/more
aggressive depending on the I/O performance or require disabling it
altogether as in my example above. Furthermore, given that we allow
cost-based options per-tablespace the infrastructure is already there and
the task is mostly to teach autovacuum to look at tablespaces in addition to
the relation storage options (in case of a conflict, relation options should
always take priority).
Regards,
Oleksii Kliukin
Hi,
On 2019-02-14 17:56:17 +0100, Oleksii Kliukin wrote:
Is there any interest in making autovacuum parameters available on a
tablespace level in order to apply those to all vacuumable objects in the
tablespace?We have a set of tables running on ZFS, where autovacuum does almost no good
to us (except for preventing anti-wraparound) due to the nature of ZFS (FS
fragmentation caused by copy-on-write leads to sequential scans doing random
access) and the fact that our tables there are append-only. Initially, the
team in charge of the application just disabled autovacuum globally, but
that lead to a huge system catalog bloat.At present, we have to re-enable autovacuum globally and then disable it
per-table using table storage parameters, but that is inelegant and requires
doing it once for existing tables and modifying the script that periodically
creates new ones (the whole system is a Postgres-based replacement of an
ElasticSearch cluster and we have to create new partitions regularly).
Won't that a) lead to periodic massive anti-wraparound sessions? b)
prevent any use of index only scans?
ISTM you'd be better off running vacuum rarely, with large
thresholds. That way it'd do most of the writes in one pass, hopefully
leading to less fragementation, and it'd set the visibilitymap bits to
prevent further need to touch those. By doing it only rarely, vacuum
should process pages sequentially, reducing the fragmentation.
Grouping tables by tablespaces for the purpose of autovacuum configuration
seems natural, as tablespaces are often placed on another filesystems/device
that may require changing how often does autovacuum run, make it less/more
aggressive depending on the I/O performance or require disabling it
altogether as in my example above. Furthermore, given that we allow
cost-based options per-tablespace the infrastructure is already there and
the task is mostly to teach autovacuum to look at tablespaces in addition to
the relation storage options (in case of a conflict, relation options should
always take priority).
While I don't buy the reasoning above, I think this'd be useful for
other cases.
Greetings,
Andres Freund
Oleksii Kliukin <alexk@hintbits.com> writes:
Is there any interest in making autovacuum parameters available on a
tablespace level in order to apply those to all vacuumable objects in the
tablespace?
I understand what you want to accomplish, and it doesn't seem
unreasonable. But I just want to point out that the situation with
vacuuming parameters is on the edge of unintelligible already; adding
another scope might push it over the edge. In particular there's no
principled way to decide whether an autovacuum parameter set at an outer
scope should override a plain-vacuum parameter set at a narrower scope.
And it's really questionable which of database-wide and tablespace-wide
should be seen as a narrower scope in the first place.
I don't know how to make this better, but I wish we'd take a step
back and think about it rather than just accreting more and more
complexity.
regards, tom lane
Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2019-02-14 17:56:17 +0100, Oleksii Kliukin wrote:
Is there any interest in making autovacuum parameters available on a
tablespace level in order to apply those to all vacuumable objects in the
tablespace?We have a set of tables running on ZFS, where autovacuum does almost no good
to us (except for preventing anti-wraparound) due to the nature of ZFS (FS
fragmentation caused by copy-on-write leads to sequential scans doing random
access) and the fact that our tables there are append-only. Initially, the
team in charge of the application just disabled autovacuum globally, but
that lead to a huge system catalog bloat.At present, we have to re-enable autovacuum globally and then disable it
per-table using table storage parameters, but that is inelegant and requires
doing it once for existing tables and modifying the script that periodically
creates new ones (the whole system is a Postgres-based replacement of an
ElasticSearch cluster and we have to create new partitions regularly).Won't that a) lead to periodic massive anti-wraparound sessions? b)
prevent any use of index only scans?
The wraparound is hardly an issue there, as the data is transient and only
exist for 14 days (I think the entire date-based partition is dropped,
that’s how we ended up with pg_class catalog bloat). The index-only scan can
be an issue, although, IIRC, there is some manual vacuum that runs from time
to time, perhaps following your advice below.
ISTM you'd be better off running vacuum rarely, with large
thresholds. That way it'd do most of the writes in one pass, hopefully
leading to less fragementation, and it'd set the visibilitymap bits to
prevent further need to touch those. By doing it only rarely, vacuum
should process pages sequentially, reducing the fragmentation.Grouping tables by tablespaces for the purpose of autovacuum configuration
seems natural, as tablespaces are often placed on another filesystems/device
that may require changing how often does autovacuum run, make it less/more
aggressive depending on the I/O performance or require disabling it
altogether as in my example above. Furthermore, given that we allow
cost-based options per-tablespace the infrastructure is already there and
the task is mostly to teach autovacuum to look at tablespaces in addition to
the relation storage options (in case of a conflict, relation options should
always take priority).While I don't buy the reasoning above, I think this'd be useful for
other cases.
Even if we don’t want to disable autovacuum completely, we might want to
make it much less frequent by increasing the thresholds or costs/delays to
reduce the I/O strain for a particular tablespace.
Regards,
Oleksii Kliukin
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Oleksii Kliukin <alexk@hintbits.com> writes:
Is there any interest in making autovacuum parameters available on a
tablespace level in order to apply those to all vacuumable objects in the
tablespace?I understand what you want to accomplish, and it doesn't seem
unreasonable. But I just want to point out that the situation with
vacuuming parameters is on the edge of unintelligible already; adding
another scope might push it over the edge. In particular there's no
principled way to decide whether an autovacuum parameter set at an outer
scope should override a plain-vacuum parameter set at a narrower scope.
My naive understanding is that vacuum and autovacuum should decide
independently which scope applies, coming from the most specific (per-table
for autovacuum, per-DB for vacuum) to the broader scopes, ending with
configuration parameters at the outermost scope . Both *_cost_limit and
*_cost_delay should be taken from the current vacuum scope only if effective
autovacuum settings yield -1.
And it's really questionable which of database-wide and tablespace-wide
should be seen as a narrower scope in the first place.
AFAIK we don’t allow setting autovacuum options per-database; neither I
suggest enabling plain vacuum to be configured per-tablespace; as a result,
we won’t be deciding between databases and tablespaces, unless we want to do
cross-lookups from autovacuum to the outer scope of plain vacuum options
before considering autovacuum’s own outer scope and I don’t see any reason
to do that.
I don't know how to make this better, but I wish we'd take a step
back and think about it rather than just accreting more and more
complexity.
I am willing to do the refactoring when necessary, any particular place in
the code that is indicative of the issue?
Regards,
Oleksii Kliukin
Hello,
Oleksii Kliukin <alexk@hintbits.com> wrote:
Tom Lane <tgl@sss.pgh.pa.us> wrote:
I don't know how to make this better, but I wish we'd take a step
back and think about it rather than just accreting more and more
complexity.I am willing to do the refactoring when necessary, any particular place in
the code that is indicative of the issue?
I’ve managed to return to that and here’s the first iteration of the patch
to add autovacuum parameters to tablespaces. I tried to make it as simple as
possible and didn’t make any decisions I found questionable, opting to
discuss them here instead. Some of them are probably linked to the kind of
issues mentioned by Tom upthread.
Things worth mentioning are:
- Fallbacks to autovacuum parameters in another scope. Right now in the
absence of the per-table and per-tablespace autovacuum parameters the code
uses the ones from the global scope. However, if only some of the reloptions
are set on a per-table level (i.e. none of the autovacuum related ones), we
assume defaults for the rest of reloptions without consulting the lower
level (i.e .per-tablespace options). This is so because we don’t have the
mechanism to tell whether the option is set to its default value (some of
them use -1 to request the fallback to the outer level, but for some it’s
not possible, i.e. autovacuum_enabled is just a boolean value).
- There are no separate per-tablespace settings for TOAST tables. I couldn't
find a strong case for setting all TOAST autovacuum options in a tablespace
to the same value that is distinct from the corresponding settings for the
regular tables. The difficulty of implementing TOAST options lies in the
fact that we strip the namespace part from the option name before storing it
in reltoptions. Changing that would break compatibility with previous
versions and require another step for pg_upgrade, I don’t think it is worth
the troubles. We could also come with a separate set of tablespace options,
i.e. prefixed with “toast_”, but that seems an ugly solution for the problem
that doesn’t seem real. As a result, if per-tablespace autovacuum options
are set and there are no table-specific TOAST options, the TOAST table will
inherit autovacuum options from the tablespace, rather than taking them from
the regular table it is attached to.
- There are a few relatively recently introduced options
(vacuum_index_cleanup, vacuum_truncate and
vacuum_cleanup_index_scale_factor) that I haven’t incorporated into the
per-tablespace options, as they are not part of autovacuum options and I see
no use for setting them on a tablespace level. This can be changed easily if
people think otherwise.
The patch is attached. It has a few tests and no documentation, I will
improvise both one we get in agreement on how the end result should look.
Kind regards,
Oleksii Kliukin
Attachments:
v1-per-tablespace-autovacuum-parameters.patchapplication/octet-stream; name=v1-per-tablespace-autovacuum-parameters.patch; x-unix-mode=0644Download
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index cfbabb5265..a576fdec5c 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -111,7 +111,7 @@ static relopt_bool boolRelOpts[] =
{
"autovacuum_enabled",
"Enables autovacuum in this relation",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
true
@@ -221,7 +221,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_vacuum_threshold",
"Minimum number of tuple updates or deletes prior to vacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0, INT_MAX
@@ -230,7 +230,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_analyze_threshold",
"Minimum number of tuple inserts, updates or deletes prior to analyze",
- RELOPT_KIND_HEAP,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TABLESPACE | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0, INT_MAX
@@ -239,7 +239,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_vacuum_cost_limit",
"Vacuum cost amount available before napping, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 1, 10000
@@ -248,7 +248,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_min_age",
"Minimum age at which VACUUM should freeze a table row, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0, 1000000000
@@ -257,7 +257,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_multixact_freeze_min_age",
"Minimum multixact age at which VACUUM should freeze a row multixact's, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0, 1000000000
@@ -266,7 +266,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_max_age",
"Age at which to autovacuum a table to prevent transaction ID wraparound",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 100000, 2000000000
@@ -275,7 +275,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_multixact_freeze_max_age",
"Multixact age at which to autovacuum a table to prevent multixact wraparound",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 10000, 2000000000
@@ -284,7 +284,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_table_age",
"Age at which VACUUM should perform a full table sweep to freeze row versions",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
}, -1, 0, 2000000000
},
@@ -292,7 +292,7 @@ static relopt_int intRelOpts[] =
{
"autovacuum_multixact_freeze_table_age",
"Age of multixact at which VACUUM should perform a full table sweep to freeze row versions",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
}, -1, 0, 2000000000
},
@@ -300,7 +300,7 @@ static relopt_int intRelOpts[] =
{
"log_autovacuum_min_duration",
"Sets the minimum execution time above which autovacuum actions will be logged",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, -1, INT_MAX
@@ -364,7 +364,7 @@ static relopt_real realRelOpts[] =
{
"autovacuum_vacuum_cost_delay",
"Vacuum cost delay in milliseconds, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0.0, 100.0
@@ -373,7 +373,7 @@ static relopt_real realRelOpts[] =
{
"autovacuum_vacuum_scale_factor",
"Number of tuple updates or deletes prior to vacuum as a fraction of reltuples",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0.0, 100.0
@@ -382,7 +382,7 @@ static relopt_real realRelOpts[] =
{
"autovacuum_analyze_scale_factor",
"Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples",
- RELOPT_KIND_HEAP,
+ RELOPT_KIND_HEAP | RELOPT_KIND_TABLESPACE,
ShareUpdateExclusiveLock
},
-1, 0.0, 100.0
@@ -766,9 +766,9 @@ add_string_reloption(bits32 kinds, const char *name, const char *desc, const cha
* that are in the passed namespace. The output values do not include the
* namespace.
*
- * This is used for three cases: CREATE TABLE/INDEX, ALTER TABLE SET, and
- * ALTER TABLE RESET. In the ALTER cases, oldOptions is the existing
- * reloptions value (possibly NULL), and we replace or remove entries
+ * This is used for four cases: CREATE TABLE/INDEX, CREATE TABLEPSACE, ALTER
+ * TABLE SET, and ALTER TABLE RESET. In the ALTER cases, oldOptions is the
+ * existing reloptions value (possibly NULL), and we replace or remove entries
* as needed.
*
* If acceptOidsOff is true, then we allow oids = false, but throw error when
@@ -1562,7 +1562,35 @@ tablespace_reloptions(Datum reloptions, bool validate)
static const relopt_parse_elt tab[] = {
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
{"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)},
- {"effective_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_concurrency)}
+ {"effective_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_concurrency)},
+ {"autovacuum_enabled", RELOPT_TYPE_BOOL,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, enabled)},
+ {"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+ {"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, analyze_threshold)},
+ {"autovacuum_vacuum_cost_delay", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_delay)},
+ {"autovacuum_vacuum_cost_limit", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_limit)},
+ {"autovacuum_freeze_min_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, freeze_min_age)},
+ {"autovacuum_freeze_max_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, freeze_max_age)},
+ {"autovacuum_freeze_table_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, freeze_table_age)},
+ {"autovacuum_multixact_freeze_min_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_min_age)},
+ {"autovacuum_multixact_freeze_max_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_max_age)},
+ {"autovacuum_multixact_freeze_table_age", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_table_age)},
+ {"log_autovacuum_min_duration", RELOPT_TYPE_INT,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, log_min_duration)},
+ {"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, vacuum_scale_factor)},
+ {"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,
+ offsetof(TableSpaceOpts, autovacuum) + offsetof(AutoVacOpts, analyze_scale_factor)},
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 53c91d9277..a5909375fc 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -102,6 +102,7 @@
#include "utils/ps_status.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
+#include "utils/spccache.h"
#include "utils/syscache.h"
#include "utils/timeout.h"
#include "utils/timestamp.h"
@@ -2718,21 +2719,29 @@ static AutoVacOpts *
extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc)
{
bytea *relopts;
- AutoVacOpts *av;
+ AutoVacOpts *av, *retav;
Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION ||
((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW ||
((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE);
relopts = extractRelOptions(tup, pg_class_desc, NULL);
- if (relopts == NULL)
+ av = (relopts != NULL) ? &(((StdRdOptions *)relopts)->autovacuum): NULL;
+ /* Fetch per-tablespace autovacuum options if any */
+ if (av == NULL)
+ {
+ Oid spcid = ((Form_pg_class) GETSTRUCT(tup))->reltablespace;
+ av = get_tablespace_autovacuum_options(spcid);
+ }
+ if (av == NULL)
return NULL;
- av = palloc(sizeof(AutoVacOpts));
- memcpy(av, &(((StdRdOptions *) relopts)->autovacuum), sizeof(AutoVacOpts));
- pfree(relopts);
+ retav = palloc(sizeof(AutoVacOpts));
+ memcpy(retav, av, sizeof(AutoVacOpts));
+ if (relopts != NULL)
+ pfree(relopts);
- return av;
+ return retav;
}
/*
diff --git a/src/backend/utils/cache/spccache.c b/src/backend/utils/cache/spccache.c
index 6309a017c1..133c5560c4 100644
--- a/src/backend/utils/cache/spccache.c
+++ b/src/backend/utils/cache/spccache.c
@@ -221,3 +221,13 @@ get_tablespace_io_concurrency(Oid spcid)
else
return spc->opts->effective_io_concurrency;
}
+
+AutoVacOpts *
+get_tablespace_autovacuum_options(Oid spcid)
+{
+ TableSpaceCacheEntry *spc = get_tablespace(spcid);
+
+ if (!spc->opts)
+ return NULL;
+ return &(spc->opts->autovacuum);
+}
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 003c874c22..67a1beef8e 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -18,6 +18,7 @@
#include "catalog/objectaddress.h"
#include "lib/stringinfo.h"
#include "nodes/parsenodes.h"
+#include "utils/rel.h"
/* XLOG stuff */
#define XLOG_TBLSPC_CREATE 0x00
@@ -40,6 +41,7 @@ typedef struct TableSpaceOpts
float8 random_page_cost;
float8 seq_page_cost;
int effective_io_concurrency;
+ AutoVacOpts autovacuum; /* autovacuum-related options */
} TableSpaceOpts;
extern Oid CreateTableSpace(CreateTableSpaceStmt *stmt);
diff --git a/src/include/utils/spccache.h b/src/include/utils/spccache.h
index 9120abcd9b..517e347301 100644
--- a/src/include/utils/spccache.h
+++ b/src/include/utils/spccache.h
@@ -13,8 +13,11 @@
#ifndef SPCCACHE_H
#define SPCCACHE_H
+#include "utils/rel.h"
+
void get_tablespace_page_costs(Oid spcid, float8 *spc_random_page_cost,
float8 *spc_seq_page_cost);
int get_tablespace_io_concurrency(Oid spcid);
+AutoVacOpts *get_tablespace_autovacuum_options(Oid spcid);
#endif /* SPCCACHE_H */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 14ce0e7e04..805424e082 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -16,6 +16,34 @@ ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+ALTER TABLESPACE regress_tblspace SET (autovacuum_enabled = true,
+ autovacuum_vacuum_threshold = 100,
+ autovacuum_analyze_threshold = 100,
+ autovacuum_vacuum_cost_limit = 1000,
+ autovacuum_freeze_min_age = 100000000,
+ autovacuum_freeze_max_age = 400000000,
+ autovacuum_freeze_table_age = 300000000,
+ autovacuum_multixact_freeze_min_age = 10000000,
+ autovacuum_multixact_freeze_table_age = 150000000,
+ autovacuum_multixact_freeze_max_age = 800000000,
+ log_autovacuum_min_duration = 250,
+ autovacuum_vacuum_cost_delay = 0,
+ autovacuum_vacuum_scale_factor = 0.4,
+ autovacuum_analyze_scale_factor = 0.4); -- ok
+ALTER TABLESPACE regress_tblspace RESET (autovacuum_enabled,
+ autovacuum_vacuum_threshold,
+ autovacuum_analyze_threshold,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_table_age,
+ autovacuum_freeze_max_age,
+ autovacuum_multixact_freeze_min_age,
+ autovacuum_multixact_freeze_table_age,
+ autovacuum_multixact_freeze_max_age,
+ log_autovacuum_min_duration,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_analyze_scale_factor); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 8ebe08b9b2..9c6f1ab19a 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,34 @@ ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+ALTER TABLESPACE regress_tblspace SET (autovacuum_enabled = true,
+ autovacuum_vacuum_threshold = 100,
+ autovacuum_analyze_threshold = 100,
+ autovacuum_vacuum_cost_limit = 1000,
+ autovacuum_freeze_min_age = 100000000,
+ autovacuum_freeze_max_age = 400000000,
+ autovacuum_freeze_table_age = 300000000,
+ autovacuum_multixact_freeze_min_age = 10000000,
+ autovacuum_multixact_freeze_table_age = 150000000,
+ autovacuum_multixact_freeze_max_age = 800000000,
+ log_autovacuum_min_duration = 250,
+ autovacuum_vacuum_cost_delay = 0,
+ autovacuum_vacuum_scale_factor = 0.4,
+ autovacuum_analyze_scale_factor = 0.4); -- ok
+ALTER TABLESPACE regress_tblspace RESET (autovacuum_enabled,
+ autovacuum_vacuum_threshold,
+ autovacuum_analyze_threshold,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_table_age,
+ autovacuum_freeze_max_age,
+ autovacuum_multixact_freeze_min_age,
+ autovacuum_multixact_freeze_table_age,
+ autovacuum_multixact_freeze_max_age,
+ log_autovacuum_min_duration,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_analyze_scale_factor); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
On Thu, Apr 25, 2019 at 12:36 PM Oleksii Kliukin <alexk@hintbits.com> wrote:
- Fallbacks to autovacuum parameters in another scope. Right now in the
absence of the per-table and per-tablespace autovacuum parameters the code
uses the ones from the global scope. However, if only some of the reloptions
are set on a per-table level (i.e. none of the autovacuum related ones), we
assume defaults for the rest of reloptions without consulting the lower
level (i.e .per-tablespace options). This is so because we don’t have the
mechanism to tell whether the option is set to its default value (some of
them use -1 to request the fallback to the outer level, but for some it’s
not possible, i.e. autovacuum_enabled is just a boolean value).
That sounds like it's probably not acceptable?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Apr 25, 2019 at 12:36 PM Oleksii Kliukin <alexk@hintbits.com> wrote:
- Fallbacks to autovacuum parameters in another scope. Right now in the
absence of the per-table and per-tablespace autovacuum parameters the code
uses the ones from the global scope. However, if only some of the reloptions
are set on a per-table level (i.e. none of the autovacuum related ones), we
assume defaults for the rest of reloptions without consulting the lower
level (i.e .per-tablespace options). This is so because we don’t have the
mechanism to tell whether the option is set to its default value (some of
them use -1 to request the fallback to the outer level, but for some it’s
not possible, i.e. autovacuum_enabled is just a boolean value).That sounds like it's probably not acceptable?
Yes, I think it would be inconsistent. However, it looks like all the
options from AutoVacOpts other than autovacuum_enabled are set to -1 by
default. This can be used to tell whether the option is set to its default
value. For autovacuum_enabled we don’t care much: it’s true by default and
it’s a safe choice (even if the global autovacuum is off, enabling per-table
or per-tablespace one is a no-op).
I will update the patch.
Cheers,
Oleksii