Instrument freezing in autovacuum log reports.

Add a new line to log reports from autovacuum (as well as VACUUM VERBOSE
output) that shows information about freezing.  Emphasis is placed on
the total number of heap pages that had one or more tuples frozen by
VACUUM.  The total number of tuples frozen is also shown.

Author: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Jeff Janes <jeff.janes@gmail.com>
Discussion: https://postgr.es/m/CAH2-WznTY6D0zyE8VLrC6Gd4kh_HGAXxnTPtcOQOOsxzLx9zog@mail.gmail.com

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 21eaf1d8c4255bc7fb727e5d3ec293de970c865c..dfbe37472f042aebbfe3b682733190a7d62a8e3a 100644 (file)
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -198,6 +198,7 @@ typedef struct LVRelState
    BlockNumber rel_pages;      /* total number of pages */
    BlockNumber scanned_pages;  /* # pages examined (not skipped via VM) */
    BlockNumber removed_pages;  /* # pages removed by relation truncation */
+   BlockNumber frozen_pages;   /* # pages with newly frozen tuples */
    BlockNumber lpdead_item_pages;  /* # pages with LP_DEAD items */
    BlockNumber missed_dead_pages;  /* # pages with missed dead tuples */
    BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -212,6 +213,7 @@ typedef struct LVRelState
    int         num_index_scans;
    /* Counters that follow are only for scanned_pages */
    int64       tuples_deleted; /* # deleted from table */
+   int64       tuples_frozen;  /* # newly frozen */
    int64       lpdead_items;   /* # deleted from indexes */
    int64       live_tuples;    /* # live tuples remaining */
    int64       recently_dead_tuples;   /* # dead, but not yet removable */
@@ -470,6 +472,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
    /* Initialize page counters explicitly (be tidy) */
    vacrel->scanned_pages = 0;
    vacrel->removed_pages = 0;
+   vacrel->frozen_pages = 0;
    vacrel->lpdead_item_pages = 0;
    vacrel->missed_dead_pages = 0;
    vacrel->nonempty_pages = 0;
@@ -484,6 +487,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
    /* Initialize remaining counters (be tidy) */
    vacrel->num_index_scans = 0;
    vacrel->tuples_deleted = 0;
+   vacrel->tuples_frozen = 0;
    vacrel->lpdead_items = 0;
    vacrel->live_tuples = 0;
    vacrel->recently_dead_tuples = 0;
@@ -721,6 +725,11 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
                                 _("new relminmxid: %u, which is %d MXIDs ahead of previous value\n"),
                                 vacrel->NewRelminMxid, diff);
            }
+           appendStringInfo(&buf, _("frozen: %u pages from table (%.2f%% of total) had %lld tuples frozen\n"),
+                            vacrel->frozen_pages,
+                            orig_rel_pages == 0 ? 100.0 :
+                            100.0 * vacrel->frozen_pages / orig_rel_pages,
+                            (long long) vacrel->tuples_frozen);
            if (vacrel->do_index_vacuuming)
            {
                if (vacrel->nindexes == 0 || vacrel->num_index_scans == 0)
@@ -1549,11 +1558,11 @@ lazy_scan_prune(LVRelState *vacrel,
    HeapTupleData tuple;
    HTSV_Result res;
    int         tuples_deleted,
+               tuples_frozen,
                lpdead_items,
                live_tuples,
                recently_dead_tuples;
    int         nnewlpdead;
-   int         nfrozen;
    TransactionId NewRelfrozenXid;
    MultiXactId NewRelminMxid;
    OffsetNumber deadoffsets[MaxHeapTuplesPerPage];
@@ -1574,6 +1583,7 @@ retry:
    NewRelfrozenXid = vacrel->NewRelfrozenXid;
    NewRelminMxid = vacrel->NewRelminMxid;
    tuples_deleted = 0;
+   tuples_frozen = 0;
    lpdead_items = 0;
    live_tuples = 0;
    recently_dead_tuples = 0;
@@ -1600,7 +1610,6 @@ retry:
    prunestate->all_visible = true;
    prunestate->all_frozen = true;
    prunestate->visibility_cutoff_xid = InvalidTransactionId;
-   nfrozen = 0;
 
    for (offnum = FirstOffsetNumber;
         offnum <= maxoff;
@@ -1779,11 +1788,12 @@ retry:
                                      vacrel->relminmxid,
                                      vacrel->FreezeLimit,
                                      vacrel->MultiXactCutoff,
-                                     &frozen[nfrozen], &tuple_totally_frozen,
+                                     &frozen[tuples_frozen],
+                                     &tuple_totally_frozen,
                                      &NewRelfrozenXid, &NewRelminMxid))
        {
            /* Will execute freeze below */
-           frozen[nfrozen++].offset = offnum;
+           frozen[tuples_frozen++].offset = offnum;
        }
 
        /*
@@ -1809,10 +1819,12 @@ retry:
     * Consider the need to freeze any items with tuple storage from the page
     * first (arbitrary)
     */
-   if (nfrozen > 0)
+   if (tuples_frozen > 0)
    {
        Assert(prunestate->hastup);
 
+       vacrel->frozen_pages++;
+
        /*
         * At least one tuple with storage needs to be frozen -- execute that
         * now.
@@ -1826,7 +1838,7 @@ retry:
        MarkBufferDirty(buf);
 
        /* execute collected freezes */
-       for (int i = 0; i < nfrozen; i++)
+       for (int i = 0; i < tuples_frozen; i++)
        {
            HeapTupleHeader htup;
 
@@ -1842,7 +1854,7 @@ retry:
            XLogRecPtr  recptr;
 
            recptr = log_heap_freeze(vacrel->rel, buf, vacrel->FreezeLimit,
-                                    frozen, nfrozen);
+                                    frozen, tuples_frozen);
            PageSetLSN(page, recptr);
        }
 
@@ -1914,6 +1926,7 @@ retry:
 
    /* Finally, add page-local counts to whole-VACUUM counts */
    vacrel->tuples_deleted += tuples_deleted;
+   vacrel->tuples_frozen += tuples_frozen;
    vacrel->lpdead_items += lpdead_items;
    vacrel->live_tuples += live_tuples;
    vacrel->recently_dead_tuples += recently_dead_tuples;