Re: log_autovacuum_min_duration doesn't log VACUUMs

On Fri, Feb 10, 2017 at 3:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I guess its fairly obvious in the title, but
> log_autovacuum_min_duration doesn't log VACUUMs only autovacuums.
>
> What isn't obvious is why that restruction is useful.
>
> I say that it would be helpful to log all kinds of VACUUM, so we get
> similar output from all methods of submission.
>
> So, changes would be
>
> 1. Allow logging whether or not it is an autovacuum (attached)
>
> 2. Change name of parameter to ...
> a) log_vacuum_min_duration
> b) log_maintenance_min_duration and have it log CLUSTER, CREATE INDEX etc also
> c) log_ddl_min_duration and just time any DDL that takes a long time
> We could do any of those and they are all about as easy as one
> another, though the last one will be a bigger patch, so a) might be
> simpler.
>
> The following patch implements (1), but not yet (2) to allow debate.

Right now, the mission of log_autovacuum_min_duration is not to log
all automatic vacuums, but to log all actions taken by the autovacuum
workers, which include both VACUUM and ANALYZE. With your proposed
patch, we'd log all VACUUM operations (whether or not performed by
autovacuum) and some ANALYZE operations (only if performed by
autovacuum). That's not very logical, so -1 for the patch as
proposed.

That having been said, I think it could certainly be useful to have
more control over what DDL gets logged in foreground processes. Right
now you can say log_statement=ddl, but you can't for example say
log_statement=vacuum,create_index,analyze,truncate. That might not be
the right interface but certainly I think people would appreciate the
ability to log some DDL without logging all of it. And maybe it's
useful to have a way to provide the same kind of logging that
log_autovacuum_min_duration does for foreground vacuums (and
analyzes?). I'd make that a separate GUC, though.

One of the things to keep in mind here is that most people would log
everything if it had no downsides. But it does - it takes CPU time
and I/O bandwidth and it makes it harder to find things in the logs.
So fine-grained control over what gets logged is important. It's
probably not wise to just rip out log_autovacuum_min_duration and
replace it with something that has a much broader remit, because then
people who are happy with the amount of logging they're getting now
might suddenly find that they're getting too much. I think the way
forward is to allow new options that add more logging rather than
making the existing options log more than they do now.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company