Thread: 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

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