Re: a verbose option for autovacuum

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.

I don't have a strong opinion about this parameter name but I think your
suggestion (log_autovaccum_verbosity) is easier to guess what this parameter is
for.

--
Euler Taveira
EDB https://www.enterprisedb.com/