public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Jim Nasby <[email protected]>
Cc: Bertrand Drouvot <[email protected]>
Cc: Ilia Evdokimov <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Andrei Zubkov <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: jian he <[email protected]>
Cc: [email protected]
Cc: Sami Imseih <[email protected]>
Cc: vignesh C <[email protected]>
Subject: Re: Vacuum statistics
Date: Mon, 2 Jun 2025 22:56:41 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdtQd29O15Cmp1qeqTCerQF0Y+BGh63qtX3RkA7k=0TZ1Q@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfduoJEuoixPTTg2tjhnXqrdobuMaQGxriqxJ9TjN1uxOuA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAMFBP2oXkhX_k9FTqtW-LdTBepVq0PDuBEGO8-LpNGbyHTBrNw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA4eK1JOUn+EqWSfRgKfgBZOXT7Q2dw2enmSZZgOhoMOFwopPA@mail.gmail.com>
<[email protected]>
<CAPpHfdtQd29O15Cmp1qeqTCerQF0Y+BGh63qtX3RkA7k=0TZ1Q@mail.gmail.com>
On 02.06.2025 19:25, Alexander Korotkov wrote:
> On Tue, May 13, 2025 at 12:49 PM Alena Rybakina
> <[email protected]> wrote:
>> On 12.05.2025 08:30, Amit Kapila wrote:
>>> On Fri, May 9, 2025 at 5:34 PM Alena Rybakina <[email protected]> wrote:
>>>> I did a rebase and finished the part with storing statistics separately from the relation statistics - now it is possible to disable the collection of statistics for relationsh using gucs and
>>>> this allows us to solve the problem with the memory consumed.
>>>>
>>> I think this patch is trying to collect data similar to what we do for
>>> pg_stat_statements for SQL statements. So, can't we follow a similar
>>> idea such that these additional statistics will be collected once some
>>> external module like pg_stat_statements is enabled? That module should
>>> be responsible for accumulating and resetting the data, so we won't
>>> have this memory consumption issue.
>> The idea is good, it will require one hook for the pgstat_report_vacuum
>> function, the extvac_stats_start and extvac_stats_end functions can be
>> run if the extension is loaded, so as not to add more hooks.
> +1
> Nice idea of a hook. Given the volume of the patch, it might be a
> good idea to keep this as an extension.
Today, I finalized the vacuum statistics separation approach and
refactored the vacuum statistics structures (patch 4).
I also reworked the table statistics to avoid mixing index statistics in
parallel vacuum mode (patch 2).
The new approach excludes buffer usage and WAL statistics for indexes
from the table’s statistics.
For timing, if vacuuming is sequential, the total time spent on all
indexes is subtracted from the table’s total vacuum time by adding up
the individual index vacuum times. If vacuuming is parallel, the total
index vacuum time is subtracted as a whole.
static void
accumulate_idxs_vacuum_statistics(LVRelState *vacrel, ExtVacReport
*extVacIdxStats)
{
if (!pgstat_track_vacuum_statistics)
return;
/* Fill heap-specific extended stats fields */
vacrel->extVacReportIdx.blk_read_time += extVacIdxStats->blk_read_time;
vacrel->extVacReportIdx.blk_write_time +=
extVacIdxStats->blk_write_time;
vacrel->extVacReportIdx.total_blks_dirtied +=
extVacIdxStats->total_blks_dirtied;
vacrel->extVacReportIdx.total_blks_hit +=
extVacIdxStats->total_blks_hit;
vacrel->extVacReportIdx.total_blks_read +=
extVacIdxStats->total_blks_read;
vacrel->extVacReportIdx.total_blks_written +=
extVacIdxStats->total_blks_written;
vacrel->extVacReportIdx.wal_bytes += extVacIdxStats->wal_bytes;
vacrel->extVacReportIdx.wal_fpi += extVacIdxStats->wal_fpi;
vacrel->extVacReportIdx.wal_records += extVacIdxStats->wal_records;
vacrel->extVacReportIdx.delay_time += extVacIdxStats->delay_time;
vacrel->extVacReportIdx.total_time += extVacIdxStats->total_time;
}
if (ParallelVacuumIsActive(vacrel))
{
LVExtStatCounters counters;
ExtVacReport extVacReport;
memset(&extVacReport, 0, sizeof(ExtVacReport));
extvac_stats_start(vacrel->rel, &counters);
/* Outsource everything to parallel variant */
parallel_vacuum_bulkdel_all_indexes(vacrel->pvs, old_live_tuples,
vacrel->num_index_scans);
extvac_stats_end(vacrel->rel, &counters, &extVacReport);
accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
}
Currently, database statistics work incorrectly — I'm investigating the
issue.
In parallel, I'm starting work on the extension.
--
Regards,
Alena Rybakina
Postgres Professional
Attachments:
[text/x-patch] 0001-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (71.3K, 2-0001-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From 6c52152b3d96d4b7dc2de2692b116af20be67dca Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Thu, 8 May 2025 21:14:23 +0300
Subject: [PATCH 1/5] Machinery for grabbing an extended vacuum statistics on
table relations.
Value of total_blks_hit, total_blks_read, total_blks_dirtied are number of
hitted, missed and dirtied pages in shared buffers during a vacuum operation
respectively.
total_blks_dirtied means 'dirtied only by this action'. So, if this page was
dirty before the vacuum operation, it doesn't count this page as 'dirtied'.
The tuples_deleted parameter is the number of tuples cleaned up by the vacuum
operation.
The delay_time value means total vacuum sleep time in vacuum delay point.
The pages_removed value is the number of pages by which the physical data
storage of the relation was reduced.
The value of pages_deleted parameter is the number of freed pages in the table
(file size may not have changed).
Tracking of IO during an (auto)vacuum operation.
Introduced variables blk_read_time and blk_write_time tracks only access to
buffer pages and flushing them to disk. Reading operation is trivial, but
writing measurement technique is not obvious.
So, during a vacuum writing time can be zero incremented because no any flushing
operations were performed.
System time and user time are parameters that describes how much time a vacuum
operation has spent in executing of code in user space and kernel space
accordingly. Also, accumulate total time of a vacuum that is a diff between
timestamps in start and finish points in the vacuum code.
Remember about idle time, when vacuum waited for IO and locks, so total time
isn't equal a sum of user and system time, but no less.
pages_frozen is a number of pages that are marked as frozen in vm during vacuum.
This parameter is incremented if page is marked as all-frozen.
pages_all_visible is a number of pages that are marked as all-visible in vm during
vacuum.
wraparound_failsafe_count is a number of times when the vacuum starts urgent cleanup
to prevent wraparound problem which is critical for the database.
Authors: Alena Rybakina <[email protected]>,
Andrei Lepikhov <[email protected]>,
Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
---
src/backend/access/heap/vacuumlazy.c | 150 +++++++++++-
src/backend/access/heap/visibilitymap.c | 10 +
src/backend/catalog/system_views.sql | 52 +++-
src/backend/commands/vacuum.c | 4 +
src/backend/commands/vacuumparallel.c | 1 +
src/backend/utils/activity/pgstat.c | 12 +-
src/backend/utils/activity/pgstat_relation.c | 46 +++-
src/backend/utils/adt/pgstatfuncs.c | 147 ++++++++++++
src/backend/utils/error/elog.c | 13 +
src/backend/utils/misc/guc_tables.c | 9 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/catalog/pg_proc.dat | 18 ++
src/include/commands/vacuum.h | 1 +
src/include/pgstat.h | 80 +++++-
src/include/utils/elog.h | 1 +
.../vacuum-extending-in-repetable-read.out | 53 ++++
src/test/isolation/isolation_schedule | 1 +
.../vacuum-extending-in-repetable-read.spec | 53 ++++
src/test/regress/expected/rules.out | 44 +++-
.../expected/vacuum_tables_statistics.out | 227 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +
.../regress/sql/vacuum_tables_statistics.sql | 183 ++++++++++++++
22 files changed, 1095 insertions(+), 16 deletions(-)
create mode 100644 src/test/isolation/expected/vacuum-extending-in-repetable-read.out
create mode 100644 src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
create mode 100644 src/test/regress/expected/vacuum_tables_statistics.out
create mode 100644 src/test/regress/sql/vacuum_tables_statistics.sql
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 708674d8fcf..ee27e70a798 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -290,6 +290,7 @@ typedef struct LVRelState
/* Error reporting state */
char *dbname;
char *relnamespace;
+ Oid reloid;
char *relname;
char *indname; /* Current index name */
BlockNumber blkno; /* used only for heap operations */
@@ -408,6 +409,8 @@ typedef struct LVRelState
* been permanently disabled.
*/
BlockNumber eager_scan_remaining_fails;
+
+ int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
} LVRelState;
@@ -419,6 +422,18 @@ typedef struct LVSavedErrInfo
VacErrPhase phase;
} LVSavedErrInfo;
+/*
+ * Counters and usage data for extended stats tracking.
+ */
+typedef struct LVExtStatCounters
+{
+ TimestampTz starttime;
+ WalUsage walusage;
+ BufferUsage bufusage;
+ double VacuumDelayTime;
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
+} LVExtStatCounters;
/* non-export function prototypes */
static void lazy_scan_heap(LVRelState *vacrel);
@@ -475,6 +490,106 @@ static void update_vacuum_error_info(LVRelState *vacrel,
static void restore_vacuum_error_info(LVRelState *vacrel,
const LVSavedErrInfo *saved_vacrel);
+/* ----------
+ * extvac_stats_start() -
+ *
+ * Save cut-off values of extended vacuum counters before start of a relation
+ * processing.
+ * ----------
+ */
+static void
+extvac_stats_start(Relation rel, LVExtStatCounters *counters)
+{
+ TimestampTz starttime;
+
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ memset(counters, 0, sizeof(LVExtStatCounters));
+
+ starttime = GetCurrentTimestamp();
+
+ counters->starttime = starttime;
+ counters->walusage = pgWalUsage;
+ counters->bufusage = pgBufferUsage;
+ counters->VacuumDelayTime = VacuumDelayTime;
+ counters->blocks_fetched = 0;
+ counters->blocks_hit = 0;
+
+ if (!rel->pgstat_info || !pgstat_track_counts)
+ /*
+ * if something goes wrong or user doesn't want to track a database
+ * activity - just suppress it.
+ */
+ return;
+
+ counters->blocks_fetched = rel->pgstat_info->counts.blocks_fetched;
+ counters->blocks_hit = rel->pgstat_info->counts.blocks_hit;
+}
+
+/* ----------
+ * extvac_stats_end() -
+ *
+ * Called to finish an extended vacuum statistic gathering and form a report.
+ * ----------
+ */
+static void
+extvac_stats_end(Relation rel, LVExtStatCounters *counters,
+ ExtVacReport *report)
+{
+ WalUsage walusage;
+ BufferUsage bufusage;
+ TimestampTz endtime;
+ long secs;
+ int usecs;
+
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ /* Calculate diffs of global stat parameters on WAL and buffer usage. */
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &counters->walusage);
+
+ memset(&bufusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufusage, &pgBufferUsage, &counters->bufusage);
+
+ endtime = GetCurrentTimestamp();
+ TimestampDifference(counters->starttime, endtime, &secs, &usecs);
+
+ memset(report, 0, sizeof(ExtVacReport));
+
+ /*
+ * Fill additional statistics on a vacuum processing operation.
+ */
+ report->total_blks_read = bufusage.local_blks_read + bufusage.shared_blks_read;
+ report->total_blks_hit = bufusage.local_blks_hit + bufusage.shared_blks_hit;
+ report->total_blks_dirtied = bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
+ report->total_blks_written = bufusage.shared_blks_written;
+
+ report->wal_records = walusage.wal_records;
+ report->wal_fpi = walusage.wal_fpi;
+ report->wal_bytes = walusage.wal_bytes;
+
+ report->blk_read_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
+ report->blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
+ report->blk_write_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
+ report->blk_write_time = INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
+ report->delay_time = VacuumDelayTime - counters->VacuumDelayTime;
+
+ report->total_time = secs * 1000. + usecs / 1000.;
+
+ if (!rel->pgstat_info || !pgstat_track_counts)
+ /*
+ * if something goes wrong or an user doesn't want to track a database
+ * activity - just suppress it.
+ */
+ return;
+
+ report->blks_fetched =
+ rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
+ report->blks_hit =
+ rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
+}
/*
@@ -632,7 +747,14 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
WalUsage startwalusage = pgWalUsage;
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
+ LVExtStatCounters extVacCounters;
+ ExtVacReport extVacReport;
char **indnames = NULL;
+ ExtVacReport allzero;
+
+ /* Initialize vacuum statistics */
+ memset(&allzero, 0, sizeof(ExtVacReport));
+ extVacReport = allzero;
verbose = (params->options & VACOPT_VERBOSE) != 0;
instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
@@ -652,7 +774,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
-
+ extvac_stats_start(rel, &extVacCounters);
/*
* Setup error traceback support for ereport() first. The idea is to set
* up an error context callback to display additional information on any
@@ -669,6 +791,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->dbname = get_database_name(MyDatabaseId);
vacrel->relnamespace = get_namespace_name(RelationGetNamespace(rel));
vacrel->relname = pstrdup(RelationGetRelationName(rel));
+ vacrel->reloid = RelationGetRelid(rel);
vacrel->indname = NULL;
vacrel->phase = VACUUM_ERRCB_PHASE_UNKNOWN;
vacrel->verbose = verbose;
@@ -758,6 +881,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->vm_new_visible_frozen_pages = 0;
vacrel->vm_new_frozen_pages = 0;
vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel);
+ vacrel->wraparound_failsafe_count = 0;
/*
* Get cutoffs that determine which deleted tuples are considered DEAD,
@@ -924,6 +1048,26 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->NewRelfrozenXid, vacrel->NewRelminMxid,
&frozenxid_updated, &minmulti_updated, false);
+ /* Make generic extended vacuum stats report */
+ extvac_stats_end(rel, &extVacCounters, &extVacReport);
+
+ if(pgstat_track_vacuum_statistics)
+ {
+ /* Fill heap-specific extended stats fields */
+ extVacReport.pages_scanned = vacrel->scanned_pages;
+ extVacReport.pages_removed = vacrel->removed_pages;
+ extVacReport.vm_new_frozen_pages = vacrel->vm_new_frozen_pages;
+ extVacReport.vm_new_visible_pages = vacrel->vm_new_visible_pages;
+ extVacReport.vm_new_visible_frozen_pages = vacrel->vm_new_visible_frozen_pages;
+ extVacReport.tuples_deleted = vacrel->tuples_deleted;
+ extVacReport.tuples_frozen = vacrel->tuples_frozen;
+ extVacReport.recently_dead_tuples = vacrel->recently_dead_tuples;
+ extVacReport.missed_dead_tuples = vacrel->missed_dead_tuples;
+ extVacReport.missed_dead_pages = vacrel->missed_dead_pages;
+ extVacReport.index_vacuum_count = vacrel->num_index_scans;
+ extVacReport.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
+ }
+
/*
* Report results to the cumulative stats system, too.
*
@@ -939,7 +1083,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
vacrel->missed_dead_tuples,
- starttime);
+ starttime,
+ &extVacReport);
pgstat_progress_end_command();
if (instrument)
@@ -2977,6 +3122,7 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
int64 progress_val[2] = {0, 0};
VacuumFailsafeActive = true;
+ vacrel->wraparound_failsafe_count ++;
/*
* Abandon use of a buffer access strategy to allow use of all of
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 745a04ef26e..07623a045fa 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -91,6 +91,7 @@
#include "access/xloginsert.h"
#include "access/xlogutils.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "port/pg_bitutils.h"
#include "storage/bufmgr.h"
#include "storage/smgr.h"
@@ -160,6 +161,15 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
if (map[mapByte] & mask)
{
+ /*
+ * As part of vacuum stats, track how often all-visible or all-frozen
+ * bits are cleared.
+ */
+ if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_VISIBLE)
+ pgstat_count_vm_rev_all_visible(rel);
+ if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_FROZEN)
+ pgstat_count_vm_rev_all_frozen(rel);
+
map[mapByte] &= ~mask;
MarkBufferDirty(vmbuf);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 08f780a2e63..47d27314b55 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -708,7 +708,9 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
- pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time
+ pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time,
+ pg_stat_get_rev_all_frozen_pages(C.oid) AS rev_all_frozen_pages,
+ pg_stat_get_rev_all_visible_pages(C.oid) AS rev_all_visible_pages
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
@@ -1407,3 +1409,51 @@ REVOKE ALL ON pg_aios FROM PUBLIC;
GRANT SELECT ON pg_aios TO pg_read_all_stats;
REVOKE EXECUTE ON FUNCTION pg_get_aios() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_get_aios() TO pg_read_all_stats;
+--
+-- Show extended cumulative statistics on a vacuum operation over all tables and
+-- databases of the instance.
+-- Use Invalid Oid "0" as an input relation id to get stat on each table in a
+-- database.
+--
+
+CREATE VIEW pg_stat_vacuum_tables AS
+SELECT
+ ns.nspname AS schemaname,
+ rel.relname AS relname,
+ stats.relid as relid,
+
+ stats.total_blks_read AS total_blks_read,
+ stats.total_blks_hit AS total_blks_hit,
+ stats.total_blks_dirtied AS total_blks_dirtied,
+ stats.total_blks_written AS total_blks_written,
+
+ stats.rel_blks_read AS rel_blks_read,
+ stats.rel_blks_hit AS rel_blks_hit,
+
+ stats.pages_scanned AS pages_scanned,
+ stats.pages_removed AS pages_removed,
+ stats.vm_new_frozen_pages AS vm_new_frozen_pages,
+ stats.vm_new_visible_pages AS vm_new_visible_pages,
+ stats.vm_new_visible_frozen_pages AS vm_new_visible_frozen_pages,
+ stats.missed_dead_pages AS missed_dead_pages,
+ stats.tuples_deleted AS tuples_deleted,
+ stats.tuples_frozen AS tuples_frozen,
+ stats.recently_dead_tuples AS recently_dead_tuples,
+ stats.missed_dead_tuples AS missed_dead_tuples,
+
+ stats.wraparound_failsafe AS wraparound_failsafe,
+ stats.index_vacuum_count AS index_vacuum_count,
+ stats.wal_records AS wal_records,
+ stats.wal_fpi AS wal_fpi,
+ stats.wal_bytes AS wal_bytes,
+
+ stats.blk_read_time AS blk_read_time,
+ stats.blk_write_time AS blk_write_time,
+
+ stats.delay_time AS delay_time,
+ stats.total_time AS total_time
+
+FROM pg_class rel
+ JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
+ LATERAL pg_stat_get_vacuum_tables(rel.oid) stats
+WHERE rel.relkind = 'r';
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 33a33bf6b1c..ffb7e1eef4c 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -115,6 +115,9 @@ pg_atomic_uint32 *VacuumSharedCostBalance = NULL;
pg_atomic_uint32 *VacuumActiveNWorkers = NULL;
int VacuumCostBalanceLocal = 0;
+/* Cumulative storage to report total vacuum delay time. */
+double VacuumDelayTime = 0; /* msec. */
+
/* non-export function prototypes */
static List *expand_vacuum_rel(VacuumRelation *vrel,
MemoryContext vac_context, int options);
@@ -2514,6 +2517,7 @@ vacuum_delay_point(bool is_analyze)
exit(1);
VacuumCostBalance = 0;
+ VacuumDelayTime += msec;
/*
* Balance and update limit values for autovacuum workers. We must do
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 0feea1d30ec..2b55d9b7c0e 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -1054,6 +1054,7 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Set cost-based vacuum delay */
VacuumUpdateCosts();
VacuumCostBalance = 0;
+ VacuumDelayTime = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = &(shared->cost_balance);
VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 8b57845e870..23cb62e36a7 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -190,7 +190,7 @@ static void pgstat_reset_after_failure(void);
static bool pgstat_flush_pending_entries(bool nowait);
static void pgstat_prep_snapshot(void);
-static void pgstat_build_snapshot(void);
+static void pgstat_build_snapshot(PgStat_Kind statKind);
static void pgstat_build_snapshot_fixed(PgStat_Kind kind);
static inline bool pgstat_is_kind_valid(PgStat_Kind kind);
@@ -203,7 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind);
bool pgstat_track_counts = false;
int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE;
-
+bool pgstat_track_vacuum_statistics = true;
/* ----------
* state shared with pgstat_*.c
@@ -260,7 +260,6 @@ static bool pgstat_is_initialized = false;
static bool pgstat_is_shutdown = false;
#endif
-
/*
* The different kinds of built-in statistics.
*
@@ -897,7 +896,6 @@ pgstat_reset_of_kind(PgStat_Kind kind)
pgstat_reset_entries_of_kind(kind, ts);
}
-
/* ------------------------------------------------------------
* Fetching of stats
* ------------------------------------------------------------
@@ -966,7 +964,7 @@ pgstat_fetch_entry(PgStat_Kind kind, Oid dboid, uint64 objid)
/* if we need to build a full snapshot, do so */
if (pgstat_fetch_consistency == PGSTAT_FETCH_CONSISTENCY_SNAPSHOT)
- pgstat_build_snapshot();
+ pgstat_build_snapshot(PGSTAT_KIND_INVALID);
/* if caching is desired, look up in cache */
if (pgstat_fetch_consistency > PGSTAT_FETCH_CONSISTENCY_NONE)
@@ -1082,7 +1080,7 @@ pgstat_snapshot_fixed(PgStat_Kind kind)
pgstat_clear_snapshot();
if (pgstat_fetch_consistency == PGSTAT_FETCH_CONSISTENCY_SNAPSHOT)
- pgstat_build_snapshot();
+ pgstat_build_snapshot(PGSTAT_KIND_INVALID);
else
pgstat_build_snapshot_fixed(kind);
@@ -1133,7 +1131,7 @@ pgstat_prep_snapshot(void)
}
static void
-pgstat_build_snapshot(void)
+pgstat_build_snapshot(PgStat_Kind statKind)
{
dshash_seq_status hstat;
PgStatShared_HashEntry *p;
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 28587e2916b..ee0385cd809 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -47,6 +47,8 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev
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_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
+ bool accumulate_reltype_specific_info);
/*
@@ -209,7 +211,7 @@ pgstat_drop_relation(Relation rel)
void
pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime)
+ TimestampTz starttime, ExtVacReport *params)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -235,6 +237,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
+ pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params, true);
+
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
* various pages, however, we'll zero the insert counter here regardless.
@@ -881,6 +885,9 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
tabentry->blocks_fetched += lstats->counts.blocks_fetched;
tabentry->blocks_hit += lstats->counts.blocks_hit;
+ tabentry->rev_all_frozen_pages += lstats->counts.rev_all_frozen_pages;
+ tabentry->rev_all_visible_pages += lstats->counts.rev_all_visible_pages;
+
/* Clamp live_tuples in case of negative delta_live_tuples */
tabentry->live_tuples = Max(tabentry->live_tuples, 0);
/* Likewise for dead_tuples */
@@ -1004,3 +1011,40 @@ restore_truncdrop_counters(PgStat_TableXactStatus *trans)
trans->tuples_deleted = trans->deleted_pre_truncdrop;
}
}
+
+static void
+pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
+ bool accumulate_reltype_specific_info)
+{
+ dst->total_blks_read += src->total_blks_read;
+ dst->total_blks_hit += src->total_blks_hit;
+ dst->total_blks_dirtied += src->total_blks_dirtied;
+ dst->total_blks_written += src->total_blks_written;
+ dst->wal_bytes += src->wal_bytes;
+ dst->wal_fpi += src->wal_fpi;
+ dst->wal_records += src->wal_records;
+ dst->blk_read_time += src->blk_read_time;
+ dst->blk_write_time += src->blk_write_time;
+ dst->delay_time += src->delay_time;
+ dst->total_time += src->total_time;
+
+ if (!accumulate_reltype_specific_info)
+ return;
+
+ dst->blks_fetched += src->blks_fetched;
+ dst->blks_hit += src->blks_hit;
+
+ dst->pages_scanned += src->pages_scanned;
+ dst->pages_removed += src->pages_removed;
+ dst->vm_new_frozen_pages += src->vm_new_frozen_pages;
+ dst->vm_new_visible_pages += src->vm_new_visible_pages;
+ dst->vm_new_visible_frozen_pages += src->vm_new_visible_frozen_pages;
+ dst->tuples_deleted += src->tuples_deleted;
+ dst->tuples_frozen += src->tuples_frozen;
+ dst->recently_dead_tuples += src->recently_dead_tuples;
+ dst->index_vacuum_count += src->index_vacuum_count;
+ dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
+ dst->missed_dead_pages += src->missed_dead_pages;
+ dst->missed_dead_tuples += src->missed_dead_tuples;
+
+}
\ No newline at end of file
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index e980109f245..a5610199893 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -106,6 +106,12 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
/* pg_stat_get_vacuum_count */
PG_STAT_GET_RELENTRY_INT64(vacuum_count)
+/* pg_stat_get_rev_frozen_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_frozen_pages)
+
+/* pg_stat_get_rev_all_visible_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_visible_pages)
+
#define PG_STAT_GET_RELENTRY_FLOAT8(stat) \
Datum \
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS) \
@@ -2258,3 +2264,144 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(pgstat_have_entry(kind, dboid, objid));
}
+
+
+/*
+ * Get the vacuum statistics for the heap tables.
+ */
+Datum
+pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
+{
+ #define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 26
+
+ Oid relid = PG_GETARG_OID(0);
+ PgStat_StatTabEntry *tabentry;
+ ExtVacReport *extvacuum;
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_VACUUM_TABLES_STATS_COLS] = {0};
+ bool nulls[PG_STAT_GET_VACUUM_TABLES_STATS_COLS] = {0};
+ char buf[256];
+ int i = 0;
+ ExtVacReport allzero;
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "relid",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_read",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_read",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_scanned",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_removed",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_frozen_pages",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_visible_pages",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_visible_frozen_pages",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "missed_dead_pages",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_deleted",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_frozen",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "recently_dead_tuples",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "missed_dead_tuples",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wraparound_failsafe_count",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "index_vacuum_count",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
+ NUMERICOID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
+ FLOAT8OID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
+ FLOAT8OID, -1, 0);
+
+ Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
+
+ BlessTupleDesc(tupdesc);
+
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+
+ if (tabentry == NULL)
+ {
+ /* If the subscription is not found, initialise its stats */
+ memset(&allzero, 0, sizeof(ExtVacReport));
+ extvacuum = &allzero;
+ }
+ else
+ {
+ extvacuum = &(tabentry->vacuum_ext);
+ }
+
+ i = 0;
+
+ values[i++] = ObjectIdGetDatum(relid);
+
+ values[i++] = Int64GetDatum(extvacuum->total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+
+ values[i++] = Int64GetDatum(extvacuum->blks_fetched -
+ extvacuum->blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->blks_hit);
+
+ values[i++] = Int64GetDatum(extvacuum->pages_scanned);
+ values[i++] = Int64GetDatum(extvacuum->pages_removed);
+ values[i++] = Int64GetDatum(extvacuum->vm_new_frozen_pages);
+ values[i++] = Int64GetDatum(extvacuum->vm_new_visible_pages);
+ values[i++] = Int64GetDatum(extvacuum->vm_new_visible_frozen_pages);
+ values[i++] = Int64GetDatum(extvacuum->missed_dead_pages);
+ values[i++] = Int64GetDatum(extvacuum->tuples_deleted);
+ values[i++] = Int64GetDatum(extvacuum->tuples_frozen);
+ values[i++] = Int64GetDatum(extvacuum->recently_dead_tuples);
+ values[i++] = Int64GetDatum(extvacuum->missed_dead_tuples);
+ values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
+ values[i++] = Int64GetDatum(extvacuum->index_vacuum_count);
+
+ values[i++] = Int64GetDatum(extvacuum->wal_records);
+ values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ values[i++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[i++] = Float8GetDatum(extvacuum->blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->delay_time);
+ values[i++] = Float8GetDatum(extvacuum->total_time);
+
+ Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
+
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
\ No newline at end of file
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index 47af743990f..8c9e8fb18e1 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -1624,6 +1624,19 @@ getinternalerrposition(void)
return edata->internalpos;
}
+/*
+ * Return elevel of errors
+ */
+int
+geterrelevel(void)
+{
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ return edata->elevel;
+}
/*
* Functions to allow construction of error message strings separately from
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 2f8cbd86759..115f0c51cc2 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1508,6 +1508,15 @@ struct config_bool ConfigureNamesBool[] =
false,
NULL, NULL, NULL
},
+ {
+ {"track_vacuum_statistics", PGC_SUSET, STATS_CUMULATIVE,
+ gettext_noop("Collects vacuum statistics for table relations."),
+ NULL
+ },
+ &pgstat_track_vacuum_statistics,
+ true,
+ NULL, NULL, NULL
+ },
{
{"track_wal_io_timing", PGC_SUSET, STATS_CUMULATIVE,
gettext_noop("Collects timing statistics for WAL I/O activity."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 87ce76b18f4..e971b390281 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,6 +661,7 @@
#track_wal_io_timing = off
#track_functions = none # none, pl, all
#stats_fetch_consistency = cache # cache, none, snapshot
+#track_vacuum_statistics = off
# - Monitoring -
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 37a484147a8..c04d3880241 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12556,4 +12556,22 @@
proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
prosrc => 'pg_get_aios' },
+{ oid => '8001',
+ descr => 'pg_stat_get_vacuum_tables returns vacuum stats values for table',
+ proname => 'pg_stat_get_vacuum_tables', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
+ proretset => 't',
+ proargtypes => 'oid',
+ proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int4,int8,int8,int8,numeric,float8,float8,float8,float8}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{reloid,relid,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,pages_scanned,pages_removed,vm_new_frozen_pages,vm_new_visible_pages,vm_new_visible_frozen_pages,missed_dead_pages,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_tuples,wraparound_failsafe,index_vacuum_count,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time}',
+ prosrc => 'pg_stat_get_vacuum_tables' },
+
+ { oid => '8002', descr => 'statistics: number of times the all-visible pages in the visibility map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_visible_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_visible_pages' },
+ { oid => '8003', descr => 'statistics: number of times the all-frozen pages in the visibility map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_frozen_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_frozen_pages' },
]
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index bc37a80dc74..6d1b2991ce5 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -327,6 +327,7 @@ extern PGDLLIMPORT double vacuum_max_eager_freeze_failure_rate;
extern PGDLLIMPORT pg_atomic_uint32 *VacuumSharedCostBalance;
extern PGDLLIMPORT pg_atomic_uint32 *VacuumActiveNWorkers;
extern PGDLLIMPORT int VacuumCostBalanceLocal;
+extern PGDLLIMPORT double VacuumDelayTime;
extern PGDLLIMPORT bool VacuumFailsafeActive;
extern PGDLLIMPORT double vacuum_cost_delay;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 378f2f2c2ba..6c88d57aef7 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -111,6 +111,53 @@ typedef struct PgStat_BackendSubEntry
PgStat_Counter conflict_count[CONFLICT_NUM_TYPES];
} PgStat_BackendSubEntry;
+/* ----------
+ *
+ * ExtVacReport
+ *
+ * Additional statistics of vacuum processing over a heap relation.
+ * pages_removed is the amount by which the physically shrank,
+ * if any (ie the change in its total size on disk)
+ * pages_deleted refer to free space within the index file
+ * ----------
+ */
+typedef struct ExtVacReport
+{
+ /* number of blocks missed, hit, dirtied and written during a vacuum of specific relation */
+ int64 total_blks_read;
+ int64 total_blks_hit;
+ int64 total_blks_dirtied;
+ int64 total_blks_written;
+
+ /* blocks missed and hit for just the heap during a vacuum of specific relation */
+ int64 blks_fetched;
+ int64 blks_hit;
+
+ /* Vacuum WAL usage stats */
+ int64 wal_records; /* wal usage: number of WAL records */
+ int64 wal_fpi; /* wal usage: number of WAL full page images produced */
+ uint64 wal_bytes; /* wal usage: size of WAL records produced */
+
+ /* Time stats. */
+ double blk_read_time; /* time spent reading pages, in msec */
+ double blk_write_time; /* time spent writing pages, in msec */
+ double delay_time; /* how long vacuum slept in vacuum delay point, in msec */
+ double total_time; /* total time of a vacuum operation, in msec */
+
+ int64 pages_scanned; /* heap pages examined (not skipped by VM) */
+ int64 pages_removed; /* heap pages removed by vacuum "truncation" */
+ int64 vm_new_frozen_pages; /* pages marked in VM as frozen */
+ int64 vm_new_visible_pages; /* pages marked in VM as all-visible */
+ int64 vm_new_visible_frozen_pages; /* pages marked in VM as all-visible and frozen */
+ int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
+ int64 missed_dead_pages; /* pages with missed dead tuples */
+ int64 tuples_deleted; /* tuples deleted by vacuum */
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
+ int64 index_vacuum_count; /* the number of index vacuumings */
+ int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
+} ExtVacReport;
+
/* ----------
* PgStat_TableCounts The actual per-table counts kept by a backend
*
@@ -153,6 +200,16 @@ typedef struct PgStat_TableCounts
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
+
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
+
+ /*
+ * Additional cumulative stat on vacuum operations.
+ * Use an expensive structure as an abstraction for different types of
+ * relations.
+ */
+ ExtVacReport vacuum_ext;
} PgStat_TableCounts;
/* ----------
@@ -211,7 +268,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB7
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB8
typedef struct PgStat_ArchiverStats
{
@@ -375,6 +432,8 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter parallel_workers_launched;
TimestampTz stat_reset_timestamp;
+
+ ExtVacReport vacuum_ext; /* extended vacuum statistics */
} PgStat_StatDBEntry;
typedef struct PgStat_StatFuncEntry
@@ -453,6 +512,11 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
PgStat_Counter total_autoanalyze_time;
+
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
+
+ ExtVacReport vacuum_ext;
} PgStat_StatTabEntry;
/* ------
@@ -660,7 +724,7 @@ extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime);
+ TimestampTz starttime, ExtVacReport *params);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
@@ -711,6 +775,17 @@ extern void pgstat_report_analyze(Relation rel,
if (pgstat_should_count_relation(rel)) \
(rel)->pgstat_info->counts.blocks_hit++; \
} while (0)
+/* accumulate unfrozen all-visible and all-frozen pages */
+#define pgstat_count_vm_rev_all_visible(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.rev_all_visible_pages++; \
+ } while (0)
+#define pgstat_count_vm_rev_all_frozen(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.rev_all_frozen_pages++; \
+ } while (0)
extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
@@ -799,6 +874,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void);
extern PGDLLIMPORT bool pgstat_track_counts;
extern PGDLLIMPORT int pgstat_track_functions;
extern PGDLLIMPORT int pgstat_fetch_consistency;
+extern PGDLLIMPORT bool pgstat_track_vacuum_statistics;
/*
diff --git a/src/include/utils/elog.h b/src/include/utils/elog.h
index 5eac0e16970..6a30a4db47d 100644
--- a/src/include/utils/elog.h
+++ b/src/include/utils/elog.h
@@ -230,6 +230,7 @@ extern int geterrcode(void);
extern int geterrposition(void);
extern int getinternalerrposition(void);
+extern int geterrelevel(void);
/*----------
* Old-style error reporting API: to be used in this way:
diff --git a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
new file mode 100644
index 00000000000..87f7e40b4a6
--- /dev/null
+++ b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
@@ -0,0 +1,53 @@
+unused step name: s2_delete
+Parsed test spec with 2 sessions
+
+starting permutation: s2_insert s2_print_vacuum_stats_table s1_begin_repeatable_read s2_update s2_insert_interrupt s2_vacuum s2_print_vacuum_stats_table s1_commit s2_checkpoint s2_vacuum s2_print_vacuum_stats_table
+step s2_insert: INSERT INTO test_vacuum_stat_isolation(id, ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 0| 0| 0| 0| 0
+(1 row)
+
+step s1_begin_repeatable_read:
+ BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
+ select count(ival) from test_vacuum_stat_isolation where id>900;
+
+count
+-----
+ 100
+(1 row)
+
+step s2_update: UPDATE test_vacuum_stat_isolation SET ival = ival + 2 where id > 900;
+step s2_insert_interrupt: INSERT INTO test_vacuum_stat_isolation values (1,1);
+step s2_vacuum: VACUUM test_vacuum_stat_isolation;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 0| 100| 0| 0| 0
+(1 row)
+
+step s1_commit: COMMIT;
+step s2_checkpoint: CHECKPOINT;
+step s2_vacuum: VACUUM test_vacuum_stat_isolation;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 100| 100| 0| 0| 101
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index e3c669a29c7..6faee3ad2c3 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -96,6 +96,7 @@ test: timeouts
test: vacuum-concurrent-drop
test: vacuum-conflict
test: vacuum-skip-locked
+test: vacuum-extending-in-repetable-read
test: stats
test: horizons
test: predicate-hash
diff --git a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
new file mode 100644
index 00000000000..5893d89573d
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -0,0 +1,53 @@
+# Test for checking recently_dead_tuples, tuples_deleted and frozen tuples in pg_stat_vacuum_tables.
+# recently_dead_tuples values are counted when vacuum hasn't cleared tuples because they were deleted recently.
+# recently_dead_tuples aren't increased after releasing lock compared with tuples_deleted, which increased
+# by the value of the cleared tuples that the vacuum managed to clear.
+
+setup
+{
+ CREATE TABLE test_vacuum_stat_isolation(id int, ival int) WITH (autovacuum_enabled = off);
+ SET track_io_timing = on;
+ SET track_vacuum_statistics TO 'on';
+}
+
+teardown
+{
+ DROP TABLE test_vacuum_stat_isolation CASCADE;
+ RESET track_io_timing;
+ RESET track_vacuum_statistics;
+}
+
+session s1
+step s1_begin_repeatable_read {
+ BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
+ select count(ival) from test_vacuum_stat_isolation where id>900;
+ }
+step s1_commit { COMMIT; }
+
+session s2
+step s2_insert { INSERT INTO test_vacuum_stat_isolation(id, ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival; }
+step s2_update { UPDATE test_vacuum_stat_isolation SET ival = ival + 2 where id > 900; }
+step s2_delete { DELETE FROM test_vacuum_stat_isolation where id > 900; }
+step s2_insert_interrupt { INSERT INTO test_vacuum_stat_isolation values (1,1); }
+step s2_vacuum { VACUUM test_vacuum_stat_isolation; }
+step s2_checkpoint { CHECKPOINT; }
+step s2_print_vacuum_stats_table
+{
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+}
+
+permutation
+ s2_insert
+ s2_print_vacuum_stats_table
+ s1_begin_repeatable_read
+ s2_update
+ s2_insert_interrupt
+ s2_vacuum
+ s2_print_vacuum_stats_table
+ s1_commit
+ s2_checkpoint
+ s2_vacuum
+ s2_print_vacuum_stats_table
\ No newline at end of file
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6cf828ca8d0..10a482e2db4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1829,7 +1829,9 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
- pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time
+ pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time,
+ pg_stat_get_rev_all_frozen_pages(c.oid) AS rev_all_frozen_pages,
+ pg_stat_get_rev_all_visible_pages(c.oid) AS rev_all_visible_pages
FROM ((pg_class c
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2222,7 +2224,9 @@ pg_stat_sys_tables| SELECT relid,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
- total_autoanalyze_time
+ total_autoanalyze_time,
+ rev_all_frozen_pages,
+ rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2274,9 +2278,43 @@ pg_stat_user_tables| SELECT relid,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
- total_autoanalyze_time
+ total_autoanalyze_time,
+ rev_all_frozen_pages,
+ rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
+pg_stat_vacuum_tables| SELECT ns.nspname AS schemaname,
+ rel.relname,
+ stats.relid,
+ stats.total_blks_read,
+ stats.total_blks_hit,
+ stats.total_blks_dirtied,
+ stats.total_blks_written,
+ stats.rel_blks_read,
+ stats.rel_blks_hit,
+ stats.pages_scanned,
+ stats.pages_removed,
+ stats.vm_new_frozen_pages,
+ stats.vm_new_visible_pages,
+ stats.vm_new_visible_frozen_pages,
+ stats.missed_dead_pages,
+ stats.tuples_deleted,
+ stats.tuples_frozen,
+ stats.recently_dead_tuples,
+ stats.missed_dead_tuples,
+ stats.wraparound_failsafe,
+ stats.index_vacuum_count,
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.total_time
+ FROM (pg_class rel
+ JOIN pg_namespace ns ON ((ns.oid = rel.relnamespace))),
+ LATERAL pg_stat_get_vacuum_tables(rel.oid) stats(relid, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit, pages_scanned, pages_removed, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, missed_dead_pages, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_tuples, wraparound_failsafe, index_vacuum_count, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, total_time)
+ WHERE (rel.relkind = 'r'::"char");
pg_stat_wal| SELECT wal_records,
wal_fpi,
wal_bytes,
diff --git a/src/test/regress/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_statistics.out
new file mode 100644
index 00000000000..b5ea9c9ab1e
--- /dev/null
+++ b/src/test/regress/expected/vacuum_tables_statistics.out
@@ -0,0 +1,227 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+ track_counts
+--------------
+ on
+(1 row)
+
+\set sample_size 10000
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+-- Test that vacuum statistics will be empty when parameter is off.
+SET track_vacuum_statistics TO 'off';
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+DELETE FROM vestat WHERE x % 2 = 0;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- Must be empty.
+SELECT relname,total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written,rel_blks_read, rel_blks_hit,
+pages_scanned, pages_removed, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, missed_dead_pages,
+tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_tuples, index_vacuum_count,
+wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time,delay_time, total_time
+FROM pg_stat_vacuum_tables vt
+WHERE vt.relname = 'vestat';
+ relname | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit | pages_scanned | pages_removed | vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | missed_dead_pages | tuples_deleted | tuples_frozen | recently_dead_tuples | missed_dead_tuples | index_vacuum_count | wal_records | wal_fpi | wal_bytes | blk_read_time | blk_write_time | delay_time | total_time
+---------+-----------------+----------------+--------------------+--------------------+---------------+--------------+---------------+---------------+---------------------+----------------------+-----------------------------+-------------------+----------------+---------------+----------------------+--------------------+--------------------+-------------+---------+-----------+---------------+----------------+------------+------------
+ vestat | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
+(1 row)
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+SHOW track_vacuum_statistics; -- must be on
+ track_vacuum_statistics
+-------------------------
+ on
+(1 row)
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+SELECT oid AS roid from pg_class where relname = 'vestat' \gset
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,vm_new_frozen_pages,tuples_deleted,relpages,pages_scanned,pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | 0 | 0 | 455 | 0 | 0
+(1 row)
+
+SELECT relpages AS rp
+FROM pg_class c
+WHERE relname = 'vestat' \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,vm_new_frozen_pages > 0 AS vm_new_frozen_pages,tuples_deleted > 0 AS tuples_deleted,relpages-:rp = 0 AS relpages,pages_scanned > 0 AS pages_scanned,pages_removed = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | f | t | t | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS dWR, wal_bytes > 0 AS dWB
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
+ dwr | dwb
+-----+-----
+ t | t
+(1 row)
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- pages_removed must be increased
+SELECT vt.relname,vm_new_frozen_pages-:fp > 0 AS vm_new_frozen_pages,tuples_deleted-:td > 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps > 0 AS pages_scanned,pages_removed-:pr > 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | f | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr, wal_bytes-:hwb AS dwb, wal_fpi-:hfpi AS dfpi
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- WAL advance should be detected.
+SELECT :dwr > 0 AS dWR, :dwb > 0 AS dWB;
+ dwr | dwb
+-----+-----
+ t | t
+(1 row)
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr2, wal_bytes-:hwb AS dwb2, wal_fpi-:hfpi AS dfpi2
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- WAL and other statistics advance should not be detected.
+SELECT :dwr2=0 AS dWR, :dfpi2=0 AS dFPI, :dwb2=0 AS dWB;
+ dwr | dfpi | dwb
+-----+------+-----
+ t | t | t
+(1 row)
+
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp < 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps,pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:hwr AS dwr3, wal_bytes-:hwb AS dwb3, wal_fpi-:hfpi AS dfpi3
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+--There are nothing changed
+SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
+ dwr | dfpi | dwb
+-----+------+-----
+ t | t | t
+(1 row)
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The vm_new_frozen_pages, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+-- must be empty
+SELECT vm_new_frozen_pages, vm_new_visible_pages, rev_all_frozen_pages,rev_all_visible_pages,vm_new_visible_frozen_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | rev_all_frozen_pages | rev_all_visible_pages | vm_new_visible_frozen_pages
+---------------------+----------------------+----------------------+-----------------------+-----------------------------
+ 0 | 0 | 0 | 0 | 0
+(1 row)
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- backend defreezed pages
+SELECT vm_new_frozen_pages > 0 AS vm_new_frozen_pages,vm_new_visible_pages > 0 AS vm_new_visible_pages,vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages,rev_all_frozen_pages = 0 AS rev_all_frozen_pages,rev_all_visible_pages = 0 AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ f | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv,vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+UPDATE vestat SET x = x + 1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+SELECT vm_new_frozen_pages > :pf AS vm_new_frozen_pages,vm_new_visible_pages > :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages > :pvf AS vm_new_visible_frozen_pages,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ f | t | f | f | f
+(1 row)
+
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv, vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- vacuum freezed pages
+SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ t | t | t | t | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index a424be2a6bf..ee0343c2729 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -140,3 +140,8 @@ test: fast_default
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
+
+# ----------
+# Check vacuum statistics
+# ----------
+test: vacuum_tables_statistics
\ No newline at end of file
diff --git a/src/test/regress/sql/vacuum_tables_statistics.sql b/src/test/regress/sql/vacuum_tables_statistics.sql
new file mode 100644
index 00000000000..5bc34bec64b
--- /dev/null
+++ b/src/test/regress/sql/vacuum_tables_statistics.sql
@@ -0,0 +1,183 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+\set sample_size 10000
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+
+-- Test that vacuum statistics will be empty when parameter is off.
+SET track_vacuum_statistics TO 'off';
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+DELETE FROM vestat WHERE x % 2 = 0;
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- Must be empty.
+SELECT relname,total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written,rel_blks_read, rel_blks_hit,
+pages_scanned, pages_removed, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, missed_dead_pages,
+tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_tuples, index_vacuum_count,
+wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time,delay_time, total_time
+FROM pg_stat_vacuum_tables vt
+WHERE vt.relname = 'vestat';
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+
+SHOW track_vacuum_statistics; -- must be on
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+SELECT oid AS roid from pg_class where relname = 'vestat' \gset
+
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,vm_new_frozen_pages,tuples_deleted,relpages,pages_scanned,pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT relpages AS rp
+FROM pg_class c
+WHERE relname = 'vestat' \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,vm_new_frozen_pages > 0 AS vm_new_frozen_pages,tuples_deleted > 0 AS tuples_deleted,relpages-:rp = 0 AS relpages,pages_scanned > 0 AS pages_scanned,pages_removed = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS dWR, wal_bytes > 0 AS dWB
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- pages_removed must be increased
+SELECT vt.relname,vm_new_frozen_pages-:fp > 0 AS vm_new_frozen_pages,tuples_deleted-:td > 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps > 0 AS pages_scanned,pages_removed-:pr > 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr, wal_bytes-:hwb AS dwb, wal_fpi-:hfpi AS dfpi
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- WAL advance should be detected.
+SELECT :dwr > 0 AS dWR, :dwb > 0 AS dWB;
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr2, wal_bytes-:hwb AS dwb2, wal_fpi-:hfpi AS dfpi2
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- WAL and other statistics advance should not be detected.
+SELECT :dwr2=0 AS dWR, :dfpi2=0 AS dFPI, :dwb2=0 AS dWB;
+
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp < 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps,pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:hwr AS dwr3, wal_bytes-:hwb AS dwb3, wal_fpi-:hfpi AS dfpi3
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+--There are nothing changed
+SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The vm_new_frozen_pages, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+
+DROP TABLE vestat CASCADE;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+-- must be empty
+SELECT vm_new_frozen_pages, vm_new_visible_pages, rev_all_frozen_pages,rev_all_visible_pages,vm_new_visible_frozen_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- backend defreezed pages
+SELECT vm_new_frozen_pages > 0 AS vm_new_frozen_pages,vm_new_visible_pages > 0 AS vm_new_visible_pages,vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages,rev_all_frozen_pages = 0 AS rev_all_frozen_pages,rev_all_visible_pages = 0 AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv,vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+
+UPDATE vestat SET x = x + 1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+SELECT vm_new_frozen_pages > :pf AS vm_new_frozen_pages,vm_new_visible_pages > :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages > :pvf AS vm_new_visible_frozen_pages,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv, vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- vacuum freezed pages
+SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+
+DROP TABLE vestat CASCADE;
\ No newline at end of file
--
2.34.1
[text/x-patch] 0002-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (55.2K, 3-0002-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From 4e19e818679ae56608a0bc087fdc463b3d2183fb Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Mon, 2 Jun 2025 19:35:02 +0300
Subject: [PATCH 2/5] Machinery for grabbing an extended vacuum statistics on
index relations.
They are gathered separatelly from table statistics.
As for tables, we gather vacuum shared buffers statistics for index relations like
value of total_blks_hit, total_blks_read, total_blks_dirtied, wal statistics, io time
during flushing buffer pages to disk, delay and total time.
Due to the fact that such statistics are common as for tables, as for indexes we
set them in the union ExtVacReport structure. We only added some determination 'type'
field to highlight what kind belong to these statistics: PGSTAT_EXTVAC_TABLE or
PGSTAT_EXTVAC_INDEX. Generally, PGSTAT_EXTVAC_INVALID type leads to wrong code process.
Some statistics belong only one type of both tables or indexes. So, we added substructures
sych table and index inside ExtVacReport structure.
Therefore, we gather only for tables such statistics like number of scanned, removed pages,
their charecteristics according VM (all-visible and frozen). In addition, for tables we
gather number frozen, deleted and recently dead tuples and how many times vacuum processed
indexes for tables.
Controversally for indexes we gather number of deleted pages and deleted tuples only.
As for tables, deleted pages and deleted tuples reflect the overall performance of the vacuum
for the index relationship.
Since the vacuum cleans up references to tuple indexes before cleaning up table tuples,
which adds some complexity to the vacuum process, namely the vacuum switches from cleaning up
a table to its indexes and back during its operation, we need to save the vacuum statistics
collected for the heap before it starts cleaning up the indexes.
That's why it's necessary to track the vacuum statistics for the heap several times during
the vacuum procedure. To avoid sending the statistics to the Cumulative Statistics System
several times, we save these statistics in the LVRelState structure and only after vacuum
finishes cleaning up the heap, it sends them to the Cumulative Statistics System.
Authors: Alena Rybakina <[email protected]>,
Andrei Lepikhov <[email protected]>,
Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
---
src/backend/access/heap/vacuumlazy.c | 270 ++++++++++++++----
src/backend/catalog/system_views.sql | 32 +++
src/backend/commands/vacuumparallel.c | 14 +
src/backend/utils/activity/pgstat.c | 4 +
src/backend/utils/activity/pgstat_relation.c | 48 +++-
src/backend/utils/adt/pgstatfuncs.c | 133 ++++++++-
src/backend/utils/misc/guc_tables.c | 2 +-
src/include/catalog/pg_proc.dat | 9 +
src/include/commands/vacuum.h | 25 ++
src/include/pgstat.h | 58 +++-
.../vacuum-extending-in-repetable-read.out | 4 +-
src/test/regress/expected/rules.out | 22 ++
.../expected/vacuum_index_statistics.out | 183 ++++++++++++
src/test/regress/parallel_schedule | 1 +
.../regress/sql/vacuum_index_statistics.sql | 151 ++++++++++
15 files changed, 864 insertions(+), 92 deletions(-)
create mode 100644 src/test/regress/expected/vacuum_index_statistics.out
create mode 100644 src/test/regress/sql/vacuum_index_statistics.sql
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index ee27e70a798..0888be2afea 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -291,6 +291,7 @@ typedef struct LVRelState
char *dbname;
char *relnamespace;
Oid reloid;
+ Oid indoid;
char *relname;
char *indname; /* Current index name */
BlockNumber blkno; /* used only for heap operations */
@@ -411,6 +412,8 @@ typedef struct LVRelState
BlockNumber eager_scan_remaining_fails;
int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
+
+ ExtVacReport extVacReportIdx;
} LVRelState;
@@ -422,19 +425,6 @@ typedef struct LVSavedErrInfo
VacErrPhase phase;
} LVSavedErrInfo;
-/*
- * Counters and usage data for extended stats tracking.
- */
-typedef struct LVExtStatCounters
-{
- TimestampTz starttime;
- WalUsage walusage;
- BufferUsage bufusage;
- double VacuumDelayTime;
- PgStat_Counter blocks_fetched;
- PgStat_Counter blocks_hit;
-} LVExtStatCounters;
-
/* non-export function prototypes */
static void lazy_scan_heap(LVRelState *vacrel);
static void heap_vacuum_eager_scan_setup(LVRelState *vacrel,
@@ -556,27 +546,25 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
endtime = GetCurrentTimestamp();
TimestampDifference(counters->starttime, endtime, &secs, &usecs);
- memset(report, 0, sizeof(ExtVacReport));
-
/*
* Fill additional statistics on a vacuum processing operation.
*/
- report->total_blks_read = bufusage.local_blks_read + bufusage.shared_blks_read;
- report->total_blks_hit = bufusage.local_blks_hit + bufusage.shared_blks_hit;
- report->total_blks_dirtied = bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
- report->total_blks_written = bufusage.shared_blks_written;
+ report->total_blks_read += bufusage.local_blks_read + bufusage.shared_blks_read;
+ report->total_blks_hit += bufusage.local_blks_hit + bufusage.shared_blks_hit;
+ report->total_blks_dirtied += bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
+ report->total_blks_written += bufusage.shared_blks_written;
- report->wal_records = walusage.wal_records;
- report->wal_fpi = walusage.wal_fpi;
- report->wal_bytes = walusage.wal_bytes;
+ report->wal_records += walusage.wal_records;
+ report->wal_fpi += walusage.wal_fpi;
+ report->wal_bytes += walusage.wal_bytes;
- report->blk_read_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
+ report->blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
report->blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
- report->blk_write_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
- report->blk_write_time = INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
- report->delay_time = VacuumDelayTime - counters->VacuumDelayTime;
+ report->blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
+ report->blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
+ report->delay_time += VacuumDelayTime - counters->VacuumDelayTime;
- report->total_time = secs * 1000. + usecs / 1000.;
+ report->total_time += secs * 1000. + usecs / 1000.;
if (!rel->pgstat_info || !pgstat_track_counts)
/*
@@ -585,12 +573,131 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
*/
return;
- report->blks_fetched =
+ report->blks_fetched +=
rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
- report->blks_hit =
+ report->blks_hit +=
rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
}
+void
+extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters)
+{
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ /* Set initial values for common heap and index statistics*/
+ extvac_stats_start(rel, &counters->common);
+ counters->pages_deleted = counters->tuples_removed = 0;
+
+ if (stats != NULL)
+ {
+ /*
+ * XXX: Why do we need this code here? If it is needed, I feel lack of
+ * comments, describing the reason.
+ */
+ counters->tuples_removed = stats->tuples_removed;
+ counters->pages_deleted = stats->pages_deleted;
+ }
+}
+
+void
+extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters, ExtVacReport *report)
+{
+ memset(report, 0, sizeof(ExtVacReport));
+
+ extvac_stats_end(rel, &counters->common, report);
+ report->type = PGSTAT_EXTVAC_INDEX;
+
+ if (stats != NULL)
+ {
+ /*
+ * if something goes wrong or an user doesn't want to track a database
+ * activity - just suppress it.
+ */
+
+ /* Fill index-specific extended stats fields */
+ report->tuples_deleted =
+ stats->tuples_removed - counters->tuples_removed;
+ report->index.pages_deleted =
+ stats->pages_deleted - counters->pages_deleted;
+ }
+}
+
+/* Accumulate vacuum statistics for heap.
+ *
+ * Because of complexity of vacuum processing: it switch procesing between
+ * the heap relation to index relations and visa versa, we need to store
+ * gathered statistics information for heap relations several times before
+ * the vacuum starts processing the indexes again.
+ *
+ * It is necessary to gather correct statistics information for heap and indexes
+ * otherwice the index statistics information would be added to his parent heap
+ * statistics information and it would be difficult to analyze it later.
+ *
+ * We can't subtract union vacuum statistics information for index from the heap relations
+ * because of total and delay time time statistics collecting during parallel vacuum
+ * procudure.
+*/
+static void
+accumulate_heap_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacStats)
+{
+ if (!pgstat_track_vacuum_statistics)
+ return;
+
+ /* Fill heap-specific extended stats fields */
+ extVacStats->type = PGSTAT_EXTVAC_TABLE;
+ extVacStats->table.pages_scanned = vacrel->scanned_pages;
+ extVacStats->table.pages_removed = vacrel->removed_pages;
+ extVacStats->table.vm_new_frozen_pages = vacrel->vm_new_frozen_pages;
+ extVacStats->table.vm_new_visible_pages = vacrel->vm_new_visible_pages;
+ extVacStats->table.vm_new_visible_frozen_pages = vacrel->vm_new_visible_frozen_pages;
+ extVacStats->tuples_deleted = vacrel->tuples_deleted;
+ extVacStats->table.tuples_frozen = vacrel->tuples_frozen;
+ extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
+ extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
+ extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
+ extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
+ extVacStats->table.index_vacuum_count = vacrel->num_index_scans;
+ extVacStats->table.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
+
+ extVacStats->blk_read_time -= vacrel->extVacReportIdx.blk_read_time;
+ extVacStats->blk_write_time -= vacrel->extVacReportIdx.blk_write_time;
+ extVacStats->total_blks_dirtied -= vacrel->extVacReportIdx.total_blks_dirtied;
+ extVacStats->total_blks_hit -= vacrel->extVacReportIdx.total_blks_hit;
+ extVacStats->total_blks_read -= vacrel->extVacReportIdx.total_blks_read;
+ extVacStats->total_blks_written -= vacrel->extVacReportIdx.total_blks_written;
+ extVacStats->wal_bytes -= vacrel->extVacReportIdx.wal_bytes;
+ extVacStats->wal_fpi -= vacrel->extVacReportIdx.wal_fpi;
+ extVacStats->wal_records -= vacrel->extVacReportIdx.wal_records;
+
+ extVacStats->total_time -= vacrel->extVacReportIdx.total_time;
+ extVacStats->delay_time -= vacrel->extVacReportIdx.delay_time;
+
+}
+
+static void
+accumulate_idxs_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacIdxStats)
+{
+ if (!pgstat_track_vacuum_statistics)
+ return;
+
+ /* Fill heap-specific extended stats fields */
+ vacrel->extVacReportIdx.blk_read_time += extVacIdxStats->blk_read_time;
+ vacrel->extVacReportIdx.blk_write_time += extVacIdxStats->blk_write_time;
+ vacrel->extVacReportIdx.total_blks_dirtied += extVacIdxStats->total_blks_dirtied;
+ vacrel->extVacReportIdx.total_blks_hit += extVacIdxStats->total_blks_hit;
+ vacrel->extVacReportIdx.total_blks_read += extVacIdxStats->total_blks_read;
+ vacrel->extVacReportIdx.total_blks_written += extVacIdxStats->total_blks_written;
+ vacrel->extVacReportIdx.wal_bytes += extVacIdxStats->wal_bytes;
+ vacrel->extVacReportIdx.wal_fpi += extVacIdxStats->wal_fpi;
+ vacrel->extVacReportIdx.wal_records += extVacIdxStats->wal_records;
+ vacrel->extVacReportIdx.delay_time += extVacIdxStats->delay_time;
+
+ vacrel->extVacReportIdx.total_time += extVacIdxStats->total_time;
+}
+
/*
* Helper to set up the eager scanning state for vacuuming a single relation.
@@ -750,11 +857,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
LVExtStatCounters extVacCounters;
ExtVacReport extVacReport;
char **indnames = NULL;
- ExtVacReport allzero;
/* Initialize vacuum statistics */
- memset(&allzero, 0, sizeof(ExtVacReport));
- extVacReport = allzero;
+ memset(&extVacReport, 0, sizeof(ExtVacReport));
verbose = (params->options & VACOPT_VERBOSE) != 0;
instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
@@ -800,6 +905,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
+ memset(&vacrel->extVacReportIdx, 0, sizeof(ExtVacReport));
+
/* Set up high level stuff about rel and its indexes */
vacrel->rel = rel;
vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
@@ -1051,23 +1158,6 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
/* Make generic extended vacuum stats report */
extvac_stats_end(rel, &extVacCounters, &extVacReport);
- if(pgstat_track_vacuum_statistics)
- {
- /* Fill heap-specific extended stats fields */
- extVacReport.pages_scanned = vacrel->scanned_pages;
- extVacReport.pages_removed = vacrel->removed_pages;
- extVacReport.vm_new_frozen_pages = vacrel->vm_new_frozen_pages;
- extVacReport.vm_new_visible_pages = vacrel->vm_new_visible_pages;
- extVacReport.vm_new_visible_frozen_pages = vacrel->vm_new_visible_frozen_pages;
- extVacReport.tuples_deleted = vacrel->tuples_deleted;
- extVacReport.tuples_frozen = vacrel->tuples_frozen;
- extVacReport.recently_dead_tuples = vacrel->recently_dead_tuples;
- extVacReport.missed_dead_tuples = vacrel->missed_dead_tuples;
- extVacReport.missed_dead_pages = vacrel->missed_dead_pages;
- extVacReport.index_vacuum_count = vacrel->num_index_scans;
- extVacReport.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
- }
-
/*
* Report results to the cumulative stats system, too.
*
@@ -1078,13 +1168,34 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
* soon in cases where the failsafe prevented significant amounts of heap
* vacuuming.
*/
- pgstat_report_vacuum(RelationGetRelid(rel),
+ if(pgstat_track_vacuum_statistics)
+ {
+ /* Make generic extended vacuum stats report and
+ * fill heap-specific extended stats fields.
+ */
+ extvac_stats_end(vacrel->rel, &extVacCounters, &extVacReport);
+ accumulate_heap_vacuum_statistics(vacrel, &extVacReport);
+
+ pgstat_report_vacuum(RelationGetRelid(rel),
rel->rd_rel->relisshared,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples,
+ vacrel->missed_dead_tuples,
starttime,
&extVacReport);
+
+ }
+ else
+ {
+ pgstat_report_vacuum(RelationGetRelid(rel),
+ rel->rd_rel->relisshared,
+ Max(vacrel->new_live_tuples, 0),
+ vacrel->recently_dead_tuples +
+ vacrel->missed_dead_tuples,
+ starttime,
+ NULL);
+ }
+
pgstat_progress_end_command();
if (instrument)
@@ -2781,10 +2892,20 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
}
else
{
+ LVExtStatCounters counters;
+ ExtVacReport extVacReport;
+
+ memset(&extVacReport, 0, sizeof(ExtVacReport));
+
+ extvac_stats_start(vacrel->rel, &counters);
+
/* Outsource everything to parallel variant */
parallel_vacuum_bulkdel_all_indexes(vacrel->pvs, old_live_tuples,
vacrel->num_index_scans);
+ extvac_stats_end(vacrel->rel, &counters, &extVacReport);
+ accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
+
/*
* Do a postcheck to consider applying wraparound failsafe now. Note
* that parallel VACUUM only gets the precheck and this postcheck.
@@ -3205,10 +3326,20 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
}
else
{
+ LVExtStatCounters counters;
+ ExtVacReport extVacReport;
+
+ memset(&extVacReport, 0, sizeof(ExtVacReport));
+
+ extvac_stats_start(vacrel->rel, &counters);
+
/* Outsource everything to parallel variant */
parallel_vacuum_cleanup_all_indexes(vacrel->pvs, reltuples,
vacrel->num_index_scans,
estimated_count);
+
+ extvac_stats_end(vacrel->rel, &counters, &extVacReport);
+ accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
}
/* Reset the progress counters */
@@ -3234,6 +3365,11 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ ExtVacReport extVacReport;
+
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -3252,6 +3388,7 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
*/
Assert(vacrel->indname == NULL);
vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+ vacrel->indoid = RelationGetRelid(indrel);
update_vacuum_error_info(vacrel, &saved_err_info,
VACUUM_ERRCB_PHASE_VACUUM_INDEX,
InvalidBlockNumber, InvalidOffsetNumber);
@@ -3260,6 +3397,19 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
istat = vac_bulkdel_one_index(&ivinfo, istat, vacrel->dead_items,
vacrel->dead_items_info);
+ if(pgstat_track_vacuum_statistics)
+ {
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+
+ if (!ParallelVacuumIsActive(vacrel))
+ accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
+ }
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
@@ -3284,6 +3434,11 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ ExtVacReport extVacReport;
+
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -3303,12 +3458,25 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
*/
Assert(vacrel->indname == NULL);
vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+ vacrel->indoid = RelationGetRelid(indrel);
update_vacuum_error_info(vacrel, &saved_err_info,
VACUUM_ERRCB_PHASE_INDEX_CLEANUP,
InvalidBlockNumber, InvalidOffsetNumber);
istat = vac_cleanup_one_index(&ivinfo, istat);
+ if(pgstat_track_vacuum_statistics)
+ {
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+ if (!ParallelVacuumIsActive(vacrel))
+ accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
+ }
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 47d27314b55..83d55e78606 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1457,3 +1457,35 @@ FROM pg_class rel
JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
LATERAL pg_stat_get_vacuum_tables(rel.oid) stats
WHERE rel.relkind = 'r';
+
+CREATE VIEW pg_stat_vacuum_indexes AS
+SELECT
+ rel.oid as relid,
+ ns.nspname AS schemaname,
+ rel.relname AS relname,
+
+ total_blks_read AS total_blks_read,
+ total_blks_hit AS total_blks_hit,
+ total_blks_dirtied AS total_blks_dirtied,
+ total_blks_written AS total_blks_written,
+
+ rel_blks_read AS rel_blks_read,
+ rel_blks_hit AS rel_blks_hit,
+
+ pages_deleted AS pages_deleted,
+ tuples_deleted AS tuples_deleted,
+
+ wal_records AS wal_records,
+ wal_fpi AS wal_fpi,
+ wal_bytes AS wal_bytes,
+
+ blk_read_time AS blk_read_time,
+ blk_write_time AS blk_write_time,
+
+ delay_time AS delay_time,
+ total_time AS total_time
+FROM
+ pg_class rel
+ JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
+ LATERAL pg_stat_get_vacuum_indexes(rel.oid) stats
+WHERE rel.relkind = 'i';
\ No newline at end of file
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 2b55d9b7c0e..65de45a4447 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -868,6 +868,8 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
IndexBulkDeleteResult *istat = NULL;
IndexBulkDeleteResult *istat_res;
IndexVacuumInfo ivinfo;
+ LVExtStatCountersIdx extVacCounters;
+ ExtVacReport extVacReport;
/*
* Update the pointer to the corresponding bulk-deletion result if someone
@@ -876,6 +878,9 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
if (indstats->istat_updated)
istat = &(indstats->istat);
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, &(indstats->istat), &extVacCounters);
+
ivinfo.index = indrel;
ivinfo.heaprel = pvs->heaprel;
ivinfo.analyze_only = false;
@@ -904,6 +909,15 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
RelationGetRelationName(indrel));
}
+ if(pgstat_track_vacuum_statistics)
+ {
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat_res, &extVacCounters, &extVacReport);
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
+ }
+
/*
* Copy the index bulk-deletion result returned from ambulkdelete and
* amvacuumcleanup to the DSM segment if it's the first cycle because they
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 23cb62e36a7..f5f75aa4264 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -1176,6 +1176,10 @@ pgstat_build_snapshot(PgStat_Kind statKind)
if (p->dropped)
continue;
+ if (statKind != PGSTAT_KIND_INVALID && statKind != p->key.kind)
+ /* Load stat of specific type, if defined */
+ continue;
+
Assert(pg_atomic_read_u32(&p->refcount) > 0);
stats_data = dsa_get_address(pgStatLocal.dsa, p->body);
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index ee0385cd809..9ee03509490 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -1016,6 +1016,9 @@ static void
pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
bool accumulate_reltype_specific_info)
{
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
dst->total_blks_read += src->total_blks_read;
dst->total_blks_hit += src->total_blks_hit;
dst->total_blks_dirtied += src->total_blks_dirtied;
@@ -1031,20 +1034,35 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
if (!accumulate_reltype_specific_info)
return;
- dst->blks_fetched += src->blks_fetched;
- dst->blks_hit += src->blks_hit;
-
- dst->pages_scanned += src->pages_scanned;
- dst->pages_removed += src->pages_removed;
- dst->vm_new_frozen_pages += src->vm_new_frozen_pages;
- dst->vm_new_visible_pages += src->vm_new_visible_pages;
- dst->vm_new_visible_frozen_pages += src->vm_new_visible_frozen_pages;
- dst->tuples_deleted += src->tuples_deleted;
- dst->tuples_frozen += src->tuples_frozen;
- dst->recently_dead_tuples += src->recently_dead_tuples;
- dst->index_vacuum_count += src->index_vacuum_count;
- dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
- dst->missed_dead_pages += src->missed_dead_pages;
- dst->missed_dead_tuples += src->missed_dead_tuples;
+ if (dst->type == PGSTAT_EXTVAC_INVALID)
+ dst->type = src->type;
+
+ Assert(src->type == PGSTAT_EXTVAC_INVALID || src->type == dst->type);
+
+ if (dst->type == src->type)
+ {
+ dst->blks_fetched += src->blks_fetched;
+ dst->blks_hit += src->blks_hit;
+ if (dst->type == PGSTAT_EXTVAC_TABLE)
+ {
+ dst->table.pages_scanned += src->table.pages_scanned;
+ dst->table.pages_removed += src->table.pages_removed;
+ dst->table.vm_new_frozen_pages += src->table.vm_new_frozen_pages;
+ dst->table.vm_new_visible_pages += src->table.vm_new_visible_pages;
+ dst->table.vm_new_visible_frozen_pages += src->table.vm_new_visible_frozen_pages;
+ dst->tuples_deleted += src->tuples_deleted;
+ dst->table.tuples_frozen += src->table.tuples_frozen;
+ dst->table.recently_dead_tuples += src->table.recently_dead_tuples;
+ dst->table.index_vacuum_count += src->table.index_vacuum_count;
+ dst->table.missed_dead_pages += src->table.missed_dead_pages;
+ dst->table.missed_dead_tuples += src->table.missed_dead_tuples;
+ dst->table.wraparound_failsafe_count += src->table.wraparound_failsafe_count;
+ }
+ else if (dst->type == PGSTAT_EXTVAC_INDEX)
+ {
+ dst->index.pages_deleted += src->index.pages_deleted;
+ dst->tuples_deleted += src->tuples_deleted;
+ }
+ }
}
\ No newline at end of file
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index a5610199893..482929b75e9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2372,18 +2372,19 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
extvacuum->blks_hit);
values[i++] = Int64GetDatum(extvacuum->blks_hit);
- values[i++] = Int64GetDatum(extvacuum->pages_scanned);
- values[i++] = Int64GetDatum(extvacuum->pages_removed);
- values[i++] = Int64GetDatum(extvacuum->vm_new_frozen_pages);
- values[i++] = Int64GetDatum(extvacuum->vm_new_visible_pages);
- values[i++] = Int64GetDatum(extvacuum->vm_new_visible_frozen_pages);
- values[i++] = Int64GetDatum(extvacuum->missed_dead_pages);
+ values[i++] = Int64GetDatum(extvacuum->table.pages_scanned);
+ values[i++] = Int64GetDatum(extvacuum->table.pages_removed);
+ values[i++] = Int64GetDatum(extvacuum->table.vm_new_frozen_pages);
+ values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_pages);
+ values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_frozen_pages);
+ values[i++] = Int64GetDatum(extvacuum->table.missed_dead_pages);
values[i++] = Int64GetDatum(extvacuum->tuples_deleted);
- values[i++] = Int64GetDatum(extvacuum->tuples_frozen);
- values[i++] = Int64GetDatum(extvacuum->recently_dead_tuples);
- values[i++] = Int64GetDatum(extvacuum->missed_dead_tuples);
- values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
- values[i++] = Int64GetDatum(extvacuum->index_vacuum_count);
+ values[i++] = Int64GetDatum(extvacuum->table.tuples_frozen);
+ values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
+ values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
+
+ values[i++] = Int32GetDatum(extvacuum->table.wraparound_failsafe_count);
+ values[i++] = Int64GetDatum(extvacuum->table.index_vacuum_count);
values[i++] = Int64GetDatum(extvacuum->wal_records);
values[i++] = Int64GetDatum(extvacuum->wal_fpi);
@@ -2402,6 +2403,116 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
+/*
+ * Get the vacuum statistics for the heap tables.
+ */
+Datum
+pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
+{
+ #define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 16
+
+ Oid relid = PG_GETARG_OID(0);
+ PgStat_StatTabEntry *tabentry;
+ ExtVacReport *extvacuum;
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_VACUUM_INDEX_STATS_COLS] = {0};
+ bool nulls[PG_STAT_GET_VACUUM_INDEX_STATS_COLS] = {0};
+ char buf[256];
+ int i = 0;
+ ExtVacReport allzero;
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "relid",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_read",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_deleted",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_deleted",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
+ NUMERICOID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
+ FLOAT8OID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
+ FLOAT8OID, -1, 0);
+
+ Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
+
+ BlessTupleDesc(tupdesc);
+
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+
+ if (tabentry == NULL)
+ {
+ /* If the subscription is not found, initialise its stats */
+ memset(&allzero, 0, sizeof(ExtVacReport));
+ extvacuum = &allzero;
+ }
+ else
+ {
+ extvacuum = &(tabentry->vacuum_ext);
+ }
+
+ i = 0;
+
+ values[i++] = ObjectIdGetDatum(relid);
+
+ values[i++] = Int64GetDatum(extvacuum->total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+
+ values[i++] = Int64GetDatum(extvacuum->blks_fetched -
+ extvacuum->blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->blks_hit);
+
+ values[i++] = Int64GetDatum(extvacuum->index.pages_deleted);
+ values[i++] = Int64GetDatum(extvacuum->tuples_deleted);
+
+ values[i++] = Int64GetDatum(extvacuum->wal_records);
+ values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ values[i++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[i++] = Float8GetDatum(extvacuum->blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->delay_time);
+ values[i++] = Float8GetDatum(extvacuum->total_time);
+
+ Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
+
/* Returns the record as Datum */
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
\ No newline at end of file
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 115f0c51cc2..42f4cac5e0e 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1510,7 +1510,7 @@ struct config_bool ConfigureNamesBool[] =
},
{
{"track_vacuum_statistics", PGC_SUSET, STATS_CUMULATIVE,
- gettext_noop("Collects vacuum statistics for table relations."),
+ gettext_noop("Collects vacuum statistics for relations."),
NULL
},
&pgstat_track_vacuum_statistics,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c04d3880241..8c77ae96100 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12574,4 +12574,13 @@
proname => 'pg_stat_get_rev_all_frozen_pages', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_rev_all_frozen_pages' },
+{ oid => '8004',
+ descr => 'pg_stat_get_vacuum_indexes return stats values',
+ proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
+ proretset => 't',
+ proargtypes => 'oid',
+ proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{reloid,relid,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,pages_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time}',
+ prosrc => 'pg_stat_get_vacuum_indexes' }
]
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 6d1b2991ce5..fb134f3402e 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -25,6 +25,7 @@
#include "storage/buf.h"
#include "storage/lock.h"
#include "utils/relcache.h"
+#include "pgstat.h"
/*
* Flags for amparallelvacuumoptions to control the participation of bulkdelete
@@ -295,6 +296,26 @@ typedef struct VacDeadItemsInfo
int64 num_items; /* current # of entries */
} VacDeadItemsInfo;
+/*
+ * Counters and usage data for extended stats tracking.
+ */
+typedef struct LVExtStatCounters
+{
+ TimestampTz starttime;
+ WalUsage walusage;
+ BufferUsage bufusage;
+ double VacuumDelayTime;
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
+} LVExtStatCounters;
+
+typedef struct LVExtStatCountersIdx
+{
+ LVExtStatCounters common;
+ int64 pages_deleted;
+ int64 tuples_removed;
+} LVExtStatCountersIdx;
+
/* GUC parameters */
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for PostGIS */
extern PGDLLIMPORT int vacuum_freeze_min_age;
@@ -408,4 +429,8 @@ extern double anl_random_fract(void);
extern double anl_init_selection_state(int n);
extern double anl_get_next_S(double t, int n, double *stateptr);
+extern void extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters);
+extern void extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters, ExtVacReport *report);
#endif /* VACUUM_H */
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 6c88d57aef7..4def2c60d1d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -111,11 +111,19 @@ typedef struct PgStat_BackendSubEntry
PgStat_Counter conflict_count[CONFLICT_NUM_TYPES];
} PgStat_BackendSubEntry;
+/* Type of ExtVacReport */
+typedef enum ExtVacReportType
+{
+ PGSTAT_EXTVAC_INVALID = 0,
+ PGSTAT_EXTVAC_TABLE = 1,
+ PGSTAT_EXTVAC_INDEX = 2
+} ExtVacReportType;
+
/* ----------
*
* ExtVacReport
*
- * Additional statistics of vacuum processing over a heap relation.
+ * Additional statistics of vacuum processing over a relation.
* pages_removed is the amount by which the physically shrank,
* if any (ie the change in its total size on disk)
* pages_deleted refer to free space within the index file
@@ -144,18 +152,44 @@ typedef struct ExtVacReport
double delay_time; /* how long vacuum slept in vacuum delay point, in msec */
double total_time; /* total time of a vacuum operation, in msec */
- int64 pages_scanned; /* heap pages examined (not skipped by VM) */
- int64 pages_removed; /* heap pages removed by vacuum "truncation" */
- int64 vm_new_frozen_pages; /* pages marked in VM as frozen */
- int64 vm_new_visible_pages; /* pages marked in VM as all-visible */
- int64 vm_new_visible_frozen_pages; /* pages marked in VM as all-visible and frozen */
- int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
- int64 missed_dead_pages; /* pages with missed dead tuples */
int64 tuples_deleted; /* tuples deleted by vacuum */
- int64 tuples_frozen; /* tuples frozen up by vacuum */
- int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
- int64 index_vacuum_count; /* the number of index vacuumings */
- int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
+
+ ExtVacReportType type; /* heap, index, etc. */
+
+ /* ----------
+ *
+ * There are separate metrics of statistic for tables and indexes,
+ * which collect during vacuum.
+ * The union operator allows to combine these statistics
+ * so that each metric is assigned to a specific class of collected statistics.
+ * Such a combined structure was called per_type_stats.
+ * The name of the structure itself is not used anywhere,
+ * it exists only for understanding the code.
+ * ----------
+ */
+ union
+ {
+ struct
+ {
+ int64 pages_scanned; /* heap pages examined (not skipped by VM) */
+ int64 pages_removed; /* heap pages removed by vacuum "truncation" */
+ int64 pages_frozen; /* pages marked in VM as frozen */
+ int64 pages_all_visible; /* pages marked in VM as all-visible */
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
+ int64 vm_new_frozen_pages; /* pages marked in VM as frozen */
+ int64 vm_new_visible_pages; /* pages marked in VM as all-visible */
+ int64 vm_new_visible_frozen_pages; /* pages marked in VM as all-visible and frozen */
+ int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
+ int64 missed_dead_pages; /* pages with missed dead tuples */
+ int64 index_vacuum_count; /* number of index vacuumings */
+ int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
+ } table;
+ struct
+ {
+ int64 pages_deleted; /* number of pages deleted by vacuum */
+ } index;
+ } /* per_type_stats */;
} ExtVacReport;
/* ----------
diff --git a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
index 87f7e40b4a6..6d960423912 100644
--- a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
+++ b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
@@ -34,7 +34,7 @@ step s2_print_vacuum_stats_table:
relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
--------------------------+--------------+--------------------+------------------+-----------------+-------------
-test_vacuum_stat_isolation| 0| 100| 0| 0| 0
+test_vacuum_stat_isolation| 0| 600| 0| 0| 0
(1 row)
step s1_commit: COMMIT;
@@ -48,6 +48,6 @@ step s2_print_vacuum_stats_table:
relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
--------------------------+--------------+--------------------+------------------+-----------------+-------------
-test_vacuum_stat_isolation| 100| 100| 0| 0| 101
+test_vacuum_stat_isolation| 300| 600| 0| 0| 303
(1 row)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 10a482e2db4..4e5e5ca54da 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2283,6 +2283,28 @@ pg_stat_user_tables| SELECT relid,
rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
+pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
+ ns.nspname AS schemaname,
+ rel.relname,
+ stats.total_blks_read,
+ stats.total_blks_hit,
+ stats.total_blks_dirtied,
+ stats.total_blks_written,
+ stats.rel_blks_read,
+ stats.rel_blks_hit,
+ stats.pages_deleted,
+ stats.tuples_deleted,
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.total_time
+ FROM (pg_class rel
+ JOIN pg_namespace ns ON ((ns.oid = rel.relnamespace))),
+ LATERAL pg_stat_get_vacuum_indexes(rel.oid) stats(relid, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit, pages_deleted, tuples_deleted, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, total_time)
+ WHERE (rel.relkind = 'i'::"char");
pg_stat_vacuum_tables| SELECT ns.nspname AS schemaname,
rel.relname,
stats.relid,
diff --git a/src/test/regress/expected/vacuum_index_statistics.out b/src/test/regress/expected/vacuum_index_statistics.out
new file mode 100644
index 00000000000..e00a0fc683c
--- /dev/null
+++ b/src/test/regress/expected/vacuum_index_statistics.out
@@ -0,0 +1,183 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+ track_counts
+--------------
+ on
+(1 row)
+
+\set sample_size 10000
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+-- Test that vacuum statistics will be empty when parameter is off.
+SET track_vacuum_statistics TO 'off';
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+DELETE FROM vestat WHERE x % 2 = 0;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- Must be empty.
+SELECT *
+FROM pg_stat_vacuum_indexes vt
+WHERE vt.relname = 'vestat';
+ relid | schemaname | relname | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit | pages_deleted | tuples_deleted | wal_records | wal_fpi | wal_bytes | blk_read_time | blk_write_time | delay_time | total_time
+-------+------------+---------+-----------------+----------------+--------------------+--------------------+---------------+--------------+---------------+----------------+-------------+---------+-----------+---------------+----------------+------------+------------
+(0 rows)
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+SHOW track_vacuum_statistics; -- must be on
+ track_vacuum_statistics
+-------------------------
+ on
+(1 row)
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+\set sample_size 10000
+SET vacuum_freeze_min_age = 0;
+SET vacuum_freeze_table_age = 0;
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int primary key) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+SELECT oid AS ioid from pg_class where relname = 'vestat_pkey' \gset
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,relpages,pages_deleted,tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+ relname | relpages | pages_deleted | tuples_deleted
+-------------+----------+---------------+----------------
+ vestat_pkey | 30 | 0 | 0
+(1 row)
+
+SELECT relpages AS irp
+FROM pg_class c
+WHERE relname = 'vestat_pkey' \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted = 0 AS pages_deleted,tuples_deleted > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+ relname | relpages | pages_deleted | tuples_deleted
+-------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS diWR, wal_bytes > 0 AS diWB
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey';
+ diwr | diwb
+------+------
+ t | t
+(1 row)
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- pages_removed must be increased
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd > 0 AS pages_deleted,tuples_deleted-:itd > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+ relname | relpages | pages_deleted | tuples_deleted
+-------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr, wal_bytes-:iwb AS diwb, wal_fpi-:ifpi AS difpi
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+-- WAL advance should be detected.
+SELECT :diwr > 0 AS diWR, :diwb > 0 AS diWB;
+ diwr | diwb
+------+------
+ t | t
+(1 row)
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr2, wal_bytes-:iwb AS diwb2, wal_fpi-:ifpi AS difpi2
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+-- WAL and other statistics advance should not be detected.
+SELECT :diwr2=0 AS diWR, :difpi2=0 AS iFPI, :diwb2=0 AS diWB;
+ diwr | ifpi | diwb
+------+------+------
+ t | t | t
+(1 row)
+
+SELECT vt.relname,relpages-:irp < 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+ relname | relpages | pages_deleted | tuples_deleted
+-------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:iwr AS diwr3, wal_bytes-:iwb AS diwb3, wal_fpi-:ifpi AS difpi3
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+--There are nothing changed
+SELECT :diwr3=0 AS diWR, :difpi3=0 AS iFPI, :diwb3=0 AS diWB;
+ diwr | ifpi | diwb
+------+------+------
+ t | t | t
+(1 row)
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+ relname | relpages | pages_deleted | tuples_deleted
+-------------+----------+---------------+----------------
+ vestat_pkey | f | t | t
+(1 row)
+
+DROP TABLE vestat;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ee0343c2729..0197830b5cd 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -144,4 +144,5 @@ test: tablespace
# ----------
# Check vacuum statistics
# ----------
+test: vacuum_index_statistics
test: vacuum_tables_statistics
\ No newline at end of file
diff --git a/src/test/regress/sql/vacuum_index_statistics.sql b/src/test/regress/sql/vacuum_index_statistics.sql
new file mode 100644
index 00000000000..ae146e1d23f
--- /dev/null
+++ b/src/test/regress/sql/vacuum_index_statistics.sql
@@ -0,0 +1,151 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+
+\set sample_size 10000
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+
+-- Test that vacuum statistics will be empty when parameter is off.
+SET track_vacuum_statistics TO 'off';
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+DELETE FROM vestat WHERE x % 2 = 0;
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- Must be empty.
+SELECT *
+FROM pg_stat_vacuum_indexes vt
+WHERE vt.relname = 'vestat';
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+
+SHOW track_vacuum_statistics; -- must be on
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+\set sample_size 10000
+SET vacuum_freeze_min_age = 0;
+SET vacuum_freeze_table_age = 0;
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int primary key) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+SELECT oid AS ioid from pg_class where relname = 'vestat_pkey' \gset
+
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,relpages,pages_deleted,tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+SELECT relpages AS irp
+FROM pg_class c
+WHERE relname = 'vestat_pkey' \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted = 0 AS pages_deleted,tuples_deleted > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS diWR, wal_bytes > 0 AS diWB
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey';
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- pages_removed must be increased
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd > 0 AS pages_deleted,tuples_deleted-:itd > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr, wal_bytes-:iwb AS diwb, wal_fpi-:ifpi AS difpi
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+-- WAL advance should be detected.
+SELECT :diwr > 0 AS diWR, :diwb > 0 AS diWB;
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr2, wal_bytes-:iwb AS diwb2, wal_fpi-:ifpi AS difpi2
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+-- WAL and other statistics advance should not be detected.
+SELECT :diwr2=0 AS diWR, :difpi2=0 AS iFPI, :diwb2=0 AS diWB;
+
+SELECT vt.relname,relpages-:irp < 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+SELECT vt.relname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:iwr AS diwr3, wal_bytes-:iwb AS diwb3, wal_fpi-:ifpi AS difpi3
+FROM pg_stat_vacuum_indexes WHERE relname = 'vestat_pkey' \gset
+
+--There are nothing changed
+SELECT :diwr3=0 AS diWR, :difpi3=0 AS iFPI, :diwb3=0 AS diWB;
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
+
+DROP TABLE vestat;
--
2.34.1
[text/x-patch] 0003-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (31.2K, 4-0003-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From 2978fa59fc553b1a711731ae83159e4f44241dee Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Tue, 4 Feb 2025 17:57:44 +0300
Subject: [PATCH 3/5] Machinery for grabbing an extended vacuum statistics on
databases.
Database vacuum statistics information is the collected general
vacuum statistics indexes and tables owned by the databases, which
they belong to.
In addition to the fact that there are far fewer databases in a system
than relations, vacuum statistics for a database contain fewer statistics
than relations, but they are enough to indicate that something may be
wrong in the system and prompt the administrator to enable extended
monitoring for relations.
So, buffer, wal, statistics of I/O time of read and writen blocks
statistics will be observed because they are collected for both
tables, indexes. In addition, we show the number of errors caught
during operation of the vacuum only for the error level.
wraparound_failsafe_count is a number of times when the vacuum starts
urgent cleanup to prevent wraparound problem which is critical for
the database.
Authors: Alena Rybakina <[email protected]>,
Andrei Lepikhov <[email protected]>,
Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
---
src/backend/access/heap/vacuumlazy.c | 17 ++-
src/backend/catalog/system_views.sql | 27 ++++-
src/backend/utils/activity/pgstat.c | 2 +-
src/backend/utils/activity/pgstat_database.c | 1 +
src/backend/utils/activity/pgstat_relation.c | 46 +++++++-
src/backend/utils/adt/pgstatfuncs.c | 100 +++++++++++++++++-
src/backend/utils/misc/guc_tables.c | 2 +-
src/include/catalog/pg_proc.dat | 13 ++-
src/include/pgstat.h | 5 +-
.../vacuum-extending-in-repetable-read.spec | 6 ++
src/test/regress/expected/rules.out | 17 +++
.../expected/vacuum_index_statistics.out | 16 +--
...ut => vacuum_tables_and_db_statistics.out} | 87 +++++++++++++--
src/test/regress/parallel_schedule | 2 +-
.../regress/sql/vacuum_index_statistics.sql | 6 +-
...ql => vacuum_tables_and_db_statistics.sql} | 69 +++++++++++-
16 files changed, 381 insertions(+), 35 deletions(-)
rename src/test/regress/expected/{vacuum_tables_statistics.out => vacuum_tables_and_db_statistics.out} (82%)
rename src/test/regress/sql/{vacuum_tables_statistics.sql => vacuum_tables_and_db_statistics.sql} (81%)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 0888be2afea..3d72f74b05e 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -660,7 +660,7 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacStats)
extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
extVacStats->table.index_vacuum_count = vacrel->num_index_scans;
- extVacStats->table.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
+ extVacStats->wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
extVacStats->blk_read_time -= vacrel->extVacReportIdx.blk_read_time;
extVacStats->blk_write_time -= vacrel->extVacReportIdx.blk_write_time;
@@ -4089,6 +4089,9 @@ vacuum_error_callback(void *arg)
switch (errinfo->phase)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -4104,6 +4107,9 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -4119,16 +4125,25 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_INDEX:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX);
+
errcontext("while vacuuming index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_INDEX_CLEANUP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX);
+
errcontext("while cleaning up index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_TRUNCATE:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
errcontext("while truncating relation \"%s.%s\" to %u blocks",
errinfo->relnamespace, errinfo->relname, errinfo->blkno);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 83d55e78606..0ae31b87989 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1488,4 +1488,29 @@ FROM
pg_class rel
JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
LATERAL pg_stat_get_vacuum_indexes(rel.oid) stats
-WHERE rel.relkind = 'i';
\ No newline at end of file
+WHERE rel.relkind = 'i';
+
+CREATE VIEW pg_stat_vacuum_database AS
+SELECT
+ db.oid as dboid,
+ db.datname AS dbname,
+
+ stats.db_blks_read AS db_blks_read,
+ stats.db_blks_hit AS db_blks_hit,
+ stats.total_blks_dirtied AS total_blks_dirtied,
+ stats.total_blks_written AS total_blks_written,
+
+ stats.wal_records AS wal_records,
+ stats.wal_fpi AS wal_fpi,
+ stats.wal_bytes AS wal_bytes,
+
+ stats.blk_read_time AS blk_read_time,
+ stats.blk_write_time AS blk_write_time,
+
+ stats.delay_time AS delay_time,
+ stats.total_time AS total_time,
+ stats.wraparound_failsafe AS wraparound_failsafe,
+ stats.errors AS errors
+FROM
+ pg_database db,
+ LATERAL pg_stat_get_vacuum_database(db.oid) stats;
\ No newline at end of file
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index f5f75aa4264..85557736a3a 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -203,7 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind);
bool pgstat_track_counts = false;
int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE;
-bool pgstat_track_vacuum_statistics = true;
+bool pgstat_track_vacuum_statistics = false;
/* ----------
* state shared with pgstat_*.c
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index b31f20d41bc..65207d30378 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -485,6 +485,7 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
pgstat_unlock_entry(entry_ref);
memset(pendingent, 0, sizeof(*pendingent));
+ memset(&(pendingent)->vacuum_ext, 0, sizeof(ExtVacReport));
return true;
}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 9ee03509490..1695680ea62 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -205,6 +205,38 @@ pgstat_drop_relation(Relation rel)
}
}
+/* ---------
+ * pgstat_report_vacuum_error() -
+ *
+ * Tell the collector about an (auto)vacuum interruption.
+ * ---------
+ */
+void
+pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ Oid dboid = MyDatabaseId;
+ PgStat_StatDBEntry *dbentry; /* pending database entry */
+
+ if (!pgstat_track_counts)
+ return;
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ dboid, tableoid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ tabentry->vacuum_ext.type = m_type;
+ pgstat_unlock_entry(entry_ref);
+
+ dbentry = pgstat_prep_database_pending(dboid);
+ dbentry->vacuum_ext.errors++;
+ dbentry->vacuum_ext.type = m_type;
+}
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*/
@@ -216,6 +248,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
PgStat_StatTabEntry *tabentry;
+ PgStatShared_Database *dbentry;
Oid dboid = (shared ? InvalidOid : MyDatabaseId);
TimestampTz ts;
PgStat_Counter elapsedtime;
@@ -274,6 +307,16 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
*/
pgstat_flush_io(false);
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
+
+ if (dboid != InvalidOid)
+ {
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE,
+ dboid, InvalidOid, false);
+ dbentry = (PgStatShared_Database *) entry_ref->shared_stats;
+
+ pgstat_accumulate_extvac_stats(&dbentry->stats.vacuum_ext, params, false);
+ pgstat_unlock_entry(entry_ref);
+ }
}
/*
@@ -1030,6 +1073,8 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
dst->blk_write_time += src->blk_write_time;
dst->delay_time += src->delay_time;
dst->total_time += src->total_time;
+ dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
+ dst->errors += src->errors;
if (!accumulate_reltype_specific_info)
return;
@@ -1057,7 +1102,6 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
dst->table.index_vacuum_count += src->table.index_vacuum_count;
dst->table.missed_dead_pages += src->table.missed_dead_pages;
dst->table.missed_dead_tuples += src->table.missed_dead_tuples;
- dst->table.wraparound_failsafe_count += src->table.wraparound_failsafe_count;
}
else if (dst->type == PGSTAT_EXTVAC_INDEX)
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 482929b75e9..a2ece2c36cf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2383,7 +2383,7 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
- values[i++] = Int32GetDatum(extvacuum->table.wraparound_failsafe_count);
+ values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
values[i++] = Int64GetDatum(extvacuum->table.index_vacuum_count);
values[i++] = Int64GetDatum(extvacuum->wal_records);
@@ -2513,6 +2513,104 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
+Datum
+pg_stat_get_vacuum_database(PG_FUNCTION_ARGS)
+{
+ #define PG_STAT_GET_VACUUM_DATABASE_STATS_COLS 14
+
+ Oid dbid = PG_GETARG_OID(0);
+ PgStat_StatDBEntry *dbentry;
+ ExtVacReport *extvacuum;
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
+ bool nulls[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
+ char buf[256];
+ int i = 0;
+ ExtVacReport allzero;
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "dbid",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
+ NUMERICOID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
+ FLOAT8OID, -1, 0);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wraparound_failsafe_count",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "errors",
+ INT4OID, -1, 0);
+
+ Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
+ BlessTupleDesc(tupdesc);
+
+ dbentry = pgstat_fetch_stat_dbentry(dbid);
+
+ if (dbentry == NULL)
+ {
+ /* If the subscription is not found, initialise its stats */
+ memset(&allzero, 0, sizeof(ExtVacReport));
+ extvacuum = &allzero;
+ }
+ else
+ {
+ extvacuum = &(dbentry->vacuum_ext);
+ }
+
+ i = 0;
+
+ values[i++] = ObjectIdGetDatum(dbid);
+
+ values[i++] = Int64GetDatum(extvacuum->total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+
+ values[i++] = Int64GetDatum(extvacuum->wal_records);
+ values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ values[i++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[i++] = Float8GetDatum(extvacuum->blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->delay_time);
+ values[i++] = Float8GetDatum(extvacuum->total_time);
+ values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
+ values[i++] = Int32GetDatum(extvacuum->errors);
+
+ Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
/* Returns the record as Datum */
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
\ No newline at end of file
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 42f4cac5e0e..a24dec63f3a 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1514,7 +1514,7 @@ struct config_bool ConfigureNamesBool[] =
NULL
},
&pgstat_track_vacuum_statistics,
- true,
+ false,
NULL, NULL, NULL
},
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8c77ae96100..4e1e29eec7e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12575,12 +12575,21 @@
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_rev_all_frozen_pages' },
{ oid => '8004',
- descr => 'pg_stat_get_vacuum_indexes return stats values',
+ descr => 'pg_stat_get_vacuum_indexes returns vacuum stats values for index',
proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
proretset => 't',
proargtypes => 'oid',
proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8}',
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{reloid,relid,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,pages_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time}',
- prosrc => 'pg_stat_get_vacuum_indexes' }
+ prosrc => 'pg_stat_get_vacuum_indexes' },
+{ oid => '8005',
+ descr => 'pg_stat_get_vacuum_database returns vacuum stats values for database',
+ proname => 'pg_stat_get_vacuum_database', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
+ proretset => 't',
+ proargtypes => 'oid',
+ proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,int4,int4}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{dbid,dboid,db_blks_read,db_blks_hit,total_blks_dirtied,total_blks_written,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time,wraparound_failsafe,errors}',
+ prosrc => 'pg_stat_get_vacuum_database' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4def2c60d1d..f8158aa353c 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -154,6 +154,9 @@ typedef struct ExtVacReport
int64 tuples_deleted; /* tuples deleted by vacuum */
+ int32 errors;
+ int32 wraparound_failsafe_count; /* the number of times to prevent wraparound problem */
+
ExtVacReportType type; /* heap, index, etc. */
/* ----------
@@ -183,7 +186,6 @@ typedef struct ExtVacReport
int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
int64 missed_dead_pages; /* pages with missed dead tuples */
int64 index_vacuum_count; /* number of index vacuumings */
- int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
} table;
struct
{
@@ -762,6 +764,7 @@ extern void pgstat_report_vacuum(Oid tableoid, bool shared,
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
+extern void pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type);
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
diff --git a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
index 5893d89573d..cfec3159580 100644
--- a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -18,6 +18,9 @@ teardown
}
session s1
+setup {
+ SET track_vacuum_statistics TO 'on';
+ }
step s1_begin_repeatable_read {
BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
select count(ival) from test_vacuum_stat_isolation where id>900;
@@ -25,6 +28,9 @@ step s1_begin_repeatable_read {
step s1_commit { COMMIT; }
session s2
+setup {
+ SET track_vacuum_statistics TO 'on';
+ }
step s2_insert { INSERT INTO test_vacuum_stat_isolation(id, ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival; }
step s2_update { UPDATE test_vacuum_stat_isolation SET ival = ival + 2 where id > 900; }
step s2_delete { DELETE FROM test_vacuum_stat_isolation where id > 900; }
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 4e5e5ca54da..f63f25f94d8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2283,6 +2283,23 @@ pg_stat_user_tables| SELECT relid,
rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
+pg_stat_vacuum_database| SELECT db.oid AS dboid,
+ db.datname AS dbname,
+ stats.db_blks_read,
+ stats.db_blks_hit,
+ stats.total_blks_dirtied,
+ stats.total_blks_written,
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.total_time,
+ stats.wraparound_failsafe,
+ stats.errors
+ FROM pg_database db,
+ LATERAL pg_stat_get_vacuum_database(db.oid) stats(dboid, db_blks_read, db_blks_hit, total_blks_dirtied, total_blks_written, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, total_time, wraparound_failsafe, errors);
pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
ns.nspname AS schemaname,
rel.relname,
diff --git a/src/test/regress/expected/vacuum_index_statistics.out b/src/test/regress/expected/vacuum_index_statistics.out
index e00a0fc683c..9e5d33342c9 100644
--- a/src/test/regress/expected/vacuum_index_statistics.out
+++ b/src/test/regress/expected/vacuum_index_statistics.out
@@ -16,8 +16,12 @@ SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(1 row)
+
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
ANALYZE vestat;
@@ -33,12 +37,7 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
- track_vacuum_statistics
--------------------------
- on
-(1 row)
-
+SET track_vacuum_statistics TO 'on';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -181,3 +180,4 @@ WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
(1 row)
DROP TABLE vestat;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
similarity index 82%
rename from src/test/regress/expected/vacuum_tables_statistics.out
rename to src/test/regress/expected/vacuum_tables_and_db_statistics.out
index b5ea9c9ab1e..0300e7b6276 100644
--- a/src/test/regress/expected/vacuum_tables_statistics.out
+++ b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
@@ -6,7 +6,6 @@
-- number of frozen and visible pages removed by backend.
-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
--
--- conditio sine qua non
SHOW track_counts; -- must be on
track_counts
--------------
@@ -16,8 +15,12 @@ SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(1 row)
+
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
ANALYZE vestat;
@@ -37,12 +40,12 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
- track_vacuum_statistics
--------------------------
- on
-(1 row)
-
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+SET track_vacuum_statistics TO on;
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -225,3 +228,69 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna
(1 row)
DROP TABLE vestat CASCADE;
+-- Now check vacuum statistics for current database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+ dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t
+(1 row)
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+UPDATE vestat SET x = 10001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+-- Now check vacuum statistics for postgres database from another database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+ dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t
+(1 row)
+
+\c regression_statistic_vacuum_db
+SET track_vacuum_statistics TO on;
+DROP TABLE vestat CASCADE;
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+ count
+-------
+ 0
+(1 row)
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 0197830b5cd..fa2489716cc 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -145,4 +145,4 @@ test: tablespace
# Check vacuum statistics
# ----------
test: vacuum_index_statistics
-test: vacuum_tables_statistics
\ No newline at end of file
+test: vacuum_tables_and_db_statistics
\ No newline at end of file
diff --git a/src/test/regress/sql/vacuum_index_statistics.sql b/src/test/regress/sql/vacuum_index_statistics.sql
index ae146e1d23f..9b7e645187d 100644
--- a/src/test/regress/sql/vacuum_index_statistics.sql
+++ b/src/test/regress/sql/vacuum_index_statistics.sql
@@ -14,8 +14,7 @@ SHOW track_counts; -- must be on
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
@@ -33,7 +32,7 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
+SET track_vacuum_statistics TO 'on';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
@@ -149,3 +148,4 @@ FROM pg_stat_vacuum_indexes vt, pg_class c
WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
DROP TABLE vestat;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/sql/vacuum_tables_statistics.sql b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
similarity index 81%
rename from src/test/regress/sql/vacuum_tables_statistics.sql
rename to src/test/regress/sql/vacuum_tables_and_db_statistics.sql
index 5bc34bec64b..ca7dbde9387 100644
--- a/src/test/regress/sql/vacuum_tables_statistics.sql
+++ b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
@@ -7,15 +7,13 @@
-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
--
--- conditio sine qua non
SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
@@ -36,7 +34,13 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+SET track_vacuum_statistics TO on;
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
@@ -180,4 +184,59 @@ VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages
FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
-DROP TABLE vestat CASCADE;
\ No newline at end of file
+DROP TABLE vestat CASCADE;
+
+-- Now check vacuum statistics for current database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+UPDATE vestat SET x = 10001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+
+-- Now check vacuum statistics for postgres database from another database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+
+\c regression_statistic_vacuum_db
+SET track_vacuum_statistics TO on;
+
+DROP TABLE vestat CASCADE;
+
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
+RESET track_vacuum_statistics;
\ No newline at end of file
--
2.34.1
[text/x-patch] 0004-Vacuum-statistics-have-been-separated-from-regular-r.patch (63.6K, 5-0004-Vacuum-statistics-have-been-separated-from-regular-r.patch)
download | inline diff:
From e2da3e08e08f3c1edc12160a237f15516e506270 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Mon, 2 Jun 2025 22:24:38 +0300
Subject: [PATCH 4/5] Vacuum statistics have been separated from regular
relation and database statistics to reduce memory usage. Dedicated
PGSTAT_KIND_VACUUM_RELATION and PGSTAT_KIND_VACUUM_DB entries were added to
the stats collector to efficiently allocate memory for vacuum-specific
metrics, which require significantly more space per relation.
---
src/backend/access/heap/vacuumlazy.c | 168 +++++------
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 1 +
src/backend/commands/dbcommands.c | 1 +
src/backend/commands/vacuumparallel.c | 14 +-
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/pgstat.c | 28 ++
src/backend/utils/activity/pgstat_database.c | 10 +-
src/backend/utils/activity/pgstat_relation.c | 111 +-------
src/backend/utils/activity/pgstat_vacuum.c | 224 +++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 285 +++++--------------
src/include/commands/vacuum.h | 2 +-
src/include/pgstat.h | 200 +++++++------
src/include/utils/pgstat_internal.h | 15 +
src/include/utils/pgstat_kind.h | 4 +-
15 files changed, 569 insertions(+), 496 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_vacuum.c
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 3d72f74b05e..24b6b64c3fc 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -413,7 +413,7 @@ typedef struct LVRelState
int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
- ExtVacReport extVacReportIdx;
+ PgStat_VacuumRelationCounts extVacReportIdx;
} LVRelState;
@@ -525,7 +525,7 @@ extvac_stats_start(Relation rel, LVExtStatCounters *counters)
*/
static void
extvac_stats_end(Relation rel, LVExtStatCounters *counters,
- ExtVacReport *report)
+ PgStat_VacuumRelationCounts *report)
{
WalUsage walusage;
BufferUsage bufusage;
@@ -549,22 +549,22 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
/*
* Fill additional statistics on a vacuum processing operation.
*/
- report->total_blks_read += bufusage.local_blks_read + bufusage.shared_blks_read;
- report->total_blks_hit += bufusage.local_blks_hit + bufusage.shared_blks_hit;
- report->total_blks_dirtied += bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
- report->total_blks_written += bufusage.shared_blks_written;
+ report->common.total_blks_read += bufusage.local_blks_read + bufusage.shared_blks_read;
+ report->common.total_blks_hit += bufusage.local_blks_hit + bufusage.shared_blks_hit;
+ report->common.total_blks_dirtied += bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
+ report->common.total_blks_written += bufusage.shared_blks_written;
- report->wal_records += walusage.wal_records;
- report->wal_fpi += walusage.wal_fpi;
- report->wal_bytes += walusage.wal_bytes;
+ report->common.wal_records += walusage.wal_records;
+ report->common.wal_fpi += walusage.wal_fpi;
+ report->common.wal_bytes += walusage.wal_bytes;
- report->blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
- report->blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
- report->blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
- report->blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
- report->delay_time += VacuumDelayTime - counters->VacuumDelayTime;
+ report->common.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
+ report->common.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
+ report->common.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
+ report->common.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
+ report->common.delay_time += VacuumDelayTime - counters->VacuumDelayTime;
- report->total_time += secs * 1000. + usecs / 1000.;
+ report->common.total_time += secs * 1000. + usecs / 1000.;
if (!rel->pgstat_info || !pgstat_track_counts)
/*
@@ -573,9 +573,9 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
*/
return;
- report->blks_fetched +=
+ report->common.blks_fetched +=
rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
- report->blks_hit +=
+ report->common.blks_hit +=
rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
}
@@ -603,9 +603,9 @@ extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
void
extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
- LVExtStatCountersIdx *counters, ExtVacReport *report)
+ LVExtStatCountersIdx *counters, PgStat_VacuumRelationCounts *report)
{
- memset(report, 0, sizeof(ExtVacReport));
+ memset(report, 0, sizeof(PgStat_VacuumRelationCounts));
extvac_stats_end(rel, &counters->common, report);
report->type = PGSTAT_EXTVAC_INDEX;
@@ -618,7 +618,7 @@ extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
*/
/* Fill index-specific extended stats fields */
- report->tuples_deleted =
+ report->common.tuples_deleted =
stats->tuples_removed - counters->tuples_removed;
report->index.pages_deleted =
stats->pages_deleted - counters->pages_deleted;
@@ -641,7 +641,7 @@ extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
* procudure.
*/
static void
-accumulate_heap_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacStats)
+accumulate_heap_vacuum_statistics(LVRelState *vacrel, PgStat_VacuumRelationCounts *extVacStats)
{
if (!pgstat_track_vacuum_statistics)
return;
@@ -653,49 +653,49 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacStats)
extVacStats->table.vm_new_frozen_pages = vacrel->vm_new_frozen_pages;
extVacStats->table.vm_new_visible_pages = vacrel->vm_new_visible_pages;
extVacStats->table.vm_new_visible_frozen_pages = vacrel->vm_new_visible_frozen_pages;
- extVacStats->tuples_deleted = vacrel->tuples_deleted;
+ extVacStats->common.tuples_deleted = vacrel->tuples_deleted;
extVacStats->table.tuples_frozen = vacrel->tuples_frozen;
extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
extVacStats->table.index_vacuum_count = vacrel->num_index_scans;
- extVacStats->wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
+ extVacStats->common.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
- extVacStats->blk_read_time -= vacrel->extVacReportIdx.blk_read_time;
- extVacStats->blk_write_time -= vacrel->extVacReportIdx.blk_write_time;
- extVacStats->total_blks_dirtied -= vacrel->extVacReportIdx.total_blks_dirtied;
- extVacStats->total_blks_hit -= vacrel->extVacReportIdx.total_blks_hit;
- extVacStats->total_blks_read -= vacrel->extVacReportIdx.total_blks_read;
- extVacStats->total_blks_written -= vacrel->extVacReportIdx.total_blks_written;
- extVacStats->wal_bytes -= vacrel->extVacReportIdx.wal_bytes;
- extVacStats->wal_fpi -= vacrel->extVacReportIdx.wal_fpi;
- extVacStats->wal_records -= vacrel->extVacReportIdx.wal_records;
+ extVacStats->common.blk_read_time -= vacrel->extVacReportIdx.common.blk_read_time;
+ extVacStats->common.blk_write_time -= vacrel->extVacReportIdx.common.blk_write_time;
+ extVacStats->common.total_blks_dirtied -= vacrel->extVacReportIdx.common.total_blks_dirtied;
+ extVacStats->common.total_blks_hit -= vacrel->extVacReportIdx.common.total_blks_hit;
+ extVacStats->common.total_blks_read -= vacrel->extVacReportIdx.common.total_blks_read;
+ extVacStats->common.total_blks_written -= vacrel->extVacReportIdx.common.total_blks_written;
+ extVacStats->common.wal_bytes -= vacrel->extVacReportIdx.common.wal_bytes;
+ extVacStats->common.wal_fpi -= vacrel->extVacReportIdx.common.wal_fpi;
+ extVacStats->common.wal_records -= vacrel->extVacReportIdx.common.wal_records;
- extVacStats->total_time -= vacrel->extVacReportIdx.total_time;
- extVacStats->delay_time -= vacrel->extVacReportIdx.delay_time;
+ extVacStats->common.total_time -= vacrel->extVacReportIdx.common.total_time;
+ extVacStats->common.delay_time -= vacrel->extVacReportIdx.common.delay_time;
}
static void
-accumulate_idxs_vacuum_statistics(LVRelState *vacrel, ExtVacReport *extVacIdxStats)
+accumulate_idxs_vacuum_statistics(LVRelState *vacrel, PgStat_VacuumRelationCounts *extVacIdxStats)
{
if (!pgstat_track_vacuum_statistics)
return;
/* Fill heap-specific extended stats fields */
- vacrel->extVacReportIdx.blk_read_time += extVacIdxStats->blk_read_time;
- vacrel->extVacReportIdx.blk_write_time += extVacIdxStats->blk_write_time;
- vacrel->extVacReportIdx.total_blks_dirtied += extVacIdxStats->total_blks_dirtied;
- vacrel->extVacReportIdx.total_blks_hit += extVacIdxStats->total_blks_hit;
- vacrel->extVacReportIdx.total_blks_read += extVacIdxStats->total_blks_read;
- vacrel->extVacReportIdx.total_blks_written += extVacIdxStats->total_blks_written;
- vacrel->extVacReportIdx.wal_bytes += extVacIdxStats->wal_bytes;
- vacrel->extVacReportIdx.wal_fpi += extVacIdxStats->wal_fpi;
- vacrel->extVacReportIdx.wal_records += extVacIdxStats->wal_records;
- vacrel->extVacReportIdx.delay_time += extVacIdxStats->delay_time;
-
- vacrel->extVacReportIdx.total_time += extVacIdxStats->total_time;
+ vacrel->extVacReportIdx.common.blk_read_time += extVacIdxStats->common.blk_read_time;
+ vacrel->extVacReportIdx.common.blk_write_time += extVacIdxStats->common.blk_write_time;
+ vacrel->extVacReportIdx.common.total_blks_dirtied += extVacIdxStats->common.total_blks_dirtied;
+ vacrel->extVacReportIdx.common.total_blks_hit += extVacIdxStats->common.total_blks_hit;
+ vacrel->extVacReportIdx.common.total_blks_read += extVacIdxStats->common.total_blks_read;
+ vacrel->extVacReportIdx.common.total_blks_written += extVacIdxStats->common.total_blks_written;
+ vacrel->extVacReportIdx.common.wal_bytes += extVacIdxStats->common.wal_bytes;
+ vacrel->extVacReportIdx.common.wal_fpi += extVacIdxStats->common.wal_fpi;
+ vacrel->extVacReportIdx.common.wal_records += extVacIdxStats->common.wal_records;
+ vacrel->extVacReportIdx.common.delay_time += extVacIdxStats->common.delay_time;
+
+ vacrel->extVacReportIdx.common.total_time += extVacIdxStats->common.total_time;
}
@@ -855,11 +855,11 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
LVExtStatCounters extVacCounters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
char **indnames = NULL;
/* Initialize vacuum statistics */
- memset(&extVacReport, 0, sizeof(ExtVacReport));
+ memset(&extVacReport, 0, sizeof(PgStat_VacuumRelationCounts));
verbose = (params->options & VACOPT_VERBOSE) != 0;
instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
@@ -905,7 +905,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
- memset(&vacrel->extVacReportIdx, 0, sizeof(ExtVacReport));
+ memset(&vacrel->extVacReportIdx, 0, sizeof(PgStat_VacuumRelationCounts));
/* Set up high level stuff about rel and its indexes */
vacrel->rel = rel;
@@ -1176,25 +1176,16 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
extvac_stats_end(vacrel->rel, &extVacCounters, &extVacReport);
accumulate_heap_vacuum_statistics(vacrel, &extVacReport);
- pgstat_report_vacuum(RelationGetRelid(rel),
- rel->rd_rel->relisshared,
- Max(vacrel->new_live_tuples, 0),
- vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples,
- starttime,
- &extVacReport);
+ pgstat_report_tab_vacuum_extstats(vacrel->reloid, rel->rd_rel->relisshared, &extVacReport);
}
- else
- {
- pgstat_report_vacuum(RelationGetRelid(rel),
+
+ pgstat_report_vacuum(RelationGetRelid(rel),
rel->rd_rel->relisshared,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
vacrel->missed_dead_tuples,
- starttime,
- NULL);
- }
+ starttime);
pgstat_progress_end_command();
@@ -2893,9 +2884,9 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
else
{
LVExtStatCounters counters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
- memset(&extVacReport, 0, sizeof(ExtVacReport));
+ memset(&extVacReport, 0, sizeof(PgStat_VacuumRelationCounts));
extvac_stats_start(vacrel->rel, &counters);
@@ -3327,9 +3318,9 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
else
{
LVExtStatCounters counters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
- memset(&extVacReport, 0, sizeof(ExtVacReport));
+ memset(&extVacReport, 0, sizeof(PgStat_VacuumRelationCounts));
extvac_stats_start(vacrel->rel, &counters);
@@ -3366,7 +3357,7 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
LVExtStatCountersIdx extVacCounters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
/* Set initial statistics values to gather vacuum statistics for the index */
extvac_stats_start_idx(indrel, istat, &extVacCounters);
@@ -3405,9 +3396,7 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
if (!ParallelVacuumIsActive(vacrel))
accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
- pgstat_report_vacuum(RelationGetRelid(indrel),
- indrel->rd_rel->relisshared,
- 0, 0, 0, &extVacReport);
+ pgstat_report_tab_vacuum_extstats(vacrel->indoid, indrel->rd_rel->relisshared, &extVacReport);
}
/* Revert to the previous phase information for error traceback */
@@ -3435,7 +3424,7 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
LVExtStatCountersIdx extVacCounters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
/* Set initial statistics values to gather vacuum statistics for the index */
extvac_stats_start_idx(indrel, istat, &extVacCounters);
@@ -3472,9 +3461,7 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
if (!ParallelVacuumIsActive(vacrel))
accumulate_idxs_vacuum_statistics(vacrel, &extVacReport);
- pgstat_report_vacuum(RelationGetRelid(indrel),
- indrel->rd_rel->relisshared,
- 0, 0, 0, &extVacReport);
+ pgstat_report_tab_vacuum_extstats(vacrel->indoid, indrel->rd_rel->relisshared, &extVacReport);
}
/* Revert to the previous phase information for error traceback */
@@ -4075,6 +4062,27 @@ update_relstats_all_indexes(LVRelState *vacrel)
}
}
+/* ---------
+ * pgstat_report_vacuum_error() -
+ *
+ * Tell the collector about an (auto)vacuum interruption.
+ * ---------
+ */
+static void
+pgstat_report_vacuum_error()
+{
+ PgStat_VacuumDBCounts *vacuum_dbentry;
+
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ vacuum_dbentry = pgstat_fetch_stat_vacuum_dbentry(MyDatabaseId);
+
+ if(vacuum_dbentry == NULL)
+ return;
+ vacuum_dbentry->errors++;
+}
+
/*
* Error context callback for errors occurring during vacuum. The error
* context messages for index phases should match the messages set in parallel
@@ -4090,7 +4098,7 @@ vacuum_error_callback(void *arg)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
if(geterrelevel() == ERROR)
- pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+ pgstat_report_vacuum_error();
if (BlockNumberIsValid(errinfo->blkno))
{
@@ -4108,7 +4116,7 @@ vacuum_error_callback(void *arg)
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
if(geterrelevel() == ERROR)
- pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+ pgstat_report_vacuum_error();
if (BlockNumberIsValid(errinfo->blkno))
{
@@ -4126,7 +4134,7 @@ vacuum_error_callback(void *arg)
case VACUUM_ERRCB_PHASE_VACUUM_INDEX:
if(geterrelevel() == ERROR)
- pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX);
+ pgstat_report_vacuum_error();
errcontext("while vacuuming index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
@@ -4134,7 +4142,7 @@ vacuum_error_callback(void *arg)
case VACUUM_ERRCB_PHASE_INDEX_CLEANUP:
if(geterrelevel() == ERROR)
- pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX);
+ pgstat_report_vacuum_error();
errcontext("while cleaning up index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
@@ -4142,7 +4150,7 @@ vacuum_error_callback(void *arg)
case VACUUM_ERRCB_PHASE_TRUNCATE:
if(geterrelevel() == ERROR)
- pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+ pgstat_report_vacuum_error();
if (BlockNumberIsValid(errinfo->blkno))
errcontext("while truncating relation \"%s.%s\" to %u blocks",
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index fbaed5359ad..72c8e339c45 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1873,6 +1873,7 @@ heap_drop_with_catalog(Oid relid)
/* ensure that stats are dropped if transaction commits */
pgstat_drop_relation(rel);
+ pgstat_vacuum_relation_delete_pending_cb(RelationGetRelid(rel));
/*
* Close relcache entry, but *keep* AccessExclusiveLock on the relation
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 739a92bdcc1..e4fa754aab4 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2327,6 +2327,7 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
/* ensure that stats are dropped if transaction commits */
pgstat_drop_relation(userIndexRelation);
+ pgstat_vacuum_relation_delete_pending_cb(RelationGetRelid(userIndexRelation));
/*
* Close and flush the index's relcache entry, to ensure relcache doesn't
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 5fbbcdaabb1..c4b910cd928 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -1789,6 +1789,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
* Tell the cumulative stats system to forget it immediately, too.
*/
pgstat_drop_database(db_id);
+ pgstat_drop_vacuum_database(db_id);
/*
* Except for the deletion of the catalog row, subsequent actions are not
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 65de45a4447..b67326eafcc 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -869,7 +869,7 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
IndexBulkDeleteResult *istat_res;
IndexVacuumInfo ivinfo;
LVExtStatCountersIdx extVacCounters;
- ExtVacReport extVacReport;
+ PgStat_VacuumRelationCounts extVacReport;
/*
* Update the pointer to the corresponding bulk-deletion result if someone
@@ -909,14 +909,10 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
RelationGetRelationName(indrel));
}
- if(pgstat_track_vacuum_statistics)
- {
- /* Make extended vacuum stats report for index */
- extvac_stats_end_idx(indrel, istat_res, &extVacCounters, &extVacReport);
- pgstat_report_vacuum(RelationGetRelid(indrel),
- indrel->rd_rel->relisshared,
- 0, 0, 0, &extVacReport);
- }
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat_res, &extVacCounters, &extVacReport);
+ pgstat_report_tab_vacuum_extstats(RelationGetRelid(indrel), indrel->rd_rel->relisshared,
+ &extVacReport);
/*
* Copy the index bulk-deletion result returned from ambulkdelete and
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index 9c2443e1ecd..183f7514d2d 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -27,6 +27,7 @@ OBJS = \
pgstat_function.o \
pgstat_io.o \
pgstat_relation.o \
+ pgstat_vacuum.o \
pgstat_replslot.o \
pgstat_shmem.o \
pgstat_slru.o \
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 85557736a3a..ca764a3a214 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -478,6 +478,34 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.reset_all_cb = pgstat_wal_reset_all_cb,
.snapshot_cb = pgstat_wal_snapshot_cb,
},
+ [PGSTAT_KIND_VACUUM_DB] = {
+ .name = "vacuum statistics",
+
+ .fixed_amount = false,
+ .write_to_file = true,
+ /* so pg_stat_database entries can be seen in all databases */
+ .accessed_across_databases = true,
+
+ .shared_size = sizeof(PgStatShared_VacuumDB),
+ .shared_data_off = offsetof(PgStatShared_VacuumDB, stats),
+ .shared_data_len = sizeof(((PgStatShared_VacuumDB *) 0)->stats),
+ .pending_size = sizeof(PgStat_VacuumDBCounts),
+
+ .flush_pending_cb = pgstat_vacuum_db_flush_cb,
+ },
+ [PGSTAT_KIND_VACUUM_RELATION] = {
+ .name = "vacuum statistics",
+
+ .fixed_amount = false,
+ .write_to_file = true,
+
+ .shared_size = sizeof(PgStatShared_VacuumRelation),
+ .shared_data_off = offsetof(PgStatShared_VacuumRelation, stats),
+ .shared_data_len = sizeof(((PgStatShared_VacuumRelation *) 0)->stats),
+ .pending_size = sizeof(PgStat_RelationVacuumPending),
+
+ .flush_pending_cb = pgstat_vacuum_relation_flush_cb
+ },
};
/*
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 65207d30378..80e6c7c229a 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -46,6 +46,15 @@ pgstat_drop_database(Oid databaseid)
pgstat_drop_transactional(PGSTAT_KIND_DATABASE, databaseid, InvalidOid);
}
+/*
+ * Remove entry for the database being dropped.
+ */
+void
+pgstat_drop_vacuum_database(Oid databaseid)
+{
+ pgstat_drop_transactional(PGSTAT_KIND_VACUUM_DB, databaseid, InvalidOid);
+}
+
/*
* Called from autovacuum.c to report startup of an autovacuum process.
* We are called before InitPostgres is done, so can't rely on MyDatabaseId;
@@ -485,7 +494,6 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
pgstat_unlock_entry(entry_ref);
memset(pendingent, 0, sizeof(*pendingent));
- memset(&(pendingent)->vacuum_ext, 0, sizeof(ExtVacReport));
return true;
}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 1695680ea62..acc8f0b8a52 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -47,8 +47,6 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev
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_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
- bool accumulate_reltype_specific_info);
/*
@@ -205,50 +203,17 @@ pgstat_drop_relation(Relation rel)
}
}
-/* ---------
- * pgstat_report_vacuum_error() -
- *
- * Tell the collector about an (auto)vacuum interruption.
- * ---------
- */
-void
-pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
-{
- PgStat_EntryRef *entry_ref;
- PgStatShared_Relation *shtabentry;
- PgStat_StatTabEntry *tabentry;
- Oid dboid = MyDatabaseId;
- PgStat_StatDBEntry *dbentry; /* pending database entry */
-
- if (!pgstat_track_counts)
- return;
-
- entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
- dboid, tableoid, false);
-
- shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
- tabentry = &shtabentry->stats;
-
- tabentry->vacuum_ext.type = m_type;
- pgstat_unlock_entry(entry_ref);
-
- dbentry = pgstat_prep_database_pending(dboid);
- dbentry->vacuum_ext.errors++;
- dbentry->vacuum_ext.type = m_type;
-}
-
/*
* Report that the table was just vacuumed and flush IO statistics.
*/
void
pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime, ExtVacReport *params)
+ TimestampTz starttime)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
PgStat_StatTabEntry *tabentry;
- PgStatShared_Database *dbentry;
Oid dboid = (shared ? InvalidOid : MyDatabaseId);
TimestampTz ts;
PgStat_Counter elapsedtime;
@@ -270,8 +235,6 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
- pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params, true);
-
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
* various pages, however, we'll zero the insert counter here regardless.
@@ -307,16 +270,6 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
*/
pgstat_flush_io(false);
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
-
- if (dboid != InvalidOid)
- {
- entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE,
- dboid, InvalidOid, false);
- dbentry = (PgStatShared_Database *) entry_ref->shared_stats;
-
- pgstat_accumulate_extvac_stats(&dbentry->stats.vacuum_ext, params, false);
- pgstat_unlock_entry(entry_ref);
- }
}
/*
@@ -951,6 +904,12 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
return true;
}
+void
+pgstat_vacuum_relation_delete_pending_cb(Oid relid)
+{
+ pgstat_drop_transactional(PGSTAT_KIND_VACUUM_RELATION, relid, InvalidOid);
+}
+
void
pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref)
{
@@ -1053,60 +1012,4 @@ restore_truncdrop_counters(PgStat_TableXactStatus *trans)
trans->tuples_updated = trans->updated_pre_truncdrop;
trans->tuples_deleted = trans->deleted_pre_truncdrop;
}
-}
-
-static void
-pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
- bool accumulate_reltype_specific_info)
-{
- if(!pgstat_track_vacuum_statistics)
- return;
-
- dst->total_blks_read += src->total_blks_read;
- dst->total_blks_hit += src->total_blks_hit;
- dst->total_blks_dirtied += src->total_blks_dirtied;
- dst->total_blks_written += src->total_blks_written;
- dst->wal_bytes += src->wal_bytes;
- dst->wal_fpi += src->wal_fpi;
- dst->wal_records += src->wal_records;
- dst->blk_read_time += src->blk_read_time;
- dst->blk_write_time += src->blk_write_time;
- dst->delay_time += src->delay_time;
- dst->total_time += src->total_time;
- dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
- dst->errors += src->errors;
-
- if (!accumulate_reltype_specific_info)
- return;
-
- if (dst->type == PGSTAT_EXTVAC_INVALID)
- dst->type = src->type;
-
- Assert(src->type == PGSTAT_EXTVAC_INVALID || src->type == dst->type);
-
- if (dst->type == src->type)
- {
- dst->blks_fetched += src->blks_fetched;
- dst->blks_hit += src->blks_hit;
-
- if (dst->type == PGSTAT_EXTVAC_TABLE)
- {
- dst->table.pages_scanned += src->table.pages_scanned;
- dst->table.pages_removed += src->table.pages_removed;
- dst->table.vm_new_frozen_pages += src->table.vm_new_frozen_pages;
- dst->table.vm_new_visible_pages += src->table.vm_new_visible_pages;
- dst->table.vm_new_visible_frozen_pages += src->table.vm_new_visible_frozen_pages;
- dst->tuples_deleted += src->tuples_deleted;
- dst->table.tuples_frozen += src->table.tuples_frozen;
- dst->table.recently_dead_tuples += src->table.recently_dead_tuples;
- dst->table.index_vacuum_count += src->table.index_vacuum_count;
- dst->table.missed_dead_pages += src->table.missed_dead_pages;
- dst->table.missed_dead_tuples += src->table.missed_dead_tuples;
- }
- else if (dst->type == PGSTAT_EXTVAC_INDEX)
- {
- dst->index.pages_deleted += src->index.pages_deleted;
- dst->tuples_deleted += src->tuples_deleted;
- }
- }
}
\ No newline at end of file
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
new file mode 100644
index 00000000000..7e1db137a18
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -0,0 +1,224 @@
+#include "postgres.h"
+
+#include "pgstat.h"
+#include "utils/pgstat_internal.h"
+#include "utils/memutils.h"
+
+/* ----------
+ * GUC parameters
+ * ----------
+ */
+bool pgstat_track_vacuum_statistics_for_relations = false;
+
+#define ACCUMULATE_FIELD(field) dst->field += src->field;
+
+#define ACCUMULATE_SUBFIELD(substruct, field) \
+ (dst->substruct.field += src->substruct.field)
+
+static void
+pgstat_accumulate_common(PgStat_CommonCounts *dst, const PgStat_CommonCounts *src)
+{
+ dst->total_blks_read += src->total_blks_read;
+ dst->total_blks_hit += src->total_blks_hit;
+ dst->total_blks_dirtied += src->total_blks_dirtied;
+ dst->total_blks_written += src->total_blks_written;
+
+ dst->blks_fetched += src->blks_fetched;
+ dst->blks_hit += src->blks_hit;
+
+ dst->wal_records += src->wal_records;
+ dst->wal_fpi += src->wal_fpi;
+ dst->wal_bytes += src->wal_bytes;
+
+ dst->blk_read_time += src->blk_read_time;
+ dst->blk_write_time += src->blk_write_time;
+ dst->delay_time += src->delay_time;
+ dst->total_time += src->total_time;
+
+ dst->tuples_deleted += src->tuples_deleted;
+ dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
+}
+
+static void
+pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst, PgStat_VacuumRelationCounts *src)
+{
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ if (dst->type == PGSTAT_EXTVAC_INVALID)
+ dst->type = src->type;
+
+ Assert(src->type != PGSTAT_EXTVAC_INVALID && src->type != PGSTAT_EXTVAC_DB && src->type == dst->type);
+
+ pgstat_accumulate_common(&dst->common, &src->common);
+
+ dst->common.blks_fetched += src->common.blks_fetched;
+ dst->common.blks_hit += src->common.blks_hit;
+
+ if (dst->type == PGSTAT_EXTVAC_TABLE)
+ {
+ ACCUMULATE_SUBFIELD(table, pages_scanned);
+ ACCUMULATE_SUBFIELD(table, pages_removed);
+ ACCUMULATE_SUBFIELD(table, vm_new_frozen_pages);
+ ACCUMULATE_SUBFIELD(table, vm_new_visible_pages);
+ ACCUMULATE_SUBFIELD(table, vm_new_visible_frozen_pages);
+ dst->common.tuples_deleted += src->common.tuples_deleted;
+ ACCUMULATE_SUBFIELD(table, tuples_frozen);
+ ACCUMULATE_SUBFIELD(table, recently_dead_tuples);
+ ACCUMULATE_SUBFIELD(table, index_vacuum_count);
+ ACCUMULATE_SUBFIELD(table, missed_dead_pages);
+ ACCUMULATE_SUBFIELD(table, missed_dead_tuples);
+ }
+ else if (dst->type == PGSTAT_EXTVAC_INDEX)
+ {
+ ACCUMULATE_SUBFIELD(index, pages_deleted);
+ dst->common.tuples_deleted += src->common.tuples_deleted;
+ }
+}
+
+static void
+pgstat_accumulate_extvac_stats_db(PgStat_VacuumDBCounts *dst, PgStat_VacuumDBCounts *src)
+{
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ pgstat_accumulate_common((PgStat_CommonCounts *) dst, (PgStat_CommonCounts *) src);
+ dst->errors += src->errors;
+}
+
+static void
+pgstat_increment_tab_extvac_stats_to_db(PgStat_VacuumDBCounts *dst, PgStat_VacuumRelationCounts *src)
+{
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ pgstat_accumulate_common((PgStat_CommonCounts *) dst, (PgStat_CommonCounts *) src);
+}
+
+/*
+ * Report that the table was just vacuumed and flush statistics.
+ */
+void
+pgstat_report_tab_vacuum_extstats(Oid tableoid, bool shared,
+ PgStat_VacuumRelationCounts *params)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_VacuumRelation *shtabentry;
+ PgStatShared_VacuumDB *shdbentry;
+ Oid dboid = (shared ? InvalidOid : MyDatabaseId);
+
+ if(!pgstat_track_vacuum_statistics)
+ return;
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_VACUUM_RELATION,
+ dboid, tableoid, false);
+ shtabentry = (PgStatShared_VacuumRelation *) entry_ref->shared_stats;
+ pgstat_accumulate_extvac_stats_relations(&shtabentry->stats, params);
+
+ pgstat_unlock_entry(entry_ref);
+
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_VACUUM_DB,
+ dboid, InvalidOid, false);
+
+ shdbentry = (PgStatShared_VacuumDB *) entry_ref->shared_stats;
+
+ pgstat_increment_tab_extvac_stats_to_db(&shdbentry->stats, params);
+
+ pgstat_unlock_entry(entry_ref);
+}
+
+/*
+ * Flush out pending stats for the entry
+ *
+ * If nowait is true, this function returns false if lock could not
+ * immediately acquired, otherwise true is returned.
+ */
+bool
+pgstat_vacuum_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_VacuumRelation *shtabstats;
+ PgStat_RelationVacuumPending *pendingent; /* table entry of shared stats */
+
+ pendingent = (PgStat_RelationVacuumPending *) entry_ref->pending;
+ shtabstats = (PgStatShared_VacuumRelation *) entry_ref->shared_stats;
+
+ /*
+ * Ignore entries that didn't accumulate any actual counts.
+ */
+ if (pg_memory_is_all_zeros(&pendingent,
+ sizeof(struct PgStat_RelationVacuumPending)))
+ return true;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ {
+ return false;
+ }
+
+ pgstat_accumulate_extvac_stats_relations(&(shtabstats->stats), &(pendingent->counts));
+
+ pgstat_unlock_entry(entry_ref);
+
+ return true;
+}
+
+/*
+ * Support function for the SQL-callable pgstat* functions. Returns
+ * the vacuum collected statistics for one relation or NULL.
+ */
+PgStat_VacuumRelationCounts *
+pgstat_fetch_stat_vacuum_tabentry(Oid relid, Oid dbid)
+{
+ return (PgStat_VacuumRelationCounts *)
+ pgstat_fetch_entry(PGSTAT_KIND_VACUUM_RELATION, dbid, relid);
+}
+
+PgStat_VacuumDBCounts *
+pgstat_fetch_stat_vacuum_dbentry(Oid dbid)
+{
+ return (PgStat_VacuumDBCounts *)
+ pgstat_fetch_entry(PGSTAT_KIND_VACUUM_DB, dbid, InvalidOid);
+}
+
+bool
+pgstat_vacuum_db_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_VacuumDB *sharedent;
+ PgStat_VacuumDBCounts *pendingent;
+
+ pendingent = (PgStat_VacuumDBCounts *) entry_ref->pending;
+ sharedent = (PgStatShared_VacuumDB *) entry_ref->shared_stats;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+ /* The entry was successfully flushed, add the same to database stats */
+ pgstat_accumulate_extvac_stats_db(&(sharedent->stats), pendingent);
+
+ pgstat_unlock_entry(entry_ref);
+
+ return true;
+}
+
+/*
+ * Find or create a local PgStat_VacuumDBCounts entry for dboid.
+ */
+PgStat_VacuumDBCounts *
+pgstat_prep_vacuum_database_pending(Oid dboid)
+{
+ PgStat_EntryRef *entry_ref;
+
+ /*
+ * This should not report stats on database objects before having
+ * connected to a database.
+ */
+ Assert(!OidIsValid(dboid) || OidIsValid(MyDatabaseId));
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_VACUUM_DB, dboid, InvalidOid,
+ NULL);
+
+ if(entry_ref == NULL)
+ return NULL;
+
+ return entry_ref->pending;
+}
\ No newline at end of file
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index a2ece2c36cf..8603d4dd576 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2265,7 +2265,6 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(pgstat_have_entry(kind, dboid, objid));
}
-
/*
* Get the vacuum statistics for the heap tables.
*/
@@ -2275,102 +2274,42 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 26
Oid relid = PG_GETARG_OID(0);
- PgStat_StatTabEntry *tabentry;
- ExtVacReport *extvacuum;
+ PgStat_VacuumRelationCounts *extvacuum;
+ PgStat_VacuumRelationCounts *pending;
TupleDesc tupdesc;
Datum values[PG_STAT_GET_VACUUM_TABLES_STATS_COLS] = {0};
bool nulls[PG_STAT_GET_VACUUM_TABLES_STATS_COLS] = {0};
char buf[256];
int i = 0;
- ExtVacReport allzero;
+ PgStat_VacuumRelationCounts allzero;
- /* Initialise attributes information in the tuple descriptor */
- tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "relid",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_read",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_read",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_hit",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_scanned",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_removed",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_frozen_pages",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_visible_pages",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "vm_new_visible_frozen_pages",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "missed_dead_pages",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_deleted",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_frozen",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "recently_dead_tuples",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "missed_dead_tuples",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wraparound_failsafe_count",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "index_vacuum_count",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
- NUMERICOID, -1, 0);
-
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
- FLOAT8OID, -1, 0);
-
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
- FLOAT8OID, -1, 0);
-
- Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
-
- BlessTupleDesc(tupdesc);
+ pending = pgstat_fetch_stat_vacuum_tabentry(relid, MyDatabaseId);
- tabentry = pgstat_fetch_stat_tabentry(relid);
-
- if (tabentry == NULL)
+ if (pending == NULL)
{
/* If the subscription is not found, initialise its stats */
- memset(&allzero, 0, sizeof(ExtVacReport));
+ memset(&allzero, 0, sizeof(PgStat_VacuumRelationCounts));
extvacuum = &allzero;
}
else
- {
- extvacuum = &(tabentry->vacuum_ext);
- }
+ extvacuum = pending;
i = 0;
values[i++] = ObjectIdGetDatum(relid);
- values[i++] = Int64GetDatum(extvacuum->total_blks_read);
- values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
- values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
- values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
- values[i++] = Int64GetDatum(extvacuum->blks_fetched -
- extvacuum->blks_hit);
- values[i++] = Int64GetDatum(extvacuum->blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.blks_fetched -
+ extvacuum->common.blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
values[i++] = Int64GetDatum(extvacuum->table.pages_scanned);
values[i++] = Int64GetDatum(extvacuum->table.pages_removed);
@@ -2378,28 +2317,28 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_pages);
values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_frozen_pages);
values[i++] = Int64GetDatum(extvacuum->table.missed_dead_pages);
- values[i++] = Int64GetDatum(extvacuum->tuples_deleted);
+ values[i++] = Int64GetDatum(extvacuum->common.tuples_deleted);
values[i++] = Int64GetDatum(extvacuum->table.tuples_frozen);
values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
- values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
+ values[i++] = Int32GetDatum(extvacuum->common.wraparound_failsafe_count);
values[i++] = Int64GetDatum(extvacuum->table.index_vacuum_count);
- values[i++] = Int64GetDatum(extvacuum->wal_records);
- values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_records);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
/* Convert to numeric, like pg_stat_statements */
- snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->common.wal_bytes);
values[i++] = DirectFunctionCall3(numeric_in,
CStringGetDatum(buf),
ObjectIdGetDatum(0),
Int32GetDatum(-1));
- values[i++] = Float8GetDatum(extvacuum->blk_read_time);
- values[i++] = Float8GetDatum(extvacuum->blk_write_time);
- values[i++] = Float8GetDatum(extvacuum->delay_time);
- values[i++] = Float8GetDatum(extvacuum->total_time);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->common.delay_time);
+ values[i++] = Float8GetDatum(extvacuum->common.total_time);
Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
@@ -2416,100 +2355,60 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
#define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 16
Oid relid = PG_GETARG_OID(0);
- PgStat_StatTabEntry *tabentry;
- ExtVacReport *extvacuum;
+ PgStat_VacuumRelationCounts *extvacuum;
+ PgStat_VacuumRelationCounts *pending;
TupleDesc tupdesc;
Datum values[PG_STAT_GET_VACUUM_INDEX_STATS_COLS] = {0};
bool nulls[PG_STAT_GET_VACUUM_INDEX_STATS_COLS] = {0};
char buf[256];
int i = 0;
- ExtVacReport allzero;
+ PgStat_VacuumRelationCounts allzero;
- /* Initialise attributes information in the tuple descriptor */
- tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "relid",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_read",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "rel_blks_hit",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "pages_deleted",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "tuples_deleted",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
- NUMERICOID, -1, 0);
-
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
- FLOAT8OID, -1, 0);
+ pending = pgstat_fetch_stat_vacuum_tabentry(relid, MyDatabaseId);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
- FLOAT8OID, -1, 0);
-
- Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
-
- BlessTupleDesc(tupdesc);
-
- tabentry = pgstat_fetch_stat_tabentry(relid);
-
- if (tabentry == NULL)
+ if (pending == NULL)
{
/* If the subscription is not found, initialise its stats */
- memset(&allzero, 0, sizeof(ExtVacReport));
+ memset(&allzero, 0, sizeof(PgStat_VacuumRelationCounts));
extvacuum = &allzero;
}
else
- {
- extvacuum = &(tabentry->vacuum_ext);
- }
+ extvacuum = pending;
i = 0;
values[i++] = ObjectIdGetDatum(relid);
- values[i++] = Int64GetDatum(extvacuum->total_blks_read);
- values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
- values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
- values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
- values[i++] = Int64GetDatum(extvacuum->blks_fetched -
- extvacuum->blks_hit);
- values[i++] = Int64GetDatum(extvacuum->blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.blks_fetched -
+ extvacuum->common.blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
values[i++] = Int64GetDatum(extvacuum->index.pages_deleted);
- values[i++] = Int64GetDatum(extvacuum->tuples_deleted);
+ values[i++] = Int64GetDatum(extvacuum->common.tuples_deleted);
- values[i++] = Int64GetDatum(extvacuum->wal_records);
- values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_records);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
/* Convert to numeric, like pg_stat_statements */
- snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->common.wal_bytes);
values[i++] = DirectFunctionCall3(numeric_in,
CStringGetDatum(buf),
ObjectIdGetDatum(0),
Int32GetDatum(-1));
- values[i++] = Float8GetDatum(extvacuum->blk_read_time);
- values[i++] = Float8GetDatum(extvacuum->blk_write_time);
- values[i++] = Float8GetDatum(extvacuum->delay_time);
- values[i++] = Float8GetDatum(extvacuum->total_time);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->common.delay_time);
+ values[i++] = Float8GetDatum(extvacuum->common.total_time);
Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
@@ -2523,90 +2422,52 @@ pg_stat_get_vacuum_database(PG_FUNCTION_ARGS)
#define PG_STAT_GET_VACUUM_DATABASE_STATS_COLS 14
Oid dbid = PG_GETARG_OID(0);
- PgStat_StatDBEntry *dbentry;
- ExtVacReport *extvacuum;
+ PgStat_VacuumDBCounts *extvacuum;
+ PgStat_VacuumDBCounts *pending;
TupleDesc tupdesc;
Datum values[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
bool nulls[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
char buf[256];
int i = 0;
- ExtVacReport allzero;
-
- /* Initialise attributes information in the tuple descriptor */
- tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
-
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "dbid",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
- INT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
- NUMERICOID, -1, 0);
+ PgStat_VacuumDBCounts allzero;
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
- FLOAT8OID, -1, 0);
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
- FLOAT8OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wraparound_failsafe_count",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "errors",
- INT4OID, -1, 0);
+ pending = pgstat_fetch_stat_vacuum_dbentry(dbid);
- Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
-
- BlessTupleDesc(tupdesc);
-
- dbentry = pgstat_fetch_stat_dbentry(dbid);
-
- if (dbentry == NULL)
+ if (pending == NULL)
{
/* If the subscription is not found, initialise its stats */
- memset(&allzero, 0, sizeof(ExtVacReport));
+ memset(&allzero, 0, sizeof(PgStat_VacuumRelationCounts));
extvacuum = &allzero;
}
else
- {
- extvacuum = &(dbentry->vacuum_ext);
- }
-
- i = 0;
+ extvacuum = pending;
values[i++] = ObjectIdGetDatum(dbid);
- values[i++] = Int64GetDatum(extvacuum->total_blks_read);
- values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
- values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
- values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_read);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_hit);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
+ values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
- values[i++] = Int64GetDatum(extvacuum->wal_records);
- values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_records);
+ values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
/* Convert to numeric, like pg_stat_statements */
- snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+ snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->common.wal_bytes);
values[i++] = DirectFunctionCall3(numeric_in,
CStringGetDatum(buf),
ObjectIdGetDatum(0),
Int32GetDatum(-1));
- values[i++] = Float8GetDatum(extvacuum->blk_read_time);
- values[i++] = Float8GetDatum(extvacuum->blk_write_time);
- values[i++] = Float8GetDatum(extvacuum->delay_time);
- values[i++] = Float8GetDatum(extvacuum->total_time);
- values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_read_time);
+ values[i++] = Float8GetDatum(extvacuum->common.blk_write_time);
+ values[i++] = Float8GetDatum(extvacuum->common.delay_time);
+ values[i++] = Float8GetDatum(extvacuum->common.total_time);
+ values[i++] = Int32GetDatum(extvacuum->common.wraparound_failsafe_count);
values[i++] = Int32GetDatum(extvacuum->errors);
Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index fb134f3402e..f895151ca09 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -432,5 +432,5 @@ extern double anl_get_next_S(double t, int n, double *stateptr);
extern void extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
LVExtStatCountersIdx *counters);
extern void extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
- LVExtStatCountersIdx *counters, ExtVacReport *report);
+ LVExtStatCountersIdx *counters, PgStat_VacuumRelationCounts *report);
#endif /* VACUUM_H */
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f8158aa353c..a12590948fb 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -116,46 +116,100 @@ typedef enum ExtVacReportType
{
PGSTAT_EXTVAC_INVALID = 0,
PGSTAT_EXTVAC_TABLE = 1,
- PGSTAT_EXTVAC_INDEX = 2
+ PGSTAT_EXTVAC_INDEX = 2,
+ PGSTAT_EXTVAC_DB = 3,
} ExtVacReportType;
/* ----------
+ * PgStat_TableCounts The actual per-table counts kept by a backend
*
- * ExtVacReport
+ * This struct should contain only actual event counters, because we make use
+ * of pg_memory_is_all_zeros() to detect whether there are any stats updates
+ * to apply.
*
- * Additional statistics of vacuum processing over a relation.
- * pages_removed is the amount by which the physically shrank,
- * if any (ie the change in its total size on disk)
- * pages_deleted refer to free space within the index file
+ * It is a component of PgStat_TableStatus (within-backend state).
+ *
+ * Note: for a table, tuples_returned is the number of tuples successfully
+ * fetched by heap_getnext, while tuples_fetched is the number of tuples
+ * successfully fetched by heap_fetch under the control of bitmap indexscans.
+ * For an index, tuples_returned is the number of index entries returned by
+ * the index AM, while tuples_fetched is the number of tuples successfully
+ * fetched by heap_fetch under the control of simple indexscans for this index.
+ *
+ * tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted
+ * actions, regardless of whether the transaction committed. delta_live_tuples,
+ * delta_dead_tuples, and changed_tuples are set depending on commit or abort.
+ * Note that delta_live_tuples and delta_dead_tuples can be negative!
* ----------
*/
-typedef struct ExtVacReport
+typedef struct PgStat_TableCounts
{
- /* number of blocks missed, hit, dirtied and written during a vacuum of specific relation */
- int64 total_blks_read;
- int64 total_blks_hit;
- int64 total_blks_dirtied;
- int64 total_blks_written;
+ PgStat_Counter numscans;
- /* blocks missed and hit for just the heap during a vacuum of specific relation */
- int64 blks_fetched;
- int64 blks_hit;
+ PgStat_Counter tuples_returned;
+ PgStat_Counter tuples_fetched;
- /* Vacuum WAL usage stats */
- int64 wal_records; /* wal usage: number of WAL records */
- int64 wal_fpi; /* wal usage: number of WAL full page images produced */
- uint64 wal_bytes; /* wal usage: size of WAL records produced */
+ PgStat_Counter tuples_inserted;
+ PgStat_Counter tuples_updated;
+ PgStat_Counter tuples_deleted;
+ PgStat_Counter tuples_hot_updated;
+ PgStat_Counter tuples_newpage_updated;
+ bool truncdropped;
+
+ PgStat_Counter delta_live_tuples;
+ PgStat_Counter delta_dead_tuples;
+ PgStat_Counter changed_tuples;
+
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
- /* Time stats. */
- double blk_read_time; /* time spent reading pages, in msec */
- double blk_write_time; /* time spent writing pages, in msec */
- double delay_time; /* how long vacuum slept in vacuum delay point, in msec */
- double total_time; /* total time of a vacuum operation, in msec */
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
+} PgStat_TableCounts;
- int64 tuples_deleted; /* tuples deleted by vacuum */
+typedef struct PgStat_CommonCounts
+{
+ /* blocks */
+ int64 total_blks_read;
+ int64 total_blks_hit;
+ int64 total_blks_dirtied;
+ int64 total_blks_written;
+
+ /* heap blocks */
+ int64 blks_fetched;
+ int64 blks_hit;
+
+ /* WAL */
+ int64 wal_records;
+ int64 wal_fpi;
+ uint64 wal_bytes;
+
+ /* Time */
+ double blk_read_time;
+ double blk_write_time;
+ double delay_time;
+ double total_time;
+
+ /* tuples */
+ int64 tuples_deleted;
+
+ /* failsafe */
+ int32 wraparound_failsafe_count;
+} PgStat_CommonCounts;
- int32 errors;
- int32 wraparound_failsafe_count; /* the number of times to prevent wraparound problem */
+/* ----------
+ *
+ * PgStat_VacuumRelationCounts
+ *
+ * Additional statistics of vacuum processing over a relation.
+ * pages_removed is the amount by which the physically shrank,
+ * if any (ie the change in its total size on disk)
+ * pages_deleted refer to free space within the index file
+ * ----------
+ */
+typedef struct PgStat_VacuumRelationCounts
+{
+ PgStat_CommonCounts common;
ExtVacReportType type; /* heap, index, etc. */
@@ -174,16 +228,16 @@ typedef struct ExtVacReport
{
struct
{
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
+ int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
int64 pages_scanned; /* heap pages examined (not skipped by VM) */
int64 pages_removed; /* heap pages removed by vacuum "truncation" */
int64 pages_frozen; /* pages marked in VM as frozen */
int64 pages_all_visible; /* pages marked in VM as all-visible */
- int64 tuples_frozen; /* tuples frozen up by vacuum */
- int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
int64 vm_new_frozen_pages; /* pages marked in VM as frozen */
int64 vm_new_visible_pages; /* pages marked in VM as all-visible */
int64 vm_new_visible_frozen_pages; /* pages marked in VM as all-visible and frozen */
- int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
int64 missed_dead_pages; /* pages with missed dead tuples */
int64 index_vacuum_count; /* number of index vacuumings */
} table;
@@ -192,61 +246,21 @@ typedef struct ExtVacReport
int64 pages_deleted; /* number of pages deleted by vacuum */
} index;
} /* per_type_stats */;
-} ExtVacReport;
+} PgStat_VacuumRelationCounts;
-/* ----------
- * PgStat_TableCounts The actual per-table counts kept by a backend
- *
- * This struct should contain only actual event counters, because we make use
- * of pg_memory_is_all_zeros() to detect whether there are any stats updates
- * to apply.
- *
- * It is a component of PgStat_TableStatus (within-backend state).
- *
- * Note: for a table, tuples_returned is the number of tuples successfully
- * fetched by heap_getnext, while tuples_fetched is the number of tuples
- * successfully fetched by heap_fetch under the control of bitmap indexscans.
- * For an index, tuples_returned is the number of index entries returned by
- * the index AM, while tuples_fetched is the number of tuples successfully
- * fetched by heap_fetch under the control of simple indexscans for this index.
- *
- * tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted
- * actions, regardless of whether the transaction committed. delta_live_tuples,
- * delta_dead_tuples, and changed_tuples are set depending on commit or abort.
- * Note that delta_live_tuples and delta_dead_tuples can be negative!
- * ----------
- */
-typedef struct PgStat_TableCounts
+typedef struct PgStat_VacuumRelationStatus
{
- PgStat_Counter numscans;
-
- PgStat_Counter tuples_returned;
- PgStat_Counter tuples_fetched;
-
- PgStat_Counter tuples_inserted;
- PgStat_Counter tuples_updated;
- PgStat_Counter tuples_deleted;
- PgStat_Counter tuples_hot_updated;
- PgStat_Counter tuples_newpage_updated;
- bool truncdropped;
-
- PgStat_Counter delta_live_tuples;
- PgStat_Counter delta_dead_tuples;
- PgStat_Counter changed_tuples;
-
- PgStat_Counter blocks_fetched;
- PgStat_Counter blocks_hit;
-
- PgStat_Counter rev_all_visible_pages;
- PgStat_Counter rev_all_frozen_pages;
+ Oid id; /* table's OID */
+ bool shared; /* is it a shared catalog? */
+ PgStat_VacuumRelationCounts counts; /* event counts to be sent */
+} PgStat_VacuumRelationStatus;
- /*
- * Additional cumulative stat on vacuum operations.
- * Use an expensive structure as an abstraction for different types of
- * relations.
- */
- ExtVacReport vacuum_ext;
-} PgStat_TableCounts;
+typedef struct PgStat_VacuumDBCounts
+{
+ Oid dbjid;
+ PgStat_CommonCounts common;
+ int32 errors;
+} PgStat_VacuumDBCounts;
/* ----------
* PgStat_TableStatus Per-table status within a backend
@@ -272,6 +286,12 @@ typedef struct PgStat_TableStatus
Relation relation; /* rel that is using this entry */
} PgStat_TableStatus;
+typedef struct PgStat_RelationVacuumPending
+{
+ Oid id; /* table's OID */
+ PgStat_VacuumRelationCounts counts; /* event counts to be sent */
+} PgStat_RelationVacuumPending;
+
/* ----------
* PgStat_TableXactStatus Per-table, per-subtransaction status
* ----------
@@ -468,8 +488,6 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter parallel_workers_launched;
TimestampTz stat_reset_timestamp;
-
- ExtVacReport vacuum_ext; /* extended vacuum statistics */
} PgStat_StatDBEntry;
typedef struct PgStat_StatFuncEntry
@@ -551,8 +569,6 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter rev_all_visible_pages;
PgStat_Counter rev_all_frozen_pages;
-
- ExtVacReport vacuum_ext;
} PgStat_StatTabEntry;
/* ------
@@ -760,11 +776,10 @@ extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime, ExtVacReport *params);
+ TimestampTz starttime);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
-extern void pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type);
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
@@ -895,6 +910,15 @@ extern int pgstat_get_transactional_drops(bool isCommit, struct xl_xact_stats_it
extern void pgstat_execute_transactional_drops(int ndrops, struct xl_xact_stats_item *items, bool is_redo);
+extern void pgstat_drop_vacuum_database(Oid databaseid);
+extern void pgstat_vacuum_relation_delete_pending_cb(Oid relid);
+extern void
+pgstat_report_tab_vacuum_extstats(Oid tableoid, bool shared,
+ PgStat_VacuumRelationCounts *params);
+extern PgStat_RelationVacuumPending * find_vacuum_relation_entry(Oid relid);
+extern PgStat_VacuumDBCounts *pgstat_prep_vacuum_database_pending(Oid dboid);
+extern PgStat_VacuumRelationCounts *pgstat_fetch_stat_vacuum_tabentry(Oid relid, Oid dbid);
+PgStat_VacuumDBCounts *pgstat_fetch_stat_vacuum_dbentry(Oid dbid);
/*
* Functions in pgstat_wal.c
*/
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index d5557e6e998..140adbcdbd6 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -439,6 +439,18 @@ typedef struct PgStatShared_Relation
PgStat_StatTabEntry stats;
} PgStatShared_Relation;
+typedef struct PgStatShared_VacuumDB
+{
+ PgStatShared_Common header;
+ PgStat_VacuumDBCounts stats;
+} PgStatShared_VacuumDB;
+
+typedef struct PgStatShared_VacuumRelation
+{
+ PgStatShared_Common header;
+ PgStat_VacuumRelationCounts stats;
+} PgStatShared_VacuumRelation;
+
typedef struct PgStatShared_Function
{
PgStatShared_Common header;
@@ -607,6 +619,9 @@ extern PgStat_EntryRef *pgstat_fetch_pending_entry(PgStat_Kind kind,
extern void *pgstat_fetch_entry(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_snapshot_fixed(PgStat_Kind kind);
+bool pgstat_vacuum_db_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+extern bool pgstat_vacuum_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+
/*
* Functions in pgstat_archiver.c
diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h
index f44169fd5a3..454661f9d6a 100644
--- a/src/include/utils/pgstat_kind.h
+++ b/src/include/utils/pgstat_kind.h
@@ -38,9 +38,11 @@
#define PGSTAT_KIND_IO 10
#define PGSTAT_KIND_SLRU 11
#define PGSTAT_KIND_WAL 12
+#define PGSTAT_KIND_VACUUM_DB 13
+#define PGSTAT_KIND_VACUUM_RELATION 14
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
-#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
+#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_VACUUM_RELATION
#define PGSTAT_KIND_BUILTIN_SIZE (PGSTAT_KIND_BUILTIN_MAX + 1)
/* Custom stats kinds */
--
2.34.1
[text/x-patch] 0005-Add-documentation-about-the-system-views-that-are-us.patch (24.5K, 6-0005-Add-documentation-about-the-system-views-that-are-us.patch)
download | inline diff:
From 66ddd445242f0181edb903ed7ace60e75ca70890 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Thu, 19 Dec 2024 12:57:49 +0300
Subject: [PATCH 5/5] Add documentation about the system views that are used in
the machinery of vacuum statistics.
---
doc/src/sgml/system-views.sgml | 755 +++++++++++++++++++++++++++++++++
1 file changed, 755 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index b58c52ea50f..7e5acd7c52e 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5474,4 +5474,759 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</table>
</sect1>
+<sect1 id="view-pg-stat-vacuum-database">
+ <title><structname>pg_stat_vacuum_database</structname></title>
+
+ <indexterm zone="view-pg-stat-vacuum-database">
+ <primary>pg_stat_vacuum_database</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_stat_vacuum_database</structname> will contain
+ one row for each database in the current cluster, showing statistics about
+ vacuuming that database.
+ </para>
+
+ <table>
+ <title><structname>pg_stat_vacuum_database</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dbid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of a database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_read</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks read by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_hit</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times database blocks were found in the
+ buffer cache by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_dirtied</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks dirtied by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_written</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks written by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_records</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL records generated by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_fpi</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL full page images generated by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_bytes</structfield> <type>numeric</type>
+ </para>
+ <para>
+ Total amount of WAL bytes generated by vacuum operations
+ performed on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_read_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Time spent reading database blocks by vacuum operations performed on
+ this database, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_write_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Time spent writing database blocks by vacuum operations performed on
+ this database, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>delay_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Time spent sleeping in a vacuum delay point by vacuum operations performed on
+ this database, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/>
+ for details)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>system_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ System CPU time of vacuuming this database, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>user_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ User CPU time of vacuuming this database, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Total time of vacuuming this database, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wraparound_failsafe_count</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of times the vacuum was run to prevent a wraparound problem.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>errors</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of times vacuum operations performed on this database
+ were interrupted on any errors
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="view-pg-stat-vacuum-indexes">
+ <title><structname>pg_stat_vacuum_indexes</structname></title>
+
+ <indexterm zone="view-pg-stat-vacuum-indexes">
+ <primary>pg_stat_vacuum_indexes</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_stat_vacuum_indexes</structname> will contain
+ one row for each index in the current database (including TOAST
+ table indexes), showing statistics about vacuuming that specific index.
+ </para>
+
+ <table>
+ <title><structname>pg_stat_vacuum_indexes</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of an index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the schema this index is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_read</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks read by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_hit</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times database blocks were found in the
+ buffer cache by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_dirtied</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks dirtied by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_written</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks written by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rel_blks_read</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of blocks vacuum operations read from this
+ index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rel_blks_hit</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times blocks of this index were already found
+ in the buffer cache by vacuum operations, so that a read was not necessary
+ (this only includes hits in the
+ project; buffer cache, not the operating system's file system cache)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pages_deleted</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of pages deleted by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>tuples_deleted</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of dead tuples vacuum operations deleted from this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_records</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL records generated by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_fpi</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL full page images generated by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_bytes</structfield> <type>numeric</type>
+ </para>
+ <para>
+ Total amount of WAL bytes generated by vacuum operations
+ performed on this index
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_read_time</structfield> <type>int8</type>
+ </para>
+ <para>
+ Time spent reading database blocks by vacuum operations performed on
+ this index, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_write_time</structfield> <type>int8</type>
+ </para>
+ <para>
+ Time spent writing database blocks by vacuum operations performed on
+ this index, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>delay_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Time spent sleeping in a vacuum delay point by vacuum operations performed on
+ this index, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/>
+ for details)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>system_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ System CPU time of vacuuming this index, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>user_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ User CPU time of vacuuming this index, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Total time of vacuuming this index, in milliseconds
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="view-pg-stat-vacuum-tables">
+ <title><structname>pg_stat_vacuum_tables</structname></title>
+
+ <indexterm zone="view-pg-stat-vacuum-tables">
+ <primary>pg_stat_vacuum_tables</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_stat_vacuum_tables</structname> will contain
+ one row for each table in the current database (including TOAST
+ tables), showing statistics about vacuuming that specific table.
+ </para>
+
+ <table>
+ <title><structname>pg_stat_vacuum_tables</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of a table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the schema this table is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_read</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks read by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_hit</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times database blocks were found in the
+ buffer cache by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_dirtied</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of blocks written directly by vacuum or auto vacuum.
+ Blocks that are dirtied by a vacuum process can be written out by another process.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_blks_written</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of database blocks written by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rel_blks_read</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of blocks vacuum operations read from this
+ table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rel_blks_hit</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times blocks of this table were already found
+ in the buffer cache by vacuum operations, so that a read was not necessary
+ (this only includes hits in the
+ project; buffer cache, not the operating system's file system cache)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pages_scanned</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of pages examined by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pages_removed</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of pages removed from the physical storage by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vm_new_frozen_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of the number of pages newly set all-frozen by vacuum
+ in the visibility map.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vm_new_visible_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of the number of pages newly set all-visible by vacuum
+ in the visibility map.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vm_new_visible_frozen_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of the number of pages newly set all-visible and all-frozen
+ by vacuum in the visibility map.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>tuples_deleted</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of dead tuples vacuum operations deleted from this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>tuples_frozen</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of tuples of this table that vacuum operations marked as
+ frozen
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>recently_dead_tuples</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of dead tuples vacuum operations left in this table due
+ to their visibility in transactions
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>missed_dead_tuples</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of fully DEAD (not just RECENTLY_DEAD) tuples that could not be
+ pruned due to failure to acquire a cleanup lock on a heap page.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>index_vacuum_count</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times indexes on this table were vacuumed
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wraparound_failsafe_count</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of times the vacuum was run to prevent a wraparound problem.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>missed_dead_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of pages that had at least one missed_dead_tuples.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_records</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL records generated by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_fpi</structfield> <type>int8</type>
+ </para>
+ <para>
+ Total number of WAL full page images generated by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wal_bytes</structfield> <type>numeric</type>
+ </para>
+ <para>
+ Total amount of WAL bytes generated by vacuum operations
+ performed on this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_read_time</structfield> <type>int8</type>
+ </para>
+ <para>
+ Time spent reading database blocks by vacuum operations performed on
+ this table, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>blk_write_time</structfield> <type>int8</type>
+ </para>
+ <para>
+ Time spent writing database blocks by vacuum operations performed on
+ this table, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>delay_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Time spent sleeping in a vacuum delay point by vacuum operations performed on
+ this table, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/>
+ for details)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>system_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ System CPU time of vacuuming this table, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>user_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ User CPU time of vacuuming this table, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_time</structfield> <type>float8</type>
+ </para>
+ <para>
+ Total time of vacuuming this table, in milliseconds
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ <para>Columns <structfield>total_*</structfield>, <structfield>wal_*</structfield>
+ and <structfield>blk_*</structfield> include data on vacuuming indexes on this table, while columns
+ <structfield>system_time</structfield> and <structfield>user_time</structfield> only include data
+ on vacuuming the heap.</para>
+ </sect1>
</chapter>
--
2.34.1
view thread (53+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Vacuum statistics
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox