Additional logging for VACUUM and ANALYZE

From: "Bossart, Nathan" <bossartn(at)amazon(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Additional logging for VACUUM and ANALYZE
Date: 2017-10-04 16:23:17
Message-ID: 875815E8-7A99-4488-AA07-F254C404E2CF@amazon.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi hackers,

Presently, there are a few edge cases in vacuum_rel() and analyze_rel() that I
believe do not have sufficient logging. This was discussed a bit in the
vacuum-multiple-relations thread [0], but it was ultimately decided that any
logging changes should be proposed separately.

There are two cases that my proposed patch handles:

1) If VACOPT_NOWAIT is specified, the relation will be skipped if we
cannot obtain a lock on the relation without waiting. This option
is currently only used for autovacuum, and there is a corresponding
log message that is only emitted for autovacuum as well. IMO this
code is operating on a pretty volatile assumption, as I can see a
potential use-case for allowing customers to specify VACOPT_NOWAIT.
For example, a nightly manual VACUUM command might benefit from
this option by skipping tables that autovacuum is already working
on or that otherwise cannot be immediately locked. However, I am
not proposing to give customers this option in manual VACUUM/ANALYZE
commands here, just that the logging logic need not assume
differently.

2) If a relation is dropped between the time vacuum() looks it up and
when it is processed, it will currently be silently skipped. This
seems prone to cause confusion when a relation does not appear
in the VERBOSE output of VACUUM/ANALYZE or in the autovacuum logs.
It is arguable that we should not emit a message for relations that
were not explicitly specified (e.g. VACUUM with no tables
specified), and I am inclined to agree. Fortunately, the recent
vacuum-multiple-relations change avoids populating the relation's
RangeVar if it was not listed in the command, which makes it trivial
to decide whether to log.

So, the attached patch changes the existing lock contention message to be
emitted for non-autovacuum sessions if necessary, and it adds a "skipping"
message when a specified relation disappears before it is processed. For
consistency, autovacuum logs are emitted at LOG, and logs for manual commands
are emitted at WARNING. This patch also includes a minor documentation change
and a test that exercises a bit of this functionality.

If this change were to be considered for back-patching, we would likely want to
also apply Michael's RangeVar fix for partitioned tables to 10 [1]. Without
this change, log messages for unspecified partitions will be emitted with the
parent's RangeVar information.

Nathan

[0] https://postgr.es/m/E061A8E3-5E3D-494D-94F0-E8A9B312BBFC@amazon.com
[1] https://postgr.es/m/CAB7nPqRX1465FP%2Bameysxxt63tCQDDW6KvaTPMfkSxaT1TFGfw%40mail.gmail.com

Attachment Content-Type Size
new_vacuum_log_messages_v1.patch application/octet-stream 9.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Brent Dearth 2017-10-04 16:41:53 Re: Horrible CREATE DATABASE Performance in High Sierra
Previous Message Robert Haas 2017-10-04 16:00:18 Re: Partition-wise join for join between (declaratively) partitioned tables