Re: Table-level log_autovacuum_min_duration

On Thu, Dec 18, 2014 at 11:15 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Michael Paquier wrote:
>> Hi all,
>>
>> Today I spent a bit of time looking at the activity of autovacuum for
>> one table particularly bloated. log_autovacuum_min_duration was
>> enabled and set to a high value but even with that I had to deal with
>> some spam from the jobs of other tables. It would be cool to have the
>> possibility to do some filtering IMO. So, what about having a relopt
>> to control log_autovacuum_min_duration? RelationData->rd_options is
>> largely accessible for a given relation in the code paths of vacuum
>> and analyze.
>
> +1

As long as I got this idea in mind, I looked at the code to see how
much it would be possible to tune log_autovacuum_min_duration in the
code paths of analyze and vacuum. First, I think that it would be good
to have parameters for both parent relations and their toast relation
similarly to the other parameters...

But now, here are some things to consider if we use directly the
reloptions available with RelationData:
- If the parameters toast.autovacuum_* are not set, toast relations
inherit values from their parent relation. Looking at autovacuum.c
which is the only place where autovacuum options are located, we keep
a hash table to save the mapping toast -> parent relation. Using that
it is easy to fetch for a given toast relation the relopts of its
parent. Note this is strictly located in the autovacuum code path, so
to let vacuum and analyze now the custom value of
log_autovacuum_min_duration, with the inheritance property kept, we
would need to pass some extra values from autovacuum to the calls of
vacuum().
- It would not be possible to log autovacuum and analyze being skipped
when lock is not available because in this case Relation cannot be
safely fetched, so there are no reltoptions directly available. This
is for example this kind of message:
skipping analyze of "foo" --- lock not available

Both those things could be solved by passing a value through
VacuumStmt, like what we do when passing a value for
multixact_freeze_min_age, or with an extra argument in vacuum() for
example. Now I am not sure if it is worth it, and we could live as
well with a parameter that do not support the inheritance parent
relation -> toast, so log value set for a toast relation and its
parent share no dependency. In short that's a trade between code
simplicity and usability.

Thoughts?
--
Michael