pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum verbose: show pages marked allvisible/frozen/hintbits
2+ messages / 2 participants
[nested] [flat]
* vacuum verbose: show pages marked allvisible/frozen/hintbits
@ 2020-01-26 14:13 Justin Pryzby <pryzby@telsasoft.com>
2020-06-15 04:30 ` Re: vacuum verbose: show pages marked allvisible/frozen/hintbits Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Justin Pryzby @ 2020-01-26 14:13 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-hackers
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%...
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.
Attachments:
[text/x-diff] v1-0001-Report-number-of-pages-marked-allvisible-frozen.patch (4.3K, ../../20200126141328.GP13621@telsasoft.com/2-v1-0001-Report-number-of-pages-marked-allvisible-frozen.patch)
download | inline diff:
From 57eede7d1158904d6b66532c7d0ce6a59803210f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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
[text/x-diff] v1-0002-Report-number-of-hint-bits-written.patch (4.4K, ../../20200126141328.GP13621@telsasoft.com/3-v1-0002-Report-number-of-hint-bits-written.patch)
download | inline diff:
From 494893c39711693e05e8b108ad863b7ee12d91d1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: vacuum verbose: show pages marked allvisible/frozen/hintbits
2020-01-26 14:13 vacuum verbose: show pages marked allvisible/frozen/hintbits Justin Pryzby <pryzby@telsasoft.com>
@ 2020-06-15 04:30 ` Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: Masahiko Sawada @ 2020-06-15 04:30 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-hackers
On Sun, 26 Jan 2020 at 23:13, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> 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%...
> 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.
Here is comments on 0001 patch from a quick review:
- BlockNumber pages_removed;
+ BlockNumber pages_removed; /* Due to truncation */
+ BlockNumber pages_allvisible;
+ BlockNumber pages_frozen;
Other codes in vacuumlazy.c uses ‘all_frozen', so how about
pages_allfrozen instead of pages_frozen?
---
@@ -1549,8 +1558,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++;
+ }
@@ -1979,10 +2000,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++;
+ }
The above changes need to follow PostgreSQL code format (a newline is
required after if statement).
---
/*
* If the all-visible page is all-frozen but not marked as such yet,
* mark it as all-frozen. Note that all_frozen is only valid if
* all_visible is true, so we must check both.
*/
else if (all_visible_according_to_vm && all_visible && all_frozen &&
!VM_ALL_FROZEN(onerel, blkno, &vmbuffer))
{
/*
* We can pass InvalidTransactionId as the cutoff XID here,
* because setting the all-frozen bit doesn't cause recovery
* conflicts.
*/
visibilitymap_set(onerel, blkno, buf, InvalidXLogRecPtr,
vmbuffer, InvalidTransactionId,
VISIBILITYMAP_ALL_FROZEN);
}
We should also count up vacrelstats->pages_frozen here.
For 0002 patch, how users will be able to make any meaning out of how
many hint bits were updated by vacuumu?
Regards,
--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-06-15 04:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-26 14:13 vacuum verbose: show pages marked allvisible/frozen/hintbits Justin Pryzby <pryzby@telsasoft.com>
2020-06-15 04:30 ` Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
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