public inbox for [email protected]
help / color / mirror / Atom feedFrom: Imseih (AWS), Sami <[email protected]>
To: Bossart, Nathan <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Add index scan progress to pg_stat_progress_vacuum
Date: Thu, 17 Feb 2022 13:52:23 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAH2-Wzmz4Z+UYqRgYHuyxYHha4Gn-aBprydmG0n+m6J2J_ABSA@mail.gmail.com>
<CAD21AoCKOFjU8XpPjXgfNrP3-V71AL5NKY+3or7eeRnNMKqi0g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAD21AoCWFyvtf_EHHLWqejDjW0=apRugn_=UkpGzY5OgKhkH2g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
The change has been broken up as 3 separate patches.
0007-Expose-progress-for-the-vacuuming-indexes-and-cleani.patch - Introduces 2 new columns to pg_stat_progress_vacuum, indexes_total and indexes_processed. These 2 columns will provide progress on the index vacuuming/cleanup.
0001-Expose-the-index-being-processed-in-the-vacuuming-in.patch - Introduces a new view called pg_stat_prgoress_vacuum_index. This view tracks the index being vacuumed/cleaned and the total number of index tuples removed.
0001-Rename-index_vacuum_count-to-index_vacuum_cycle_coun.patch - Renames the existing index_vacuum_count to index_vacuum_cycle_count in pg_stat_progress_vacuum. Due to the other changes, it makes sense to include "cycle" in the column name to be crystal clear that the column refers to the index cycle count.
Thanks
On 2/10/22, 1:39 PM, "Imseih (AWS), Sami" <[email protected]> wrote:
Attached is the latest version of the patch to deal with the changes in the recent commit aa64f23b02924724eafbd9eadbf26d85df30a12b
On 2/1/22, 2:32 PM, "Imseih (AWS), Sami" <[email protected]> wrote:
After speaking with Nathan offline, A few changes have been made to the patch.
As mentioned earlier in the thread, tracking how many indexes are processed in PARALLEL vacuum mode is not very straightforward since only the workers or leader process have ability to inspect the Vacuum shared parallel state.
The latest version of the patch introduces a shared memory to track indexes vacuumed/cleaned by each worker ( or leader ) in a PARALLEL vacuum. In order to present this data in the pg_stat_progress_vacuum view, the value of the new column "indexes_processed" is retrieved from shared memory by pg_stat_get_progress_info. For non-parallel vacuums, the value of "indexes_processed" is retrieved from the backend progress array directly.
The patch also includes the changes to implement the new view pg_stat_progress_vacuum_index which exposes the index being vacuumed/cleaned up.
postgres=# \d+ pg_stat_progress_vacuum ;
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------------+---------+-----------+----------+---------+----------+-------------
pid | integer | | | | plain |
datid | oid | | | | plain |
datname | name | | | | plain |
relid | oid | | | | plain |
phase | text | | | | extended |
heap_blks_total | bigint | | | | plain |
heap_blks_scanned | bigint | | | | plain |
heap_blks_vacuumed | bigint | | | | plain |
index_vacuum_count | bigint | | | | plain |
max_dead_tuples | bigint | | | | plain |
num_dead_tuples | bigint | | | | plain |
indexes_total | bigint | | | | plain | <<<-- new column
indexes_processed | bigint | | | | plain | <<<-- new column
<<<--- new view --->>>
postgres=# \d pg_stat_progress_vacuum_index
View "pg_catalog.pg_stat_progress_vacuum_index"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
indexrelid | bigint | | |
leader_pid | bigint | | |
phase | text | | |
tuples_removed | bigint | | |
On 1/26/22, 8:07 PM, "Imseih (AWS), Sami" <[email protected]> wrote:
Attached is the latest patch and associated documentation.
This version addresses the index_ordinal_position column confusion. Rather than displaying the index position, the pg_stat_progress_vacuum view now has 2 new column(s):
index_total - this column will show the total number of indexes to be vacuumed
index_complete_count - this column will show the total number of indexes processed so far. In order to deal with the parallel vacuums, the parallel_workers ( planned workers ) value had to be exposed and each backends performing an index vacuum/cleanup in parallel had to advertise the number of indexes it vacuumed/cleaned. The # of indexes vacuumed for the parallel cleanup can then be derived the pg_stat_progress_vacuum view.
postgres=# \d pg_stat_progress_vacuum
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
index_total | bigint | | |. <<<---------------------
index_complete_count | numeric | | |. <<<---------------------
The pg_stat_progress_vacuum_index view includes:
Indexrelid - the currently vacuumed index
Leader_pid - the pid of the leader process. NULL if the process is the leader or vacuum is not parallel
tuples_removed - the amount of indexes tuples removed. The user can use this column to see that the index vacuum has movement.
postgres=# \d pg_stat_progress_vacuum_index
View "pg_catalog.pg_stat_progress_vacuum_index"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
indexrelid | bigint | | |
phase | text | | |
leader_pid | bigint | | |
tuples_removed | bigint | | |
On 1/12/22, 9:52 PM, "Imseih (AWS), Sami" <[email protected]> wrote:
On 1/12/22, 1:28 PM, "Bossart, Nathan" <[email protected]> wrote:
On 1/11/22, 11:46 PM, "Masahiko Sawada" <[email protected]> wrote:
> Regarding the new pg_stat_progress_vacuum_index view, why do we need
> to have a separate view? Users will have to check two views. If this
> view is expected to be used together with and joined to
> pg_stat_progress_vacuum, why don't we provide one view that has full
> information from the beginning? Especially, I think it's not useful
> that the total number of indexes to vacuum (num_indexes_to_vacuum
> column) and the current number of indexes that have been vacuumed
> (index_ordinal_position column) are shown in separate views.
> I suppose we could add all of the new columns to
> pg_stat_progress_vacuum and just set columns to NULL as appropriate.
> But is that really better than having a separate view?
To add, since a vacuum can utilize parallel worker processes + the main vacuum process to perform index vacuuming, it made sense to separate the backends doing index vacuum/cleanup in a separate view.
Besides what Nathan suggested, the only other clean option I can think of is to perhaps create a json column in pg_stat_progress_vacuum which will include all the new fields. My concern with this approach is that it will make usability, to flatten the json, difficult for users.
> Also, I’m not sure how useful index_tuples_removed is; what can we
> infer from this value (without a total number)?
> I think the idea was that you can compare it against max_dead_tuples
> and num_dead_tuples to get an estimate of the current cycle progress.
> Otherwise, it just shows that progress is being made.
The main purpose is to really show that the "index vacuum" phase is actually making progress. Note that for certain types of indexes, i.e. GIN/GIST the number of tuples_removed will end up exceeding the number of num_dead_tuples.
Nathan
[0] https://postgr.es/m/7874FB21-FAA5-49BD-8386-2866552656C7%40amazon.com
Attachments:
[application/octet-stream] 0007-Expose-progress-for-the-vacuuming-indexes-and-cleani.patch (17.2K, ../[email protected]/2-0007-Expose-progress-for-the-vacuuming-indexes-and-cleani.patch)
download | inline diff:
From 76d8a1bfd5207d28a4e9fe98a0e1ea7c096d70aa Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)" <[email protected]>
Date: Thu, 17 Feb 2022 04:21:04 +0000
Subject: [PATCH 1/1] Expose progress for the "vacuuming indexes" and "cleaning
up indexes" phase of a VACUUM operation.
Add 2 new columns to pg_stat_progress_vacuum. The columns are
indexes_total as the total indexes to be vacuumed or cleaned and
indexes_processed as the number of indexes vacuumed or cleaned up so
far.
Author: Sami Imseih, based on suggestions by Nathan Bossart, Peter Geoghegan and Masahiko Sawada
Reviewed by: Nathan Bossart, Justin Pryzby
---
doc/src/sgml/monitoring.sgml | 22 +++
src/backend/access/heap/vacuumlazy.c | 207 +++++++++++++++++++++++++-
src/backend/catalog/system_views.sql | 3 +-
src/backend/commands/vacuumparallel.c | 7 +
src/backend/storage/ipc/ipci.c | 3 +
src/backend/utils/adt/pgstatfuncs.c | 12 ++
src/include/commands/progress.h | 2 +
src/include/commands/vacuum.h | 8 +
src/test/regress/expected/rules.out | 4 +-
9 files changed, 261 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index bf7625d988..04440dfa88 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6278,6 +6278,28 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
Number of dead tuples collected since the last index vacuum cycle.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indexes_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of indexes to be processed in the
+ <literal>vacuuming indexes</literal> or <literal>cleaning up indexes</literal> phase
+ of the vacuum.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indexes_processed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of indexes processed in the
+ <literal>vacuuming indexes</literal> or <literal>cleaning up indexes</literal> phase.
+ At the start of an index vacuum cycle, this value is set to <literal>0</literal>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 242511a235..1198677bc0 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -58,6 +58,7 @@
#include "postmaster/autovacuum.h"
#include "storage/bufmgr.h"
#include "storage/freespace.h"
+#include "storage/ipc.h"
#include "storage/lmgr.h"
#include "tcop/tcopprot.h"
#include "utils/lsyscache.h"
@@ -245,6 +246,26 @@ typedef struct LVSavedErrInfo
VacErrPhase phase;
} LVSavedErrInfo;
+/*
+ * Structs for tracking shared Progress information
+ * amongst worker ( and leader ) processes of a vacuum.
+ */
+typedef struct VacOneWorkerProgressInfo
+{
+ int leader_pid;
+ int indexes_processed;
+} VacOneWorkerProgressInfo;
+
+typedef struct VacWorkerProgressInfo
+{
+ int num_vacuums; /* number of active VACUUMS with parallel workers */
+ int max_vacuums; /* max number of VACUUMS with parallel workers */
+ slock_t mutex;
+ VacOneWorkerProgressInfo vacuums[FLEXIBLE_ARRAY_MEMBER];
+} VacWorkerProgressInfo;
+
+static VacWorkerProgressInfo *vacworkerprogress;
+
/* non-export function prototypes */
static void lazy_scan_heap(LVRelState *vacrel, int nworkers);
@@ -420,6 +441,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->rel = rel;
vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
&vacrel->indrels);
+ /* Advertise the number of indexes we are vacuuming */
+ pgstat_progress_update_param(PROGRESS_VACUUM_TOTAL_INDEXES, vacrel->nindexes);
if (instrument && vacrel->nindexes > 0)
{
/* Copy index names used by instrumentation (not error reporting) */
@@ -2328,6 +2351,13 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
lazy_vacuum_one_index(indrel, istat, vacrel->old_live_tuples,
vacrel);
+ /*
+ * For the non-parallel variant of a vacuum, the array position
+ * of the index determines how many indexes are processed so far.
+ * Add 1 to the posititon as vacrel->nindexes is a 0-based array.
+ */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
+
if (lazy_check_wraparound_failsafe(vacrel))
{
/* Wraparound emergency -- end current index scan */
@@ -2338,9 +2368,20 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
}
else
{
- /* Outsource everything to parallel variant */
- parallel_vacuum_bulkdel_all_indexes(vacrel->pvs, vacrel->old_live_tuples,
- vacrel->num_index_scans);
+ /*
+ * parallel_vacuum_bulkdel_all_indexes will call vacuum_worker_update
+ * which updates shared memory for the index progress. To ensure shared
+ * memory cleanup, do the work with PG_ENSURE_ERROR_CLEANUP.
+ */
+ PG_ENSURE_ERROR_CLEANUP(vacuum_worker_end_callback, Int32GetDatum(MyProcPid));
+ {
+
+ parallel_vacuum_bulkdel_all_indexes(vacrel->pvs,
+ vacrel->old_live_tuples,
+ vacrel->num_index_scans);
+ }
+ PG_END_ENSURE_ERROR_CLEANUP(vacuum_worker_end_callback, Int32GetDatum(MyProcPid));
+ vacuum_worker_end(MyProcPid);
/*
* Do a postcheck to consider applying wraparound failsafe now. Note
@@ -2350,6 +2391,12 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
allindexes = false;
}
+ /*
+ * We're done with index vacuuming.
+ * Set the total number of indexes completed as the total number of indexes
+ */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, vacrel->nindexes) ;
+
/*
* We delete all LP_DEAD items from the first heap pass in all indexes on
* each call here (except calls where we choose to do the failsafe). This
@@ -2675,15 +2722,34 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
vacrel->indstats[idx] =
lazy_cleanup_one_index(indrel, istat, reltuples,
estimated_count, vacrel);
+
+ /* See the lazy_vacuum_all_indexes comments */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
}
}
else
{
- /* Outsource everything to parallel variant */
- parallel_vacuum_cleanup_all_indexes(vacrel->pvs, vacrel->new_rel_tuples,
+ /*
+ * Outsource everything to parallel variant
+ *
+ * See the lazy_vacuum_all_indexes comments
+ */
+ PG_ENSURE_ERROR_CLEANUP(vacuum_worker_end_callback, Int32GetDatum(MyProcPid));
+ {
+ parallel_vacuum_cleanup_all_indexes(vacrel->pvs, vacrel->new_rel_tuples,
vacrel->num_index_scans,
(vacrel->scanned_pages < vacrel->rel_pages));
+ }
+ PG_END_ENSURE_ERROR_CLEANUP(vacuum_worker_end_callback, Int32GetDatum(MyProcPid));
+ vacuum_worker_end(MyProcPid);
}
+
+ /*
+ * We're done with index cleanup.
+ * Set the total number of indexes completed as the total number of indexes
+ */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, vacrel->nindexes) ;
+
}
/*
@@ -3464,3 +3530,134 @@ restore_vacuum_error_info(LVRelState *vacrel,
vacrel->offnum = saved_vacrel->offnum;
vacrel->phase = saved_vacrel->phase;
}
+
+void
+vacuum_worker_end(int leader_pid)
+{
+ SpinLockAcquire(&vacworkerprogress->mutex);
+ for (int i = 0; i < vacworkerprogress->num_vacuums; i++)
+ {
+ VacOneWorkerProgressInfo *vac = &vacworkerprogress->vacuums[i];
+
+ if (vac->leader_pid == leader_pid)
+ {
+ *vac = vacworkerprogress->vacuums[vacworkerprogress->num_vacuums - 1];
+ vacworkerprogress->num_vacuums--;
+ SpinLockRelease(&vacworkerprogress->mutex);
+ break;
+ }
+ }
+ SpinLockRelease(&vacworkerprogress->mutex);
+}
+
+/*
+ * vacuum_worker_end wrapped as an on_shmem_exit callback function
+ */
+void
+vacuum_worker_end_callback(int code, Datum arg)
+{
+ vacuum_worker_end(DatumGetInt32(arg));
+}
+
+/*
+ * vacuum_worker_update sets the number of indexes processed so far
+ * in a parallel vacuum.
+ */
+void
+vacuum_worker_update(int leader_pid)
+{
+ VacOneWorkerProgressInfo *vac;
+
+ SpinLockAcquire(&vacworkerprogress->mutex);
+
+ for (int i = 0; i < vacworkerprogress->num_vacuums; i++)
+ {
+ int next_leader_pid;
+
+ vac = &vacworkerprogress->vacuums[i];
+
+ next_leader_pid = vac->leader_pid;
+
+ if (next_leader_pid == leader_pid)
+ {
+ vac->indexes_processed++;
+ SpinLockRelease(&vacworkerprogress->mutex);
+ return;
+ }
+ }
+
+ if (vacworkerprogress->num_vacuums >= vacworkerprogress->max_vacuums)
+ {
+ SpinLockRelease(&vacworkerprogress->mutex);
+ elog(ERROR, "out of vacuum worker progress slots");
+ }
+
+ vac = &vacworkerprogress->vacuums[vacworkerprogress->num_vacuums];
+ vac->leader_pid = leader_pid;
+ vac->indexes_processed = 1;
+ vacworkerprogress->num_vacuums++;
+ SpinLockRelease(&vacworkerprogress->mutex);
+}
+
+/*
+ * vacuum_progress_cb updates the number of indexes that have been
+ * vacuumed or cleaned up in a parallel vacuum.
+ */
+void
+vacuum_progress_cb(Datum *values, int offset)
+{
+ VacOneWorkerProgressInfo *vac;
+ int leader_pid = values[0];
+
+ /* If we are vacuuming in parallel, set the number of indexes vacuumed
+ * from the shared memory counter.
+ * */
+ for (int i = 0; i < vacworkerprogress->num_vacuums; i++)
+ {
+ int next_leader_pid;
+
+ vac = &vacworkerprogress->vacuums[i];
+
+ next_leader_pid = vac->leader_pid;
+
+ if (next_leader_pid == leader_pid)
+ values[PROGRESS_VACUUM_INDEXES_COMPLETED + offset] = vac->indexes_processed;
+ }
+}
+
+/*
+ * VacuumWorkerProgressShmemSize --- report amount of shared memory space needed
+ */
+Size
+VacuumWorkerProgressShmemSize(void)
+{
+ Size size;
+
+ size = offsetof(VacWorkerProgressInfo, vacuums);
+ size = add_size(size, mul_size(GetMaxBackends(), sizeof(VacOneWorkerProgressInfo)));
+ return size;
+}
+
+/*
+ * VacuumWorkerProgressShmemInit --- initialize this module's shared memory
+ */
+void
+VacuumWorkerProgressShmemInit(void)
+{
+ bool found;
+
+ vacworkerprogress = (VacWorkerProgressInfo *) ShmemInitStruct("Vacuum Worker Progress Stats",
+ VacuumWorkerProgressShmemSize(),
+ &found);
+
+ if (!IsUnderPostmaster)
+ {
+ /* Initialize shared memory area */
+ Assert(!found);
+
+ vacworkerprogress->max_vacuums = GetMaxBackends();
+ SpinLockInit(&vacworkerprogress->mutex);
+ }
+ else
+ Assert(found);
+}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3cb69b1f87..eaa0508c0b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1126,7 +1126,8 @@ CREATE VIEW pg_stat_progress_vacuum AS
END AS phase,
S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
- S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples
+ S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples,
+ S.param8 AS indexes_total, S.param9 AS indexes_processed
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 974a29e7a9..9b465e12cc 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -29,6 +29,7 @@
#include "access/amapi.h"
#include "access/table.h"
#include "catalog/index.h"
+#include "commands/progress.h"
#include "commands/vacuum.h"
#include "optimizer/paths.h"
#include "pgstat.h"
@@ -101,6 +102,9 @@ typedef struct PVShared
/* Counter for vacuuming and cleanup */
pg_atomic_uint32 idx;
+
+ /* Leader PID of the vacuum */
+ int leader_pid;
} PVShared;
/* Status used during parallel index vacuum or cleanup */
@@ -357,6 +361,7 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
(nindexes_mwm > 0) ?
maintenance_work_mem / Min(parallel_workers, nindexes_mwm) :
maintenance_work_mem;
+ shared->leader_pid = MyProcPid;
pg_atomic_init_u32(&(shared->cost_balance), 0);
pg_atomic_init_u32(&(shared->active_nworkers), 0);
@@ -844,9 +849,11 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
{
case PARALLEL_INDVAC_STATUS_NEED_BULKDELETE:
istat_res = vac_bulkdel_one_index(&ivinfo, istat, pvs->dead_items);
+ vacuum_worker_update(pvs->shared->leader_pid);
break;
case PARALLEL_INDVAC_STATUS_NEED_CLEANUP:
istat_res = vac_cleanup_one_index(&ivinfo, istat);
+ vacuum_worker_update(pvs->shared->leader_pid);
break;
default:
elog(ERROR, "unexpected parallel vacuum index status %d for index \"%s\"",
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index cd4ebe2fc5..a4bd6a14a3 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -24,6 +24,7 @@
#include "access/twophase.h"
#include "access/xlogrecovery.h"
#include "commands/async.h"
+#include "commands/vacuum.h"
#include "miscadmin.h"
#include "pgstat.h"
#include "postmaster/autovacuum.h"
@@ -145,6 +146,7 @@ CalculateShmemSize(int *num_semaphores)
size = add_size(size, BTreeShmemSize());
size = add_size(size, SyncScanShmemSize());
size = add_size(size, AsyncShmemSize());
+ size = add_size(size, VacuumWorkerProgressShmemSize());
#ifdef EXEC_BACKEND
size = add_size(size, ShmemBackendArraySize());
#endif
@@ -296,6 +298,7 @@ CreateSharedMemoryAndSemaphores(void)
BTreeShmemInit();
SyncScanShmemInit();
AsyncShmemInit();
+ VacuumWorkerProgressShmemInit();
#ifdef EXEC_BACKEND
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 15cb17ace4..3d90b23ce4 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -18,6 +18,7 @@
#include "access/xlog.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
+#include "commands/vacuum.h"
#include "common/ip.h"
#include "funcapi.h"
#include "miscadmin.h"
@@ -452,6 +453,10 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
/*
* Returns command progress information for the named command.
+ *
+ * A command type can optionally define a callback function
+ * which will derive Datum values rather than use values
+ * directly from the backends progress array.
*/
Datum
pg_stat_get_progress_info(PG_FUNCTION_ARGS)
@@ -466,6 +471,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
MemoryContext per_query_ctx;
MemoryContext oldcontext;
+ void (*callback)(Datum *, int) = NULL;
/* check to see if caller supports us returning a tuplestore */
if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
@@ -483,7 +489,10 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
/* Translate command name into command type code. */
if (pg_strcasecmp(cmd, "VACUUM") == 0)
+ {
cmdtype = PROGRESS_COMMAND_VACUUM;
+ callback = vacuum_progress_cb;
+ }
else if (pg_strcasecmp(cmd, "ANALYZE") == 0)
cmdtype = PROGRESS_COMMAND_ANALYZE;
else if (pg_strcasecmp(cmd, "CLUSTER") == 0)
@@ -552,6 +561,9 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
nulls[i + 3] = true;
}
+ if (callback)
+ callback(values, 3);
+
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index a28938caf4..e4f3cd9133 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -25,6 +25,8 @@
#define PROGRESS_VACUUM_NUM_INDEX_VACUUMS 4
#define PROGRESS_VACUUM_MAX_DEAD_TUPLES 5
#define PROGRESS_VACUUM_NUM_DEAD_TUPLES 6
+#define PROGRESS_VACUUM_TOTAL_INDEXES 7
+#define PROGRESS_VACUUM_INDEXES_COMPLETED 8
/* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index d64f6268f2..5642fae0cd 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -336,4 +336,12 @@ extern double anl_random_fract(void);
extern double anl_init_selection_state(int n);
extern double anl_get_next_S(double t, int n, double *stateptr);
+/* in commands/vacuumparallel.c */
+extern Size VacuumWorkerProgressShmemSize(void);
+extern void VacuumWorkerProgressShmemInit(void);
+extern void vacuum_worker_end(int leader_pid);
+extern void vacuum_worker_update(int leader_pid);
+extern void vacuum_progress_cb(Datum *values, int offset);
+extern void vacuum_worker_end_callback(int code, Datum arg);
+
#endif /* VACUUM_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1420288d67..dc27b8614e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2002,7 +2002,9 @@ pg_stat_progress_vacuum| SELECT s.pid,
s.param4 AS heap_blks_vacuumed,
s.param5 AS index_vacuum_count,
s.param6 AS max_dead_tuples,
- s.param7 AS num_dead_tuples
+ s.param7 AS num_dead_tuples,
+ s.param8 AS indexes_total,
+ s.param9 AS indexes_processed
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_replication| SELECT s.pid,
--
2.32.0
[application/octet-stream] 0001-Expose-the-index-being-processed-in-the-vacuuming-in.patch (16.8K, ../[email protected]/3-0001-Expose-the-index-being-processed-in-the-vacuuming-in.patch)
download | inline diff:
From 4bb70bc6dd3005d07b3c9b74d527e31b446b833f Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)" <[email protected]>
Date: Thu, 17 Feb 2022 06:27:10 +0000
Subject: [PATCH 1/1] Expose the index being processed in the "vacuuming
indexes" or "cleaning up indexes" phase of a VACUUM operation.
A new view called pg_stat_progress_vacuum_index to show the indexrelid
being vacuumed or cleaned during a vacuum. The view also shows the
number of tuples removed for the index during the vacuuming indexes phase.
Author: Sami Imseih, based on suggestions by Nathan Bossart, Peter Geoghegan and Masahiko Sawada
Reviewed by: Nathan Bossart, Justin Pryzby
---
src/backend/access/gin/ginvacuum.c | 3 ++
src/backend/access/gist/gistvacuum.c | 3 ++
src/backend/access/hash/hash.c | 1 +
src/backend/access/heap/vacuumlazy.c | 15 ++++++++++
src/backend/access/nbtree/nbtree.c | 1 +
src/backend/access/spgist/spgvacuum.c | 4 +++
src/backend/catalog/system_views.sql | 41 +++++++++++++++++++++++++++
src/backend/commands/vacuumparallel.c | 12 ++++++++
src/backend/utils/adt/pgstatfuncs.c | 2 ++
src/include/commands/progress.h | 17 +++++++----
src/include/utils/backend_progress.h | 1 +
src/test/regress/expected/rules.out | 36 +++++++++++++++++++++++
12 files changed, 130 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/gin/ginvacuum.c b/src/backend/access/gin/ginvacuum.c
index b4fa5f6bf8..1d5d003780 100644
--- a/src/backend/access/gin/ginvacuum.c
+++ b/src/backend/access/gin/ginvacuum.c
@@ -17,8 +17,10 @@
#include "access/gin_private.h"
#include "access/ginxlog.h"
#include "access/xloginsert.h"
+#include "commands/progress.h"
#include "commands/vacuum.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "postmaster/autovacuum.h"
#include "storage/indexfsm.h"
#include "storage/lmgr.h"
@@ -60,6 +62,7 @@ ginVacuumItemPointers(GinVacuumState *gvs, ItemPointerData *items,
if (gvs->callback(items + i, gvs->callback_state))
{
gvs->result->tuples_removed += 1;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, gvs->result->tuples_removed);
if (!tmpitems)
{
/*
diff --git a/src/backend/access/gist/gistvacuum.c b/src/backend/access/gist/gistvacuum.c
index aac4afab8f..8a0f23388b 100644
--- a/src/backend/access/gist/gistvacuum.c
+++ b/src/backend/access/gist/gistvacuum.c
@@ -17,9 +17,11 @@
#include "access/genam.h"
#include "access/gist_private.h"
#include "access/transam.h"
+#include "commands/progress.h"
#include "commands/vacuum.h"
#include "lib/integerset.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "storage/indexfsm.h"
#include "storage/lmgr.h"
#include "utils/memutils.h"
@@ -375,6 +377,7 @@ restart:
END_CRIT_SECTION();
vstate->stats->tuples_removed += ntodelete;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, vstate->stats->tuples_removed);
/* must recompute maxoff */
maxoff = PageGetMaxOffsetNumber(page);
}
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index a259a301fa..23dacee52e 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -632,6 +632,7 @@ loop_top:
stats->estimated_count = false;
stats->num_index_tuples = num_index_tuples;
stats->tuples_removed += tuples_removed;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, stats->tuples_removed);
/* hashvacuumcleanup will fill in num_pages */
return stats;
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 514582972d..57dc9bc63b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -370,6 +370,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
+ pgstat_progress_update_param(PROGRESS_VACUUM_LEADER_PID, MyProcPid);
/*
* Get OldestXmin cutoff, which is used to determine which deleted tuples
@@ -2347,6 +2348,9 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
Relation indrel = vacrel->indrels[idx];
IndexBulkDeleteResult *istat = vacrel->indstats[idx];
+ /* Advertise the index being vacuumed in non-parallel vacuum */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
vacrel->indstats[idx] =
lazy_vacuum_one_index(indrel, istat, vacrel->old_live_tuples,
vacrel);
@@ -2358,6 +2362,10 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
*/
pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
+ /* Advertise we are done vacuuming indexes in non-parallel vacuum */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
+
if (lazy_check_wraparound_failsafe(vacrel))
{
/* Wraparound emergency -- end current index scan */
@@ -2719,12 +2727,19 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
Relation indrel = vacrel->indrels[idx];
IndexBulkDeleteResult *istat = vacrel->indstats[idx];
+ /* Advertise the index being cleaned in non-parallel vacuum*/
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
vacrel->indstats[idx] =
lazy_cleanup_one_index(indrel, istat, reltuples,
estimated_count, vacrel);
/* See the lazy_vacuum_all_indexes comments */
pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
+
+ /* Advertise we are done cleaning indexes in non-parallel vacuum */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
}
}
else
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index c9b4964c1e..09edf49082 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -1273,6 +1273,7 @@ backtrack:
nupdatable);
stats->tuples_removed += nhtidsdead;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, stats->tuples_removed);
/* must recompute maxoff */
maxoff = PageGetMaxOffsetNumber(page);
diff --git a/src/backend/access/spgist/spgvacuum.c b/src/backend/access/spgist/spgvacuum.c
index 0049630532..db73f8ef59 100644
--- a/src/backend/access/spgist/spgvacuum.c
+++ b/src/backend/access/spgist/spgvacuum.c
@@ -21,8 +21,10 @@
#include "access/transam.h"
#include "access/xloginsert.h"
#include "catalog/storage_xlog.h"
+#include "commands/progress.h"
#include "commands/vacuum.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/indexfsm.h"
#include "storage/lmgr.h"
@@ -160,6 +162,7 @@ vacuumLeafPage(spgBulkDeleteState *bds, Relation index, Buffer buffer,
bds->stats->tuples_removed += 1;
deletable[i] = true;
nDeletable++;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, bds->stats->tuples_removed);
}
else
{
@@ -430,6 +433,7 @@ vacuumLeafRoot(spgBulkDeleteState *bds, Relation index, Buffer buffer)
bds->stats->tuples_removed += 1;
toDelete[xlrec.nDelete] = i;
xlrec.nDelete++;
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, bds->stats->tuples_removed);
}
else
{
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index eaa0508c0b..5bd6953996 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1123,6 +1123,8 @@ CREATE VIEW pg_stat_progress_vacuum AS
WHEN 4 THEN 'cleaning up indexes'
WHEN 5 THEN 'truncating heap'
WHEN 6 THEN 'performing final cleanup'
+ WHEN 7 THEN 'vacuuming indexes'
+ WHEN 8 THEN 'cleaning up indexes'
END AS phase,
S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
@@ -1131,6 +1133,45 @@ CREATE VIEW pg_stat_progress_vacuum AS
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
+CREATE VIEW pg_stat_progress_vacuum_index AS
+ SELECT
+ S.pid,
+ S.datid,
+ S.datname,
+ S.indexrelid,
+ S.leader_pid,
+ CASE S.phase WHEN 2 THEN 'vacuuming indexes'
+ WHEN 4 THEN 'cleaning up indexes'
+ WHEN 7 THEN 'vacuuming indexes'
+ WHEN 8 THEN 'cleaning up indexes'
+ END AS phase,
+ S.tuples_removed
+ FROM (
+ SELECT
+ S.pid AS pid,
+ S.datid AS datid,
+ D.datname AS datname,
+ S.param10 AS indexrelid,
+ S.param12 AS leader_pid,
+ S.param1 AS phase,
+ S.param11 AS tuples_removed
+ FROM pg_stat_get_progress_info('VACUUM') AS S
+ LEFT JOIN pg_database D ON S.datid = D.oid
+ WHERE S.param1 IN (2, 4, 7, 8) AND S.param10 > 0
+ UNION ALL
+ SELECT
+ S.pid AS pid,
+ S.datid AS datid,
+ D.datname AS datname,
+ S.param10 AS indexrelid,
+ S.param12 AS leader_pid,
+ S.param1 AS phase,
+ S.param11 AS tuples_removed
+ FROM pg_stat_get_progress_info('VACUUM_PARALLEL') AS S
+ LEFT JOIN pg_database D ON S.datid = D.oid
+ ) AS S
+ WHERE S.phase IN (2, 4, 7, 8) AND S.indexrelid > 0;
+
CREATE VIEW pg_stat_progress_cluster AS
SELECT
S.pid AS pid,
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 9b465e12cc..0c6eac8bb9 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -845,14 +845,19 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
pvs->indname = pstrdup(RelationGetRelationName(indrel));
pvs->status = indstats->status;
+ /* Advertise the index we are cleaning or vacuuming */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
switch (indstats->status)
{
case PARALLEL_INDVAC_STATUS_NEED_BULKDELETE:
istat_res = vac_bulkdel_one_index(&ivinfo, istat, pvs->dead_items);
+ pgstat_progress_update_param(PROGRESS_VACUUM_PHASE, PROGRESS_VACUUM_PHASE_VACUUM_INDEX_PARALLEL);
vacuum_worker_update(pvs->shared->leader_pid);
break;
case PARALLEL_INDVAC_STATUS_NEED_CLEANUP:
istat_res = vac_cleanup_one_index(&ivinfo, istat);
+ pgstat_progress_update_param(PROGRESS_VACUUM_PHASE, PROGRESS_VACUUM_PHASE_INDEX_CLEANUP_PARALLEL);
vacuum_worker_update(pvs->shared->leader_pid);
break;
default:
@@ -888,6 +893,10 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
*/
indstats->status = PARALLEL_INDVAC_STATUS_COMPLETED;
+ /* Advertise we are no longer vacuuming/cleaning an index */
+ pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+ pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
+
/* Reset error traceback information */
pvs->status = PARALLEL_INDVAC_STATUS_COMPLETED;
pfree(pvs->indname);
@@ -972,6 +981,8 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
* workers.
*/
rel = table_open(shared->relid, ShareUpdateExclusiveLock);
+ pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM_PARALLEL, RelationGetRelid(rel));
+ pgstat_progress_update_param(PROGRESS_VACUUM_LEADER_PID, shared->leader_pid);
/*
* Open all indexes. indrels are sorted in order by OID, which should be
@@ -1041,6 +1052,7 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
error_context_stack = errcallback.previous;
vac_close_indexes(nindexes, indrels, RowExclusiveLock);
+ pgstat_progress_end_command();
table_close(rel, ShareUpdateExclusiveLock);
FreeAccessStrategy(pvs.bstrategy);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index e9b41da6b9..4813fae40e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -493,6 +493,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
cmdtype = PROGRESS_COMMAND_VACUUM;
callback = vacuum_progress_cb;
}
+ else if (pg_strcasecmp(cmd, "VACUUM_PARALLEL") == 0)
+ cmdtype = PROGRESS_COMMAND_VACUUM_PARALLEL;
else if (pg_strcasecmp(cmd, "ANALYZE") == 0)
cmdtype = PROGRESS_COMMAND_ANALYZE;
else if (pg_strcasecmp(cmd, "CLUSTER") == 0)
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index e4f3cd9133..c20466cc5f 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -27,14 +27,19 @@
#define PROGRESS_VACUUM_NUM_DEAD_TUPLES 6
#define PROGRESS_VACUUM_TOTAL_INDEXES 7
#define PROGRESS_VACUUM_INDEXES_COMPLETED 8
+#define PROGRESS_VACUUM_INDEXRELID 9
+#define PROGRESS_VACUUM_TUPLES_REMOVED 10
+#define PROGRESS_VACUUM_LEADER_PID 11
/* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
-#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
-#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX 2
-#define PROGRESS_VACUUM_PHASE_VACUUM_HEAP 3
-#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP 4
-#define PROGRESS_VACUUM_PHASE_TRUNCATE 5
-#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6
+#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
+#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX 2
+#define PROGRESS_VACUUM_PHASE_VACUUM_HEAP 3
+#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP 4
+#define PROGRESS_VACUUM_PHASE_TRUNCATE 5
+#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6
+#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX_PARALLEL 7
+#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP_PARALLEL 8
/* Progress parameters for analyze */
#define PROGRESS_ANALYZE_PHASE 0
diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backend_progress.h
index 47bf8029b0..4651e45c40 100644
--- a/src/include/utils/backend_progress.h
+++ b/src/include/utils/backend_progress.h
@@ -23,6 +23,7 @@ typedef enum ProgressCommandType
{
PROGRESS_COMMAND_INVALID,
PROGRESS_COMMAND_VACUUM,
+ PROGRESS_COMMAND_VACUUM_PARALLEL,
PROGRESS_COMMAND_ANALYZE,
PROGRESS_COMMAND_CLUSTER,
PROGRESS_COMMAND_CREATE_INDEX,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index dc27b8614e..c230c6a441 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1995,6 +1995,8 @@ pg_stat_progress_vacuum| SELECT s.pid,
WHEN 4 THEN 'cleaning up indexes'::text
WHEN 5 THEN 'truncating heap'::text
WHEN 6 THEN 'performing final cleanup'::text
+ WHEN 7 THEN 'vacuuming indexes'::text
+ WHEN 8 THEN 'cleaning up indexes'::text
ELSE NULL::text
END AS phase,
s.param2 AS heap_blks_total,
@@ -2007,6 +2009,40 @@ pg_stat_progress_vacuum| SELECT s.pid,
s.param9 AS indexes_processed
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
+pg_stat_progress_vacuum_index| SELECT s.pid,
+ s.datid,
+ s.datname,
+ s.indexrelid,
+ s.leader_pid,
+ CASE s.phase
+ WHEN 2 THEN 'vacuuming indexes'::text
+ WHEN 4 THEN 'cleaning up indexes'::text
+ WHEN 7 THEN 'vacuuming indexes'::text
+ WHEN 8 THEN 'cleaning up indexes'::text
+ ELSE NULL::text
+ END AS phase,
+ s.tuples_removed
+ FROM ( SELECT s_1.pid,
+ s_1.datid,
+ d.datname,
+ s_1.param10 AS indexrelid,
+ s_1.param12 AS leader_pid,
+ s_1.param1 AS phase,
+ s_1.param11 AS tuples_removed
+ FROM (pg_stat_get_progress_info('VACUUM'::text) s_1(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
+ LEFT JOIN pg_database d ON ((s_1.datid = d.oid)))
+ WHERE ((s_1.param1 = ANY (ARRAY[(2)::bigint, (4)::bigint, (7)::bigint, (8)::bigint])) AND (s_1.param10 > 0))
+ UNION ALL
+ SELECT s_1.pid,
+ s_1.datid,
+ d.datname,
+ s_1.param10 AS indexrelid,
+ s_1.param12 AS leader_pid,
+ s_1.param1 AS phase,
+ s_1.param11 AS tuples_removed
+ FROM (pg_stat_get_progress_info('VACUUM_PARALLEL'::text) s_1(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
+ LEFT JOIN pg_database d ON ((s_1.datid = d.oid)))) s
+ WHERE ((s.phase = ANY (ARRAY[(2)::bigint, (4)::bigint, (7)::bigint, (8)::bigint])) AND (s.indexrelid > 0));
pg_stat_replication| SELECT s.pid,
s.usesysid,
u.rolname AS usename,
--
2.32.0
[application/octet-stream] 0001-Rename-index_vacuum_count-to-index_vacuum_cycle_coun.patch (2.9K, ../[email protected]/4-0001-Rename-index_vacuum_count-to-index_vacuum_cycle_coun.patch)
download | inline diff:
From f907d2e18743502a04e4c6ef878612cbcf99bf61 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)" <[email protected]>
Date: Thu, 17 Feb 2022 13:39:38 +0000
Subject: [PATCH 1/1] Rename "index_vacuum_count" to "index_vacuum_cycle_count"
in in pg_stat_pogress_vacuum.
Commit 76d8a1bfd5207d28a4e9fe98a0e1ea7c096d70aa introduces 2 new columns
to track index vacuum/cleanup progress. The columns have "index" in the
name. To make it clear what the existing "index_vacuum_count" column
refers to, which is the cycle count of the index vacuum, this change
renames the column to "index_vacuum_count"
Author: Sami Imseih, based on suggestions by Nathan Bossart, Peter Geoghegan and Masahiko Sawada
Reviewed by: Nathan Bossart, Justin Pryzby
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
---
doc/src/sgml/monitoring.sgml | 2 +-
src/backend/catalog/system_views.sql | 2 +-
src/test/regress/expected/rules.out | 2 +-
3 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 04440dfa88..7bf31f9686 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6252,7 +6252,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>index_vacuum_count</structfield> <type>bigint</type>
+ <structfield>index_vacuum_cycle_count</structfield> <type>bigint</type>
</para>
<para>
Number of completed index vacuum cycles.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5bd6953996..0fa9c927e4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1127,7 +1127,7 @@ CREATE VIEW pg_stat_progress_vacuum AS
WHEN 8 THEN 'cleaning up indexes'
END AS phase,
S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
- S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
+ S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_cycle_count,
S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples,
S.param8 AS indexes_total, S.param9 AS indexes_processed
FROM pg_stat_get_progress_info('VACUUM') AS S
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c230c6a441..c08bcb8e74 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2002,7 +2002,7 @@ pg_stat_progress_vacuum| SELECT s.pid,
s.param2 AS heap_blks_total,
s.param3 AS heap_blks_scanned,
s.param4 AS heap_blks_vacuumed,
- s.param5 AS index_vacuum_count,
+ s.param5 AS index_vacuum_cycle_count,
s.param6 AS max_dead_tuples,
s.param7 AS num_dead_tuples,
s.param8 AS indexes_total,
--
2.32.0
view thread (74+ 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]
Subject: Re: Add index scan progress to pg_stat_progress_vacuum
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