public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Ilia Evdokimov <[email protected]>
Cc: Andrei Zubkov <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: [email protected]
Cc: jian he <[email protected]>
Subject: Re: Vacuum statistics
Date: Sun, 25 Aug 2024 18:59:46 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdug0s2MD7bBf-5nDQGn1WBxCKiTmZyGfxHz_7P0CDOjbg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxHb_YGCp=pVH6DZcpk9yML+SueffPeaRbX2LzXZVahd_w@mail.gmail.com>
<CACJufxE6yAP+jUm4_GyKp7gUCkzbuJ9HGB-rR=92_hcLLZ9KTg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAPpHfdug0s2MD7bBf-5nDQGn1WBxCKiTmZyGfxHz_7P0CDOjbg@mail.gmail.com>
Hi!
On 23.08.2024 04:07, Alexander Korotkov wrote:
> On Wed, Aug 21, 2024 at 1:39 AM Alena Rybakina
> <[email protected]> wrote:
> >
> > I think you've counted the above system tables from the database, but
> > I'll double-check it. Thank you for your review!
> >
> > On 19.08.2024 19:28, Ilia Evdokimov wrote:
> > > Are you certain that all tables are included in
> > > `pg_stat_vacuum_tables`? I'm asking because of the following:
> > >
> > >
> > > SELECT count(*) FROM pg_stat_all_tables ;
> > > count
> > > -------
> > > 108
> > > (1 row)
> > >
> > > SELECT count(*) FROM pg_stat_vacuum_tables ;
> > > count
> > > -------
> > > 20
> > > (1 row)
> > >
>
> I'd like to do some review a well.
Thank you very much for your review and contribution to this thread!
>
> + MyDatabaseId = dbid;
> +
> + PG_TRY();
> + {
> + tabentry = pgstat_fetch_stat_tabentry(relid);
> + MyDatabaseId = storedMyDatabaseId;
> + }
> + PG_CATCH();
> + {
> + MyDatabaseId = storedMyDatabaseId;
> + }
> + PG_END_TRY();
>
> I think this is generally wrong to change MyDatabaseId, especially if
> you have to wrap it with PG_TRY()/PG_CATCH(). I think, instead we
> need proper API changes, i.e. make pgstat_fetch_stat_tabentry() and
> others take dboid as an argument.
I fixed it by deleting this part pf the code. We can display statistics
only for current database.
>
> +/*
> + * Get the vacuum statistics for the heap tables.
> + */
> +Datum
> +pg_stat_vacuum_tables(PG_FUNCTION_ARGS)
> +{
> + return pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_HEAP,
> EXTVACHEAPSTAT_COLUMNS);
> +
> + PG_RETURN_NULL();
> +}
>
> The PG_RETURN_NULL() is unneeded after another return statement.
> However, does pg_stats_vacuum() need to return anything? What about
> making its return type void?
I think you are right, we can not return anything. Fixed.
>
> @@ -874,4 +874,38 @@ pgstat_get_custom_snapshot_data(PgStat_Kind kind)
> return pgStatLocal.snapshot.custom_data[idx];
> }
>
> +/* hash table for statistics snapshots entry */
> +typedef struct PgStat_SnapshotEntry
> +{
> + PgStat_HashKey key;
> + char status; /* for simplehash use */
> + void *data; /* the stats data itself */
> +} PgStat_SnapshotEntry;
>
> It would be nice to preserve encapsulation and don't
> expose pgstat_snapshot hash in the headers. I see there is only one
> usage of it outside of pgstat.c: pg_stats_vacuum().
Fixed.
>
> + Oid storedMyDatabaseId = MyDatabaseId;
> +
> + pgstat_update_snapshot(PGSTAT_KIND_RELATION);
> + MyDatabaseId = storedMyDatabaseId;
>
> This manipulation with storedMyDatabaseId looks pretty useless. It
> seems to be intended to change MyDatabaseId, while I'm not fan of this
> as I mentioned above.
Fixed.
>
> +static PgStat_StatTabEntry *
> +fetch_dbstat_tabentry(Oid dbid, Oid relid)
> +{
> + Oid storedMyDatabaseId = MyDatabaseId;
> + PgStat_StatTabEntry *tabentry = NULL;
> +
> + if (OidIsValid(CurrentDatabaseId) && CurrentDatabaseId == dbid)
> + /* Quick path when we read data from the same database */
> + return pgstat_fetch_stat_tabentry(relid);
> +
> + pgstat_clear_snapshot();
>
> It looks scary to reset the whole snapshot each time we access another
> database. Need to also mention that the CurrentDatabaseId machinery
> isn't implemented.
Fixed.
>
> New functions
> pg_stat_vacuum_tables(), pg_stat_vacuum_indexes(), pg_stat_vacuum_database()
> are SRFs. When zero Oid is passed they report all the objects.
> However, it seems they aren't intended to be used directly. Instead,
> there are views with the same names. These views always call them with
> particular Oids, therefore SRFs always return one row. Then why
> bother with SRF? They could return plain records instead.
I didn't understand correctly - did you mean that we don't need SRF if
we need to display statistics for a specific object?
Otherwise, we need this when we display information on all database
objects (tables or indexes):
while ((entry = ScanStatSnapshot(pgStatLocal.snapshot.stats, &hashiter))
!= NULL)
{
CHECK_FOR_INTERRUPTS();
tabentry = (PgStat_StatTabEntry *) entry->data;
if (tabentry != NULL && tabentry->vacuum_ext.type == type)
tuplestore_put_for_relation(relid, rsinfo, tabentry);
}
I know we can construct a HeapTuple object containing a TupleDesc,
values, and nulls for a particular object, but I'm not sure we can
augment it while looping through multiple objects.
/* Initialise attributes information in the tuple descriptor */
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_SUBSCRIPTION_STATS_COLS);
...
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
If I missed something or misunderstood, can you explain in more detail?
>
> Also, as I mentioned above patchset makes a lot of trouble accessing
> statistics of relations of another database. But that seems to be
> useless given corresponding views allow to see only relations of the
> current database. Even if you call functions directly, what is the
> value of this information given that you don't know the relation oids
> in another database? So, I think if we will give up and limit access
> to the relations of the current database patch will become simpler and
> clearer.
>
I agree with that and have fixed it already.
--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
[text/x-patch] v6-0001-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (63.5K, 2-v6-0001-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From 8903b692430e0e999665bc4a41d5fd088749131e Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Sun, 25 Aug 2024 10:49:40 +0300
Subject: [PATCH 1/4] Machinery for grabbing an extended vacuum statistics on
heap 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).
Interruptions number of (auto)vacuum process during vacuuming of a relation.
We report from the vacuum_error_callback routine. So we can log all ERROR
reports. In the case of autovacuum we can report SIGINT signals too.
It maybe dangerous to make such complex task (send) in an error callback -
we can catch ERROR in ERROR problem. But it looks like we have so small
chance to stuck into this problem. So, let's try to use.
This parameter relates to a problem, covered by b19e4250.
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 - 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 - number of pages that are marked as all-visible in vm during
vacuum.
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]>
---
src/backend/access/heap/vacuumlazy.c | 159 +++++++++++++-
src/backend/access/heap/visibilitymap.c | 13 ++
src/backend/catalog/system_views.sql | 54 +++++
src/backend/commands/vacuum.c | 4 +
src/backend/commands/vacuumparallel.c | 1 +
src/backend/utils/activity/pgstat.c | 65 +++++-
src/backend/utils/activity/pgstat_relation.c | 35 ++-
src/backend/utils/adt/pgstatfuncs.c | 157 ++++++++++++++
src/backend/utils/error/elog.c | 13 ++
src/include/catalog/pg_proc.dat | 10 +-
src/include/commands/vacuum.h | 1 +
src/include/pgstat.h | 84 +++++++-
src/include/utils/elog.h | 1 +
src/include/utils/pgstat_internal.h | 2 +-
.../vacuum-extending-in-repetable-read.out | 53 +++++
src/test/isolation/isolation_schedule | 1 +
.../vacuum-extending-in-repetable-read.spec | 51 +++++
src/test/regress/expected/opr_sanity.out | 7 +-
src/test/regress/expected/rules.out | 34 +++
.../expected/vacuum_tables_statistics.out | 200 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +
.../regress/sql/vacuum_tables_statistics.sql | 158 ++++++++++++++
22 files changed, 1092 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 d82aa3d4896..3941ae26f2d 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -167,6 +167,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 */
@@ -194,6 +195,8 @@ typedef struct LVRelState
BlockNumber lpdead_item_pages; /* # pages with LP_DEAD items */
BlockNumber missed_dead_pages; /* # pages with missed dead tuples */
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
+ BlockNumber set_frozen_pages; /* pages are marked as frozen in vm during vacuum */
+ BlockNumber set_all_visible_pages; /* pages are marked as all-visible in vm during vacuum */
/* Statistics output by us, for table */
double new_rel_tuples; /* new estimated total # of tuples */
@@ -226,6 +229,22 @@ typedef struct LVSavedErrInfo
VacErrPhase phase;
} LVSavedErrInfo;
+/*
+ * Cut-off values of parameters which changes implicitly during a vacuum
+ * process.
+ * Vacuum can't control their values, so we should store them before and after
+ * the processing.
+ */
+typedef struct LVExtStatCounters
+{
+ TimestampTz time;
+ PGRUsage ru;
+ 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);
@@ -279,6 +298,115 @@ 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;
+ PGRUsage ru0;
+
+ memset(counters, 0, sizeof(LVExtStatCounters));
+
+ pg_rusage_init(&ru0);
+ starttime = GetCurrentTimestamp();
+
+ counters->ru = ru0;
+ counters->time = 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 an 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;
+ PGRUsage ru1;
+
+ /* 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->time, 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;
+
+ /*
+ * Get difference of a system time and user time values in milliseconds.
+ * Use floating point representation to show tails of time diffs.
+ */
+ pg_rusage_init(&ru1);
+ report->system_time =
+ (ru1.ru.ru_stime.tv_sec - counters->ru.ru.ru_stime.tv_sec) * 1000. +
+ (ru1.ru.ru_stime.tv_usec - counters->ru.ru.ru_stime.tv_usec) * 0.001;
+ report->user_time =
+ (ru1.ru.ru_utime.tv_sec - counters->ru.ru.ru_utime.tv_sec) * 1000. +
+ (ru1.ru.ru_utime.tv_usec - counters->ru.ru.ru_utime.tv_usec) * 0.001;
+ 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;
+}
/*
* heap_vacuum_rel() -- perform VACUUM for one heap relation
@@ -311,6 +439,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
WalUsage startwalusage = pgWalUsage;
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
+ LVExtStatCounters extVacCounters;
+ ExtVacReport extVacReport;
char **indnames = NULL;
verbose = (params->options & VACOPT_VERBOSE) != 0;
@@ -329,7 +459,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
@@ -346,6 +476,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;
@@ -413,6 +544,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->lpdead_item_pages = 0;
vacrel->missed_dead_pages = 0;
vacrel->nonempty_pages = 0;
+ vacrel->set_frozen_pages = 0;
+ vacrel->set_all_visible_pages = 0;
/* dead_items_alloc allocates vacrel->dead_items later on */
/* Allocate/initialize output statistics state */
@@ -574,6 +707,19 @@ 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);
+
+ /* Fill heap-specific extended stats fields */
+ extVacReport.pages_scanned = vacrel->scanned_pages;
+ extVacReport.pages_removed = vacrel->removed_pages;
+ extVacReport.pages_frozen = vacrel->set_frozen_pages;
+ extVacReport.pages_all_visible = vacrel->set_all_visible_pages;
+ extVacReport.tuples_deleted = vacrel->tuples_deleted;
+ extVacReport.tuples_frozen = vacrel->tuples_frozen;
+ extVacReport.dead_tuples = vacrel->recently_dead_tuples + vacrel->missed_dead_tuples;
+ extVacReport.index_vacuum_count = vacrel->num_index_scans;
+
/*
* Report results to the cumulative stats system, too.
*
@@ -588,7 +734,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
rel->rd_rel->relisshared,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples);
+ vacrel->missed_dead_tuples,
+ &extVacReport);
pgstat_progress_end_command();
if (instrument)
@@ -1380,6 +1527,8 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
vmbuffer, InvalidTransactionId,
VISIBILITYMAP_ALL_VISIBLE | VISIBILITYMAP_ALL_FROZEN);
END_CRIT_SECTION();
+ vacrel->set_all_visible_pages++;
+ vacrel->set_frozen_pages++;
}
freespace = PageGetHeapFreeSpace(page);
@@ -2277,11 +2426,13 @@ lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, Buffer buffer,
&all_frozen))
{
uint8 flags = VISIBILITYMAP_ALL_VISIBLE;
+ vacrel->set_all_visible_pages++;
if (all_frozen)
{
Assert(!TransactionIdIsValid(visibility_cutoff_xid));
flags |= VISIBILITYMAP_ALL_FROZEN;
+ vacrel->set_frozen_pages++;
}
PageSetAllVisible(page);
@@ -3122,6 +3273,8 @@ vacuum_error_callback(void *arg)
switch (errinfo->phase)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
+ if(geterrelevel() >= ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid);
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3137,6 +3290,8 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
+ if(geterrelevel() >= ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid);
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 8b24e7bc33c..d72cade60a4 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,18 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
if (map[mapByte] & mask)
{
+ /*
+ * Initially, it didn't matter what type of flags (all-visible or frozen) we received,
+ * we just performed a reverse concatenation operation. But this information is very important
+ * for vacuum statistics. We need to find out this usingthe bit concatenation operation
+ * with the VISIBILITYMAP_ALL_VISIBLE and VISIBILITYMAP_ALL_FROZEN masks,
+ * and where the desired one matches, we increment the value there.
+ */
+ 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 19cabc9a47f..e84d6881403 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1371,3 +1371,57 @@ CREATE VIEW pg_stat_subscription_stats AS
CREATE VIEW pg_wait_events AS
SELECT * FROM pg_get_wait_events();
+--
+-- 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
+ rel.oid as relid,
+ ns.nspname AS "schema",
+ rel.relname AS 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_scanned,
+ stats.pages_removed,
+ stats.pages_frozen,
+ stats.pages_all_visible,
+ stats.tuples_deleted,
+ stats.tuples_frozen,
+ stats.dead_tuples,
+
+ stats.index_vacuum_count,
+ stats.rev_all_frozen_pages,
+ stats.rev_all_visible_pages,
+
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+
+ stats.blk_read_time,
+ stats.blk_write_time,
+
+ stats.delay_time,
+ stats.system_time,
+ stats.user_time,
+ stats.total_time,
+ stats.interrupts
+FROM
+ pg_database db,
+ pg_class rel,
+ pg_namespace ns,
+ pg_stat_vacuum_tables(rel.oid) stats
+WHERE
+ db.datname = current_database() AND
+ rel.oid = stats.relid AND
+ ns.oid = rel.relnamespace;
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d20454..363924d00db 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -103,6 +103,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);
@@ -2394,6 +2397,7 @@ vacuum_delay_point(void)
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 22c057fe61b..13ab633086a 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -1043,6 +1043,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 b2ca3f39b7a..4e8d8f8dc77 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);
@@ -260,7 +260,6 @@ static bool pgstat_is_initialized = false;
static bool pgstat_is_shutdown = false;
#endif
-
/*
* The different kinds of built-in statistics.
*
@@ -830,6 +829,40 @@ pgstat_reset_of_kind(PgStat_Kind kind)
pgstat_reset_entries_of_kind(kind, ts);
}
+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->system_time += src->system_time;
+ dst->user_time += src->user_time;
+ dst->total_time += src->total_time;
+ dst->interrupts += src->interrupts;
+
+ 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->pages_frozen += src->pages_frozen;
+ dst->pages_all_visible += src->pages_all_visible;
+ dst->tuples_deleted += src->tuples_deleted;
+ dst->tuples_frozen += src->tuples_frozen;
+ dst->dead_tuples += src->dead_tuples;
+ dst->index_vacuum_count += src->index_vacuum_count;
+}
/* ------------------------------------------------------------
* Fetching of stats
@@ -896,7 +929,7 @@ pgstat_fetch_entry(PgStat_Kind kind, Oid dboid, Oid objoid)
/* 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)
@@ -1012,7 +1045,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);
@@ -1062,8 +1095,30 @@ pgstat_prep_snapshot(void)
NULL);
}
+
+/*
+ * Trivial external interface to build a snapshot for table statistics only.
+ */
+void
+pgstat_update_snapshot(PgStat_Kind kind)
+{
+ int save_consistency_guc = pgstat_fetch_consistency;
+ pgstat_clear_snapshot();
+
+ PG_TRY();
+ {
+ pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_SNAPSHOT;
+ pgstat_build_snapshot(PGSTAT_KIND_RELATION);
+ }
+ PG_FINALLY();
+ {
+ pgstat_fetch_consistency = save_consistency_guc;
+ }
+ PG_END_TRY();
+}
+
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 8a3f7d434cf..d40d43cdb4a 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -204,12 +204,40 @@ pgstat_drop_relation(Relation rel)
}
}
+/* ---------
+ * pgstat_report_vacuum_error() -
+ *
+ * Tell the collector about an (auto)vacuum interruption.
+ * ---------
+ */
+void
+pgstat_report_vacuum_error(Oid tableoid)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ Oid dboid = MyDatabaseId;
+
+ 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.interrupts++;
+ pgstat_unlock_entry(entry_ref);
+}
+
/*
* 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)
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ ExtVacReport *params)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -233,6 +261,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.
@@ -861,6 +891,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 */
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 32211371237..1df271286e6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -31,6 +31,42 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/timestamp.h"
+#include "utils/pgstat_internal.h"
+
+/* hash table for statistics snapshots entry */
+typedef struct PgStat_SnapshotEntry
+{
+ PgStat_HashKey key;
+ char status; /* for simplehash use */
+ void *data; /* the stats data itself */
+} PgStat_SnapshotEntry;
+
+/* ----------
+ * Backend-local Hash Table Definitions
+ * ----------
+ */
+
+/* for stats snapshot entries */
+#define SH_PREFIX pgstat_snapshot
+#define SH_ELEMENT_TYPE PgStat_SnapshotEntry
+#define SH_KEY_TYPE PgStat_HashKey
+#define SH_KEY key
+#define SH_HASH_KEY(tb, key) \
+ pgstat_hash_hash_key(&key, sizeof(PgStat_HashKey), NULL)
+#define SH_EQUAL(tb, a, b) \
+ pgstat_cmp_hash_key(&a, &b, sizeof(PgStat_HashKey), NULL) == 0
+#define SH_SCOPE static inline
+#define SH_DEFINE
+#define SH_DECLARE
+#include "lib/simplehash.h"
+
+typedef pgstat_snapshot_iterator SnapshotIterator;
+
+#define InitSnapshotIterator(htable, iter) \
+ pgstat_snapshot_start_iterate(htable, iter);
+#define ScanStatSnapshot(htable, iter) \
+ pgstat_snapshot_iterate(htable, iter)
+
#define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var))))
@@ -2032,3 +2068,124 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(pgstat_have_entry(kind, dboid, objoid));
}
+
+#define EXTVACHEAPSTAT_COLUMNS 27
+
+static void
+tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
+ PgStat_StatTabEntry *tabentry)
+{
+ Datum values[EXTVACHEAPSTAT_COLUMNS];
+ bool nulls[EXTVACHEAPSTAT_COLUMNS];
+ char buf[256];
+ int i = 0;
+
+ memset(nulls, 0, EXTVACHEAPSTAT_COLUMNS * sizeof(bool));
+
+ values[i++] = ObjectIdGetDatum(relid);
+
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.total_blks_read);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.total_blks_hit);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.total_blks_dirtied);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.total_blks_written);
+
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.blks_fetched -
+ tabentry->vacuum_ext.blks_hit);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.blks_hit);
+
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_scanned);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_removed);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_frozen);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_all_visible);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.tuples_deleted);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.tuples_frozen);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.dead_tuples);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.index_vacuum_count);
+ values[i++] = Int64GetDatum(tabentry->rev_all_frozen_pages);
+ values[i++] = Int64GetDatum(tabentry->rev_all_visible_pages);
+
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.wal_records);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, tabentry->vacuum_ext.wal_bytes);
+ values[i++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.blk_read_time);
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.blk_write_time);
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.delay_time);
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.system_time);
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.user_time);
+ values[i++] = Float8GetDatum(tabentry->vacuum_ext.total_time);
+ values[i++] = Int32GetDatum(tabentry->vacuum_ext.interrupts);
+
+ Assert(i == rsinfo->setDesc->natts);
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
+/*
+ * Get the vacuum statistics for the heap tables or indexes.
+ */
+static void
+pg_stats_vacuum(FunctionCallInfo fcinfo, int ncolumns)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ Oid relid = PG_GETARG_OID(0);
+ PgStat_StatTabEntry *tabentry;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* Check if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ Assert(rsinfo->setDesc->natts == ncolumns);
+ Assert(rsinfo->setResult != NULL);
+
+ /* Load table statistics for specified database. */
+ if (OidIsValid(relid))
+ {
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+ if (tabentry == NULL)
+ /* Table don't exists or isn't an heap relation. */
+ return;
+
+ tuplestore_put_for_relation(relid, rsinfo, tabentry);
+ }
+ else
+ {
+ SnapshotIterator hashiter;
+ PgStat_SnapshotEntry *entry;
+
+ /* Iterate the snapshot */
+ InitSnapshotIterator(pgStatLocal.snapshot.stats, &hashiter);
+
+ while ((entry = ScanStatSnapshot(pgStatLocal.snapshot.stats, &hashiter)) != NULL)
+ {
+ Oid reloid;
+
+ CHECK_FOR_INTERRUPTS();
+
+ tabentry = (PgStat_StatTabEntry *) entry->data;
+ reloid = entry->key.objoid;
+
+ if (tabentry != NULL)
+ tuplestore_put_for_relation(reloid, rsinfo, tabentry);
+ }
+ }
+}
+
+/*
+ * Get the vacuum statistics for the heap tables.
+ */
+Datum
+pg_stat_vacuum_tables(PG_FUNCTION_ARGS)
+{
+ pg_stats_vacuum(fcinfo, EXTVACHEAPSTAT_COLUMNS);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index 5cbb5b54168..5ead2a8aff8 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -1619,6 +1619,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/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4abc6d95262..2023270f923 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12254,5 +12254,13 @@
proallargtypes => '{int8,pg_lsn,pg_lsn,int4}', proargmodes => '{o,o,o,o}',
proargnames => '{summarized_tli,summarized_lsn,pending_lsn,summarizer_pid}',
prosrc => 'pg_get_wal_summarizer_state' },
-
+{ oid => '8001',
+ descr => 'pg_stat_vacuum_tables return stats values',
+ proname => 'pg_stat_vacuum_tables', 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,int8,int8,numeric,float8,float8,float8,float8,float8,float8,int4}',
+ 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,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,pages_frozen,pages_all_visible,tuples_deleted,tuples_frozen,dead_tuples,index_vacuum_count,rev_all_frozen_pages,rev_all_visible_pages,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
+ prosrc => 'pg_stat_vacuum_tables' },
]
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 759f9a87d38..07b28b15d9f 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -308,6 +308,7 @@ extern PGDLLIMPORT int vacuum_multixact_failsafe_age;
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 f63159c55ca..4492a0572c6 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -167,6 +167,52 @@ typedef struct PgStat_BackendSubEntry
PgStat_Counter sync_error_count;
} 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
+{
+ int64 total_blks_read; /* number of pages that were missed in shared buffers during a vacuum of specific relation */
+ int64 total_blks_hit; /* number of pages that were found in shared buffers during a vacuum of specific relation */
+ int64 total_blks_dirtied; /* number of pages marked as 'Dirty' during a vacuum of specific relation. */
+ int64 total_blks_written; /* number of pages written during a vacuum of specific relation. */
+
+ int64 blks_fetched; /* number of a relation blocks, fetched during the vacuum. */
+ int64 blks_hit; /* number of a relation blocks, found in shared buffers during the vacuum. */
+
+ /* 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 system_time; /* amount of time the CPU was busy executing vacuum code in kernel space, in msec */
+ double user_time; /* amount of time the CPU was busy executing vacuum code in user space, in msec */
+ double total_time; /* total time of a vacuum operation, in msec */
+
+ /* Interruptions on any errors. */
+ int32 interrupts;
+
+ int64 pages_scanned; /* number of pages we examined */
+ int64 pages_removed; /* number of pages removed by vacuum */
+ int64 pages_frozen; /* number of pages marked in VM as frozen */
+ int64 pages_all_visible; /* number of pages marked in VM as all-visible */
+ int64 tuples_deleted; /* tuples deleted by vacuum */
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 dead_tuples; /* number of deleted tuples which vacuum cannot clean up by vacuum operation */
+ int64 index_vacuum_count; /* number of index vacuumings */
+} ExtVacReport;
+
/* ----------
* PgStat_TableCounts The actual per-table counts kept by a backend
*
@@ -207,6 +253,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;
/* ----------
@@ -265,7 +321,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCAE
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCAF
typedef struct PgStat_ArchiverStats
{
@@ -384,6 +440,8 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter sessions_killed;
TimestampTz stat_reset_timestamp;
+
+ ExtVacReport vacuum_ext; /* extended vacuum statistics */
} PgStat_StatDBEntry;
typedef struct PgStat_StatFuncEntry
@@ -456,6 +514,11 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter analyze_count;
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
+
+ ExtVacReport vacuum_ext;
} PgStat_StatTabEntry;
typedef struct PgStat_WalStats
@@ -621,10 +684,12 @@ extern void pgstat_assoc_relation(Relation rel);
extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ ExtVacReport *params);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter);
+extern void pgstat_report_vacuum_error(Oid tableoid);
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
@@ -672,6 +737,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);
@@ -688,7 +764,9 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry_ext(bool shared,
Oid reloid);
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
-
+extern void
+pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
+ bool accumulate_reltype_specific_info);
/*
* Functions in pgstat_replslot.c
diff --git a/src/include/utils/elog.h b/src/include/utils/elog.h
index e54eca5b489..e752c0ce015 100644
--- a/src/include/utils/elog.h
+++ b/src/include/utils/elog.h
@@ -230,6 +230,7 @@ extern int geterrlevel(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/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index fb132e439dc..24ab3ceb717 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -549,7 +549,7 @@ extern PgStat_EntryRef *pgstat_fetch_pending_entry(PgStat_Kind kind, Oid dboid,
extern void *pgstat_fetch_entry(PgStat_Kind kind, Oid dboid, Oid objoid);
extern void pgstat_snapshot_fixed(PgStat_Kind kind);
-
+extern void pgstat_update_snapshot(PgStat_Kind kind);
/*
* Functions in pgstat_archiver.c
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..7cdb79c0ec4
--- /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.dead_tuples, 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|dead_tuples|tuples_frozen
+--------------------------+--------------+-----------+-------------
+test_vacuum_stat_isolation| 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.dead_tuples, 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|dead_tuples|tuples_frozen
+--------------------------+--------------+-----------+-------------
+test_vacuum_stat_isolation| 0| 100| 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.dead_tuples, 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|dead_tuples|tuples_frozen
+--------------------------+--------------+-----------+-------------
+test_vacuum_stat_isolation| 100| 100| 101
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 6da98cffaca..c612de70083 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -95,6 +95,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..7d31ddbece9
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -0,0 +1,51 @@
+# Test for checking dead_tuples, tuples_deleted and frozen tuples in pg_stat_vacuum_tables.
+# Dead_tuples values are counted when vacuum cannot clean up unused tuples while lock is using another transaction.
+# 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;
+}
+
+teardown
+{
+ DROP TABLE test_vacuum_stat_isolation CASCADE;
+ RESET track_io_timing;
+}
+
+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.dead_tuples, 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
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0d734169f11..9ae743eae0c 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -32,9 +32,10 @@ WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
prokind NOT IN ('f', 'a', 'w', 'p') OR
provolatile NOT IN ('i', 's', 'v') OR
proparallel NOT IN ('s', 'r', 'u');
- oid | proname
------+---------
-(0 rows)
+ oid | proname
+------+-----------------------
+ 8001 | pg_stat_vacuum_tables
+(1 row)
-- prosrc should never be null; it can be empty only if prosqlbody isn't null
SELECT p1.oid, p1.proname
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 862433ee52b..cc0b5bde0a1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2229,6 +2229,40 @@ pg_stat_user_tables| SELECT relid,
autoanalyze_count
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 rel.oid AS relid,
+ ns.nspname AS schema,
+ 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_scanned,
+ stats.pages_removed,
+ stats.pages_frozen,
+ stats.pages_all_visible,
+ stats.tuples_deleted,
+ stats.tuples_frozen,
+ stats.dead_tuples,
+ stats.index_vacuum_count,
+ stats.rev_all_frozen_pages,
+ stats.rev_all_visible_pages,
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.system_time,
+ stats.user_time,
+ stats.total_time,
+ stats.interrupts
+ FROM pg_database db,
+ pg_class rel,
+ pg_namespace ns,
+ LATERAL pg_stat_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, pages_frozen, pages_all_visible, tuples_deleted, tuples_frozen, dead_tuples, index_vacuum_count, rev_all_frozen_pages, rev_all_visible_pages, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, system_time, user_time, total_time, interrupts)
+ WHERE ((db.datname = current_database()) AND (rel.oid = stats.relid) AND (ns.oid = rel.relnamespace));
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..1a7d04b0590
--- /dev/null
+++ b/src/test/regress/expected/vacuum_tables_statistics.out
@@ -0,0 +1,200 @@
+--
+-- 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)
+
+-- 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
+--------------------------
+
+(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) 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,pages_frozen,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 | pages_frozen | 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,pages_frozen > 0 AS pages_frozen,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 | pages_frozen | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+--------------+----------------+----------+---------------+---------------
+ vestat | f | t | t | t | t
+(1 row)
+
+SELECT pages_frozen 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,pages_frozen-:fp > 0 AS pages_frozen,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 | pages_frozen | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+--------------+----------------+----------+---------------+---------------
+ vestat | f | t | f | t | t
+(1 row)
+
+SELECT pages_frozen 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,pages_frozen-:fp = 0 AS pages_frozen,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 | pages_frozen | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+--------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+SELECT pages_frozen 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 pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,pages_frozen-:fp = 0 AS pages_frozen,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 | pages_frozen | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+--------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+-- must be empty
+SELECT pages_frozen, pages_all_visible, rev_all_frozen_pages,rev_all_visible_pages
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
+ pages_frozen | pages_all_visible | rev_all_frozen_pages | rev_all_visible_pages
+--------------+-------------------+----------------------+-----------------------
+ 0 | 0 | 0 | 0
+(1 row)
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- backend defreezed pages
+SELECT pages_frozen > 0 AS pages_frozen,pages_all_visible > 0 AS pages_all_visible,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 WHERE relname = 'vestat';
+ pages_frozen | pages_all_visible | rev_all_frozen_pages | rev_all_visible_pages
+--------------+-------------------+----------------------+-----------------------
+ f | f | t | t
+(1 row)
+
+SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+UPDATE vestat SET x = x+1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+SELECT pages_frozen > :pf AS pages_frozen,pages_all_visible > :pv AS pages_all_visible,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 WHERE relname = 'vestat';
+ pages_frozen | pages_all_visible | rev_all_frozen_pages | rev_all_visible_pages
+--------------+-------------------+----------------------+-----------------------
+ f | f | f | f
+(1 row)
+
+SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- vacuum freezed pages
+SELECT pages_frozen = :pf AS pages_frozen,pages_all_visible = :pv AS pages_all_visible,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 WHERE relname = 'vestat';
+ pages_frozen | pages_all_visible | rev_all_frozen_pages | rev_all_visible_pages
+--------------+-------------------+----------------------+-----------------------
+ 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 2429ec2bbaa..f8a4bcccc9d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -136,3 +136,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..41e387dd304
--- /dev/null
+++ b/src/test/regress/sql/vacuum_tables_statistics.sql
@@ -0,0 +1,158 @@
+--
+-- 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
+-- 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();
+
+\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) 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,pages_frozen,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,pages_frozen > 0 AS pages_frozen,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 pages_frozen 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,pages_frozen-:fp > 0 AS pages_frozen,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 pages_frozen 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,pages_frozen-:fp = 0 AS pages_frozen,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 pages_frozen 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 pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,pages_frozen-:fp = 0 AS pages_frozen,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;
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+-- must be empty
+SELECT pages_frozen, pages_all_visible, rev_all_frozen_pages,rev_all_visible_pages
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- backend defreezed pages
+SELECT pages_frozen > 0 AS pages_frozen,pages_all_visible > 0 AS pages_all_visible,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 WHERE relname = 'vestat';
+SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+UPDATE vestat SET x = x+1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+SELECT pages_frozen > :pf AS pages_frozen,pages_all_visible > :pv AS pages_all_visible,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 WHERE relname = 'vestat';
+SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- vacuum freezed pages
+SELECT pages_frozen = :pf AS pages_frozen,pages_all_visible = :pv AS pages_all_visible,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 WHERE relname = 'vestat';
+
+DROP TABLE vestat CASCADE;
\ No newline at end of file
--
2.34.1
[text/x-patch] v6-0002-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (40.7K, 3-v6-0002-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From 35aac7704eabeaefd5ab76bb18c0e68d29388be1 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Sun, 25 Aug 2024 17:09:21 +0300
Subject: [PATCH 2/4] Machinery for grabbing an extended vacuum statistics on
heap and index relations. Remember, statistic on heap and index relations a
bit different (see ExtVacReport to find out more information). The concept of
the ExtVacReport structure has been complicated to store statistic
information for two kinds of relations: for heap and index relations.
ExtVacReportType variable helps to determine what the kind is considering
now.
---
src/backend/access/heap/vacuumlazy.c | 99 +++++++++--
src/backend/catalog/system_views.sql | 41 +++++
src/backend/utils/activity/pgstat.c | 45 +++--
src/backend/utils/activity/pgstat_relation.c | 3 +-
src/backend/utils/adt/pgstatfuncs.c | 99 ++++++-----
src/include/catalog/pg_proc.dat | 9 +
src/include/pgstat.h | 53 ++++--
.../vacuum-extending-in-repetable-read.out | 7 +-
.../vacuum-extending-in-repetable-read.spec | 2 +-
src/test/regress/expected/opr_sanity.out | 7 +-
src/test/regress/expected/rules.out | 26 +++
.../expected/vacuum_index_statistics.out | 158 ++++++++++++++++++
.../expected/vacuum_tables_statistics.out | 3 +-
src/test/regress/parallel_schedule | 1 +
.../regress/sql/vacuum_index_statistics.sql | 128 ++++++++++++++
15 files changed, 600 insertions(+), 81 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 3941ae26f2d..4e2ae78d255 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -168,6 +168,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 */
@@ -246,6 +247,13 @@ typedef struct LVExtStatCounters
PgStat_Counter blocks_hit;
} LVExtStatCounters;
+typedef struct LVExtStatCountersIdx
+{
+ LVExtStatCounters common;
+ int64 pages_deleted;
+ int64 tuples_removed;
+} LVExtStatCountersIdx;
+
/* non-export function prototypes */
static void lazy_scan_heap(LVRelState *vacrel);
static bool heap_vac_scan_next_block(LVRelState *vacrel, BlockNumber *blkno,
@@ -408,6 +416,46 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
}
+static void
+extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters)
+{
+ 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;
+ }
+}
+
+static void
+extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters, ExtVacReport *report)
+{
+ 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->index.tuples_deleted =
+ stats->tuples_removed - counters->tuples_removed;
+ report->index.pages_deleted =
+ stats->pages_deleted - counters->pages_deleted;
+ }
+}
+
/*
* heap_vacuum_rel() -- perform VACUUM for one heap relation
*
@@ -711,14 +759,15 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
extvac_stats_end(rel, &extVacCounters, &extVacReport);
/* Fill heap-specific extended stats fields */
- extVacReport.pages_scanned = vacrel->scanned_pages;
- extVacReport.pages_removed = vacrel->removed_pages;
- extVacReport.pages_frozen = vacrel->set_frozen_pages;
- extVacReport.pages_all_visible = vacrel->set_all_visible_pages;
- extVacReport.tuples_deleted = vacrel->tuples_deleted;
- extVacReport.tuples_frozen = vacrel->tuples_frozen;
- extVacReport.dead_tuples = vacrel->recently_dead_tuples + vacrel->missed_dead_tuples;
- extVacReport.index_vacuum_count = vacrel->num_index_scans;
+ extVacReport.type = PGSTAT_EXTVAC_HEAP;
+ extVacReport.heap.pages_scanned = vacrel->scanned_pages;
+ extVacReport.heap.pages_removed = vacrel->removed_pages;
+ extVacReport.heap.pages_frozen = vacrel->set_frozen_pages;
+ extVacReport.heap.pages_all_visible = vacrel->set_all_visible_pages;
+ extVacReport.heap.tuples_deleted = vacrel->tuples_deleted;
+ extVacReport.heap.tuples_frozen = vacrel->tuples_frozen;
+ extVacReport.heap.dead_tuples = vacrel->recently_dead_tuples + vacrel->missed_dead_tuples;
+ extVacReport.heap.index_vacuum_count = vacrel->num_index_scans;
/*
* Report results to the cumulative stats system, too.
@@ -2583,6 +2632,10 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ ExtVacReport extVacReport;
+
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -2601,6 +2654,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);
@@ -2609,6 +2663,13 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
istat = vac_bulkdel_one_index(&ivinfo, istat, (void *) vacrel->dead_items,
vacrel->dead_items_info);
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, &extVacReport);
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
@@ -2633,6 +2694,10 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ ExtVacReport extVacReport;
+
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -2652,12 +2717,20 @@ 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);
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, &extVacReport);
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
@@ -3274,7 +3347,7 @@ vacuum_error_callback(void *arg)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
if(geterrelevel() >= ERROR)
- pgstat_report_vacuum_error(errinfo->reloid);
+ pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_HEAP);
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3291,7 +3364,7 @@ vacuum_error_callback(void *arg)
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
if(geterrelevel() >= ERROR)
- pgstat_report_vacuum_error(errinfo->reloid);
+ pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_HEAP);
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3307,16 +3380,22 @@ 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_HEAP);
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 e84d6881403..ee759cdc5c3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1425,3 +1425,44 @@ WHERE
db.datname = current_database() AND
rel.oid = stats.relid AND
ns.oid = rel.relnamespace;
+
+CREATE VIEW pg_stat_vacuum_indexes AS
+SELECT
+ rel.oid as relid,
+ ns.nspname AS "schema",
+ rel.relname AS 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.system_time,
+ stats.user_time,
+ stats.total_time,
+
+ stats.interrupts
+FROM
+ pg_database db,
+ pg_class rel,
+ pg_namespace ns,
+ pg_stat_vacuum_indexes(rel.oid) stats
+WHERE
+ db.datname = current_database() AND
+ rel.oid = stats.relid AND
+ ns.oid = rel.relnamespace;
+
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 4e8d8f8dc77..a6f971b5a68 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -851,17 +851,33 @@ 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->pages_frozen += src->pages_frozen;
- dst->pages_all_visible += src->pages_all_visible;
- dst->tuples_deleted += src->tuples_deleted;
- dst->tuples_frozen += src->tuples_frozen;
- dst->dead_tuples += src->dead_tuples;
- dst->index_vacuum_count += src->index_vacuum_count;
+ 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_HEAP)
+ {
+ dst->heap.pages_scanned += src->heap.pages_scanned;
+ dst->heap.pages_removed += src->heap.pages_removed;
+ dst->heap.pages_frozen += src->heap.pages_frozen;
+ dst->heap.pages_all_visible += src->heap.pages_all_visible;
+ dst->heap.tuples_deleted += src->heap.tuples_deleted;
+ dst->heap.tuples_frozen += src->heap.tuples_frozen;
+ dst->heap.dead_tuples += src->heap.dead_tuples;
+ dst->heap.index_vacuum_count += src->heap.index_vacuum_count;
+ }
+ else if (dst->type == PGSTAT_EXTVAC_INDEX)
+ {
+ dst->index.pages_deleted += src->index.pages_deleted;
+ dst->index.tuples_deleted += src->index.tuples_deleted;
+ }
+ }
}
/* ------------------------------------------------------------
@@ -1108,7 +1124,8 @@ pgstat_update_snapshot(PgStat_Kind kind)
PG_TRY();
{
pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_SNAPSHOT;
- pgstat_build_snapshot(PGSTAT_KIND_RELATION);
+ if (kind == PGSTAT_KIND_RELATION)
+ pgstat_build_snapshot(PGSTAT_KIND_RELATION);
}
PG_FINALLY();
{
@@ -1163,6 +1180,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 d40d43cdb4a..5b06b04faad 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -211,7 +211,7 @@ pgstat_drop_relation(Relation rel)
* ---------
*/
void
-pgstat_report_vacuum_error(Oid tableoid)
+pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -228,6 +228,7 @@ pgstat_report_vacuum_error(Oid tableoid)
tabentry = &shtabentry->stats;
tabentry->vacuum_ext.interrupts++;
+ tabentry->vacuum_ext.type = m_type;
pgstat_unlock_entry(entry_ref);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1df271286e6..915c3e59bfa 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2070,17 +2070,19 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
}
#define EXTVACHEAPSTAT_COLUMNS 27
+#define EXTVACIDXSTAT_COLUMNS 19
+#define EXTVACSTAT_COLUMNS Max(EXTVACHEAPSTAT_COLUMNS, EXTVACIDXSTAT_COLUMNS)
static void
tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
PgStat_StatTabEntry *tabentry)
{
- Datum values[EXTVACHEAPSTAT_COLUMNS];
- bool nulls[EXTVACHEAPSTAT_COLUMNS];
+ Datum values[EXTVACSTAT_COLUMNS];
+ bool nulls[EXTVACSTAT_COLUMNS];
char buf[256];
int i = 0;
- memset(nulls, 0, EXTVACHEAPSTAT_COLUMNS * sizeof(bool));
+ memset(nulls, 0, EXTVACSTAT_COLUMNS * sizeof(bool));
values[i++] = ObjectIdGetDatum(relid);
@@ -2093,16 +2095,25 @@ tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
tabentry->vacuum_ext.blks_hit);
values[i++] = Int64GetDatum(tabentry->vacuum_ext.blks_hit);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_scanned);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_removed);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_frozen);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.pages_all_visible);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.tuples_deleted);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.tuples_frozen);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.dead_tuples);
- values[i++] = Int64GetDatum(tabentry->vacuum_ext.index_vacuum_count);
- values[i++] = Int64GetDatum(tabentry->rev_all_frozen_pages);
- values[i++] = Int64GetDatum(tabentry->rev_all_visible_pages);
+ if (tabentry->vacuum_ext.type == PGSTAT_EXTVAC_HEAP)
+ {
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.pages_scanned);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.pages_removed);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.pages_frozen);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.pages_all_visible);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.tuples_deleted);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.tuples_frozen);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.dead_tuples);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.heap.index_vacuum_count);
+ values[i++] = Int64GetDatum(tabentry->rev_all_frozen_pages);
+ values[i++] = Int64GetDatum(tabentry->rev_all_visible_pages);
+
+ }
+ else if (tabentry->vacuum_ext.type == PGSTAT_EXTVAC_INDEX)
+ {
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.index.pages_deleted);
+ values[i++] = Int64GetDatum(tabentry->vacuum_ext.index.tuples_deleted);
+ }
values[i++] = Int64GetDatum(tabentry->vacuum_ext.wal_records);
values[i++] = Int64GetDatum(tabentry->vacuum_ext.wal_fpi);
@@ -2130,10 +2141,9 @@ tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
* Get the vacuum statistics for the heap tables or indexes.
*/
static void
-pg_stats_vacuum(FunctionCallInfo fcinfo, int ncolumns)
+pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type, int ncolumns)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- Oid relid = PG_GETARG_OID(0);
PgStat_StatTabEntry *tabentry;
InitMaterializedSRF(fcinfo, 0);
@@ -2146,35 +2156,37 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, int ncolumns)
Assert(rsinfo->setDesc->natts == ncolumns);
Assert(rsinfo->setResult != NULL);
- /* Load table statistics for specified database. */
- if (OidIsValid(relid))
+ if (type == PGSTAT_EXTVAC_INDEX || type == PGSTAT_EXTVAC_HEAP)
{
- tabentry = pgstat_fetch_stat_tabentry(relid);
- if (tabentry == NULL)
- /* Table don't exists or isn't an heap relation. */
- return;
+ Oid relid = PG_GETARG_OID(0);
- tuplestore_put_for_relation(relid, rsinfo, tabentry);
- }
- else
- {
- SnapshotIterator hashiter;
- PgStat_SnapshotEntry *entry;
-
- /* Iterate the snapshot */
- InitSnapshotIterator(pgStatLocal.snapshot.stats, &hashiter);
+ /* Load table statistics for specified relation. */
+ if (OidIsValid(relid))
+ {
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+ if (tabentry == NULL || tabentry->vacuum_ext.type != type)
+ /* Table don't exists or isn't an heap relation. */
+ return;
- while ((entry = ScanStatSnapshot(pgStatLocal.snapshot.stats, &hashiter)) != NULL)
+ tuplestore_put_for_relation(relid, rsinfo, tabentry);
+ }
+ else
{
- Oid reloid;
+ SnapshotIterator hashiter;
+ PgStat_SnapshotEntry *entry;
+
+ /* Iterate the snapshot */
+ InitSnapshotIterator(pgStatLocal.snapshot.stats, &hashiter);
- CHECK_FOR_INTERRUPTS();
+ while ((entry = ScanStatSnapshot(pgStatLocal.snapshot.stats, &hashiter)) != NULL)
+ {
+ CHECK_FOR_INTERRUPTS();
- tabentry = (PgStat_StatTabEntry *) entry->data;
- reloid = entry->key.objoid;
+ tabentry = (PgStat_StatTabEntry *) entry->data;
- if (tabentry != NULL)
- tuplestore_put_for_relation(reloid, rsinfo, tabentry);
+ if (tabentry != NULL && tabentry->vacuum_ext.type == type)
+ tuplestore_put_for_relation(relid, rsinfo, tabentry);
+ }
}
}
}
@@ -2185,7 +2197,18 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, int ncolumns)
Datum
pg_stat_vacuum_tables(PG_FUNCTION_ARGS)
{
- pg_stats_vacuum(fcinfo, EXTVACHEAPSTAT_COLUMNS);
+ pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_HEAP, EXTVACHEAPSTAT_COLUMNS);
PG_RETURN_VOID();
}
+
+/*
+ * Get the vacuum statistics for the indexes.
+ */
+Datum
+pg_stat_vacuum_indexes(PG_FUNCTION_ARGS)
+{
+ pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_INDEX, EXTVACIDXSTAT_COLUMNS);
+
+ PG_RETURN_VOID();
+ }
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2023270f923..604b4f44930 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12263,4 +12263,13 @@
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,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,pages_frozen,pages_all_visible,tuples_deleted,tuples_frozen,dead_tuples,index_vacuum_count,rev_all_frozen_pages,rev_all_visible_pages,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
prosrc => 'pg_stat_vacuum_tables' },
+{ oid => '8002',
+ descr => 'pg_stat_vacuum_indexes return stats values',
+ proname => 'pg_stat_vacuum_indexes', 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,float8,float8,int4}',
+ proargmodes => '{i,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_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
+ prosrc => 'pg_stat_vacuum_indexes' }
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4492a0572c6..762b53b88ed 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -167,11 +167,20 @@ typedef struct PgStat_BackendSubEntry
PgStat_Counter sync_error_count;
} PgStat_BackendSubEntry;
+
+/* Type of ExtVacReport */
+typedef enum ExtVacReportType
+{
+ PGSTAT_EXTVAC_INVALID = 0,
+ PGSTAT_EXTVAC_HEAP = 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
@@ -203,14 +212,38 @@ typedef struct ExtVacReport
/* Interruptions on any errors. */
int32 interrupts;
- int64 pages_scanned; /* number of pages we examined */
- int64 pages_removed; /* number of pages removed by vacuum */
- int64 pages_frozen; /* number of pages marked in VM as frozen */
- int64 pages_all_visible; /* number of pages marked in VM as all-visible */
- int64 tuples_deleted; /* tuples deleted by vacuum */
- int64 tuples_frozen; /* tuples frozen up by vacuum */
- int64 dead_tuples; /* number of deleted tuples which vacuum cannot clean up by vacuum operation */
- int64 index_vacuum_count; /* number of index vacuumings */
+ 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; /* number of pages we examined */
+ int64 pages_removed; /* number of pages removed by vacuum */
+ int64 pages_frozen; /* number of pages marked in VM as frozen */
+ int64 pages_all_visible; /* number of pages marked in VM as all-visible */
+ int64 tuples_deleted; /* tuples deleted by vacuum */
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 dead_tuples; /* number of deleted tuples which vacuum cannot clean up by vacuum operation */
+ int64 index_vacuum_count; /* number of index vacuumings */
+ } heap;
+ struct
+ {
+ int64 pages_deleted; /* number of pages deleted by vacuum */
+ int64 tuples_deleted; /* tuples deleted by vacuum */
+ } index;
+ } /* per_type_stats */;
} ExtVacReport;
/* ----------
@@ -689,7 +722,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);
-extern void pgstat_report_vacuum_error(Oid tableoid);
+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/expected/vacuum-extending-in-repetable-read.out b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
index 7cdb79c0ec4..93fe15c01f9 100644
--- a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
+++ b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
@@ -9,10 +9,9 @@ step s2_print_vacuum_stats_table:
FROM pg_stat_vacuum_tables vt, pg_class c
WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
-relname |tuples_deleted|dead_tuples|tuples_frozen
---------------------------+--------------+-----------+-------------
-test_vacuum_stat_isolation| 0| 0| 0
-(1 row)
+relname|tuples_deleted|dead_tuples|tuples_frozen
+-------+--------------+-----------+-------------
+(0 rows)
step s1_begin_repeatable_read:
BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
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 7d31ddbece9..bca3e8516b2 100644
--- a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -48,4 +48,4 @@ permutation
s1_commit
s2_checkpoint
s2_vacuum
- s2_print_vacuum_stats_table
+ s2_print_vacuum_stats_table
\ No newline at end of file
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 9ae743eae0c..5d72b970b03 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -32,10 +32,11 @@ WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
prokind NOT IN ('f', 'a', 'w', 'p') OR
provolatile NOT IN ('i', 's', 'v') OR
proparallel NOT IN ('s', 'r', 'u');
- oid | proname
-------+-----------------------
+ oid | proname
+------+------------------------
8001 | pg_stat_vacuum_tables
-(1 row)
+ 8002 | pg_stat_vacuum_indexes
+(2 rows)
-- prosrc should never be null; it can be empty only if prosqlbody isn't null
SELECT p1.oid, p1.proname
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index cc0b5bde0a1..265eb597d69 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2229,6 +2229,32 @@ pg_stat_user_tables| SELECT relid,
autoanalyze_count
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 schema,
+ 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.system_time,
+ stats.user_time,
+ stats.total_time,
+ stats.interrupts
+ FROM pg_database db,
+ pg_class rel,
+ pg_namespace ns,
+ LATERAL pg_stat_vacuum_indexes(db.oid, 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, system_time, user_time, total_time, interrupts)
+ WHERE ((db.datname = current_database()) AND (rel.oid = stats.relid) AND (ns.oid = rel.relnamespace));
pg_stat_vacuum_tables| SELECT rel.oid AS relid,
ns.nspname AS schema,
rel.relname,
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..a0da8d25f1a
--- /dev/null
+++ b/src/test/regress/expected/vacuum_index_statistics.out
@@ -0,0 +1,158 @@
+--
+-- 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)
+
+-- 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
+--------------------------
+
+(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
+---------+----------+---------------+----------------
+(0 rows)
+
+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/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_statistics.out
index 1a7d04b0590..b85a5cab9af 100644
--- a/src/test/regress/expected/vacuum_tables_statistics.out
+++ b/src/test/regress/expected/vacuum_tables_statistics.out
@@ -37,8 +37,7 @@ FROM pg_stat_vacuum_tables vt, pg_class c
WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
relname | pages_frozen | tuples_deleted | relpages | pages_scanned | pages_removed
---------+--------------+----------------+----------+---------------+---------------
- vestat | 0 | 0 | 455 | 0 | 0
-(1 row)
+(0 rows)
SELECT relpages AS rp
FROM pg_class c
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f8a4bcccc9d..b9408a43f71 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -140,4 +140,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..9113fd26e6f
--- /dev/null
+++ b/src/test/regress/sql/vacuum_index_statistics.sql
@@ -0,0 +1,128 @@
+--
+-- 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
+-- 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();
+
+\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] v6-0003-Machinery-for-grabbing-an-extended-vacuum-statistics.patch (19.9K, 4-v6-0003-Machinery-for-grabbing-an-extended-vacuum-statistics.patch)
download | inline diff:
From b33b32ec0fa31a2ed4349adb9e087722cd23484b Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Sun, 25 Aug 2024 17:42:28 +0300
Subject: [PATCH 3/4] Machinery for grabbing an extended vacuum statistics on
databases. It transmits vacuum statistical information about each table and
accumulates it for the database which the table belonged.
---
src/backend/catalog/system_views.sql | 28 +++++++
src/backend/utils/activity/pgstat.c | 2 +
src/backend/utils/activity/pgstat_database.c | 1 +
src/backend/utils/activity/pgstat_relation.c | 16 ++++
src/backend/utils/adt/pgstatfuncs.c | 75 +++++++++++++++++-
src/include/catalog/pg_proc.dat | 11 ++-
src/include/pgstat.h | 3 +-
src/test/regress/expected/opr_sanity.out | 7 +-
src/test/regress/expected/rules.out | 20 ++++-
...ut => vacuum_tables_and_db_statistics.out} | 78 +++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
...ql => vacuum_tables_and_db_statistics.sql} | 66 +++++++++++++++-
12 files changed, 300 insertions(+), 9 deletions(-)
rename src/test/regress/expected/{vacuum_tables_statistics.out => vacuum_tables_and_db_statistics.out} (76%)
rename src/test/regress/sql/{vacuum_tables_statistics.sql => vacuum_tables_and_db_statistics.sql} (78%)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ee759cdc5c3..76a2ffff2bb 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1466,3 +1466,31 @@ WHERE
rel.oid = stats.relid AND
ns.oid = rel.relnamespace;
+CREATE VIEW pg_stat_vacuum_database AS
+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.system_time,
+ stats.user_time,
+ stats.total_time,
+
+ stats.interrupts
+FROM
+ pg_database db LEFT JOIN pg_stat_vacuum_database(db.oid) stats
+ON
+ db.oid = stats.dboid;
+
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index a6f971b5a68..b633408777e 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -1126,6 +1126,8 @@ pgstat_update_snapshot(PgStat_Kind kind)
pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_SNAPSHOT;
if (kind == PGSTAT_KIND_RELATION)
pgstat_build_snapshot(PGSTAT_KIND_RELATION);
+ else if (kind == PGSTAT_KIND_DATABASE)
+ pgstat_build_snapshot(PGSTAT_KIND_DATABASE);
}
PG_FINALLY();
{
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 29bc0909748..a060d1a4042 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -430,6 +430,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 5b06b04faad..cc09aba571f 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -217,6 +217,7 @@ pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
PgStatShared_Relation *shtabentry;
PgStat_StatTabEntry *tabentry;
Oid dboid = MyDatabaseId;
+ PgStat_StatDBEntry *dbentry; /* pending database entry */
if (!pgstat_track_counts)
return;
@@ -230,6 +231,10 @@ pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
tabentry->vacuum_ext.interrupts++;
tabentry->vacuum_ext.type = m_type;
pgstat_unlock_entry(entry_ref);
+
+ dbentry = pgstat_prep_database_pending(dboid);
+ dbentry->vacuum_ext.interrupts++;
+ dbentry->vacuum_ext.type = m_type;
}
/*
@@ -243,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;
@@ -296,6 +302,16 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
* VACUUM command has processed all tables and committed.
*/
pgstat_flush_io(false);
+ 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);
+ }
+
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 915c3e59bfa..9a9b6f807bf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2071,8 +2071,49 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
#define EXTVACHEAPSTAT_COLUMNS 27
#define EXTVACIDXSTAT_COLUMNS 19
+#define EXTVACDBSTAT_COLUMNS 15
#define EXTVACSTAT_COLUMNS Max(EXTVACHEAPSTAT_COLUMNS, EXTVACIDXSTAT_COLUMNS)
+static void
+tuplestore_put_for_database(Oid dbid, ReturnSetInfo *rsinfo,
+ PgStatShared_Database *dbentry)
+{
+ Datum values[EXTVACDBSTAT_COLUMNS];
+ bool nulls[EXTVACDBSTAT_COLUMNS];
+ char buf[256];
+ int i = 0;
+
+ memset(nulls, 0, EXTVACDBSTAT_COLUMNS * sizeof(bool));
+
+ values[i++] = ObjectIdGetDatum(dbid);
+
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.total_blks_read);
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.total_blks_hit);
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.total_blks_dirtied);
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.total_blks_written);
+
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.wal_records);
+ values[i++] = Int64GetDatum(dbentry->stats.vacuum_ext.wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, dbentry->stats.vacuum_ext.wal_bytes);
+ values[i++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.blk_read_time);
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.blk_write_time);
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.delay_time);
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.system_time);
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.user_time);
+ values[i++] = Float8GetDatum(dbentry->stats.vacuum_ext.total_time);
+ values[i++] = Int32GetDatum(dbentry->stats.vacuum_ext.interrupts);
+
+ Assert(i == rsinfo->setDesc->natts);
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
static void
tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
PgStat_StatTabEntry *tabentry)
@@ -2189,6 +2230,26 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type, int ncolumns)
}
}
}
+ else if (type == PGSTAT_EXTVAC_DB)
+ {
+ PgStatShared_Database *dbentry;
+ PgStat_EntryRef *entry_ref;
+ Oid dbid = PG_GETARG_OID(0);
+
+ if (OidIsValid(dbid))
+ {
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE,
+ dbid, InvalidOid, false);
+ dbentry = (PgStatShared_Database *) entry_ref->shared_stats;
+
+ if (dbentry == NULL)
+ /* Table doesn't exist or isn't a heap relation */
+ return;
+
+ tuplestore_put_for_database(dbid, rsinfo, dbentry);
+ pgstat_unlock_entry(entry_ref);
+ }
+ }
}
/*
@@ -2211,4 +2272,16 @@ pg_stat_vacuum_indexes(PG_FUNCTION_ARGS)
pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_INDEX, EXTVACIDXSTAT_COLUMNS);
PG_RETURN_VOID();
- }
\ No newline at end of file
+ }
+
+
+/*
+ * Get the vacuum statistics for the database.
+ */
+Datum
+pg_stat_vacuum_database(PG_FUNCTION_ARGS)
+{
+ pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_DB, EXTVACDBSTAT_COLUMNS);
+
+ PG_RETURN_VOID();
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 604b4f44930..d4696d0c055 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12271,5 +12271,14 @@
proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,float8,float8,int4}',
proargmodes => '{i,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_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
- prosrc => 'pg_stat_vacuum_indexes' }
+ prosrc => 'pg_stat_vacuum_indexes' },
+{ oid => '8003',
+ descr => 'pg_stat_vacuum_database return stats values',
+ proname => 'pg_stat_vacuum_database', provolatile => 's', prorettype => 'record',proisstrict => 'f',
+ proretset => 't',
+ proargtypes => 'oid',
+ proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,float8,float8,int4}',
+ proargmodes => '{i,o,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,system_time,user_time,total_time,interrupts}',
+ prosrc => 'pg_stat_vacuum_database' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 762b53b88ed..110e9472f3c 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -173,7 +173,8 @@ typedef enum ExtVacReportType
{
PGSTAT_EXTVAC_INVALID = 0,
PGSTAT_EXTVAC_HEAP = 1,
- PGSTAT_EXTVAC_INDEX = 2
+ PGSTAT_EXTVAC_INDEX = 2,
+ PGSTAT_EXTVAC_DB = 3,
} ExtVacReportType;
/* ----------
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 5d72b970b03..7026de157e4 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -32,11 +32,12 @@ WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
prokind NOT IN ('f', 'a', 'w', 'p') OR
provolatile NOT IN ('i', 's', 'v') OR
proparallel NOT IN ('s', 'r', 'u');
- oid | proname
-------+------------------------
+ oid | proname
+------+-------------------------
8001 | pg_stat_vacuum_tables
8002 | pg_stat_vacuum_indexes
-(2 rows)
+ 8003 | pg_stat_vacuum_database
+(3 rows)
-- prosrc should never be null; it can be empty only if prosqlbody isn't null
SELECT p1.oid, p1.proname
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 265eb597d69..5d7f73c25fd 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2229,6 +2229,24 @@ pg_stat_user_tables| SELECT relid,
autoanalyze_count
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.system_time,
+ stats.user_time,
+ stats.total_time,
+ stats.interrupts
+ FROM (pg_database db
+ LEFT JOIN LATERAL pg_stat_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, system_time, user_time, total_time, interrupts) ON ((db.oid = stats.dboid)));
pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
ns.nspname AS schema,
rel.relname,
@@ -2253,7 +2271,7 @@ pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
FROM pg_database db,
pg_class rel,
pg_namespace ns,
- LATERAL pg_stat_vacuum_indexes(db.oid, 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, system_time, user_time, total_time, interrupts)
+ LATERAL pg_stat_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, system_time, user_time, total_time, interrupts)
WHERE ((db.datname = current_database()) AND (rel.oid = stats.relid) AND (ns.oid = rel.relnamespace));
pg_stat_vacuum_tables| SELECT rel.oid AS relid,
ns.nspname AS schema,
diff --git a/src/test/regress/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
similarity index 76%
rename from src/test/regress/expected/vacuum_tables_statistics.out
rename to src/test/regress/expected/vacuum_tables_and_db_statistics.out
index b85a5cab9af..ec0cf97e2da 100644
--- a/src/test/regress/expected/vacuum_tables_statistics.out
+++ b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
@@ -6,6 +6,9 @@
-- number of frozen and visible pages removed by backend.
-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
--
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
-- conditio sine qua non
SHOW track_counts; -- must be on
track_counts
@@ -196,4 +199,79 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
t | t | t | t
(1 row)
+-- 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,
+ user_time > 0 AS user_time,
+ 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 | user_time | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
+-- 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;
+-- 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,
+ user_time > 0 AS user_time,
+ 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 | user_time | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t | t
+(1 row)
+
+\c regression_statistic_vacuum_db
+RESET vacuum_freeze_min_age;
+RESET vacuum_freeze_table_age;
DROP TABLE vestat CASCADE;
+\c regression_statistic_vacuum_db1;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_database(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;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index b9408a43f71..129b1102028 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -141,4 +141,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_tables_statistics.sql b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
similarity index 78%
rename from src/test/regress/sql/vacuum_tables_statistics.sql
rename to src/test/regress/sql/vacuum_tables_and_db_statistics.sql
index 41e387dd304..ed9bb852625 100644
--- a/src/test/regress/sql/vacuum_tables_statistics.sql
+++ b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
@@ -7,6 +7,10 @@
-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
--
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+
-- conditio sine qua non
SHOW track_counts; -- must be on
-- not enabled by default, but we want to test it...
@@ -155,4 +159,64 @@ VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
SELECT pages_frozen = :pf AS pages_frozen,pages_all_visible = :pv AS pages_all_visible,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 WHERE relname = 'vestat';
-DROP TABLE vestat CASCADE;
\ No newline at end of file
+-- 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,
+ user_time > 0 AS user_time,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+
+DROP TABLE vestat CASCADE;
+
+-- 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;
+
+-- 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,
+ user_time > 0 AS user_time,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+
+\c regression_statistic_vacuum_db
+
+RESET vacuum_freeze_min_age;
+RESET vacuum_freeze_table_age;
+DROP TABLE vestat CASCADE;
+
+\c regression_statistic_vacuum_db1;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_database(0)
+WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
--
2.34.1
[text/x-patch] v6-0004-Add-documentation-about-the-system-views-that-are-us.patch (24.2K, 5-v6-0004-Add-documentation-about-the-system-views-that-are-us.patch)
download | inline diff:
From 0cbb749c24b3c4ee9891c078a4906ee3f24da762 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Sun, 25 Aug 2024 17:47:55 +0300
Subject: [PATCH 4/4] Add documentation about the system views that are used in
the machinery of vacuum statistics.
---
doc/src/sgml/system-views.sgml | 747 +++++++++++++++++++++++++++++++++
1 file changed, 747 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 634a4c0fab4..8cbccdc4a4d 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5064,4 +5064,751 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</table>
</sect1>
+<sect1 id="view-pg-stats-vacuum-database">
+ <title><structname>pg_stat_vacuum_database</structname></title>
+
+ <indexterm zone="view-pg-stats-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>interrupts</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-stats-vacuum-indexes">
+ <title><structname>pg_stat_vacuum_indexes</structname></title>
+
+ <indexterm zone="view-pg-stats-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>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>interrupts</structfield> <type>float8</type>
+ </para>
+ <para>
+ Number of times vacuum operations performed on this index
+ were interrupted on any errors
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="view-pg-stats-vacuum-tables">
+ <title><structname>pg_stat_vacuum_tables</structname></title>
+
+ <indexterm zone="view-pg-stats-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 database blocks dirtied by vacuum operations
+ performed on this table
+ </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>pages_frozen</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times vacuum operations marked pages of this table
+ as all-frozen in the visibility map
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pages_all_visible</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times vacuum operations marked pages of this table
+ as all-visible 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>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>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>rev_all_frozen_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times the all-frozen mark in the visibility map
+ was removed for pages of this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rev_all_visible_pages</structfield> <type>int8</type>
+ </para>
+ <para>
+ Number of times the all-visible mark in the visibility map
+ was removed for pages of this table
+ </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>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>interrupts</structfield> <type>float8</type>
+ </para>
+ <para>
+ Number of times vacuum operations performed on this table
+ were interrupted on any errors
+ </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 (34+ 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]
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