On Wed, Oct 11, 2023 at 8:43 PM Andres Freund <and...@anarazel.de> wrote:
>
> A rough sketch of a freezing heuristic:
>
> - We concluded that to intelligently control opportunistic freezing we need
> statistics about the number of freezes and unfreezes
>
> - We should track page freezes / unfreezes in shared memory stats on a
> per-relation basis
>
> - To use such statistics to control heuristics, we need to turn them into
> rates. For that we need to keep snapshots of absolute values at certain
> times (when vacuuming), allowing us to compute a rate.
>
> - If we snapshot some stats, we need to limit the amount of data that
> occupies
>
> - evict based on wall clock time (we don't care about unfreezing pages
> frozen a month ago)
>
> - "thin out" data when exceeding limited amount of stats per relation
> using random sampling or such
>
> - need a smarter approach than just keeping N last vacuums, as there are
> situations where a table is (auto-) vacuumed at a high frequency
>
>
> - only looking at recent-ish table stats is fine, because we
> - a) don't want to look at too old data, as we need to deal with changing
> workloads
>
> - b) if there aren't recent vacuums, falsely freezing is of bounded cost
>
> - shared memory stats being lost on crash-restart/failover might be a
> problem
>
> - we certainly don't want to immediate store these stats in a table, due
> to the xid consumption that'd imply
>
>
> - Attributing "unfreezes" to specific vacuums would be powerful:
>
> - "Number of pages frozen during vacuum" and "Number of pages unfrozen that
> were frozen during the same vacuum" provides numerator / denominator for
> an "error rate"
>
> - We can perform this attribution by comparing the page LSN with recorded
> start/end LSNs of recent vacuums
>
> - If the freezing error rate of recent vacuums is low, freeze more
> aggressively. This is important to deal with insert mostly workloads.
>
> - If old data is "unfrozen", that's fine, we can ignore such unfreezes when
> controlling "freezing aggressiveness"
>
> - Ignoring unfreezing of old pages is important to e.g. deal with
> workloads that delete old data
>
> - This approach could provide "goals" for opportunistic freezing in a
> somewhat understandable way. E.g. aiming to rarely unfreeze data that has
> been frozen within 1h/1d/...
I have taken a stab at implementing the freeze stats tracking
infrastructure we would need to drive a heuristic based on error rate.
Attached is a series of patches which adds a ring buffer of vacuum
freeze statistics to each table's stats (PgStat_StatTabEntry).
It also introduces a guc: target_page_freeze_duration. This is the time
in seconds which we would like pages to stay frozen for. The aim is to
adjust opportunistic freeze behavior such that pages stay frozen for at
least target_page_freeze_duration seconds.
When a table is vacuumed the next entry is initialized in the ring
buffer (PgStat_StatTabEntry->frz_buckets[frz_current]). If all of the
buckets are in use, the two oldest buckets both ending either before or
after now - target_page_freeze_duration are combined.
When vacuum freezes a page, we increment the freeze counter in the
current PgStat_Frz entry in the ring buffer and update the counters used
to calculate the max, min, and average page age.
When a frozen page is modified, we increment "unfreezes" in the bucket
spanning the page freeze LSN. We also update the counters used to
calculate the min, max, and average frozen duration. Because we have
both the LSNs and time elapsed during the vacuum which froze the page,
we can derive the approximate time at which the page was frozen (using
linear interpolation) and use that to speculate how long it remained
frozen. If we are unfreezing it sooner than target_page_freeze_duration,
this is counted as an "early unfreeze". Using early unfreezes and page
freezes, we can calculate an error rate.
The guc, opp_freeze_algo, remains to allow us to test different freeze
heuristics during development. I included a dummy algorithm (algo 2) to
demonstrate what we could do with the data. If the error rate is above a
hard-coded threshold and the page is older than the average page age, it
is frozen. This is missing an "aggressiveness" knob.
There are two workloads I think we can focus on. The first is a variant
of our former workload I. The second workload, J, is the pgbench
built-in simple-update workload.
I2. Work queue
WL 1: 32 clients inserting a single row, updating an indexed
column in another row twice, then deleting the last updated row
pgbench scale 100
WL 2: 2 clients, running the TPC-B like built-in workload
rate-limited to 10,000 TPS
J. simple-update
pgbench scale 450
WL 1: 16 clients running built-in simple-update workload
The simple-update workload works well because it only updates
pgbench_accounts and inserts into pgbench_history. This gives us an
insert-only table and a table with uniform data modification. The former
should be frozen aggressively, the latter should be frozen as little as
possible.
The convenience function pg_stat_get_table_vacuums(tablename) returns
all of the collected vacuum freeze statistics for all of the vacuums of
the specified table (the contents of the ring buffer in
PgStat_StatTabEntry).
To start with, I ran workloads I2 and J with the criteria from master
and with the criteria that we freeze any page that is all frozen and all
visible. Having run the benchmarks for 40 minutes with the
target_page_freeze_duration set to 300 seconds, the following was my
error and efficacy (I am calling efficacy the % of pages frozen at the
end of the benchmark run).
I2 (work queue)
+-------+---------+---------+-----------+--------+----------+----------+
| table | algo | freezes |early unfrz| error | #frzn end| %frzn end|
+-------+---------+---------+-----------+--------+----------+----------+
| queue | av + af | 274,459 | 272,794 | 99% | 906 | 75% |
| queue | master | 274,666 | 272,798 | 99% | 908 | 56% |
+-------+---------+---------+-----------+--------+----------+----------+
J (simple-update)
+-----------+-------+---------+-----------+-------+----------+---------+
| pgbench | algo | freezes |early unfrz| error |#frzn end |%frzn end|
| tab | | | | | | |
+-----------+-------+---------+-----------+-------+----------+---------+
| accounts |av + af| 258,482 | 258,482 | 100% | 0 | 0% |
| history |av + af| 287,362 | 357 | 0% | 287,005 | 86% |
| accounts | master| 0 | 0 | | 0 | 0% |
| history | master| 0 | 0 | | 0 | 0% |
+-----------+-------+---------+-----------+-------+----------+---------+
The next step is to devise different heuristics and measure their
efficacy. IMO, the goal of the algorithm it is to freeze pages in a
relation such that we drive early unfreezes/freezes -> 0 and pages
frozen/number of pages of a certain age -> 1.
We have already agreed to consider early unfreezes/freezes to be the
"error". I have been thinking of pages frozen/number of pages of a
certain age as efficacy.
An alternative measure of efficacy is the average page freeze duration.
I keep track of this as well as the min and max page freeze durations.
I think we also will want some measure of magnitude in order to
determine how much more or less aggressively we should freeze. To this
end, I track the total number of pages scanned by a vacuum, the total
number of pages in the relation at the end of the vacuum, and the total
number of frozen pages at the beginning and end of the vacuum.
Besides error, efficacy, and magnitude, we need a way to compare a
freeze candidate page to the relation-level metrics. We had settled on
using the page's age -- how long it has been since the page was last
modified. I track the average, min, and max page ages at the time of
freezing. The difference between the min and max gives us some idea how
reliable the average page age may be.
For example, if we have high error, low variation (max - min page age),
and high magnitude (high # pages frozen), freezing was uniformly
ineffective.
Right now, I don't have an "aggressiveness" knob. Vacuum has the average
error rate over the past vacuums, the average page age calculated over
the past vacuums, and the target freeze duration (from the user set
guc). We need some kind of aggressiveness knob, but I'm not sure if it
is a page age threshold or something else.
- Melanie
From 62ae12c486b4c33ed92ad458d1466e62605be583 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 13:47:48 -0500
Subject: [PATCH v1 2/9] Rename frozen_pages to pages_frozen for clarity
LVRelState counts the number of pages frozen during a vacuum of a
relation. Newly frozen pages may not stay frozen, so rename this member
to clarify that it is the number of pages which were frozen, not the
number of pages that are frozen.
---
src/backend/access/heap/vacuumlazy.c | 8 ++++----
src/include/commands/vacuum.h | 2 +-
2 files changed, 5 insertions(+), 5 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 0b86c9d4ea..9409cf6b38 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -335,7 +335,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->pages_frozen = 0;
vacrel->lpdead_item_pages = 0;
vacrel->missed_dead_pages = 0;
vacrel->nonempty_pages = 0;
@@ -601,9 +601,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->NewRelminMxid, diff);
}
appendStringInfo(&buf, _("frozen: %u pages from table (%.2f%% of total) had %lld tuples frozen\n"),
- vacrel->frozen_pages,
+ vacrel->pages_frozen,
orig_rel_pages == 0 ? 100.0 :
- 100.0 * vacrel->frozen_pages / orig_rel_pages,
+ 100.0 * vacrel->pages_frozen / orig_rel_pages,
(long long) vacrel->tuples_frozen);
if (vacrel->do_index_vacuuming)
{
@@ -1712,7 +1712,7 @@ lazy_scan_prune(LVRelState *vacrel,
{
TransactionId snapshotConflictHorizon;
- vacrel->frozen_pages++;
+ vacrel->pages_frozen++;
/*
* We can use visibility_cutoff_xid as our cutoff for conflicts
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index f48c4ab95a..fa91e1b465 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -356,7 +356,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 pages_frozen; /* # 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 */
--
2.37.2
From 1a53794326472848eae57fd442d1779402e1e1d8 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 13:43:34 -0500
Subject: [PATCH v1 1/9] Make LVRelState accessible to stats
Relocate LVRelState and VacErrPhase to vacuum.h so that
pgstat_report_vacuum() can access members of LVRelState directly. This
is to prepare for future commits which will add additional LVRelState
members to be accumulated into the stats system by pgstat_report_vacuum().
---
src/backend/access/heap/vacuumlazy.c | 100 +------------------
src/backend/commands/vacuum.c | 1 -
src/backend/utils/activity/pgstat_relation.c | 20 +++-
src/include/commands/vacuum.h | 87 ++++++++++++++++
src/include/pgstat.h | 4 +-
5 files changed, 107 insertions(+), 105 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 6985d299b2..0b86c9d4ea 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -127,90 +127,6 @@
*/
#define ParallelVacuumIsActive(vacrel) ((vacrel)->pvs != NULL)
-/* Phases of vacuum during which we report error context. */
-typedef enum
-{
- VACUUM_ERRCB_PHASE_UNKNOWN,
- VACUUM_ERRCB_PHASE_SCAN_HEAP,
- VACUUM_ERRCB_PHASE_VACUUM_INDEX,
- VACUUM_ERRCB_PHASE_VACUUM_HEAP,
- VACUUM_ERRCB_PHASE_INDEX_CLEANUP,
- VACUUM_ERRCB_PHASE_TRUNCATE,
-} VacErrPhase;
-
-typedef struct LVRelState
-{
- /* Target heap relation and its indexes */
- Relation rel;
- Relation *indrels;
- int nindexes;
-
- /* Buffer access strategy and parallel vacuum state */
- BufferAccessStrategy bstrategy;
- ParallelVacuumState *pvs;
-
- /* Aggressive VACUUM? (must set relfrozenxid >= FreezeLimit) */
- bool aggressive;
- /* Use visibility map to skip? (disabled by DISABLE_PAGE_SKIPPING) */
- bool skipwithvm;
- /* Consider index vacuuming bypass optimization? */
- bool consider_bypass_optimization;
-
- /* Doing index vacuuming, index cleanup, rel truncation? */
- bool do_index_vacuuming;
- bool do_index_cleanup;
- bool do_rel_truncate;
-
- /* VACUUM operation's cutoffs for freezing and pruning */
- struct VacuumCutoffs cutoffs;
- GlobalVisState *vistest;
- /* Tracks oldest extant XID/MXID for setting relfrozenxid/relminmxid */
- TransactionId NewRelfrozenXid;
- MultiXactId NewRelminMxid;
- bool skippedallvis;
-
- /* Error reporting state */
- char *dbname;
- char *relnamespace;
- char *relname;
- char *indname; /* Current index name */
- BlockNumber blkno; /* used only for heap operations */
- OffsetNumber offnum; /* used only for heap operations */
- VacErrPhase phase;
- bool verbose; /* VACUUM VERBOSE? */
-
- /*
- * dead_items stores TIDs whose index tuples are deleted by index
- * vacuuming. Each TID points to an LP_DEAD line pointer from a heap page
- * that has been processed by lazy_scan_prune. Also needed by
- * lazy_vacuum_heap_rel, which marks the same LP_DEAD line pointers as
- * LP_UNUSED during second heap pass.
- */
- VacDeadItems *dead_items; /* TIDs whose index tuples we'll delete */
- 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 */
-
- /* Statistics output by us, for table */
- double new_rel_tuples; /* new estimated total # of tuples */
- double new_live_tuples; /* new estimated total # of live tuples */
- /* Statistics output by index AMs */
- IndexBulkDeleteResult **indstats;
-
- /* Instrumentation counters */
- 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 */
- int64 missed_dead_tuples; /* # removable, but not removed */
-} LVRelState;
/*
* State returned by lazy_scan_prune()
@@ -583,21 +499,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->NewRelfrozenXid, vacrel->NewRelminMxid,
&frozenxid_updated, &minmulti_updated, false);
- /*
- * Report results to the cumulative stats system, too.
- *
- * Deliberately avoid telling the stats system about LP_DEAD items that
- * remain in the table due to VACUUM bypassing index and heap vacuuming.
- * ANALYZE will consider the remaining LP_DEAD items to be dead "tuples".
- * It seems like a good idea to err on the side of not vacuuming again too
- * soon in cases where the failsafe prevented significant amounts of heap
- * vacuuming.
- */
+ /* Report results to the cumulative stats system, too. */
pgstat_report_vacuum(RelationGetRelid(rel),
- rel->rd_rel->relisshared,
- Max(vacrel->new_live_tuples, 0),
- vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples);
+ rel->rd_rel->relisshared, vacrel);
pgstat_progress_end_command();
if (instrument)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 8bdbee6841..823fb67010 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -59,7 +59,6 @@
#include "utils/guc_hooks.h"
#include "utils/memutils.h"
#include "utils/pg_rusage.h"
-#include "utils/snapmgr.h"
#include "utils/syscache.h"
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index f5d726e292..bd92380a68 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -207,10 +207,12 @@ pgstat_drop_relation(Relation rel)
/*
* Report that the table was just vacuumed and flush IO statistics.
+ *
+ * vacrel is an input parameter only and will not be modified by
+ * pgstat_report_vacuum().
*/
void
-pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -231,8 +233,18 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
tabentry = &shtabentry->stats;
- tabentry->live_tuples = livetuples;
- tabentry->dead_tuples = deadtuples;
+ tabentry->live_tuples = Max(vacrel->new_live_tuples, 0);
+
+ /*
+ * Deliberately avoid telling the stats system about LP_DEAD items that
+ * remain in the table due to VACUUM bypassing index and heap vacuuming.
+ * ANALYZE will consider the remaining LP_DEAD items to be dead "tuples".
+ * It seems like a good idea to err on the side of not vacuuming again too
+ * soon in cases where the failsafe prevented significant amounts of heap
+ * vacuuming.
+ */
+ tabentry->dead_tuples = vacrel->recently_dead_tuples +
+ vacrel->missed_dead_tuples;
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 4af02940c5..f48c4ab95a 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -24,6 +24,7 @@
#include "storage/buf.h"
#include "storage/lock.h"
#include "utils/relcache.h"
+#include "utils/snapmgr.h"
/*
* Flags for amparallelvacuumoptions to control the participation of bulkdelete
@@ -175,6 +176,7 @@ typedef struct VacAttrStats
int rowstride;
} VacAttrStats;
+
/* flag bits for VacuumParams->options */
#define VACOPT_VACUUM 0x01 /* do VACUUM */
#define VACOPT_ANALYZE 0x02 /* do ANALYZE */
@@ -291,6 +293,91 @@ typedef struct VacDeadItems
#define MAXDEADITEMS(avail_mem) \
(((avail_mem) - offsetof(VacDeadItems, items)) / sizeof(ItemPointerData))
+/* Phases of vacuum during which we report error context. */
+typedef enum
+{
+ VACUUM_ERRCB_PHASE_UNKNOWN,
+ VACUUM_ERRCB_PHASE_SCAN_HEAP,
+ VACUUM_ERRCB_PHASE_VACUUM_INDEX,
+ VACUUM_ERRCB_PHASE_VACUUM_HEAP,
+ VACUUM_ERRCB_PHASE_INDEX_CLEANUP,
+ VACUUM_ERRCB_PHASE_TRUNCATE,
+} VacErrPhase;
+
+typedef struct LVRelState
+{
+ /* Target heap relation and its indexes */
+ Relation rel;
+ Relation *indrels;
+ int nindexes;
+
+ /* Buffer access strategy and parallel vacuum state */
+ BufferAccessStrategy bstrategy;
+ ParallelVacuumState *pvs;
+
+ /* Aggressive VACUUM? (must set relfrozenxid >= FreezeLimit) */
+ bool aggressive;
+ /* Use visibility map to skip? (disabled by DISABLE_PAGE_SKIPPING) */
+ bool skipwithvm;
+ /* Consider index vacuuming bypass optimization? */
+ bool consider_bypass_optimization;
+
+ /* Doing index vacuuming, index cleanup, rel truncation? */
+ bool do_index_vacuuming;
+ bool do_index_cleanup;
+ bool do_rel_truncate;
+
+ /* VACUUM operation's cutoffs for freezing and pruning */
+ struct VacuumCutoffs cutoffs;
+ GlobalVisState *vistest;
+ /* Tracks oldest extant XID/MXID for setting relfrozenxid/relminmxid */
+ TransactionId NewRelfrozenXid;
+ MultiXactId NewRelminMxid;
+ bool skippedallvis;
+
+ /* Error reporting state */
+ char *dbname;
+ char *relnamespace;
+ char *relname;
+ char *indname; /* Current index name */
+ BlockNumber blkno; /* used only for heap operations */
+ OffsetNumber offnum; /* used only for heap operations */
+ VacErrPhase phase;
+ bool verbose; /* VACUUM VERBOSE? */
+
+ /*
+ * dead_items stores TIDs whose index tuples are deleted by index
+ * vacuuming. Each TID points to an LP_DEAD line pointer from a heap page
+ * that has been processed by lazy_scan_prune. Also needed by
+ * lazy_vacuum_heap_rel, which marks the same LP_DEAD line pointers as
+ * LP_UNUSED during second heap pass.
+ */
+ VacDeadItems *dead_items; /* TIDs whose index tuples we'll delete */
+ 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 */
+
+ /* Statistics output by us, for table */
+ double new_rel_tuples; /* new estimated total # of tuples */
+ double new_live_tuples; /* new estimated total # of live tuples */
+ /* Statistics output by index AMs */
+ IndexBulkDeleteResult **indstats;
+
+ /* Instrumentation counters */
+ 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 */
+ int64 missed_dead_tuples; /* # removable, but not removed */
+} LVRelState;
+
/* GUC parameters */
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for PostGIS */
extern PGDLLIMPORT int vacuum_freeze_min_age;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f95d8db0c4..5e84deec9a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -11,6 +11,7 @@
#ifndef PGSTAT_H
#define PGSTAT_H
+#include "commands/vacuum.h"
#include "datatype/timestamp.h"
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
@@ -587,8 +588,7 @@ extern void pgstat_init_relation(Relation rel);
extern void pgstat_assoc_relation(Relation rel);
extern void pgstat_unlink_relation(Relation rel);
-extern void pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples);
+extern void pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter);
--
2.37.2
From 937b5fd8d656eea4394c6149a34fb5daa268f413 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 14:18:19 -0500
Subject: [PATCH v1 4/9] visibilitymap_set/clear() return previous vm bits
Change the return type of visibilitymap_set() and visibilitymap_clear()
to return both the all frozen and all visible bits for the specified page
prior to modification. This allows us to check individually if either of
those bits were set before clearing or setting them regardless of which
bits are being set or cleared.
---
src/backend/access/heap/heapam.c | 27 ++++++++++++++-----------
src/backend/access/heap/visibilitymap.c | 18 ++++++++++-------
src/include/access/visibilitymap.h | 10 ++++-----
3 files changed, 31 insertions(+), 24 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 14de8158d4..80828f3efe 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -3554,10 +3554,11 @@ l2:
* overhead would be unchanged, that doesn't seem necessarily
* worthwhile.
*/
- if (PageIsAllVisible(page) &&
- visibilitymap_clear(relation, block, vmbuffer,
- VISIBILITYMAP_ALL_FROZEN))
- cleared_all_frozen = true;
+ if (PageIsAllVisible(page))
+ {
+ cleared_all_frozen = visibilitymap_clear(relation, block, vmbuffer,
+ VISIBILITYMAP_ALL_FROZEN) & VISIBILITYMAP_ALL_FROZEN;
+ }
MarkBufferDirty(buffer);
@@ -4751,10 +4752,11 @@ failed:
tuple->t_data->t_ctid = *tid;
/* Clear only the all-frozen bit on visibility map if needed */
- if (PageIsAllVisible(page) &&
- visibilitymap_clear(relation, block, vmbuffer,
- VISIBILITYMAP_ALL_FROZEN))
- cleared_all_frozen = true;
+ if (PageIsAllVisible(page))
+ {
+ cleared_all_frozen = visibilitymap_clear(relation, block, vmbuffer,
+ VISIBILITYMAP_ALL_FROZEN) & VISIBILITYMAP_ALL_FROZEN;
+ }
MarkBufferDirty(*buffer);
@@ -5505,10 +5507,11 @@ l4:
xid, mode, false,
&new_xmax, &new_infomask, &new_infomask2);
- if (PageIsAllVisible(BufferGetPage(buf)) &&
- visibilitymap_clear(rel, block, vmbuffer,
- VISIBILITYMAP_ALL_FROZEN))
- cleared_all_frozen = true;
+ if (PageIsAllVisible(BufferGetPage(buf)))
+ {
+ cleared_all_frozen = visibilitymap_clear(rel, block, vmbuffer,
+ VISIBILITYMAP_ALL_FROZEN) & VISIBILITYMAP_ALL_FROZEN;
+ }
START_CRIT_SECTION();
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 2e18cd88bc..5586b727fd 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -134,9 +134,9 @@ static Buffer vm_extend(Relation rel, BlockNumber vm_nblocks);
*
* You must pass a buffer containing the correct map page to this function.
* Call visibilitymap_pin first to pin the right one. This function doesn't do
- * any I/O. Returns true if any bits have been cleared and false otherwise.
+ * any I/O. Returns the visibility map status before clearing the bits.
*/
-bool
+uint8
visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags)
{
BlockNumber mapBlock = HEAPBLK_TO_MAPBLOCK(heapBlk);
@@ -144,7 +144,7 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
int mapOffset = HEAPBLK_TO_OFFSET(heapBlk);
uint8 mask = flags << mapOffset;
char *map;
- bool cleared = false;
+ uint8 status;
/* Must never clear all_visible bit while leaving all_frozen bit set */
Assert(flags & VISIBILITYMAP_VALID_BITS);
@@ -160,17 +160,18 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
LockBuffer(vmbuf, BUFFER_LOCK_EXCLUSIVE);
map = PageGetContents(BufferGetPage(vmbuf));
+ status = ((map[mapByte] >> mapOffset) & VISIBILITYMAP_VALID_BITS);
+
if (map[mapByte] & mask)
{
map[mapByte] &= ~mask;
MarkBufferDirty(vmbuf);
- cleared = true;
}
LockBuffer(vmbuf, BUFFER_LOCK_UNLOCK);
- return cleared;
+ return status;
}
/*
@@ -240,9 +241,9 @@ visibilitymap_pin_ok(BlockNumber heapBlk, Buffer vmbuf)
*
* You must pass a buffer containing the correct map page to this function.
* Call visibilitymap_pin first to pin the right one. This function doesn't do
- * any I/O.
+ * any I/O. Returns the visibility map status before setting the bits.
*/
-void
+uint8
visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
XLogRecPtr recptr, Buffer vmBuf, TransactionId cutoff_xid,
uint8 flags)
@@ -252,6 +253,7 @@ visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
uint8 mapOffset = HEAPBLK_TO_OFFSET(heapBlk);
Page page;
uint8 *map;
+ uint8 status;
#ifdef TRACE_VISIBILITYMAP
elog(DEBUG1, "vm_set %s %d", RelationGetRelationName(rel), heapBlk);
@@ -276,6 +278,7 @@ visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
map = (uint8 *) PageGetContents(page);
LockBuffer(vmBuf, BUFFER_LOCK_EXCLUSIVE);
+ status = ((map[mapByte] >> mapOffset) & VISIBILITYMAP_VALID_BITS);
if (flags != (map[mapByte] >> mapOffset & VISIBILITYMAP_VALID_BITS))
{
START_CRIT_SECTION();
@@ -313,6 +316,7 @@ visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
}
LockBuffer(vmBuf, BUFFER_LOCK_UNLOCK);
+ return status;
}
/*
diff --git a/src/include/access/visibilitymap.h b/src/include/access/visibilitymap.h
index daaa01a257..29608d4a7a 100644
--- a/src/include/access/visibilitymap.h
+++ b/src/include/access/visibilitymap.h
@@ -26,14 +26,14 @@
#define VM_ALL_FROZEN(r, b, v) \
((visibilitymap_get_status((r), (b), (v)) & VISIBILITYMAP_ALL_FROZEN) != 0)
-extern bool visibilitymap_clear(Relation rel, BlockNumber heapBlk,
- Buffer vmbuf, uint8 flags);
+extern uint8 visibilitymap_clear(Relation rel, BlockNumber heapBlk,
+ Buffer vmbuf, uint8 flags);
extern void visibilitymap_pin(Relation rel, BlockNumber heapBlk,
Buffer *vmbuf);
extern bool visibilitymap_pin_ok(BlockNumber heapBlk, Buffer vmbuf);
-extern void visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
- XLogRecPtr recptr, Buffer vmBuf, TransactionId cutoff_xid,
- uint8 flags);
+extern uint8 visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
+ XLogRecPtr recptr, Buffer vmBuf, TransactionId cutoff_xid,
+ uint8 flags);
extern uint8 visibilitymap_get_status(Relation rel, BlockNumber heapBlk, Buffer *vmbuf);
extern void visibilitymap_count(Relation rel, BlockNumber *all_visible, BlockNumber *all_frozen);
extern BlockNumber visibilitymap_prepare_truncate(Relation rel,
--
2.37.2
From b060c93fc7015352e69eb130491f3b20ad33ba5a Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 15:22:59 -0500
Subject: [PATCH v1 5/9] Add guc target_page_freeze_duration
Add target_page_freeze_duration, a guc specifying the minimum time in
seconds a page should stay frozen. This will be used to measure and
control vacuum's opportunistic page freezing behavior in future commits.
---
src/backend/utils/init/globals.c | 1 +
src/backend/utils/misc/guc_tables.c | 11 +++++++++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/miscadmin.h | 1 +
4 files changed, 14 insertions(+)
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 60bc1217fb..43212ea55c 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -149,6 +149,7 @@ int VacuumCostPageMiss = 2;
int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
+int target_page_freeze_duration = 1;
int64 VacuumPageHit = 0;
int64 VacuumPageMiss = 0;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 7605eff9b9..710f7f8a7d 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2465,6 +2465,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"target_page_freeze_duration", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("minimum amount of time in seconds that a page should stay frozen."),
+ NULL,
+ GUC_UNIT_S
+ },
+ &target_page_freeze_duration,
+ 1, 0, 10000000,
+ NULL, NULL, NULL
+ },
+
{
{"max_files_per_process", PGC_POSTMASTER, RESOURCES_KERNEL,
gettext_noop("Sets the maximum number of simultaneously open files for each server process."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e48c066a5b..9966746c4e 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,6 +661,7 @@
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
+#target_page_freeze_duration = 1 # desired time for page to stay frozen in seconds
#------------------------------------------------------------------------------
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index f0cc651435..bd04ff2899 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -279,6 +279,7 @@ extern PGDLLIMPORT int VacuumCostPageMiss;
extern PGDLLIMPORT int VacuumCostPageDirty;
extern PGDLLIMPORT int VacuumCostLimit;
extern PGDLLIMPORT double VacuumCostDelay;
+extern PGDLLIMPORT int target_page_freeze_duration;
extern PGDLLIMPORT int64 VacuumPageHit;
extern PGDLLIMPORT int64 VacuumPageMiss;
--
2.37.2
From f9dc3eddad100cfd5b2519c8c3409f789b75c569 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 14:07:36 -0500
Subject: [PATCH v1 3/9] Add pg_visibility_map_summary_extended()
Add a new pg_visibility function, pg_visibility_map_summary_extended(),
which returns the total number of blocks in the relation in addition to
the number of all frozen and all visible pages returned by
pg_visibility_map_summary(). It is easy and cheap to get the number of
blocks at the same time as the number of all visible and all frozen
pages and doing so is useful for calculating percent visible/frozen. The
old pg_visibility_map_summary() function API is left as is but is
implemented by selecting a subset of the columns returned by
pg_visibility_map_summary_extended().
---
contrib/pg_visibility/expected/pg_visibility.out | 5 +++++
contrib/pg_visibility/pg_visibility--1.1.sql | 11 +++++++++--
contrib/pg_visibility/pg_visibility.c | 13 +++++++------
doc/src/sgml/pgvisibility.sgml | 12 ++++++++++++
4 files changed, 33 insertions(+), 8 deletions(-)
diff --git a/contrib/pg_visibility/expected/pg_visibility.out b/contrib/pg_visibility/expected/pg_visibility.out
index 9de54db2a2..db52d40739 100644
--- a/contrib/pg_visibility/expected/pg_visibility.out
+++ b/contrib/pg_visibility/expected/pg_visibility.out
@@ -49,6 +49,7 @@ DETAIL: This operation is not supported for partitioned tables.
select pg_visibility_map_summary('test_partitioned');
ERROR: relation "test_partitioned" is of wrong relation kind
DETAIL: This operation is not supported for partitioned tables.
+CONTEXT: SQL function "pg_visibility_map_summary" statement 1
select pg_check_frozen('test_partitioned');
ERROR: relation "test_partitioned" is of wrong relation kind
DETAIL: This operation is not supported for partitioned tables.
@@ -67,6 +68,7 @@ DETAIL: This operation is not supported for indexes.
select pg_visibility_map_summary('test_index');
ERROR: relation "test_index" is of wrong relation kind
DETAIL: This operation is not supported for indexes.
+CONTEXT: SQL function "pg_visibility_map_summary" statement 1
select pg_check_frozen('test_index');
ERROR: relation "test_index" is of wrong relation kind
DETAIL: This operation is not supported for indexes.
@@ -84,6 +86,7 @@ DETAIL: This operation is not supported for views.
select pg_visibility_map_summary('test_view');
ERROR: relation "test_view" is of wrong relation kind
DETAIL: This operation is not supported for views.
+CONTEXT: SQL function "pg_visibility_map_summary" statement 1
select pg_check_frozen('test_view');
ERROR: relation "test_view" is of wrong relation kind
DETAIL: This operation is not supported for views.
@@ -101,6 +104,7 @@ DETAIL: This operation is not supported for sequences.
select pg_visibility_map_summary('test_sequence');
ERROR: relation "test_sequence" is of wrong relation kind
DETAIL: This operation is not supported for sequences.
+CONTEXT: SQL function "pg_visibility_map_summary" statement 1
select pg_check_frozen('test_sequence');
ERROR: relation "test_sequence" is of wrong relation kind
DETAIL: This operation is not supported for sequences.
@@ -120,6 +124,7 @@ DETAIL: This operation is not supported for foreign tables.
select pg_visibility_map_summary('test_foreign_table');
ERROR: relation "test_foreign_table" is of wrong relation kind
DETAIL: This operation is not supported for foreign tables.
+CONTEXT: SQL function "pg_visibility_map_summary" statement 1
select pg_check_frozen('test_foreign_table');
ERROR: relation "test_foreign_table" is of wrong relation kind
DETAIL: This operation is not supported for foreign tables.
diff --git a/contrib/pg_visibility/pg_visibility--1.1.sql b/contrib/pg_visibility/pg_visibility--1.1.sql
index 0a29967ee6..c2f8137736 100644
--- a/contrib/pg_visibility/pg_visibility--1.1.sql
+++ b/contrib/pg_visibility/pg_visibility--1.1.sql
@@ -37,12 +37,19 @@ RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_visibility_rel'
LANGUAGE C STRICT;
+-- Show summary of visibility map bits for a relation and the number of blocks
+CREATE FUNCTION pg_visibility_map_summary_extended(regclass,
+ OUT all_visible bigint, OUT all_frozen bigint, OUT nblocks bigint)
+RETURNS record
+AS 'MODULE_PATHNAME', 'pg_visibility_map_summary_extended'
+LANGUAGE C STRICT;
+
-- Show summary of visibility map bits for a relation.
CREATE FUNCTION pg_visibility_map_summary(regclass,
OUT all_visible bigint, OUT all_frozen bigint)
RETURNS record
-AS 'MODULE_PATHNAME', 'pg_visibility_map_summary'
-LANGUAGE C STRICT;
+AS $$ SELECT all_visible, all_frozen FROM pg_visibility_map_summary_extended($1) $$
+LANGUAGE SQL;
-- Show tupleids of non-frozen tuples if any in all_frozen pages
-- for a relation.
diff --git a/contrib/pg_visibility/pg_visibility.c b/contrib/pg_visibility/pg_visibility.c
index 2a4acfd1ee..48c30b222a 100644
--- a/contrib/pg_visibility/pg_visibility.c
+++ b/contrib/pg_visibility/pg_visibility.c
@@ -44,7 +44,7 @@ PG_FUNCTION_INFO_V1(pg_visibility_map);
PG_FUNCTION_INFO_V1(pg_visibility_map_rel);
PG_FUNCTION_INFO_V1(pg_visibility);
PG_FUNCTION_INFO_V1(pg_visibility_rel);
-PG_FUNCTION_INFO_V1(pg_visibility_map_summary);
+PG_FUNCTION_INFO_V1(pg_visibility_map_summary_extended);
PG_FUNCTION_INFO_V1(pg_check_frozen);
PG_FUNCTION_INFO_V1(pg_check_visible);
PG_FUNCTION_INFO_V1(pg_truncate_visibility_map);
@@ -247,11 +247,11 @@ pg_visibility_rel(PG_FUNCTION_ARGS)
}
/*
- * Count the number of all-visible and all-frozen pages in the visibility
- * map for a particular relation.
+ * Count the number of all-visible and all-frozen pages in the visibility map
+ * as well as the total number of blocks of a particular relation.
*/
Datum
-pg_visibility_map_summary(PG_FUNCTION_ARGS)
+pg_visibility_map_summary_extended(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
Relation rel;
@@ -261,8 +261,8 @@ pg_visibility_map_summary(PG_FUNCTION_ARGS)
int64 all_visible = 0;
int64 all_frozen = 0;
TupleDesc tupdesc;
- Datum values[2];
- bool nulls[2] = {0};
+ Datum values[3];
+ bool nulls[3] = {0};
rel = relation_open(relid, AccessShareLock);
@@ -296,6 +296,7 @@ pg_visibility_map_summary(PG_FUNCTION_ARGS)
values[0] = Int64GetDatum(all_visible);
values[1] = Int64GetDatum(all_frozen);
+ values[2] = Int64GetDatum(nblocks);
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
diff --git a/doc/src/sgml/pgvisibility.sgml b/doc/src/sgml/pgvisibility.sgml
index 097f7e0566..4358e267b1 100644
--- a/doc/src/sgml/pgvisibility.sgml
+++ b/doc/src/sgml/pgvisibility.sgml
@@ -99,6 +99,18 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><function>pg_visibility_map_summary_extended(relation regclass, all_visible OUT bigint, all_frozen OUT bigint, nblocks OUT bigint) returns record</function></term>
+
+ <listitem>
+ <para>
+ Returns the number of all-visible pages, the number of all-frozen pages,
+ and the total number of blocks in the relation according to the
+ visibility map.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><function>pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid</function></term>
--
2.37.2
From c15216bbf15e288acc758abdb88a4ec1ad17f9d1 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 16:00:15 -0500
Subject: [PATCH v1 8/9] Display freeze statistics
Display the number of freezes and unfreezes for a relation in
pg_stat_all_tables. Also add a new function pg_stat_get_table_vacuums()
which returns all the PgStat_Frz entries in a table's
PgStat_StatTabEntry->frz_buckets.
---
doc/src/sgml/monitoring.sgml | 20 +++
src/backend/catalog/system_views.sql | 2 +
src/backend/utils/adt/pgstatfuncs.c | 178 +++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 20 +++
src/test/regress/expected/rules.out | 6 +
5 files changed, 226 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e068f7e247..5bb7dda326 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3864,6 +3864,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>page_freezes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times pages were marked frozen in the visibility map by
+ vacuums of this table.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>page_unfreezes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times frozen pages of this table were modified and the frozen
+ bit unset in the visibility map.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>analyze_count</structfield> <type>bigint</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b65f6b5249..64b8dc7f7c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -675,6 +675,8 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
+ pg_stat_get_page_freezes(C.oid) AS page_freezes,
+ pg_stat_get_page_unfreezes(C.oid) AS page_unfreezes,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1fb8b31863..bee3980623 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -32,6 +32,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/inet.h"
+#include "utils/pg_lsn.h"
#include "utils/timestamp.h"
#define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var))))
@@ -108,6 +109,53 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
/* pg_stat_get_vacuum_count */
PG_STAT_GET_RELENTRY_INT64(vacuum_count)
+Datum
+pg_stat_get_page_freezes(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 freezes = 0;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ PG_RETURN_NULL();
+
+ for (int i = 0; i < tabentry->frz_nbuckets_used; i++)
+ {
+ PgStat_Frz *frz = &tabentry->frz_buckets[i];
+
+ if (frz->start_lsn == InvalidXLogRecPtr)
+ continue;
+
+ freezes += frz->vm_page_freezes;
+ }
+
+ PG_RETURN_INT64(freezes);
+}
+
+Datum
+pg_stat_get_page_unfreezes(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 unfreezes = 0;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ PG_RETURN_NULL();
+
+ for (int i = 0; i < tabentry->frz_nbuckets_used; i++)
+ {
+ PgStat_Frz *frz = &tabentry->frz_buckets[i];
+
+ if (frz->start_lsn == InvalidXLogRecPtr)
+ continue;
+
+ unfreezes += frz->unfreezes;
+ }
+
+ PG_RETURN_INT64(unfreezes);
+}
+
+
#define PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat) \
Datum \
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS) \
@@ -1446,6 +1494,136 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * Calculate the LSN generation rate for a given freeze bucket in LSNs/second.
+ */
+static float
+pgstat_frz_vac_lsn_gen_rate(PgStat_Frz *vacuum)
+{
+ TimestampTz end_time;
+ XLogRecPtr end_lsn;
+ int64 time_elapsed;
+ int64 lsns_elapsed;
+
+ Assert(vacuum->start_lsn != InvalidXLogRecPtr);
+ Assert(vacuum->start_time >= 0);
+
+ if (vacuum->end_lsn == InvalidXLogRecPtr)
+ {
+ /* get exact current LSN since cost isn't very important here */
+ end_lsn = GetXLogInsertRecPtr();
+ end_time = GetCurrentTimestamp();
+ }
+ else
+ {
+ end_lsn = vacuum->end_lsn;
+ end_time = vacuum->end_time;
+ }
+
+ time_elapsed = end_time - vacuum->start_time;
+ lsns_elapsed = end_lsn - vacuum->start_lsn;
+
+ /* If nothing happened during this vacuum, it is possible 0 LSNs elapsed. */
+ if (lsns_elapsed <= 0)
+ return 0;
+
+ return (float) time_elapsed * USECS_PER_SEC / lsns_elapsed;
+}
+
+
+#define TABLE_VACUUM_STAT_NCOLS 21
+Datum
+pg_stat_get_table_vacuums(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_StatTabEntry *tabentry;
+ Oid tableoid = PG_GETARG_OID(0);
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(tableoid)) == NULL)
+ return (Datum) 0;
+
+ for (int i = 0; i < tabentry->frz_nbuckets_used; i++)
+ {
+ PgStat_Frz *frz;
+ Datum values[TABLE_VACUUM_STAT_NCOLS] = {0};
+ bool nulls[TABLE_VACUUM_STAT_NCOLS] = {0};
+
+ frz = &tabentry->frz_buckets[
+ (tabentry->frz_oldest + i) % VAC_FRZ_STATS_MAX_NBUCKETS
+ ];
+
+ Assert(frz->start_lsn != InvalidXLogRecPtr);
+
+ values[0] = ObjectIdGetDatum(tableoid);
+ values[1] = Int32GetDatum(frz->count);
+ values[2] = LSNGetDatum(frz->start_time);
+ values[3] = LSNGetDatum(frz->start_lsn);
+ values[4] = LSNGetDatum(frz->end_time);
+ values[5] = LSNGetDatum(frz->end_lsn);
+ values[6] = Float8GetDatum(pgstat_frz_vac_lsn_gen_rate(frz));
+
+ values[7] = Int64GetDatum(frz->vm_page_freezes);
+ values[8] = Int64GetDatum(frz->freezes);
+
+ if (frz->freezes > 0)
+ {
+ int64 page_age_range;
+
+ values[9] = Float8GetDatum(frz->sum_page_age_lsns / frz->freezes);
+
+ page_age_range =
+ Int64GetDatum(frz->max_frz_page_age - frz->min_frz_page_age);
+
+ values[10] = Int64GetDatum(page_age_range);
+ }
+ else
+ {
+ nulls[9] = true;
+ nulls[10] = true;
+ }
+
+ values[11] = Int64GetDatum(frz->unfreezes);
+
+ if (frz->unfreezes > 0)
+ {
+ int64 frz_duration_range;
+
+ values[12] = Float8GetDatum(frz->total_frozen_duration_lsns / frz->unfreezes);
+
+ frz_duration_range =
+ Int64GetDatum(frz->max_frozen_duration_lsns - frz->min_frozen_duration_lsns);
+
+ values[13] = Int64GetDatum(frz_duration_range);
+ }
+ else
+ {
+ nulls[12] = true;
+ nulls[13] = true;
+ }
+
+ if (frz->early_unfreezes > 0)
+ values[14] = Int64GetDatum(frz->early_unfreezes);
+ else
+ nulls[14] = true;
+
+ values[15] = Int64GetDatum(frz->relsize_start);
+ values[16] = Int64GetDatum(frz->frozen_pages_start);
+ values[17] = Int64GetDatum(frz->relsize_end);
+ values[18] = Int64GetDatum(frz->frozen_pages_end);
+ values[19] = Int64GetDatum(frz->scanned_pages);
+ values[20] = Int64GetDatum(frz->freeze_fpis);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+
+ return (Datum) 0;
+}
+
+
/*
* Returns statistics of WAL activity
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f14aed422a..c113adcebc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5407,6 +5407,17 @@
proname => 'pg_stat_get_vacuum_count', provolatile => 's', proparallel => 'r',
prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_vacuum_count' },
+
+{ oid => '9998', descr => 'statistics: number of page freezes for this relation',
+ proname => 'pg_stat_get_page_freezes', provolatile => 's', proparallel => 'r',
+ prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_page_freezes' },
+
+{ oid => '9997', descr => 'statistics: number of page unfreezes for this relation',
+ proname => 'pg_stat_get_page_unfreezes', provolatile => 's', proparallel => 'r',
+ prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_page_unfreezes' },
+
{ oid => '3055', descr => 'statistics: number of auto vacuums for a table',
proname => 'pg_stat_get_autovacuum_count', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
@@ -5755,6 +5766,15 @@
proargnames => '{backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
prosrc => 'pg_stat_get_io' },
+{ oid => '9999', descr => 'statistics: table freeze heuristic info',
+ proname => 'pg_stat_get_table_vacuums', prorows => '15', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => 'oid',
+ proallargtypes => '{oid,oid,int4,timestamptz,pg_lsn,timestamptz,pg_lsn,float8,int8,int8,float8,int8,int8,float8,int8,int8,int8,int8,int8,int8,int8,int8}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{reloid,reloid,members,start_time,start_lsn,end_time,end_lsn,lsn_gen_rate,vm_page_freezes,freezes,avg_page_age_lsns,range_page_age_lsns,unfreezes,avg_frozen_duration_lsns,range_frozen_duration_lsns,early_unfreezes,npages_start,nfrozen_start,npages_end,nfrozen_end,scanned_pages,freeze_fpis}',
+ prosrc => 'pg_stat_get_table_vacuums' },
+
{ oid => '1136', descr => 'statistics: information about WAL activity',
proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1442c43d9c..ff592e1f6b 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1800,6 +1800,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
+ pg_stat_get_page_freezes(c.oid) AS page_freezes,
+ pg_stat_get_page_unfreezes(c.oid) AS page_unfreezes,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
@@ -2169,6 +2171,8 @@ pg_stat_sys_tables| SELECT relid,
last_analyze,
last_autoanalyze,
vacuum_count,
+ page_freezes,
+ page_unfreezes,
autovacuum_count,
analyze_count,
autoanalyze_count
@@ -2217,6 +2221,8 @@ pg_stat_user_tables| SELECT relid,
last_analyze,
last_autoanalyze,
vacuum_count,
+ page_freezes,
+ page_unfreezes,
autovacuum_count,
analyze_count,
autoanalyze_count
--
2.37.2
From f7ce64ee76a20929392ead5f3e3a6298219a3e38 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 17:36:22 -0500
Subject: [PATCH v1 7/9] Track vacuum freeze and page unfreeze stats
At the beginning of the vacuum of a relation, set up a new PgStat_Frz
entry in the table-level freeze statistics,
PgStat_StatTabEntry->frz_buckets and initialize relevant members in
the LVRelState.
When vacuum freezes a page, increment the freeze-related statistics in the
LVRelState. Then, at the end of the vacuum, transfer the stats gathered
during vacuum to the current PgStat_Frz in
PgStat_StatTabEntry->frz_buckets.
When modifying a frozen page, count this as an unfreeze in the
PgStat_Frz entry which covers the page freeze LSN.
---
src/backend/access/heap/heapam.c | 116 ++++++++++--
src/backend/access/heap/pruneheap.c | 1 +
src/backend/access/heap/vacuumlazy.c | 110 +++++++++++-
src/backend/utils/activity/pgstat_relation.c | 175 ++++++++++++++++++-
src/include/access/heapam.h | 5 +
src/include/commands/vacuum.h | 29 +++
src/include/pgstat.h | 11 +-
7 files changed, 423 insertions(+), 24 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 80828f3efe..c8d0045bef 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1831,8 +1831,11 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
TransactionId xid = GetCurrentTransactionId();
HeapTuple heaptup;
Buffer buffer;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
+ XLogRecPtr page_lsn = InvalidXLogRecPtr;
Buffer vmbuffer = InvalidBuffer;
bool all_visible_cleared = false;
+ bool all_frozen_cleared = false;
/* Cheap, simplistic check that the tuple matches the rel's rowtype. */
Assert(HeapTupleHeaderGetNatts(tup->t_data) <=
@@ -1882,9 +1885,11 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
{
all_visible_cleared = true;
PageClearAllVisible(BufferGetPage(buffer));
- visibilitymap_clear(relation,
- ItemPointerGetBlockNumber(&(heaptup->t_self)),
- vmbuffer, VISIBILITYMAP_VALID_BITS);
+
+ all_frozen_cleared = visibilitymap_clear(relation,
+ ItemPointerGetBlockNumber(&(heaptup->t_self)),
+ vmbuffer,
+ VISIBILITYMAP_VALID_BITS) & VISIBILITYMAP_ALL_FROZEN;
}
/*
@@ -1976,6 +1981,9 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
recptr = XLogInsert(RM_HEAP_ID, info);
+ insert_lsn = recptr;
+ page_lsn = PageGetLSN(page);
+
PageSetLSN(page, recptr);
}
@@ -1996,6 +2004,11 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
/* Note: speculative insertions are counted too, even if aborted later */
pgstat_count_heap_insert(relation, 1);
+ if (all_frozen_cleared)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ page_lsn, insert_lsn);
+
/*
* If heaptup is a private copy, release it. Don't forget to copy t_self
* back to the caller's image, too.
@@ -2161,6 +2174,9 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
while (ndone < ntuples)
{
Buffer buffer;
+ XLogRecPtr page_lsn = InvalidXLogRecPtr;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
+ bool all_frozen_cleared = false;
bool all_visible_cleared = false;
bool all_frozen_set = false;
int nthispage;
@@ -2248,9 +2264,10 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
{
all_visible_cleared = true;
PageClearAllVisible(page);
- visibilitymap_clear(relation,
- BufferGetBlockNumber(buffer),
- vmbuffer, VISIBILITYMAP_VALID_BITS);
+ all_frozen_cleared = visibilitymap_clear(relation,
+ BufferGetBlockNumber(buffer),
+ vmbuffer,
+ VISIBILITYMAP_VALID_BITS) & VISIBILITYMAP_ALL_FROZEN;
}
else if (all_frozen_set)
PageSetAllVisible(page);
@@ -2372,14 +2389,22 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
recptr = XLogInsert(RM_HEAP2_ID, info);
+ insert_lsn = recptr;
+ page_lsn = PageGetLSN(page);
PageSetLSN(page, recptr);
}
END_CRIT_SECTION();
+ if (all_frozen_cleared)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ page_lsn, insert_lsn);
+
/*
* If we've frozen everything on the page, update the visibilitymap.
- * We're already holding pin on the vmbuffer.
+ * We're already holding pin on the vmbuffer. We don't count page
+ * freezes done as part of COPY FREEZE toward page freeze stats.
*/
if (all_frozen_set)
{
@@ -2532,6 +2557,9 @@ heap_delete(Relation relation, ItemPointer tid,
bool have_tuple_lock = false;
bool iscombo;
bool all_visible_cleared = false;
+ bool all_frozen_cleared = false;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
+ XLogRecPtr page_lsn = InvalidXLogRecPtr;
HeapTuple old_key_tuple = NULL; /* replica identity of the tuple */
bool old_key_copied = false;
@@ -2788,8 +2816,10 @@ l1:
{
all_visible_cleared = true;
PageClearAllVisible(page);
- visibilitymap_clear(relation, BufferGetBlockNumber(buffer),
- vmbuffer, VISIBILITYMAP_VALID_BITS);
+ all_frozen_cleared = visibilitymap_clear(relation,
+ BufferGetBlockNumber(buffer),
+ vmbuffer,
+ VISIBILITYMAP_VALID_BITS) & VISIBILITYMAP_ALL_FROZEN;
}
/* store transaction information of xact deleting the tuple */
@@ -2872,6 +2902,9 @@ l1:
recptr = XLogInsert(RM_HEAP_ID, XLOG_HEAP_DELETE);
+ insert_lsn = recptr;
+ page_lsn = PageGetLSN(page);
+
PageSetLSN(page, recptr);
}
@@ -2915,6 +2948,11 @@ l1:
pgstat_count_heap_delete(relation);
+ if (all_frozen_cleared)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ page_lsn, insert_lsn);
+
if (old_key_tuple != NULL && old_key_copied)
heap_freetuple(old_key_tuple);
@@ -3021,6 +3059,12 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
infomask_new_tuple,
infomask2_new_tuple;
+ bool cleared_all_frozen = false;
+ bool cleared_all_frozen_new = false;
+ XLogRecPtr old_page_lsn = InvalidXLogRecPtr;
+ XLogRecPtr new_page_lsn = InvalidXLogRecPtr;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
+
Assert(ItemPointerIsValid(otid));
/* Cheap, simplistic check that the tuple matches the rel's rowtype. */
@@ -3503,7 +3547,6 @@ l2:
TransactionId xmax_lock_old_tuple;
uint16 infomask_lock_old_tuple,
infomask2_lock_old_tuple;
- bool cleared_all_frozen = false;
/*
* To prevent concurrent sessions from updating the tuple, we have to
@@ -3578,6 +3621,8 @@ l2:
cleared_all_frozen ? XLH_LOCK_ALL_FROZEN_CLEARED : 0;
XLogRegisterData((char *) &xlrec, SizeOfHeapLock);
recptr = XLogInsert(RM_HEAP_ID, XLOG_HEAP_LOCK);
+ insert_lsn = recptr;
+ old_page_lsn = PageGetLSN(page);
PageSetLSN(page, recptr);
}
@@ -3585,6 +3630,16 @@ l2:
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
+ if (cleared_all_frozen)
+ {
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ old_page_lsn, insert_lsn);
+
+ /* Avoid double counting page unfreezes. */
+ cleared_all_frozen = false;
+ }
+
/*
* Let the toaster do its thing, if needed.
*
@@ -3786,15 +3841,15 @@ l2:
{
all_visible_cleared = true;
PageClearAllVisible(BufferGetPage(buffer));
- visibilitymap_clear(relation, BufferGetBlockNumber(buffer),
- vmbuffer, VISIBILITYMAP_VALID_BITS);
+ cleared_all_frozen = visibilitymap_clear(relation, BufferGetBlockNumber(buffer),
+ vmbuffer, VISIBILITYMAP_VALID_BITS) & VISIBILITYMAP_ALL_FROZEN;
}
if (newbuf != buffer && PageIsAllVisible(BufferGetPage(newbuf)))
{
all_visible_cleared_new = true;
PageClearAllVisible(BufferGetPage(newbuf));
- visibilitymap_clear(relation, BufferGetBlockNumber(newbuf),
- vmbuffer_new, VISIBILITYMAP_VALID_BITS);
+ cleared_all_frozen_new = visibilitymap_clear(relation, BufferGetBlockNumber(newbuf),
+ vmbuffer_new, VISIBILITYMAP_VALID_BITS) & VISIBILITYMAP_ALL_FROZEN;
}
if (newbuf != buffer)
@@ -3823,9 +3878,14 @@ l2:
all_visible_cleared_new);
if (newbuf != buffer)
{
+ new_page_lsn = PageGetLSN(BufferGetPage(newbuf));
PageSetLSN(BufferGetPage(newbuf), recptr);
}
+
+ old_page_lsn = PageGetLSN(BufferGetPage(buffer));
PageSetLSN(BufferGetPage(buffer), recptr);
+
+ insert_lsn = recptr;
}
END_CRIT_SECTION();
@@ -3861,6 +3921,16 @@ l2:
pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer);
+ if (cleared_all_frozen)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ old_page_lsn, insert_lsn);
+
+ if (newbuf != buffer && cleared_all_frozen_new)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ new_page_lsn, insert_lsn);
+
/*
* If heaptup is a private copy, release it. Don't forget to copy t_self
* back to the caller's image, too.
@@ -4146,6 +4216,8 @@ heap_lock_tuple(Relation relation, HeapTuple tuple,
Page page;
Buffer vmbuffer = InvalidBuffer;
BlockNumber block;
+ XLogRecPtr page_lsn = InvalidXLogRecPtr;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
TransactionId xid,
xmax;
uint16 old_infomask,
@@ -4791,6 +4863,8 @@ failed:
/* we don't decode row locks atm, so no need to log the origin */
recptr = XLogInsert(RM_HEAP_ID, XLOG_HEAP_LOCK);
+ insert_lsn = recptr;
+ page_lsn = PageGetLSN(page);
PageSetLSN(page, recptr);
}
@@ -4818,6 +4892,11 @@ out_unlocked:
if (have_tuple_lock)
UnlockTupleTuplock(relation, tid, mode);
+ if (cleared_all_frozen)
+ pgstat_count_page_unfreeze(RelationGetRelid(relation),
+ relation->rd_rel->relisshared,
+ page_lsn, insert_lsn);
+
return result;
}
@@ -5270,6 +5349,8 @@ heap_lock_updated_tuple_rec(Relation rel, ItemPointer tid, TransactionId xid,
new_xmax;
TransactionId priorXmax = InvalidTransactionId;
bool cleared_all_frozen = false;
+ XLogRecPtr page_lsn = InvalidXLogRecPtr;
+ XLogRecPtr insert_lsn = InvalidXLogRecPtr;
bool pinned_desired_page;
Buffer vmbuffer = InvalidBuffer;
BlockNumber block;
@@ -5543,6 +5624,8 @@ l4:
XLogRegisterData((char *) &xlrec, SizeOfHeapLockUpdated);
recptr = XLogInsert(RM_HEAP2_ID, XLOG_HEAP2_LOCK_UPDATED);
+ insert_lsn = recptr;
+ page_lsn = PageGetLSN(page);
PageSetLSN(page, recptr);
}
@@ -5575,6 +5658,11 @@ out_unlocked:
if (vmbuffer != InvalidBuffer)
ReleaseBuffer(vmbuffer);
+ if (cleared_all_frozen)
+ pgstat_count_page_unfreeze(RelationGetRelid(rel),
+ rel->rd_rel->relisshared,
+ page_lsn, insert_lsn);
+
return result;
}
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index c5f1abd95a..cfd764c1f3 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -237,6 +237,7 @@ heap_page_prune(Relation relation, Buffer buffer,
*/
presult->ndeleted = 0;
presult->nnewlpdead = 0;
+ presult->page_lsn = PageGetLSN(page);
maxoff = PageGetMaxOffsetNumber(page);
tup.t_tableOid = RelationGetRelid(prstate.rel);
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 9409cf6b38..a74516003f 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -227,7 +227,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
minmulti_updated;
BlockNumber orig_rel_pages,
new_rel_pages,
- new_rel_allvisible;
+ new_rel_allvisible,
+ new_rel_allfrozen;
PGRUsage ru0;
TimestampTz starttime = 0;
PgStat_Counter startreadtime = 0,
@@ -341,6 +342,13 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->nonempty_pages = 0;
/* dead_items_alloc allocates vacrel->dead_items later on */
+ vacrel->sum_frozen_page_ages = 0;
+ vacrel->vm_pages_frozen = 0;
+ vacrel->already_frozen_pages = 0;
+ vacrel->max_frz_page_age = InvalidXLogRecPtr;
+ vacrel->min_frz_page_age = InvalidXLogRecPtr;
+ vacrel->freeze_fpis = 0;
+
/* Allocate/initialize output statistics state */
vacrel->new_rel_tuples = 0;
vacrel->new_live_tuples = 0;
@@ -405,6 +413,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->relname)));
}
+ pgstat_setup_vacuum_frz_stats(RelationGetRelid(rel),
+ rel->rd_rel->relisshared);
+
/*
* Allocate dead_items array memory using dead_items_alloc. This handles
* parallel VACUUM initialization as part of allocating shared memory
@@ -483,10 +494,13 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
* pg_class.relpages to
*/
new_rel_pages = vacrel->rel_pages; /* After possible rel truncation */
- visibilitymap_count(rel, &new_rel_allvisible, NULL);
+ visibilitymap_count(rel, &new_rel_allvisible, &new_rel_allfrozen);
if (new_rel_allvisible > new_rel_pages)
new_rel_allvisible = new_rel_pages;
+ if (new_rel_allfrozen > new_rel_pages)
+ new_rel_allfrozen = new_rel_pages;
+
/*
* Now actually update rel's pg_class entry.
*
@@ -501,7 +515,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
/* Report results to the cumulative stats system, too. */
pgstat_report_vacuum(RelationGetRelid(rel),
- rel->rd_rel->relisshared, vacrel);
+ rel->rd_rel->relisshared, vacrel,
+ orig_rel_pages, new_rel_allfrozen);
+
pgstat_progress_end_command();
if (instrument)
@@ -995,6 +1011,7 @@ lazy_scan_heap(LVRelState *vacrel)
if (!all_visible_according_to_vm && prunestate.all_visible)
{
uint8 flags = VISIBILITYMAP_ALL_VISIBLE;
+ uint8 previous_flags;
if (prunestate.all_frozen)
{
@@ -1017,9 +1034,18 @@ lazy_scan_heap(LVRelState *vacrel)
*/
PageSetAllVisible(page);
MarkBufferDirty(buf);
- visibilitymap_set(vacrel->rel, blkno, buf, InvalidXLogRecPtr,
- vmbuffer, prunestate.visibility_cutoff_xid,
- flags);
+ previous_flags = visibilitymap_set(vacrel->rel, blkno, buf, InvalidXLogRecPtr,
+ vmbuffer, prunestate.visibility_cutoff_xid,
+ flags);
+
+ /*
+ * If we newly set all frozen here, count it. Don't worry about
+ * updating page age statistics since we are not actually
+ * modifying the tuples on the page.
+ */
+ if (prunestate.all_frozen &&
+ !(previous_flags & VISIBILITYMAP_ALL_FROZEN))
+ vacrel->vm_pages_frozen++;
}
/*
@@ -1033,6 +1059,13 @@ lazy_scan_heap(LVRelState *vacrel)
{
elog(WARNING, "page is not marked all-visible but visibility map bit is set in relation \"%s\" page %u",
vacrel->relname, blkno);
+
+ /*
+ * In the case of data corruption, we don't bother counting the
+ * page as unfrozen in the stats. We don't have easy access to the
+ * page LSN from before any modifications were made by vacuum, so
+ * it is hard to count it properly here anyway.
+ */
visibilitymap_clear(vacrel->rel, blkno, vmbuffer,
VISIBILITYMAP_VALID_BITS);
}
@@ -1058,6 +1091,11 @@ lazy_scan_heap(LVRelState *vacrel)
vacrel->relname, blkno);
PageClearAllVisible(page);
MarkBufferDirty(buf);
+
+ /*
+ * This unfreeze is not counted in stats for the same reason
+ * detailed above.
+ */
visibilitymap_clear(vacrel->rel, blkno, vmbuffer,
VISIBILITYMAP_VALID_BITS);
}
@@ -1094,6 +1132,12 @@ lazy_scan_heap(LVRelState *vacrel)
vmbuffer, InvalidTransactionId,
VISIBILITYMAP_ALL_VISIBLE |
VISIBILITYMAP_ALL_FROZEN);
+
+ /*
+ * Don't worry about updating page age stats since we only updated
+ * the VM.
+ */
+ vacrel->vm_pages_frozen++;
}
/*
@@ -1212,6 +1256,9 @@ lazy_scan_skip(LVRelState *vacrel, Buffer *vmbuffer, BlockNumber next_block,
next_unskippable_block,
vmbuffer);
+ if ((mapbits & VISIBILITYMAP_ALL_FROZEN) != 0)
+ vacrel->already_frozen_pages++;
+
if ((mapbits & VISIBILITYMAP_ALL_VISIBLE) == 0)
{
Assert((mapbits & VISIBILITYMAP_ALL_FROZEN) == 0);
@@ -1403,6 +1450,9 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
vmbuffer, InvalidTransactionId,
VISIBILITYMAP_ALL_VISIBLE | VISIBILITYMAP_ALL_FROZEN);
END_CRIT_SECTION();
+
+ /* Count a page freeze since we set it in the VM. */
+ vacrel->vm_pages_frozen++;
}
freespace = PageGetHeapFreeSpace(page);
@@ -1452,6 +1502,8 @@ lazy_scan_prune(LVRelState *vacrel,
lpdead_items,
live_tuples,
recently_dead_tuples;
+ XLogRecPtr insert_lsn;
+ int64 page_age;
HeapPageFreeze pagefrz;
int64 fpi_before = pgWalUsage.wal_fpi;
OffsetNumber deadoffsets[MaxHeapTuplesPerPage];
@@ -1675,6 +1727,22 @@ lazy_scan_prune(LVRelState *vacrel,
*/
vacrel->offnum = InvalidOffsetNumber;
+ insert_lsn = GetInsertRecPtr();
+
+ /*
+ * The page may have been modified by pruning, however, we want to know
+ * how many LSNs since it was last modified by a DML operation.
+ */
+ page_age = insert_lsn - presult.page_lsn;
+
+ /*
+ * Because GetInsertRecPtr() returns the approximate insert LSN (it may be
+ * up to a page behind the real insert LSN), there is a small chance for
+ * it to be behind page lsn. In this case, the page is basically 0, so
+ * count it as such.
+ */
+ page_age = Max(page_age, 0);
+
/*
* Freeze the page when heap_prepare_freeze_tuple indicates that at least
* one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
@@ -1712,8 +1780,20 @@ lazy_scan_prune(LVRelState *vacrel,
{
TransactionId snapshotConflictHorizon;
+ fpi_before = pgWalUsage.wal_fpi;
+
vacrel->pages_frozen++;
+ vacrel->sum_frozen_page_ages += page_age;
+
+ if (vacrel->max_frz_page_age == InvalidXLogRecPtr ||
+ page_age > vacrel->max_frz_page_age)
+ vacrel->max_frz_page_age = page_age;
+
+ if (vacrel->min_frz_page_age == InvalidXLogRecPtr ||
+ page_age < vacrel->min_frz_page_age)
+ vacrel->min_frz_page_age = page_age;
+
/*
* We can use visibility_cutoff_xid as our cutoff for conflicts
* when the whole page is eligible to become all-frozen in the VM
@@ -1737,6 +1817,9 @@ lazy_scan_prune(LVRelState *vacrel,
heap_freeze_execute_prepared(vacrel->rel, buf,
snapshotConflictHorizon,
frozen, tuples_frozen);
+
+ if (pgWalUsage.wal_fpi > fpi_before)
+ vacrel->freeze_fpis++;
}
}
else
@@ -2489,6 +2572,7 @@ lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, Buffer buffer,
if (heap_page_is_all_visible(vacrel, buffer, &visibility_cutoff_xid,
&all_frozen))
{
+ uint8 previous_flags;
uint8 flags = VISIBILITYMAP_ALL_VISIBLE;
if (all_frozen)
@@ -2498,8 +2582,18 @@ lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, Buffer buffer,
}
PageSetAllVisible(page);
- visibilitymap_set(vacrel->rel, blkno, buffer, InvalidXLogRecPtr,
- vmbuffer, visibility_cutoff_xid, flags);
+ previous_flags = visibilitymap_set(vacrel->rel, blkno, buffer, InvalidXLogRecPtr,
+ vmbuffer, visibility_cutoff_xid, flags);
+
+ /*
+ * If we set the page all frozen in the VM and it was not marked as
+ * such before, count it here. We don't consider page age for max and
+ * min page age for the purpose of per-vacuum stats here since we will
+ * not have newly frozen tuples on the page and only will have marked
+ * the page frozen in the VM.
+ */
+ if (all_frozen && !(previous_flags & VISIBILITYMAP_ALL_FROZEN))
+ vacrel->vm_pages_frozen++;
}
/* Revert to the previous phase information for error traceback */
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index eb387e7eac..e985a6a514 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -368,6 +368,154 @@ pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared)
}
+/*
+ * When a frozen page from a table with oid tableoid is modified, the page LSN
+ * before modification is passed into this function. This LSN is used to
+ * identify which bucket contains stats from the freeze period in which this
+ * page was frozen. Then that bucket's unfreeze counter incremented. If the
+ * page did not stay frozen for target_page_freeze_duration amount of time, it
+ * is also counted as an early unfreeze.
+ *
+ * MTODO: instead of accessing the table in shared memory, this should be
+ * cached locally and refetched when counting an unfreeze which is newer than
+ * any of its local recorded freeze periods.
+ */
+void
+pgstat_count_page_unfreeze(Oid tableoid, bool shared,
+ XLogRecPtr page_lsn, XLogRecPtr insert_lsn)
+{
+ Oid dboid = (shared ? InvalidOid : MyDatabaseId);
+ PgStat_EntryRef *entry_ref;
+ PgStat_StatTabEntry *tabentry;
+ TimestampTz current_time;
+
+ if (!pgstat_track_counts)
+ return;
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ dboid, tableoid, false);
+
+ tabentry = &((PgStatShared_Relation *) entry_ref->shared_stats)->stats;
+
+ current_time = GetCurrentTimestamp();
+
+ /*
+ * Loop through the freeze stats stored in the ring, starting with the
+ * oldest. By starting with the oldest, and since they are in order, we
+ * know that we will run into the bucket containing the period in which
+ * our page was frozen.
+ */
+ for (int i = 0; i < tabentry->frz_nbuckets_used; i++)
+ {
+ PgStat_Frz *frz;
+ XLogRecPtr end_lsn;
+ TimestampTz end_time;
+ int64 time_elapsed;
+ int64 lsns_elapsed;
+ int64 frz_lsn;
+ int64 page_frz_time;
+ int64 page_frz_duration;
+ int64 page_age;
+
+ frz = &tabentry->frz_buckets[
+ (tabentry->frz_oldest + i) % VAC_FRZ_STATS_MAX_NBUCKETS
+ ];
+
+ /* no entry should have been added without a start lsn */
+ Assert(frz->start_lsn != InvalidXLogRecPtr);
+
+ /*
+ * If the bucket starts after our page LSN, we know we have passed any
+ * freeze bucket containing the freeze period in which our page could
+ * have been frozen, so we are done.
+ */
+ if (frz->start_lsn >= page_lsn)
+ break;
+
+ /*
+ * If this is a past sample (not a current, unfinished sample) and it
+ * ended before our page was frozen, we know our page was not frozen
+ * by this sample.
+ */
+ if (frz->end_lsn != InvalidXLogRecPtr && frz->end_lsn < page_lsn)
+ continue;
+
+ /*
+ * We've found the bucket to which this page LSN belongs. If this
+ * entry isn't over, then let's use the current time as the end time
+ * for the purpose of calculation.
+ */
+ if (frz->end_lsn == InvalidXLogRecPtr)
+ {
+ end_lsn = insert_lsn;
+ end_time = current_time;
+ }
+ else
+ {
+ end_lsn = frz->end_lsn;
+ end_time = frz->end_time;
+ }
+
+ /* Time in microseconds covered by the freeze bucket */
+ time_elapsed = end_time - frz->start_time;
+ /* LSNs covered by the freeze bucket */
+ lsns_elapsed = end_lsn - frz->start_lsn;
+
+ /* How many LSNs into the bucket was the page frozen */
+ frz_lsn = page_lsn - frz->start_lsn;
+
+ /*
+ * Time that corresponds to page LSN at which the page was frozen;
+ * basically the time at which the page was frozen
+ */
+ page_frz_time = (float) frz_lsn /
+ lsns_elapsed * time_elapsed + frz->start_time;
+
+ /* amount of time page stayed frozen (in microseconds) */
+ page_frz_duration = current_time - page_frz_time;
+
+ /*
+ * Depending on the LSN generation rate, if the page was frozen close
+ * to the end of the bucket, page_frz_duration may be negative.
+ */
+ page_frz_duration = Max(page_frz_duration, 0);
+
+ /*
+ * If the page stayed frozen less than target page freeze duration, it
+ * is an early unfreeze. Note that target_page_freeze_duration is in
+ * seconds.
+ */
+ if (page_frz_duration < target_page_freeze_duration * USECS_PER_SEC)
+ frz->early_unfreezes++;
+
+ frz->unfreezes++;
+
+ page_age = insert_lsn - page_lsn;
+
+ frz->total_frozen_duration_lsns += page_age;
+
+ if (frz->max_frozen_duration_lsns == InvalidXLogRecPtr ||
+ page_age > frz->max_frozen_duration_lsns)
+ frz->max_frozen_duration_lsns = page_age;
+
+ if (frz->min_frozen_duration_lsns == InvalidXLogRecPtr ||
+ page_age < frz->min_frozen_duration_lsns)
+ frz->min_frozen_duration_lsns = page_age;
+
+ break;
+ }
+
+ /*
+ * If the page is older than any of our currently tracked vacuums, we
+ * aren't going to count it. We are only concerned with the efficacy of
+ * our more recent vacuums. If a very old page is being unfrozen, that is
+ * fine anyway.
+ */
+
+ pgstat_unlock_entry(entry_ref);
+}
+
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*
@@ -375,13 +523,17 @@ pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared)
* pgstat_report_vacuum().
*/
void
-pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel)
+pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel,
+ BlockNumber orig_rel_pages,
+ BlockNumber new_rel_all_frozen)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
PgStat_StatTabEntry *tabentry;
Oid dboid = (shared ? InvalidOid : MyDatabaseId);
TimestampTz ts;
+ XLogRecPtr end_lsn;
+ PgStat_Frz *vacstat;
if (!pgstat_track_counts)
return;
@@ -389,6 +541,9 @@ pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel)
/* Store the data in the table's hash table entry. */
ts = GetCurrentTimestamp();
+ /* Don't use an approximate insert LSN for vacuum start and end */
+ end_lsn = GetXLogInsertRecPtr();
+
/* block acquiring lock for the same reason as pgstat_report_autovac() */
entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
dboid, tableoid, false);
@@ -409,6 +564,24 @@ pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel)
tabentry->dead_tuples = vacrel->recently_dead_tuples +
vacrel->missed_dead_tuples;
+ vacstat = &tabentry->frz_buckets[tabentry->frz_current];
+ vacstat->end_lsn = end_lsn;
+ vacstat->end_time = ts;
+
+ vacstat->scanned_pages = vacrel->scanned_pages;
+ vacstat->relsize_end = vacrel->rel_pages;
+ vacstat->relsize_start = orig_rel_pages;
+
+ vacstat->frozen_pages_end = new_rel_all_frozen;
+ vacstat->frozen_pages_start = vacrel->already_frozen_pages;
+
+ vacstat->freezes = vacrel->pages_frozen;
+ vacstat->sum_page_age_lsns = vacrel->sum_frozen_page_ages;
+ vacstat->vm_page_freezes = vacrel->vm_pages_frozen;
+ vacstat->max_frz_page_age = vacrel->max_frz_page_age;
+ vacstat->min_frz_page_age = vacrel->min_frz_page_age;
+ vacstat->freeze_fpis = vacrel->freeze_fpis;
+
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
* various pages, however, we'll zero the insert counter here regardless.
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index a2d7a0ea72..5b7e7b33f0 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -196,6 +196,11 @@ typedef struct HeapPageFreeze
*/
typedef struct PruneResult
{
+ /*
+ * Page LSN prior to pruning, regardless of whether or not the page was
+ * pruned. This is used by freeze logic.
+ */
+ XLogRecPtr page_lsn;
int ndeleted; /* Number of tuples deleted from the page */
int nnewlpdead; /* Number of newly LP_DEAD items */
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index fa91e1b465..ff7c75ed05 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -361,6 +361,35 @@ typedef struct LVRelState
BlockNumber missed_dead_pages; /* # pages with missed dead tuples */
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
+ /*
+ * The following members track freeze-related statistics which will be
+ * accumulated into the PgStat_StatTabEntry at the end of the vacuum.
+ */
+
+ /*
+ * Number of pages marked frozen in the VM before relation is vacuumed.
+ * This does not include pages marked frozen by this vacuum.
+ */
+ BlockNumber already_frozen_pages;
+
+ /*
+ * The sum of the age of every page with tuples frozen by this vacuum of
+ * the relation.
+ */
+ int64 sum_frozen_page_ages;
+
+ /*
+ * Pages newly marked frozen in the VM. This is inclusive of pages_frozen.
+ */
+ BlockNumber vm_pages_frozen;
+
+ /* oldest and youngest page we froze during this vacuum */
+ XLogRecPtr max_frz_page_age;
+ XLogRecPtr min_frz_page_age;
+
+ /* number of freeze records emitted by this vacuum containing FPIs */
+ BlockNumber freeze_fpis;
+
/* Statistics output by us, for table */
double new_rel_tuples; /* new estimated total # of tuples */
double new_live_tuples; /* new estimated total # of live tuples */
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 00c67deda4..e273b3aaee 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -11,10 +11,12 @@
#ifndef PGSTAT_H
#define PGSTAT_H
+#include "access/xlogdefs.h"
#include "commands/vacuum.h"
#include "datatype/timestamp.h"
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
+#include "storage/block.h"
#include "utils/backend_progress.h" /* for backward compatibility */
#include "utils/backend_status.h" /* for backward compatibility */
#include "utils/relcache.h"
@@ -718,10 +720,17 @@ extern void pgstat_init_relation(Relation rel);
extern void pgstat_assoc_relation(Relation rel);
extern void pgstat_unlink_relation(Relation rel);
-extern void pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel);
+extern void pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel,
+ BlockNumber orig_rel_pages,
+ BlockNumber new_rel_all_frozen);
extern void pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared);
+extern void pgstat_count_page_unfreeze(Oid tableoid, bool shared,
+ XLogRecPtr page_lsn, XLogRecPtr insert_lsn);
+
+extern void pgstat_count_page_freeze(Oid tableoid, bool shared, int64 page_age);
+
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter);
--
2.37.2
From 29d7292e7008282de4c9d7c9197b33d28b104f07 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 16:02:22 -0500
Subject: [PATCH v1 9/9] Make opportunistic freezing heuristic configurable
For the purposes of development, add a guc, opp_freeze_algo, which
determines the heuristic used by vacuum when deciding whether or not to
freeze a page. We anticipate using the average error rate across recent
vacuums to determine whether we need to freeze more or less
aggressively, so add a function to calculate that as well.
---
src/backend/access/heap/vacuumlazy.c | 82 ++++++++++++++++++-
src/backend/utils/activity/pgstat_relation.c | 70 ++++++++++++++++
src/backend/utils/init/globals.c | 1 +
src/backend/utils/misc/guc_tables.c | 10 +++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/miscadmin.h | 1 +
src/include/pgstat.h | 2 +
7 files changed, 165 insertions(+), 2 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index a74516003f..7b128eb3e8 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -203,6 +203,9 @@ static void update_vacuum_error_info(LVRelState *vacrel,
static void restore_vacuum_error_info(LVRelState *vacrel,
const LVSavedErrInfo *saved_vacrel);
+static bool vacuum_opp_freeze(LVRelState *vacrel,
+ int64 page_age, bool all_visible_all_frozen,
+ bool prune_emitted_fpi);
/*
* heap_vacuum_rel() -- perform VACUUM for one heap relation
@@ -1750,8 +1753,9 @@ lazy_scan_prune(LVRelState *vacrel,
* page all-frozen afterwards (might not happen until final heap pass).
*/
if (pagefrz.freeze_required || tuples_frozen == 0 ||
- (prunestate->all_visible && prunestate->all_frozen &&
- fpi_before != pgWalUsage.wal_fpi))
+ vacuum_opp_freeze(vacrel, page_age,
+ prunestate->all_visible && prunestate->all_frozen,
+ fpi_before != pgWalUsage.wal_fpi))
{
/*
* We're freezing the page. Our final NewRelfrozenXid doesn't need to
@@ -3499,3 +3503,77 @@ restore_vacuum_error_info(LVRelState *vacrel,
vacrel->offnum = saved_vacrel->offnum;
vacrel->phase = saved_vacrel->phase;
}
+
+#define VAC_FRZ_ERR_THRESHOLD 0.5
+
+/*
+ * Determine whether or not vacuum should opportunistically freeze a page.
+ * Given freeze statistics about the relation contained in LVRelState, whether
+ * or not the page will be able to be marked all visible and all frozen, and
+ * whether or not pruning emitted an FPI, return whether or not the page should
+ * be frozen. The LVRelState should not be modified.
+ */
+static bool
+vacuum_opp_freeze(LVRelState *vacrel,
+ int64 page_age,
+ bool all_visible_all_frozen,
+ bool prune_emitted_fpi)
+{
+ if (opp_freeze_algo == 0)
+ return all_visible_all_frozen && prune_emitted_fpi;
+
+ if (opp_freeze_algo == 1)
+ return all_visible_all_frozen;
+
+ if (opp_freeze_algo == 2)
+ {
+ float error_rate = 0;
+
+ /*
+ * if the page LSN is the same as the approximate insert LSN, it was
+ * modified very recently, so it isn't a good candidate for freezing
+ */
+ if (page_age == 0)
+ return false;
+
+ /*
+ * Calculate the average error rate for our past vacuums.
+ */
+ error_rate = pgstat_frz_error_rate(RelationGetRelid(vacrel->rel));
+
+ /*
+ * If the error rate is 0, either we don't have enough vacuums yet to
+ * have data, we haven't frozen anything yet, or we have a perfect
+ * track record. In all of these cases, freeze the page.
+ */
+ if (error_rate == 0)
+ return true;
+
+ /*
+ * Here it may be worth freezing the page if it is older than the
+ * oldest page scanned so far and a certain number of pages have been
+ * scanned. Similarly, we could check if the page is younger than the
+ * youngest page scanned and a certain number of pages have been
+ * scanned.
+ */
+
+ /*
+ * If we have frozen some pages, we can calculate the average page age
+ * and use that to determine whether or not to freeze the page.
+ */
+ if (vacrel->pages_frozen > 0)
+ {
+ float avg_page_age = 0;
+
+ avg_page_age = (float) vacrel->sum_frozen_page_ages /
+ vacrel->pages_frozen;
+
+ if (error_rate > VAC_FRZ_ERR_THRESHOLD &&
+ page_age > avg_page_age)
+ return true;
+
+ }
+ }
+
+ return false;
+}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index e985a6a514..3f2df583fe 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -367,6 +367,76 @@ pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared)
pgstat_flush_io(false);
}
+/*
+ * Calculate the average error rate for recorded freeze periods.
+ *
+ * MTODO: should this be done in the cached backend local copy or the copy in
+ * shared memory.
+ */
+float
+pgstat_frz_error_rate(Oid tableoid)
+{
+ PgStat_StatTabEntry *tabentry;
+ int64 early_unfreezes = 0;
+ int64 freezes = 0;
+ bool skip_oldest;
+ int i;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(tableoid)) == NULL)
+ return 0;
+
+ if (tabentry->frz_nbuckets_used == 0)
+ return 0;
+
+ /*
+ * When calculating the freeze error rate, once we have used all available
+ * buckets, we restrict the calculation to exclude the oldest period to
+ * avoid a feedback loop where a high error rate causes us to freeze less
+ * and then because we freeze less we have less new data causing the old
+ * data to be over-represented. This could be done with a weighted average
+ * but, for now, simply exclude the oldest entry.
+ */
+ skip_oldest = tabentry->frz_nbuckets_used >= VAC_FRZ_STATS_MAX_NBUCKETS;
+
+ for (i = skip_oldest; i < tabentry->frz_nbuckets_used; i++)
+ {
+ PgStat_Frz *frz;
+
+ frz = &tabentry->frz_buckets[(tabentry->frz_oldest + i) % VAC_FRZ_STATS_MAX_NBUCKETS];
+
+ /*
+ * We don't include the current freeze period in our error rate rate
+ * calculation, as we have yet to see the consequences of those freeze
+ * decisions and including it will skew the numbers.
+ */
+ if (frz->start_lsn == InvalidXLogRecPtr ||
+ frz->end_lsn == InvalidXLogRecPtr)
+ continue;
+
+ /*
+ * We use vm_page_freezes, even though those are not all subject to
+ * the opportunistic freezing algorithm (and thus not in our power to
+ * decide whether or not to freeze) because we cannot distinguish
+ * between an unfreeze of a page that was opportunistically frozen and
+ * one that was marked frozen in the VM because it happened to qualify
+ * as all frozen.
+ */
+ freezes += frz->vm_page_freezes;
+
+ /*
+ * We care only about early_unfreezes, those pages which were unfrozen
+ * before target_page_freeze_duration had elapsed.
+ */
+ early_unfreezes += frz->early_unfreezes;
+ }
+
+ Assert(early_unfreezes <= freezes);
+
+ if (freezes == 0)
+ return 0;
+
+ return (float) early_unfreezes / freezes;
+}
/*
* When a frozen page from a table with oid tableoid is modified, the page LSN
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 43212ea55c..ab296da86c 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -149,6 +149,7 @@ int VacuumCostPageMiss = 2;
int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
+int opp_freeze_algo = 0;
int target_page_freeze_duration = 1;
int64 VacuumPageHit = 0;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 710f7f8a7d..99b909ac74 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2465,6 +2465,16 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"opp_freeze_algo", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("algorithm used to determine whether or not to freeze a page during vacuum"),
+ NULL
+ },
+ &opp_freeze_algo,
+ 0, 0, 10000,
+ NULL, NULL, NULL
+ },
+
{
{"target_page_freeze_duration", PGC_USERSET, AUTOVACUUM,
gettext_noop("minimum amount of time in seconds that a page should stay frozen."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9966746c4e..16cc0eab22 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,6 +661,7 @@
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
+#opp_freeze_algo = 0 # default opp_freeze_algo is 0 which means master
#target_page_freeze_duration = 1 # desired time for page to stay frozen in seconds
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index bd04ff2899..40f7ad3372 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -279,6 +279,7 @@ extern PGDLLIMPORT int VacuumCostPageMiss;
extern PGDLLIMPORT int VacuumCostPageDirty;
extern PGDLLIMPORT int VacuumCostLimit;
extern PGDLLIMPORT double VacuumCostDelay;
+extern PGDLLIMPORT int opp_freeze_algo;
extern PGDLLIMPORT int target_page_freeze_duration;
extern PGDLLIMPORT int64 VacuumPageHit;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index e273b3aaee..8965211e3d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -729,6 +729,8 @@ extern void pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared);
extern void pgstat_count_page_unfreeze(Oid tableoid, bool shared,
XLogRecPtr page_lsn, XLogRecPtr insert_lsn);
+extern float pgstat_frz_error_rate(Oid tableoid);
+
extern void pgstat_count_page_freeze(Oid tableoid, bool shared, int64 page_age);
extern void pgstat_report_analyze(Relation rel,
--
2.37.2
From 97e8c7f74c9c7c7450de3fbf872a436d26b16a05 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 8 Nov 2023 15:15:31 -0500
Subject: [PATCH v1 6/9] Add vacuum freeze statistics structures
Add a ring buffer of PgStat_Frz to the table-level stats in
PgStat_StatTabEntry. At the beginning of a vacuum of a relation,
initialize a new PgStat_Frz entry to record stats including how many
pages in the relation are frozen by this vacuum. Once all of the
available spots in the ring buffer are used, the oldest entries are
combined -- being careful not to combine stats from vacuums ending
before now - target_page_freeze_duration with those ending after.
Future commits will increment these stats and then use them to alter how
aggressively vacuum opportunistically freezes pages.
---
src/backend/utils/activity/pgstat_relation.c | 163 +++++++++++++++++++
src/include/pgstat.h | 133 +++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 297 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bd92380a68..eb387e7eac 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -50,6 +50,8 @@ static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info);
static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop);
static void restore_truncdrop_counters(PgStat_TableXactStatus *trans);
+static void pgstat_combine_vacuum_stats(PgStat_Frz *next, PgStat_Frz *oldest);
+
/*
* Copy stats between relations. This is used for things like REINDEX
@@ -205,6 +207,167 @@ pgstat_drop_relation(Relation rel)
}
}
+/*
+ * Given two adjacent PgStat_Frz, combine the data from the older PgStat_Frz
+ * into the newer one. This is used when PgStat_StatTabEntry has filled and we
+ * need to free up a spot for an imminent vacuum.
+ */
+static void
+pgstat_combine_vacuum_stats(PgStat_Frz *next, PgStat_Frz *oldest)
+{
+ next->start_lsn = oldest->start_lsn;
+ next->start_time = oldest->start_time;
+
+ next->count = oldest->count + 1;
+
+ if (oldest->freezes > 0)
+ {
+ next->freezes += oldest->freezes;
+
+ /*
+ * We only track page age when freezing tuples on a page during
+ * vacuum, not when simply setting a page frozen in the VM.
+ */
+ next->sum_page_age_lsns += oldest->sum_page_age_lsns;
+ next->max_frz_page_age = Max(next->max_frz_page_age,
+ oldest->max_frz_page_age);
+
+ next->min_frz_page_age = Min(next->min_frz_page_age,
+ oldest->min_frz_page_age);
+ }
+
+ next->vm_page_freezes += oldest->vm_page_freezes;
+
+ if (oldest->unfreezes > 0)
+ {
+ next->unfreezes += oldest->unfreezes;
+ next->early_unfreezes += oldest->early_unfreezes;
+ next->total_frozen_duration_lsns += oldest->total_frozen_duration_lsns;
+ next->max_frozen_duration_lsns = Max(next->max_frozen_duration_lsns,
+ oldest->max_frozen_duration_lsns);
+
+ next->min_frozen_duration_lsns = Min(next->min_frozen_duration_lsns,
+ oldest->min_frozen_duration_lsns);
+ }
+
+ /*
+ * Though the total number of pages (and frozen pages) in the relation at
+ * the beginning and end of vacuum does not mean anything on its own when
+ * combined across entries, we use these numbers to calculate ratios, so
+ * we still must sum them.
+ */
+ next->frozen_pages_end += oldest->frozen_pages_end;
+ next->frozen_pages_start += oldest->frozen_pages_start;
+
+ next->relsize_end += oldest->relsize_end;
+ next->relsize_start += oldest->relsize_start;
+
+ next->scanned_pages += oldest->scanned_pages;
+
+ next->freeze_fpis += oldest->freeze_fpis;
+}
+
+/*
+ * At the beginning of a vacuum, set up a PgStat_Frz. If there are no free
+ * buckets in PgStat_StatTabEntry->frz_buckets, combine two PgStat_Frz entries
+ * into a single bucket -- being mindful not to combine PgStat_Frz ending
+ * before now - target_page_freeze_duration with those ending after.
+ */
+void
+pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStat_StatTabEntry *tabentry;
+ PgStat_Frz *current;
+ XLogRecPtr insert_lsn;
+ Oid dboid = (shared ? InvalidOid : MyDatabaseId);
+ TimestampTz ts = GetCurrentTimestamp();
+
+ if (!pgstat_track_counts)
+ return;
+
+ /* Use exact (not approximate) insert LSN at vacuum start/end */
+ insert_lsn = GetXLogInsertRecPtr();
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ dboid, tableoid, false);
+
+ tabentry = &((PgStatShared_Relation *) entry_ref->shared_stats)->stats;
+
+ /*
+ * While free buckets remain, simply use the next bucket for the next
+ * freeze period.
+ */
+ if (tabentry->frz_nbuckets_used < VAC_FRZ_STATS_MAX_NBUCKETS)
+ {
+ tabentry->frz_current = tabentry->frz_nbuckets_used;
+ tabentry->frz_nbuckets_used++;
+ }
+ else
+ {
+ PgStat_Frz *oldest;
+ PgStat_Frz *next;
+ int next_idx;
+ TimestampTz cutoff;
+
+ /*
+ * We want pages to stay frozen for at least
+ * target_page_freeze_duration. If they are unfrozen before that, it
+ * is an early unfreeze. We want all earlier unfreezes to be correctly
+ * attributed to a vacuum. So, don't combine vacuums which ended
+ * before the cutoff with those that ended after. cutoff is how long
+ * ago a pages would be allowed to have been unfrozen to not be
+ * considered an early unfreeze.
+ */
+ cutoff = ts - (target_page_freeze_duration * USECS_PER_SEC);
+
+ next_idx = (tabentry->frz_oldest + 1) % VAC_FRZ_STATS_MAX_NBUCKETS;
+
+ oldest = &tabentry->frz_buckets[tabentry->frz_oldest];
+ next = &tabentry->frz_buckets[next_idx];
+
+ /*
+ * If oldest is old enough but next is not old enough, we can't just
+ * combine them. instead combine next and next next, then copy oldest
+ * into next.
+ */
+ if (oldest->end_time < cutoff && next->end_time > cutoff)
+ {
+ int next_next_idx = (next_idx + 1) % VAC_FRZ_STATS_MAX_NBUCKETS;
+ PgStat_Frz *next_next = &tabentry->frz_buckets[next_next_idx];
+
+ pgstat_combine_vacuum_stats(next_next, next);
+
+ memcpy(next, oldest, sizeof(PgStat_Frz));
+ }
+ else
+ pgstat_combine_vacuum_stats(next, oldest);
+
+ tabentry->frz_current = tabentry->frz_oldest;
+ tabentry->frz_oldest = next_idx;
+ }
+
+ Assert(tabentry->frz_current < VAC_FRZ_STATS_MAX_NBUCKETS);
+ Assert(tabentry->frz_oldest < VAC_FRZ_STATS_MAX_NBUCKETS);
+
+ current = &tabentry->frz_buckets[tabentry->frz_current];
+ memset(current, 0, sizeof(PgStat_Frz));
+ current->start_lsn = insert_lsn;
+ current->count = 1;
+ current->start_time = ts;
+
+ pgstat_unlock_entry(entry_ref);
+
+ /*
+ * Flush IO stats at the beginning of the vacuum after setting the start
+ * time and start LSN for this vacuum. This ensures that pages that are
+ * unfrozen before the end of the vacuum are still attributed as an
+ * unfreeze to that vacuum.
+ */
+ pgstat_flush_io(false);
+}
+
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 5e84deec9a..00c67deda4 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -394,6 +394,131 @@ typedef struct PgStat_StatSubEntry
TimestampTz stat_reset_timestamp;
} PgStat_StatSubEntry;
+/*
+ * Each PgStat_Frz is a bucket in a ring buffer containing stats from one or
+ * more freeze periods, PgStat_StatTabEntry->frz_buckets. Each freeze period is
+ * a single vacuum of a single relation. Once VAC_FRZ_STATS_MAX_NBUCKETS # of
+ * freeze periods have been recorded, multiple older freeze periods are
+ * combined into single buckets (PgStat_Frz).
+ *
+ * Pages frozen by vacuum are tracked in the current PgStat_Frz. Once those
+ * pages are modified, those "unfreezes" are tracked in the PgStat_Frz bucket
+ * whose LSN span (start_lsn -> end_lsn) covers the page freeze LSN.
+ *
+ * Because each PgStat_Frz may contain stats from multiple freeze periods, many
+ * of the stats only make sense when used to calculate a ratio. For example,
+ * adding the relsize at the end of a vacuum across multiple vacuums is
+ * meaningless. However, we use frozen_pages_end and relsize_end to calculate
+ * the percentage of the relation that is frozen at the end of the vacuum. This
+ * is effectively an average when calculated across multiple vacuums in the
+ * same bucket.
+ */
+typedef struct PgStat_Frz
+{
+ /* insert LSN at the start of the oldest freeze period in this bucket */
+ XLogRecPtr start_lsn;
+ /* insert LSN at the end of the newest freeze period in this bucket */
+ XLogRecPtr end_lsn;
+ /* start time of the oldest freeze period in this bucket */
+ TimestampTz start_time;
+ /* end time of the newest freeze period in this bucket */
+ TimestampTz end_time;
+ /* number of freeze periods we have combined into this bucket */
+ int count;
+
+ /*
+ * number of pages with newly frozen tuples for all freeze periods in this
+ * bucket
+ */
+ int64 freezes;
+
+ /*
+ * number of pages newly marked frozen in the visibility map by vacuum
+ * during all freeze periods in this bucket
+ */
+ int64 vm_page_freezes;
+
+ /*
+ * number of pages whose all frozen bit was cleared in the visibility map
+ * which were frozen during all freeze periods in this bucket
+ */
+ int64 unfreezes;
+
+ /*
+ * a subset of unfreezes, this is the number of pages frozen during all
+ * freeze periods in this bucket which were unfrozen before
+ * target_page_freeze_duration seconds had elapsed
+ */
+ int64 early_unfreezes;
+
+ /*
+ * Number of pages of this relation marked all frozen in the visibility
+ * map at the end of all freeze periods in this bucket
+ */
+ int64 frozen_pages_end;
+
+ /*
+ * Number of pages of this relation marked all frozen in the visibility
+ * map at the start of this freeze period.
+ */
+ int64 frozen_pages_start;
+
+ /*
+ * number of pages in the relation at the beginning and end of all freeze
+ * periods in this bucket
+ */
+ int64 relsize_end;
+ int64 relsize_start;
+
+ /*
+ * number of pages actually scanned by vacuum (not skipped) during all
+ * freeze periods in this bucket.
+ */
+ int64 scanned_pages;
+
+ /*
+ * number of freeze records emitted by vacuum containing FPIs during all
+ * freeze periods in this bucket
+ */
+ int64 freeze_fpis;
+
+ /*
+ * When a page is frozen, its age in LSNs is the number of LSNs elapsed
+ * since it was last modified. Keeping track of a running sum of page ages
+ * at the time of freezing for freezes happening during all freeze periods
+ * in this bucket allows us to calculate an average page age, in LSNs, for
+ * pages we end up freezing.
+ */
+ double sum_page_age_lsns;
+
+ /*
+ * the oldest and youngest pages (in LSNs) that we froze during all freeze
+ * periods in this bucket
+ */
+ XLogRecPtr max_frz_page_age;
+ XLogRecPtr min_frz_page_age;
+
+ /*
+ * When a page is unfrozen, the number of LSNs for which it stayed frozen
+ * is added to this total. This allows us to calculate the average "time"
+ * in LSNs that a page stays frozen for.
+ */
+ double total_frozen_duration_lsns;
+
+ /*
+ * The page frozen during any of the freeze periods in this bucket which
+ * lasted the longest before being modified and unfrozen.
+ */
+ XLogRecPtr max_frozen_duration_lsns;
+
+ /*
+ * The page frozen during any of the freeze periods in this bucket which
+ * was modified the soonest after being frozen.
+ */
+ XLogRecPtr min_frozen_duration_lsns;
+} PgStat_Frz;
+
+#define VAC_FRZ_STATS_MAX_NBUCKETS 15
typedef struct PgStat_StatTabEntry
{
PgStat_Counter numscans;
@@ -424,6 +549,11 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter analyze_count;
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+
+ int frz_current;
+ int frz_oldest;
+ int frz_nbuckets_used;
+ PgStat_Frz frz_buckets[VAC_FRZ_STATS_MAX_NBUCKETS];
} PgStat_StatTabEntry;
typedef struct PgStat_WalStats
@@ -589,6 +719,9 @@ extern void pgstat_assoc_relation(Relation rel);
extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared, LVRelState *vacrel);
+
+extern void pgstat_setup_vacuum_frz_stats(Oid tableoid, bool shared);
+
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 87c1aee379..7cda07cdf8 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3594,6 +3594,7 @@ pgssStoreKind
pgssVersion
pgstat_entry_ref_hash_hash
pgstat_entry_ref_hash_iterator
+PgStat_Frz
pgstat_page
pgstat_snapshot_hash
pgstattuple_type
--
2.37.2
