On 2016/03/07 23:48, Robert Haas wrote:
> On Sun, Mar 6, 2016 at 11:02 PM, Kyotaro HORIGUCHI
> <horiguchi.kyot...@lab.ntt.co.jp> wrote:
>> The 0001-P.. adds the following interface functions.
>>
>> +extern void pgstat_progress_set_command(BackendCommandType cmdtype);
>> +extern void pgstat_progress_set_command_target(Oid objid);
>> +extern void pgstat_progress_update_param(int index, uint32 val);
>> +extern void pgstat_reset_local_progress(void);
>> +extern int pgstat_progress_get_num_param(BackendCommandType cmdtype);
>>
>> I don't like to treat the target object id differently from other
>> parameters. It could not be needed at all, or could be needed two
>> or more in contrast. Although oids are not guaranteed to fit
>> uint32, we have already stored BlockNumber there.
>
> Well...
>
> There's not much point in deciding that the parameters are uint32,
> because we don't have that type at the SQL level.
> pgstat_progress_update_param() really ought to take either int32 or
> int64 as an argument, because that's what we can actually handle from
> SQL, and it seems pretty clear that int64 is better since otherwise we
> can't fit, among other things, a block number.
>
> Given that, I tend to think that treating the command target specially
> and passing that as an OID is reasonable. We're not going to be able
> to pass variable-sized arrays through this mechanism, ever, because
> our shared memory segment doesn't work like that. And it seems to me
> that nearly every command somebody might want to report progress on
> will touch, basically, one relation a a time. So I don't see the harm
> in hardcoding that idea into the facility.
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).
* I attempted to implement a method to report index blocks done from
lazy_tid_reaped() albeit with some limitations. Patch 0003 is that
attempt. In summary, I modified the index bulk delete callback interface
to receive a BlockNumber argument index_blkno:
/* 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);
Then added 2 more fields to LVRelStats:
@@ -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;
Then in lazy_tid_reaped(), if the index block number received in the
index_blkno argument has changed from the previous call, increment the
count of index blocks processed and
pgstat_report_update_param(index_blks_done). I wonder if we should reset
the the saved block number and the count for every index vacuumed by
lazy_vacuum_index(). Right now, total_index_blks counts all indexes and
counting blocks using the rough method mentioned above is sensible only
for one index at time. Actually, the method has different kinds of
problems to deal with anyway. For example, with a btree index, one can
expect that the final count does not match total_index_blks obtained using
RelationGetNumberOfBlocks(). Moreover, each AM has its own idiosyncratic
way of traversing the index pages. I dared only touch the btree case to
make it pass current block number to the callback. It finishes with
index_blks_done << total_index_blks since I guess the callback is called
only on the leaf pages. Any ideas?
* I am also tempted to add num_dead_tuples and dead_tuples_vacuumed to add
granularity to 'vacuuming heap' phase but didn't in this version. Should we?
Thanks,
Amit
>From 1608f3bd8153045da9cb3db269597b92042a4d9c 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.
There is a SQL-callable function pg_stat_reset_local_progress() which
when called, resets the progress information of the backend of the session
in which its called. It is useful to erase progress info of commands
previously run in the session.
---
doc/src/sgml/monitoring.sgml | 8 +++
src/backend/postmaster/pgstat.c | 76 ++++++++++++++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 93 +++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 4 ++
src/include/pgstat.h | 26 ++++++++++
5 files changed, 207 insertions(+), 0 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 85459d0..45d9ed7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1935,6 +1935,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
zero (requires superuser privileges)
</entry>
</row>
+
+ <row>
+ <entry><literal><function>pg_stat_reset_local_progress</function>()</literal><indexterm><primary>pg_stat_reset_local_progress</primary></indexterm></entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Reset command progress parameters of local backend
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index da768c6..af5be5f 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2731,6 +2731,8 @@ 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;
+ MemSet(&beentry->st_progress_param, 0, sizeof(beentry->st_progress_param));
pgstat_increment_changecount_after(beentry);
@@ -2851,6 +2853,80 @@ 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;
+
+ 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_reset_local_progress()-
+ *
+ * Reset local backend's progress parameters. Setting st_command to
+ * COMMAND_INVALID will do.
+ *--------
+ */
+void
+pgstat_reset_local_progress()
+{
+ PgBackendStatus *beentry = MyBEEntry;
+
+ pgstat_increment_changecount_before(beentry);
+ beentry->st_command = COMMAND_INVALID;
+ 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..ac72def 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -64,6 +64,8 @@ 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_reset_local_progress(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 +527,97 @@ 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 N_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. XXX - privilege check is maybe dubious.
+ */
+ if (!beentry ||
+ beentry->st_command != cmdtype ||
+ !has_privs_of_role(GetUserId(), beentry->st_userid))
+ continue;
+
+ /* Values available to all callers */
+ values[0] = Int32GetDatum(beentry->st_procpid);
+ values[1] = ObjectIdGetDatum(beentry->st_command_target);
+ for(i = 0; i < N_PROGRESS_PARAM; i++)
+ values[i+2] = UInt32GetDatum(beentry->st_progress_param[i]);
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ return (Datum) 0;
+}
+
+/* Reset local backend's command progress info */
+Datum
+pg_stat_reset_local_progress(PG_FUNCTION_ARGS)
+{
+ pgstat_reset_local_progress();
+
+ PG_RETURN_VOID();
+}
+
+/*
* Returns activity of PG backends.
*/
Datum
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index cbbb883..daaa233 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_ ));
@@ -2849,6 +2851,8 @@ DATA(insert OID = 3776 ( pg_stat_reset_single_table_counters PGNSP PGUID 12 1 0
DESCR("statistics: reset collected statistics for a single table or index in the current database");
DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 2278 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_reset_single_function_counters _null_ _null_ _null_ ));
DESCR("statistics: reset collected statistics for a single function in the current database");
+DATA(insert OID = 3319 ( pg_stat_reset_local_progress PGNSP PGUID 12 1 0 0 0 f f f f f f v s 0 0 2278 "" _null_ _null_ _null_ _null_ _null_ pg_stat_reset_local_progress _null_ _null_ _null_ ));
+DESCR("statistics: reset progress information of the local backend");
DATA(insert OID = 3163 ( pg_trigger_depth PGNSP PGUID 12 1 0 0 0 f f f f t f s s 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_trigger_depth _null_ _null_ _null_ ));
DESCR("current trigger depth");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 65e968e..dfa6b31 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 N_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
+ * N_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 command processing.
+ */
+ BackendCommandType st_command;
+ Oid st_command_target;
+ int64 st_progress_param[N_PROGRESS_PARAM];
} PgBackendStatus;
/*
@@ -935,6 +957,10 @@ 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 void pgstat_reset_local_progress(void);
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id);
--
1.7.1
>From cdeb9655479bf983d19b39ef9c5674a3eaca12e1 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] 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 45d9ed7..e4361ad 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 dfa6b31..948783e 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 N_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 5972d8516786615bed3a587b0d2fb78dba2e74ed 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] 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 | 25 ++++++++++++++-----------
src/include/access/genam.h | 4 +++-
8 files changed, 27 insertions(+), 21 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..2e28f4b 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);
@@ -657,16 +659,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 +1220,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 +1917,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
