I'm forking this thread since it's separate topic, and since keeping in a
single branch hasn't made maintaining the patches any easier.
https://www.postgresql.org/message-id/CAMkU%3D1xAyWnwnLGORBOD%3Dpyv%3DccEkDi%3DwKeyhwF%3DgtB7QxLBwQ%40mail.gmail.com
On Sun, Dec 29, 2019 at 01:15:24PM -0500, Jeff Janes wrote:
> Also, I'd appreciate a report on how many hint-bits were set, and how many
> pages were marked all-visible and/or frozen. When I do a manual vacuum, it
> is more often for those purposes than it is for removing removable rows
> (which autovac generally does a good enough job of).
The first patch seems simple enough but the 2nd could use critical review.
>From 57eede7d1158904d6b66532c7d0ce6a59803210f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 29 Dec 2019 14:56:02 -0600
Subject: [PATCH v1 1/2] Report number of pages marked allvisible/frozen..
..as requested by Jeff Janes
---
src/backend/access/heap/vacuumlazy.c | 37 ++++++++++++++++++++++++++++++------
1 file changed, 31 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 8ce5011..9975699 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -283,7 +283,9 @@ typedef struct LVRelStats
double new_rel_tuples; /* new estimated total # of tuples */
double new_live_tuples; /* new estimated total # of live tuples */
double new_dead_tuples; /* new estimated total # of dead tuples */
- BlockNumber pages_removed;
+ BlockNumber pages_removed; /* Due to truncation */
+ BlockNumber pages_allvisible;
+ BlockNumber pages_frozen;
double tuples_deleted;
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
LVDeadTuples *dead_tuples;
@@ -602,11 +604,13 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
get_namespace_name(RelationGetNamespace(onerel)),
RelationGetRelationName(onerel),
vacrelstats->num_index_scans);
- appendStringInfo(&buf, _("pages: %u removed, %u remain, %u skipped due to pins, %u skipped frozen\n"),
+ appendStringInfo(&buf, _("pages: %u removed, %u remain, %u skipped due to pins, %u skipped frozen, %u marked all visible, %u marked frozen\n"),
vacrelstats->pages_removed,
vacrelstats->rel_pages,
vacrelstats->pinskipped_pages,
- vacrelstats->frozenskipped_pages);
+ vacrelstats->frozenskipped_pages,
+ vacrelstats->pages_allvisible,
+ vacrelstats->pages_frozen);
appendStringInfo(&buf,
_("tuples: %.0f removed, %.0f remain, %.0f are dead but not yet removable, oldest xmin: %u\n"),
vacrelstats->tuples_deleted,
@@ -751,6 +755,9 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
vacrelstats->scanned_pages = 0;
vacrelstats->tupcount_pages = 0;
vacrelstats->nonempty_pages = 0;
+ vacrelstats->pages_allvisible = 0;
+ vacrelstats->pages_frozen = 0;
+
vacrelstats->latestRemovedXid = InvalidTransactionId;
/*
@@ -1170,6 +1177,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
visibilitymap_set(onerel, blkno, buf, InvalidXLogRecPtr,
vmbuffer, InvalidTransactionId,
VISIBILITYMAP_ALL_VISIBLE | VISIBILITYMAP_ALL_FROZEN);
+ vacrelstats->pages_allvisible++;
+ vacrelstats->pages_frozen++;
END_CRIT_SECTION();
}
@@ -1501,8 +1510,12 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
{
uint8 flags = VISIBILITYMAP_ALL_VISIBLE;
- if (all_frozen)
+ if (all_frozen) {
flags |= VISIBILITYMAP_ALL_FROZEN;
+ vacrelstats->pages_frozen++;
+ }
+
+ vacrelstats->pages_allvisible++;
/*
* It should never be the case that the visibility map page is set
@@ -1690,6 +1703,14 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+ appendStringInfo(&buf, ngettext("Marked %u page all visible, ",
+ "Marked %u pages all visible, ",
+ vacrelstats->pages_allvisible),
+ vacrelstats->pages_allvisible);
+ appendStringInfo(&buf, ngettext("%u page frozen.\n",
+ "%u pages frozen.\n",
+ vacrelstats->pages_frozen),
+ vacrelstats->pages_frozen);
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
@@ -1912,10 +1933,14 @@ lazy_vacuum_page(Relation onerel, BlockNumber blkno, Buffer buffer,
uint8 flags = 0;
/* Set the VM all-frozen bit to flag, if needed */
- if ((vm_status & VISIBILITYMAP_ALL_VISIBLE) == 0)
+ if ((vm_status & VISIBILITYMAP_ALL_VISIBLE) == 0) {
flags |= VISIBILITYMAP_ALL_VISIBLE;
- if ((vm_status & VISIBILITYMAP_ALL_FROZEN) == 0 && all_frozen)
+ vacrelstats->pages_allvisible++;
+ }
+ if ((vm_status & VISIBILITYMAP_ALL_FROZEN) == 0 && all_frozen) {
flags |= VISIBILITYMAP_ALL_FROZEN;
+ vacrelstats->pages_frozen++;
+ }
Assert(BufferIsValid(*vmbuffer));
if (flags != 0)
--
2.7.4
>From 494893c39711693e05e8b108ad863b7ee12d91d1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 29 Dec 2019 16:02:50 -0600
Subject: [PATCH v1 2/2] Report number of hint bits written..
..as requested by Jeff Janes
---
src/backend/access/heap/vacuumlazy.c | 39 +++++++++++++++++++++++++-----------
1 file changed, 27 insertions(+), 12 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 9975699..8cea0d6 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -287,6 +287,7 @@ typedef struct LVRelStats
BlockNumber pages_allvisible;
BlockNumber pages_frozen;
double tuples_deleted;
+ double hintbit_tuples;
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
LVDeadTuples *dead_tuples;
int num_index_scans;
@@ -612,11 +613,12 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
vacrelstats->pages_allvisible,
vacrelstats->pages_frozen);
appendStringInfo(&buf,
- _("tuples: %.0f removed, %.0f remain, %.0f are dead but not yet removable, oldest xmin: %u\n"),
+ _("tuples: %.0f removed, %.0f remain, %.0f are dead but not yet removable, oldest xmin: %u, wrote %.0f hint bits\n"),
vacrelstats->tuples_deleted,
vacrelstats->new_rel_tuples,
vacrelstats->new_dead_tuples,
- OldestXmin);
+ OldestXmin,
+ vacrelstats->hintbit_tuples);
appendStringInfo(&buf,
_("buffer usage: %d hits, %d misses, %d dirtied\n"),
VacuumPageHit,
@@ -1188,14 +1190,6 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
}
/*
- * Prune all HOT-update chains in this page.
- *
- * We count tuples removed by the pruning step as removed by VACUUM.
- */
- tups_vacuumed += heap_page_prune(onerel, buf, OldestXmin, false,
- &vacrelstats->latestRemovedXid);
-
- /*
* Now scan the page to collect vacuumable items and check for tuples
* requiring freezing.
*/
@@ -1215,6 +1209,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
offnum = OffsetNumberNext(offnum))
{
ItemId itemid;
+ HTSV_Result satisfies;
+ int oldmask;
itemid = PageGetItemId(page, offnum);
@@ -1266,7 +1262,12 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
* cases impossible (e.g. in-progress insert from the same
* transaction).
*/
- switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))
+ oldmask = tuple.t_data->t_infomask;
+ satisfies = HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf);
+#define HINT_FLAGS (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID|HEAP_XMAX_COMMITTED|HEAP_XMAX_INVALID)
+ if ((oldmask&HINT_FLAGS) != (tuple.t_data->t_infomask&HINT_FLAGS))
+ vacrelstats->hintbit_tuples++;
+ switch (satisfies)
{
case HEAPTUPLE_DEAD:
@@ -1405,7 +1406,9 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
* Each non-removable tuple must be checked to see if it needs
* freezing. Note we already have exclusive buffer lock.
*/
- if (heap_prepare_freeze_tuple(tuple.t_data,
+ // Avoid freezing HEAPTUPLE_DEAD, as required
+ if (satisfies!=HEAPTUPLE_DEAD &&
+ heap_prepare_freeze_tuple(tuple.t_data,
relfrozenxid, relminmxid,
FreezeLimit, MultiXactCutoff,
&frozen[nfrozen],
@@ -1418,6 +1421,14 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
} /* scan along page */
/*
+ * Prune all HOT-update chains in this page.
+ *
+ * We count tuples removed by the pruning step as removed by VACUUM.
+ */
+ tups_vacuumed += heap_page_prune(onerel, buf, OldestXmin, false,
+ &vacrelstats->latestRemovedXid);
+
+ /*
* If we froze any tuples, mark the buffer dirty, and write a WAL
* record recording the changes. We must log the changes to be
* crash-safe against future truncation of CLOG.
@@ -1711,6 +1722,10 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages frozen.\n",
vacrelstats->pages_frozen),
vacrelstats->pages_frozen);
+ appendStringInfo(&buf, ngettext("Wrote %.0f hint bit.\n",
+ "Wrote %.0f hint bits.\n",
+ vacrelstats->hintbit_tuples),
+ vacrelstats->hintbit_tuples);
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.7.4
