Re: a verbose option for autovacuum

On Tue, Feb 2, 2021 at 10:51 AM Euler Taveira <euler(at)eulerto(dot)com> wrote:
>
> On Fri, Jan 29, 2021, at 4:35 AM, Masahiko Sawada wrote:
>
> I agree to have autovacuum log more information, especially index
> vacuums. Currently, the log related to index vacuum is only the number
> of index scans. I think it would be helpful if the log has more
> details about each index vacuum.
>
> +1 for this feature. Sometimes this analysis is useful to confirm your theory;
> without data, it is just a wild guess.
>
> But I'm not sure that neither always logging that nor having set the
> parameter per-table basis is a good idea. In the former case, it could
> be log spam for example in the case of anti-wraparound vacuums that
> vacuums on all tables (and their indexes) in the database. If we set
> it per-table basis, it’s useful when the user already knows which
> tables are likely to take a long time for autovacuum but won’t work
> when the users want to check the autovacuum details for tables that
> autovacuum could take a long time for.
>
> I prefer a per-table parameter since it allows us a fine-grained tuning. It
> covers the cases you provided above. You can disable it at all and only enable
> it in critical tables or enable it and disable it for known-to-be-spam tables.
>
> Given that we already have log_autovacuum_min_duration, I think this
> verbose logging should work together with that. I’d prefer to enable
> the verbose logging by default for the same reason Stephen mentioned.
> Or maybe we can have a parameter to control verbosity, say
> log_autovaucum_verbosity.
>
> IMO this new parameter is just an option to inject VERBOSE into VACUUM command.
> Since there is already a parameter to avoid spam autovacuum messages, this
> feature shouldn't hijack log_autovacuum_min_duration behavior. If the
> autovacuum command execution time runs less than l_a_m_d, the output should be
> discarded.

Yeah, if autovacuum execution time doesn't exceed
log_autovacuum_min_duration, the output should be discarded. My idea
is to show autovacuum log along with the new information about index
vacuum etc if autovacuum execution time exceeds the threshold.

Regarding the new parameter being discussed here, I think it depends
on how much the amount of autovacuum logs increases. I think if we
added a few information about indexes to the current autovacuum log
the new parameter would not required. So I just posted[1] another idea
to show only index page statistics (i.g., what lazy_cleanup_index()
shows) along with the current autovacuum logs. Please refer to it.

Regards,

[1] https://www.postgresql.org/message-id/CAD21AoAy6SxHiTivh5yAPJSUE4S%3DQRPpSZUdafOSz0R%2BfRcM6Q%40mail.gmail.com

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/