On 2016/03/09 0:24, Robert Haas wrote:
> On Tue, Mar 8, 2016 at 3:02 AM, Amit Langote
> <langote_amit...@lab.ntt.co.jp> wrote:
>> Updated versions attached.
>>
>> * changed st_progress_param to int64 and so did the argument of
>> pgstat_progress_update_param(). Likewise changed param1..param10 of
>> pg_stat_get_progress_info()'s output columns to bigint.
>>
>> * Added back the Oid field st_command_target and corresponding function
>> pgstat_progress_set_command_target(Oid).
>
> What the heck do we have an SQL-visible pg_stat_reset_local_progress()
> for? Surely if we ever need that, it's a bug.
OK, now I am not sure what I was thinking adding that function. Removed.
> I think pgstat_progress_update_param() should Assert(index >= 0 &&
> index < N_PROGRESS_PARAM). But I'd rename N_PROGRESS_PARAM to
> PGSTAT_NUM_PROGRESS_PARAM.
Agreed, done.
> Regarding "XXX - privilege check is maybe dubious" - I think the
> privilege check here should match pg_stat_activity. If it does,
> there's nothing dubious about that IMHO.
OK, done. So, it shows pid column to all, while rest of the values -
relid, param1..param10 are only shown to role members. Unlike
pg_stat_activity, there is no text column to stash a "<insufficient
privilege>" message into, so all that's done is to output null values.
The attached revision addresses above and one of Horiguchi-san's comments
in his email yesterday.
Thanks a lot for the review.
Thanks,
Amit
>From 9473230af72e0a0e3b60a8ddf1922698f7f17145 Mon Sep 17 00:00:00 2001
From: Amit <amitlangot...@gmail.com>
Date: Sun, 28 Feb 2016 01:50:07 +0900
Subject: [PATCH 1/3] Provide a way for utility commands to report progress
Commands can update a set of parameters in shared memory using:
pgstat_progress_update_param(param_index, param_value)
Up to 10 independent 64-bit integer values can be published by commands.
In addition to those, a command should always report its BackendCommandType
and the OID of the relation it is about to begin processing at the beginning
of the processing using:
pgstat_progress_start_command(cmdtype)
pgstat_progress_set_command_target(relid)
A view can be defined in system_views.sql that outputs the values returned
by pg_stat_get_progress_info(cmdtype), where 'cmdtype' is numeric value as
mentioned above. Each such view has columns corresponding to the counters
published by respective commands.
---
src/backend/postmaster/pgstat.c | 60 +++++++++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 91 +++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 2 +
src/include/pgstat.h | 25 ++++++++++
4 files changed, 178 insertions(+), 0 deletions(-)
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index da768c6..a7d0133 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2731,6 +2731,7 @@ pgstat_bestart(void)
beentry->st_clienthostname[NAMEDATALEN - 1] = '\0';
beentry->st_appname[NAMEDATALEN - 1] = '\0';
beentry->st_activity[pgstat_track_activity_query_size - 1] = '\0';
+ beentry->st_command = COMMAND_INVALID;
pgstat_increment_changecount_after(beentry);
@@ -2851,6 +2852,65 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
pgstat_increment_changecount_after(beentry);
}
+/*-----------
+ * pgstat_progress_update_param() -
+ *
+ * Update index'th member in st_progress_param[] of own backend entry.
+ *-----------
+ */
+void
+pgstat_progress_update_param(int index, int64 val)
+{
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ if(!beentry)
+ return;
+
+ if (!pgstat_track_activities)
+ return;
+
+ Assert(index >= 0 && index < PGSTAT_NUM_PROGRESS_PARAM);
+
+ pgstat_increment_changecount_before(beentry);
+ beentry->st_progress_param[index] = val;
+ pgstat_increment_changecount_after(beentry);
+}
+
+/*-----------
+ * pgstat_progress_start_command()-
+ *
+ * Set st_command in own backend entry. Also, zero-initialize
+ * st_progress_param array.
+ *-----------
+ */
+void
+pgstat_progress_start_command(BackendCommandType cmdtype)
+{
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ pgstat_increment_changecount_before(beentry);
+ beentry->st_command = cmdtype;
+ MemSet(&beentry->st_progress_param, 0,
+ sizeof(beentry->st_progress_param));
+ pgstat_increment_changecount_after(beentry);
+}
+
+/*-----------
+ * pgstat_progress_set_command_target()-
+ *
+ * Set st_command_target in own backend entry.
+ *-----------
+ */
+void
+pgstat_progress_set_command_target(Oid relid)
+{
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ pgstat_increment_changecount_before(beentry);
+ beentry->st_command_target = relid;
+ pgstat_increment_changecount_after(beentry);
+}
+
/* ----------
* pgstat_report_appname() -
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1b22fcc..a12310d 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -64,6 +64,7 @@ extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_progress_info(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
@@ -525,6 +526,96 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
}
/*
+ * Returns progress parameter values of backends running a given command
+ */
+Datum
+pg_stat_get_progress_info(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_PROGRESS_COLS PGSTAT_NUM_PROGRESS_PARAM + 2
+ int num_backends = pgstat_fetch_stat_numbackends();
+ int curr_backend;
+ int32 cmdtype = PG_GETARG_INT32(0);
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+
+ /* check to see 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")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not " \
+ "allowed in this context")));
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+ MemoryContextSwitchTo(oldcontext);
+
+ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+ {
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+ Datum values[PG_STAT_GET_PROGRESS_COLS];
+ bool nulls[PG_STAT_GET_PROGRESS_COLS];
+ int i;
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
+
+ if (!local_beentry)
+ continue;
+
+ beentry = &local_beentry->backendStatus;
+
+ /*
+ * Report values for only those backends which are running the given
+ * command.
+ */
+ if (!beentry || beentry->st_command != cmdtype)
+ continue;
+
+ /* Value available to all callers */
+ values[0] = Int32GetDatum(beentry->st_procpid);
+
+ /* show rest of the values including relid only to role members */
+ if (has_privs_of_role(GetUserId(), beentry->st_userid))
+ {
+ values[1] = ObjectIdGetDatum(beentry->st_command_target);
+ for(i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++)
+ values[i+2] = UInt32GetDatum(beentry->st_progress_param[i]);
+ }
+ else
+ {
+ for (i = 1; i < PGSTAT_NUM_PROGRESS_PARAM + 1; i++)
+ nulls[i] = true;
+ }
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ return (Datum) 0;
+}
+
+/*
* Returns activity of PG backends.
*/
Datum
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index cbbb883..61a310a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2710,6 +2710,8 @@ DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f
DESCR("statistics: currently active backend IDs");
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s r 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ _null_ pg_stat_get_activity _null_ _null_ _null_ ));
DESCR("statistics: information about currently active backends");
+DATA(insert OID = 3318 ( pg_stat_get_progress_info PGNSP PGUID 12 1 100 0 0 f f f f f t s r 1 0 2249 "23" "{23,23,26,20,20,20,20,20,20,20,20,20,20}" "{i,o,o,o,o,o,o,o,o,o,o,o,o}" "{cmdtype,pid,relid,param1,param2,param3,param4,param5,param6,param7,param8,param9,param10}" _null_ _null_ pg_stat_get_progress_info _null_ _null_ _null_ ));
+DESCR("statistics: information about progress of backends running maintenance command");
DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s r 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
DESCR("statistics: information about currently active replication");
DATA(insert OID = 3317 ( pg_stat_get_wal_receiver PGNSP PGUID 12 1 0 0 0 f f f f f f s r 0 0 2249 "" "{23,25,3220,23,3220,23,1184,1184,3220,1184,25}" "{o,o,o,o,o,o,o,o,o,o,o}" "{pid,status,receive_start_lsn,receive_start_tli,received_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name}" _null_ _null_ pg_stat_get_wal_receiver _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 65e968e..c529e66 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -696,6 +696,17 @@ typedef enum BackendState
} BackendState;
/* ----------
+ * Command type for progress reporting purposes
+ * ----------
+ */
+typedef enum BackendCommandType
+{
+ COMMAND_INVALID = 0,
+} BackendCommandType;
+
+#define PGSTAT_NUM_PROGRESS_PARAM 10
+
+/* ----------
* Shared-memory data structures
* ----------
*/
@@ -776,6 +787,17 @@ typedef struct PgBackendStatus
/* current command string; MUST be null-terminated */
char *st_activity;
+
+ /*
+ * Progress parameters of currently running utility command in the backend
+ * Different commands store different number of up to
+ * PGSTAT_NUM_PROGRESS_PARAM values in st_progress_param. However, each
+ * command must set st_command and st_command_target (OID of the relation
+ * it is about to begin to process) at the beginning of processing.
+ */
+ BackendCommandType st_command;
+ Oid st_command_target;
+ int64 st_progress_param[PGSTAT_NUM_PROGRESS_PARAM];
} PgBackendStatus;
/*
@@ -935,6 +957,9 @@ extern void pgstat_report_waiting(bool waiting);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
int buflen);
+extern void pgstat_progress_start_command(BackendCommandType cmdtype);
+extern void pgstat_progress_set_command_target(Oid relid);
+extern void pgstat_progress_update_param(int index, int64 val);
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id);
--
1.7.1
>From 6016d554a5aa269db6916eec7130910eabc85237 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 7 Mar 2016 14:38:34 +0900
Subject: [PATCH 2/3] WIP: Implement progress reporting for VACUUM command.
This basically utilizes the pgstat_progress* API to report a handful of
paramters to indicate its progress. lazy_vacuum_rel() and lazy_scan_heap()
have been altered to report command start, command target table, and the
following parameters: processing phase, number of heap blocks, number of
index blocks (all indexes), current heap block number in the main scan loop
(whenever changes), index blocks vacuumed (once per finished index vacuum),
and number of index vacuum passes (every time when all indexes are vacuumed).
Following processing phases are identified and reported whenever one changes
to another: 'scanning heap', 'vacuuming indexes', 'vacuuming heap', 'cleanup',
'done'. The last value is really a misnomer but maybe clearer when someone is
staring at the progress view being polled.
TODO: find a way to report index pages vacuumed in a more granular manner than
the current report per index vacuumed.
A view named pg_stat_vacuum_progress has been added that shows these values.
---
doc/src/sgml/monitoring.sgml | 114 ++++++++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 23 +++++++
src/backend/commands/vacuumlazy.c | 80 +++++++++++++++++++++++-
src/include/pgstat.h | 1 +
src/test/regress/expected/rules.out | 20 ++++++
5 files changed, 237 insertions(+), 1 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 85459d0..d60efbe 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -507,6 +507,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
yet included in <structname>pg_stat_user_functions</>).</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_vacuum_progress</><indexterm><primary>pg_stat_vacuum_progress</primary></indexterm></entry>
+ <entry>One row for each backend (including autovacuum worker processes) running
+ <command>VACUUM</>, showing current progress in terms of heap pages it
+ has finished processing. Note that the backends running
+ <command>VACUUM FULL</> are not included.</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -1822,6 +1829,113 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
controls exactly which functions are tracked.
</para>
+ <table id="pg-stat-vacuum-progress" xreflabel="pg_stat_vacuum_progress">
+ <title><structname>pg_stat_vacuum_progress</structname> View</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>pid</></entry>
+ <entry><type>integer</></entry>
+ <entry>Process ID of backend</entry>
+ </row>
+ <row>
+ <entry><structfield>relid</></entry>
+ <entry><type>oid</></entry>
+ <entry>OID of a table</entry>
+ </row>
+ <row>
+ <entry><structfield>processing_phase</></entry>
+ <entry><type>integer</></entry>
+ <entry>Current phase of vacuum.
+ Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>scanning heap</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>vacuuming indexes</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>vacuuming heap</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>cleanup</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>done</>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>total_heap_blks</></entry>
+ <entry><type>integer</></entry>
+ <entry>Total number of heap blocks in the table</entry>
+ </row>
+ <row>
+ <entry><structfield>current_heap_blkno</></entry>
+ <entry><type>integer</></entry>
+ <entry>Current heap block being processed</entry>
+ </row>
+ <row>
+ <entry><structfield>total_index_blks</></entry>
+ <entry><type>integer</></entry>
+ <entry>Total number of index blocks to be processed</entry>
+ </row>
+ <row>
+ <entry><structfield>index_blks_done</></entry>
+ <entry><type>integer</></entry>
+ <entry>Number of index blocks processed</entry>
+ </row>
+ <row>
+ <entry><structfield>index_scan_count</></entry>
+ <entry><type>integer</></entry>
+ <entry>Number of times index scans has been performed so far</entry>
+ </row>
+ <row>
+ <entry><structfield>percent_done</></entry>
+ <entry><type>numeric</></entry>
+ <entry>
+ Amount of work finished in percent in terms of table blocks processed
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_vacuum_progress</structname> view will contain
+ one row for each backend (including autovacuum worker processes), showing
+ progress of <command>VACUUM</> running in it. Note that the backends
+ running <command>VACUUM FULL</> are not shown.
+ </para>
+
+ <para>
+ When interpreting the value of the <structfield>percent_done</> column, also
+ note the value of <structfield>processing_phase</>. It's possible for the
+ former to be <literal>100.00</literal>, while the <command>VACUUM</> still
+ has not returned. In that case, wait for the latter to turn to the value
+ <literal>done</literal>.
+ </para>
+
</sect2>
<sect2 id="monitoring-stats-functions">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index abf9a70..f622a4a 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -971,3 +971,26 @@ RETURNS jsonb
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'jsonb_set';
+
+CREATE VIEW pg_stat_vacuum_progress AS
+ SELECT
+ S.pid AS pid,
+ S.relid AS relid,
+ CASE S.param1
+ WHEN 1 THEN 'scanning heap'
+ WHEN 2 THEN 'vacuuming indexes'
+ WHEN 3 THEN 'vacuuming heap'
+ WHEN 4 THEN 'cleanup'
+ WHEN 5 THEN 'done'
+ ELSE 'unknown'
+ END AS processing_phase,
+ S.param2 AS total_heap_blks,
+ S.param3 AS current_heap_blkno,
+ S.param4 AS total_index_blks,
+ S.param5 AS index_blks_done,
+ S.param6 AS index_scan_count,
+ CASE S.param2
+ WHEN 0 THEN 100::numeric(5, 2)
+ ELSE ((S.param3 + 1)::numeric / S.param2 * 100)::numeric(5, 2)
+ END AS percent_done
+ FROM pg_stat_get_progress_info(1) AS S;
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 8f7b248..60c3b3b 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -97,6 +97,28 @@
*/
#define SKIP_PAGES_THRESHOLD ((BlockNumber) 32)
+/*
+ * Follwing progress parameters for lazy vacuum are reported to pgstat
+ */
+#define PROG_PAR_VAC_PHASE_ID 0
+#define PROG_PAR_VAC_HEAP_BLKS 1
+#define PROG_PAR_VAC_CUR_HEAP_BLK 2
+#define PROG_PAR_VAC_IDX_BLKS 3
+#define PROG_PAR_VAC_IDX_BLKS_DONE 4
+#define PROG_PAR_VAC_N_IDX_SCAN 5
+
+/*
+ * Following distinct phases of lazy vacuum are identified. Although #1, #2
+ * and #3 run in a cyclical manner due to possibly limited memory to work
+ * with, wherein #1 is periodically interrupted to run #2 followed by #3
+ * and back, until all the blocks of the relations have been covered by #1.
+ */
+#define LV_PHASE_SCAN_HEAP 1
+#define LV_PHASE_VACUUM_INDEX 2
+#define LV_PHASE_VACUUM_HEAP 3
+#define LV_PHASE_CLEANUP 4
+#define LV_PHASE_DONE 5
+
typedef struct LVRelStats
{
/* hasindex = true means two-pass strategy; false means one-pass */
@@ -195,6 +217,10 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
Assert(params != NULL);
+ /* initialize pgstat progress info */
+ pgstat_progress_start_command(COMMAND_LAZY_VACUUM);
+ pgstat_progress_set_command_target(RelationGetRelid(onerel));
+
/* measure elapsed time iff autovacuum logging requires it */
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
{
@@ -270,6 +296,9 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
/* Vacuum the Free Space Map */
FreeSpaceMapVacuum(onerel);
+ /* We're done doing any heavy handling, so report */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_DONE);
+
/*
* Update statistics in pg_class.
*
@@ -433,7 +462,9 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
Relation *Irel, int nindexes, bool scan_all)
{
BlockNumber nblocks,
- blkno;
+ blkno,
+ total_index_blks,
+ *current_index_blks;
HeapTupleData tuple;
char *relname;
BlockNumber empty_pages,
@@ -474,6 +505,24 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
lazy_space_alloc(vacrelstats, nblocks);
frozen = palloc(sizeof(xl_heap_freeze_tuple) * MaxHeapTuplesPerPage);
+ /* about to begin heap scan */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_SCAN_HEAP);
+
+ /* total_heap_blks */
+ pgstat_progress_update_param(PROG_PAR_VAC_HEAP_BLKS, nblocks);
+
+ /* total_index_blks */
+ current_index_blks = (BlockNumber *) palloc(nindexes * sizeof(BlockNumber));
+ total_index_blks = 0;
+ for (i = 0; i < nindexes; i++)
+ {
+ BlockNumber nblocks = RelationGetNumberOfBlocks(Irel[i]);
+
+ current_index_blks[i] = nblocks;
+ total_index_blks += nblocks;
+ }
+ pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS, total_index_blks);
+
/*
* We want to skip pages that don't require vacuuming according to the
* visibility map, but only when we can skip at least SKIP_PAGES_THRESHOLD
@@ -581,6 +630,9 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
vacuum_delay_point();
+ /* current_heap_blkno: 0..nblocks-1 */
+ pgstat_progress_update_param(PROG_PAR_VAC_CUR_HEAP_BLK, blkno);
+
/*
* If we are close to overrunning the available space for dead-tuple
* TIDs, pause and do a cycle of vacuuming before we tackle this page.
@@ -604,11 +656,22 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
vacuum_log_cleanup_info(onerel, vacrelstats);
/* Remove index entries */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_INDEX);
for (i = 0; i < nindexes; i++)
+ {
lazy_vacuum_index(Irel[i],
&indstats[i],
vacrelstats);
+
+ pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS_DONE,
+ current_index_blks[i]);
+ }
+
+ pgstat_progress_update_param(PROG_PAR_VAC_N_IDX_SCAN,
+ vacrelstats->num_index_scans+1);
+
/* Remove tuples from heap */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_HEAP);
lazy_vacuum_heap(onerel, vacrelstats);
/*
@@ -618,6 +681,10 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
*/
vacrelstats->num_dead_tuples = 0;
vacrelstats->num_index_scans++;
+
+ /* go back to scanning the heap */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID,
+ LV_PHASE_SCAN_HEAP);
}
/*
@@ -1154,16 +1221,27 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
vacuum_log_cleanup_info(onerel, vacrelstats);
/* Remove index entries */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_INDEX);
for (i = 0; i < nindexes; i++)
+ {
lazy_vacuum_index(Irel[i],
&indstats[i],
vacrelstats);
+
+ pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS_DONE,
+ current_index_blks[i]);
+ }
+ pgstat_progress_update_param(PROG_PAR_VAC_N_IDX_SCAN,
+ vacrelstats->num_index_scans + 1);
+
/* Remove tuples from heap */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_HEAP);
lazy_vacuum_heap(onerel, vacrelstats);
vacrelstats->num_index_scans++;
}
/* Do post-vacuum cleanup and statistics update for each index */
+ pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_CLEANUP);
for (i = 0; i < nindexes; i++)
lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index c529e66..fecac83 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -702,6 +702,7 @@ typedef enum BackendState
typedef enum BackendCommandType
{
COMMAND_INVALID = 0,
+ COMMAND_LAZY_VACUUM
} BackendCommandType;
#define PGSTAT_NUM_PROGRESS_PARAM 10
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 81bc5c9..0ef6c76 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1851,6 +1851,26 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.autoanalyze_count
FROM pg_stat_all_tables
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
+pg_stat_vacuum_progress| SELECT s.pid,
+ s.relid,
+ CASE s.param1
+ WHEN 1 THEN 'scanning heap'::text
+ WHEN 2 THEN 'vacuuming indexes'::text
+ WHEN 3 THEN 'vacuuming heap'::text
+ WHEN 4 THEN 'cleanup'::text
+ WHEN 5 THEN 'done'::text
+ ELSE 'unknown'::text
+ END AS processing_phase,
+ s.param2 AS total_heap_blks,
+ s.param3 AS current_heap_blkno,
+ s.param4 AS total_index_blks,
+ s.param5 AS index_blks_done,
+ s.param6 AS index_scan_count,
+ CASE s.param2
+ WHEN 0 THEN (100)::numeric(5,2)
+ ELSE (((((s.param3 + 1))::numeric / (s.param2)::numeric) * (100)::numeric))::numeric(5,2)
+ END AS percent_done
+ FROM pg_stat_get_progress_info(1) s(pid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10);
pg_stat_wal_receiver| SELECT s.pid,
s.status,
s.receive_start_lsn,
--
1.7.1
>From a2b820f79f206489f3d1a9f2a97466480699f0a6 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 8 Mar 2016 15:23:38 +0900
Subject: [PATCH 3/3] WIP: Add a block number argument to index bulk delete callback.
Currently, index AMs can only pass a ItemPointer (heap tid) and a generic
pointer to caller specified struct. Add a BlockNumber so that the callback
can use it for something. For example, lazy_tid_reaped() now uses it to
track the progress of AM's bulk delete scan of an index by comparing it with
the last call's value that it stores in its private state on every change.
The private state being the LVRelStats struct that now has the corresponding
field. On every change, the count of index blocks vacuumed is incremented
which is also a new field in LVRelStats. The count is reset for every index
vacuum round.
Then a pgstat_progress_update_param call has been added to lazy_tid_reaped,
that pushes the count on every increment.
Currently, only btree AM is changed to pass the block number, others pass
InvalidBlockNumber.
---
src/backend/access/gin/ginvacuum.c | 2 +-
src/backend/access/gist/gistvacuum.c | 2 +-
src/backend/access/hash/hash.c | 2 +-
src/backend/access/nbtree/nbtree.c | 2 +-
src/backend/access/spgist/spgvacuum.c | 6 ++--
src/backend/catalog/index.c | 5 ++-
src/backend/commands/vacuumlazy.c | 36 ++++++++++++++------------------
src/include/access/genam.h | 4 ++-
8 files changed, 29 insertions(+), 30 deletions(-)
diff --git a/src/backend/access/gin/ginvacuum.c b/src/backend/access/gin/ginvacuum.c
index 6a4b98a..64e69d6 100644
--- a/src/backend/access/gin/ginvacuum.c
+++ b/src/backend/access/gin/ginvacuum.c
@@ -55,7 +55,7 @@ ginVacuumItemPointers(GinVacuumState *gvs, ItemPointerData *items,
*/
for (i = 0; i < nitem; i++)
{
- if (gvs->callback(items + i, gvs->callback_state))
+ if (gvs->callback(items + i, InvalidBlockNumber, gvs->callback_state))
{
gvs->result->tuples_removed += 1;
if (!tmpitems)
diff --git a/src/backend/access/gist/gistvacuum.c b/src/backend/access/gist/gistvacuum.c
index 7947ff9..96e89c3 100644
--- a/src/backend/access/gist/gistvacuum.c
+++ b/src/backend/access/gist/gistvacuum.c
@@ -202,7 +202,7 @@ gistbulkdelete(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
iid = PageGetItemId(page, i);
idxtuple = (IndexTuple) PageGetItem(page, iid);
- if (callback(&(idxtuple->t_tid), callback_state))
+ if (callback(&(idxtuple->t_tid), InvalidBlockNumber, callback_state))
todelete[ntodelete++] = i;
else
stats->num_index_tuples += 1;
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index 3d48c4f..1530f0b 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -575,7 +575,7 @@ loop_top:
itup = (IndexTuple) PageGetItem(page,
PageGetItemId(page, offno));
htup = &(itup->t_tid);
- if (callback(htup, callback_state))
+ if (callback(htup, InvalidBlockNumber, callback_state))
{
/* mark the item for deletion */
deletable[ndeletable++] = offno;
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index f2905cb..8c6be30 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -1034,7 +1034,7 @@ restart:
* applies to *any* type of index that marks index tuples as
* killed.
*/
- if (callback(htup, callback_state))
+ if (callback(htup, blkno, callback_state))
deletable[ndeletable++] = offnum;
}
}
diff --git a/src/backend/access/spgist/spgvacuum.c b/src/backend/access/spgist/spgvacuum.c
index 15b867f..6c7265b 100644
--- a/src/backend/access/spgist/spgvacuum.c
+++ b/src/backend/access/spgist/spgvacuum.c
@@ -154,7 +154,7 @@ vacuumLeafPage(spgBulkDeleteState *bds, Relation index, Buffer buffer,
{
Assert(ItemPointerIsValid(<->heapPtr));
- if (bds->callback(<->heapPtr, bds->callback_state))
+ if (bds->callback(<->heapPtr, InvalidBlockNumber, bds->callback_state))
{
bds->stats->tuples_removed += 1;
deletable[i] = true;
@@ -424,7 +424,7 @@ vacuumLeafRoot(spgBulkDeleteState *bds, Relation index, Buffer buffer)
{
Assert(ItemPointerIsValid(<->heapPtr));
- if (bds->callback(<->heapPtr, bds->callback_state))
+ if (bds->callback(<->heapPtr, InvalidBlockNumber, bds->callback_state))
{
bds->stats->tuples_removed += 1;
toDelete[xlrec.nDelete] = i;
@@ -902,7 +902,7 @@ spgbulkdelete(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
/* Dummy callback to delete no tuples during spgvacuumcleanup */
static bool
-dummy_callback(ItemPointer itemptr, void *state)
+dummy_callback(ItemPointer itemptr, BlockNumber index_blkno, void *state)
{
return false;
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 31a1438..d792fdb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -114,7 +114,8 @@ static void IndexCheckExclusion(Relation heapRelation,
IndexInfo *indexInfo);
static inline int64 itemptr_encode(ItemPointer itemptr);
static inline void itemptr_decode(ItemPointer itemptr, int64 encoded);
-static bool validate_index_callback(ItemPointer itemptr, void *opaque);
+static bool validate_index_callback(ItemPointer itemptr, BlockNumber index_blkno,
+ void *opaque);
static void validate_index_heapscan(Relation heapRelation,
Relation indexRelation,
IndexInfo *indexInfo,
@@ -2913,7 +2914,7 @@ itemptr_decode(ItemPointer itemptr, int64 encoded)
* validate_index_callback - bulkdelete callback to collect the index TIDs
*/
static bool
-validate_index_callback(ItemPointer itemptr, void *opaque)
+validate_index_callback(ItemPointer itemptr, BlockNumber index_blkno, void *opaque)
{
v_i_state *state = (v_i_state *) opaque;
int64 encoded = itemptr_encode(itemptr);
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 60c3b3b..d628822 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -143,6 +143,8 @@ typedef struct LVRelStats
int num_index_scans;
TransactionId latestRemovedXid;
bool lock_waiter_detected;
+ BlockNumber last_index_blkno;
+ BlockNumber index_blks_vacuumed;
} LVRelStats;
@@ -176,7 +178,7 @@ static BlockNumber count_nondeletable_pages(Relation onerel,
static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks);
static void lazy_record_dead_tuple(LVRelStats *vacrelstats,
ItemPointer itemptr);
-static bool lazy_tid_reaped(ItemPointer itemptr, void *state);
+static bool lazy_tid_reaped(ItemPointer itemptr, BlockNumber index_blkno, void *state);
static int vac_cmp_itemptr(const void *left, const void *right);
static bool heap_page_is_all_visible(Relation rel, Buffer buf,
TransactionId *visibility_cutoff_xid, bool *all_frozen);
@@ -463,8 +465,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
{
BlockNumber nblocks,
blkno,
- total_index_blks,
- *current_index_blks;
+ total_index_blks;
HeapTupleData tuple;
char *relname;
BlockNumber empty_pages,
@@ -512,15 +513,9 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
pgstat_progress_update_param(PROG_PAR_VAC_HEAP_BLKS, nblocks);
/* total_index_blks */
- current_index_blks = (BlockNumber *) palloc(nindexes * sizeof(BlockNumber));
total_index_blks = 0;
for (i = 0; i < nindexes; i++)
- {
- BlockNumber nblocks = RelationGetNumberOfBlocks(Irel[i]);
-
- current_index_blks[i] = nblocks;
- total_index_blks += nblocks;
- }
+ total_index_blks += RelationGetNumberOfBlocks(Irel[i]);
pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS, total_index_blks);
/*
@@ -657,16 +652,12 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
/* Remove index entries */
pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_INDEX);
+ vacrelstats->index_blks_vacuumed = 0; /* reset for this round */
for (i = 0; i < nindexes; i++)
- {
lazy_vacuum_index(Irel[i],
&indstats[i],
vacrelstats);
- pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS_DONE,
- current_index_blks[i]);
- }
-
pgstat_progress_update_param(PROG_PAR_VAC_N_IDX_SCAN,
vacrelstats->num_index_scans+1);
@@ -1222,15 +1213,12 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
/* Remove index entries */
pgstat_progress_update_param(PROG_PAR_VAC_PHASE_ID, LV_PHASE_VACUUM_INDEX);
+ vacrelstats->index_blks_vacuumed = 0; /* reset for this round */
for (i = 0; i < nindexes; i++)
- {
lazy_vacuum_index(Irel[i],
&indstats[i],
vacrelstats);
- pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS_DONE,
- current_index_blks[i]);
- }
pgstat_progress_update_param(PROG_PAR_VAC_N_IDX_SCAN,
vacrelstats->num_index_scans + 1);
@@ -1922,11 +1910,19 @@ lazy_record_dead_tuple(LVRelStats *vacrelstats,
* Assumes dead_tuples array is in sorted order.
*/
static bool
-lazy_tid_reaped(ItemPointer itemptr, void *state)
+lazy_tid_reaped(ItemPointer itemptr, BlockNumber index_blkno, void *state)
{
LVRelStats *vacrelstats = (LVRelStats *) state;
ItemPointer res;
+ if (index_blkno != vacrelstats->last_index_blkno)
+ {
+ ++vacrelstats->index_blks_vacuumed;
+ pgstat_progress_update_param(PROG_PAR_VAC_IDX_BLKS_DONE,
+ vacrelstats->index_blks_vacuumed);
+ vacrelstats->last_index_blkno = index_blkno;
+ }
+
res = (ItemPointer) bsearch((void *) itemptr,
(void *) vacrelstats->dead_tuples,
vacrelstats->num_dead_tuples,
diff --git a/src/include/access/genam.h b/src/include/access/genam.h
index 81907d5..8d67d1c 100644
--- a/src/include/access/genam.h
+++ b/src/include/access/genam.h
@@ -77,7 +77,9 @@ typedef struct IndexBulkDeleteResult
} IndexBulkDeleteResult;
/* Typedef for callback function to determine if a tuple is bulk-deletable */
-typedef bool (*IndexBulkDeleteCallback) (ItemPointer itemptr, void *state);
+typedef bool (*IndexBulkDeleteCallback) (ItemPointer itemptr,
+ BlockNumber index_blkno,
+ void *state);
/* struct definitions appear in relscan.h */
typedef struct IndexScanDescData *IndexScanDesc;
--
1.7.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
