pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Geoghegan <pg@bowt.ie>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add VACUUM instrumentation for scanned pages, relfrozenxid.
Date: Sat, 12 Feb 2022 00:49:57 +0000
Message-ID: <E1nIgbt-0000vX-Gu@gemulon.postgresql.org> (raw)

Add VACUUM instrumentation for scanned pages, relfrozenxid.

Report on scanned pages within VACUUM VERBOSE and autovacuum logging.
These are pages that were physically examined during the VACUUM
operation.  Note that this can include a small number of pages that were
marked all-visible in the visibility map by some earlier VACUUM
operation.  VACUUM won't skip all-visible pages that aren't part of a
range of all-visible pages that's at least 32 blocks in length (partly
to avoid missing out on opportunities to advance relfrozenxid during
non-aggressive VACUUMs).

Commit 44fa8488 simplified the definition of scanned pages.  It became
the complement of the pages (of those pages from rel_pages) that were
skipped using the visibility map.  And so scanned pages precisely
indicates how effective the visibility map was at saving work.  (Before
now we displayed the number of pages skipped via the visibility map when
happened to be frozen pages, but not when they were merely all-visible,
which was less useful to users.)

Rename the user-visible OldestXmin output field to "removal cutoff", and
show some supplementary information: how far behind the cutoff is
(number of XIDs behind) by the time the VACUUM operation finished.  This
will help users to figure out what's _not_ working in extreme cases
where VACUUM is fundamentally unable to remove dead tuples or freeze
older tuples (e.g., due to a leaked replication slot).  Also report when
relfrozenxid is advanced by VACUUM in output that immediately follows
"removal cutoff".  This structure is intended to highlight the
relationship between the new relfrozenxid value for the table, and the
VACUUM operation's removal cutoff.

Finally, add instrumentation of "missed dead tuples", and the number of
pages that had at least one such tuple.  These are fully DEAD (not just
RECENTLY_DEAD) tuples with storage that could not be pruned due to
failure to acquire a cleanup lock on a heap page.  This is a replacement
for the "skipped due to pin" instrumentation removed by commit 44fa8488.
It shows more details than before for pages where failing to get a
cleanup lock actually resulted in VACUUM missing out on useful work, but
usually shows nothing at all instead (the mere fact that we couldn't get
a cleanup lock is usually of no consequence whatsoever now).

Author: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CAH2-Wznp=c=Opj8Z7RMR3G=ec3_JfGYMN_YvmCEjoPCHzWbx0g@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/872770fd6ccf12596b9e26234df9a37cae083af2

Modified Files
--------------
src/backend/access/heap/vacuumlazy.c | 97 +++++++++++++++++++++++++-----------
src/backend/commands/analyze.c       |  3 ++
src/backend/commands/vacuum.c        |  9 ++++
src/include/commands/vacuum.h        |  2 +
4 files changed, 82 insertions(+), 29 deletions(-)



view thread (3+ messages)  latest in thread

Message-ID: <E1nIgbt-0000vX-Gu@gemulon.postgresql.org>
Permalink:  ../E1nIgbt-0000vX-Gu@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1nIgbt-0000vX-Gu@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: pg@bowt.ie
  Subject: Re: pgsql: Add VACUUM instrumentation for scanned pages, relfrozenxid.
  In-Reply-To: <E1nIgbt-0000vX-Gu@gemulon.postgresql.org>
  References: <E1nIgbt-0000vX-Gu@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox