Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dohIE-00089v-3Q for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2017 02:39:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dohID-0002g8-MK for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2017 02:39:17 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dohGe-0006qq-39 for pgsql-hackers@postgresql.org; Mon, 04 Sep 2017 02:37:40 +0000 Received: from tama500.ecl.ntt.co.jp ([129.60.39.148]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dohGR-00054e-EI for pgsql-hackers@postgresql.org; Mon, 04 Sep 2017 02:37:38 +0000 Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154]) by tama500.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id v842bN9Y004100; Mon, 4 Sep 2017 11:37:23 +0900 Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 1F4C55F65A; Mon, 4 Sep 2017 11:37:23 +0900 (JST) Received: from jcms-pop21.ecl.ntt.co.jp (jcms-pop21.ecl.ntt.co.jp [129.60.87.134]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 1432A5F63F; Mon, 4 Sep 2017 11:37:23 +0900 (JST) Received: from [127.0.0.1] (unknown [129.60.241.68]) by jcms-pop21.ecl.ntt.co.jp (Postfix) with ESMTPSA id 0FA04400863; Mon, 4 Sep 2017 11:37:23 +0900 (JST) Message-ID: <59ACBC50.3090106@lab.ntt.co.jp> Date: Mon, 04 Sep 2017 11:37:04 +0900 From: Tatsuro Yamada User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.1.0 MIME-Version: 1.0 Subject: Re: CLUSTER command progress monitor References: <59A77072.3090401@lab.ntt.co.jp> <59A90054.8090708@lab.ntt.co.jp> In-Reply-To: Content-Type: multipart/mixed; boundary="------------010306050501040408090209" To: Masahiko Sawada Cc: Thomas Munro , Pg Hackers X-TM-AS-MML: disable List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------010306050501040408090209 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Hi Sawada-san, Thomas, Thanks for sharing the reggression.diff. I realized Thomas's comment is right. Attached patch is fixed version. Could you try it? Regards, Tatsuro Yamada NTT Open Source Software Center On 2017/09/01 17:59, Masahiko Sawada wrote: > On Fri, Sep 1, 2017 at 3:38 PM, Tatsuro Yamada > wrote: >> Hi Thomas, >> >>>> Any comments or suggestion are welcome. >>> >>> >>> Although this patch updates src/test/regress/expected/rules.out I >>> think perhaps you included the wrong version? That regression test >>> fails for me >> >> >> Thanks for the comment. >> >> I use the patch on 7b69b6ce and it's fine. >> Did you use "initdb" command after "make install"? >> The pg_stat_progress_cluster view is created in initdb, probably. >> > > I also got a regression test error (applied to abe85ef). Here is > regression.diff file. > > *** /home/masahiko/source/postgresql/src/test/regress/expected/rules.out > 2017-09-01 17:27:33.680055612 -0700 > --- /home/masahiko/source/postgresql/src/test/regress/results/rules.out > 2017-09-01 17:28:10.410055596 -0700 > *************** > *** 1819,1824 **** > --- 1819,1849 ---- > pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, > pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock > FROM pg_database d; > + pg_stat_progress_cluster| SELECT s.pid, > + s.datid, > + d.datname, > + s.relid, > + CASE s.param1 > + WHEN 0 THEN 'initializing'::text > + WHEN 1 THEN 'scanning heap'::text > + WHEN 2 THEN 'sorting tuples'::text > + WHEN 3 THEN 'writing new heap'::text > + WHEN 4 THEN 'scan heap and write new heap'::text > + WHEN 5 THEN 'swapping relation files'::text > + WHEN 6 THEN 'rebuilding index'::text > + WHEN 7 THEN 'performing final cleanup'::text > + ELSE NULL::text > + END AS phase, > + CASE s.param2 > + WHEN 0 THEN 'index scan'::text > + WHEN 1 THEN 'seq scan'::text > + ELSE NULL::text > + END AS scan_method, > + s.param3 AS scan_index_relid, > + s.param4 AS heap_tuples_total, > + s.param5 AS heap_tuples_scanned > + FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, > relid, param1, param2, param3, param4, param5, param6, param7, param8, > param9, param10) > + LEFT JOIN pg_database d ON ((s.datid = d.oid))); > pg_stat_progress_vacuum| SELECT s.pid, > s.datid, > d.datname, > *************** > *** 1841,1871 **** > s.param7 AS num_dead_tuples > FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, > relid, param1, param2, param3, param4, param5, param6, param7, param8, > param9, param10) > LEFT JOIN pg_database d ON ((s.datid = d.oid))); > - pg_stat_progress_cluster| SELECT > - s.pid, > - s.datid, > - d.datname, > - s.relid, > - CASE s.param1 > - WHEN 0 THEN 'initializing'::text > - WHEN 1 THEN 'scanning heap'::text > - WHEN 2 THEN 'sorting tuples'::text > - WHEN 3 THEN 'writing new heap'::text > - WHEN 4 THEN 'scan heap and write new heap'::text > - WHEN 5 THEN 'swapping relation files'::text > - WHEN 6 THEN 'rebuilding index'::text > - WHEN 7 THEN 'performing final cleanup'::text > - ELSE NULL::text > - END AS phase, > - CASE S.param2 > - WHEN 0 THEN 'index scan' > - WHEN 1 THEN 'seq scan' > - END AS scan_method, > - s.param3 AS index_relid, > - s.param4 AS heap_blks_total, > - s.param5 AS heap_blks_scanned > - FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, > relid, param1, param2, param3, param4, param5) > - LEFT JOIN pg_database d ON ((s.datid = d.oid))); > pg_stat_replication| SELECT s.pid, > s.usesysid, > u.rolname AS usename, > --- 1866,1871 ---- > > ====================================================================== > > Regards, > > -- > Masahiko Sawada > NIPPON TELEGRAPH AND TELEPHONE CORPORATION > NTT Open Source Software Center --------------010306050501040408090209 Content-Type: text/x-patch; name="progress_monitor_cluster_v2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="progress_monitor_cluster_v2.patch" diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 38bf636..35a5c63 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -332,6 +332,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + pg_stat_progress_clusterpg_stat_progress_cluster + One row for each backend running + CLUSTER, showing current progress. + See . + + + @@ -3233,9 +3241,9 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, PostgreSQL has the ability to report the progress of - certain commands during command execution. Currently, the only command - which supports progress reporting is VACUUM. This may be - expanded in the future. + certain commands during command execution. Currently, the suppoted + progress reporting commands are VACUUM and CLUSTER. + This may be expanded in the future. @@ -3427,6 +3435,157 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, + + + CLUSTER Progress Reporting + + + Whenever CLUSTER is running, the + pg_stat_progress_cluster view will contain + one row for each backend that is currently clustering. + The tables below describe the information that will be reported and + provide information about how to interpret it. + + + + <structname>pg_stat_progress_cluster</structname> View + + + + Column + Type + Description + + + + + + pid + integer + Process ID of backend. + + + datid + oid + OID of the database to which this backend is connected. + + + datname + name + Name of the database to which this backend is connected. + + + relid + oid + OID of the table being clustered. + + + phase + text + + Current processing phase of cluster. See . + + + + scan_method + text + + Scan method of table: index scan/seq scan. + + + + scan_index_relid + bigint + + OID of the index. + + + + heap_tuples_total + bigint + + Total number of heap tuples in the table. This number is reported + as of the beginning of the scan; tuples added later will not be (and + need not be) visited by this CLUSTER. + + + + heap_tuples_scanned + bigint + + Number of heap tuples scanned. + This counter only advances when the phase is scanning heap, + writing new heap and scan heap and write new heap. + + + + +
+ + + CLUSTER phases + + + + Phase + Description + + + + + + initializing + + CLUSTER is preparing to begin scanning the heap. This + phase is expected to be very brief. + + + + scanning heap + + CLUSTER is currently scanning heap from the table by + seq scan. This phase is shown when the scan_method is seq scan. + + + + sorting tuples + + CLUSTER is currently sorting tuples. + This phase is shown when the scan_method is seq scan. + + + + scan heap and write new heap + + CLUSTER is currently scanning heap from the table and + writing new clusterd heap. This phase is shown when the scan_method is + index scan. + + + + swapping relation files + + CLUSTER is currently swapping old heap and new clustered heap. + + + + rebuilding index + + CLUSTER is rebuilding index. + + + + performing final cleanup + + CLUSTER is performing final cleanup. When this phase is + completed, CLUSTER will end. + + + + +
+ + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index dc40cde..c10c830 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -899,6 +899,30 @@ 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_cluster AS + SELECT + S.pid AS pid, + S.datid AS datid, + D.datname AS datname, + S.relid AS relid, + CASE S.param1 WHEN 0 THEN 'initializing' + WHEN 1 THEN 'scanning heap' + WHEN 2 THEN 'sorting tuples' + WHEN 3 THEN 'writing new heap' + WHEN 4 THEN 'scan heap and write new heap' + WHEN 5 THEN 'swapping relation files' + WHEN 6 THEN 'rebuilding index' + WHEN 7 THEN 'performing final cleanup' + END AS phase, + CASE S.param2 WHEN 0 THEN 'index scan' + WHEN 1 THEN 'seq scan' + END AS scan_method, + S.param3 AS scan_index_relid, + S.param4 AS heap_tuples_total, + S.param5 AS heap_tuples_scanned + FROM pg_stat_get_progress_info('CLUSTER') AS S + LEFT JOIN pg_database D ON S.datid = D.oid; + CREATE VIEW pg_user_mappings AS SELECT U.oid AS umid, diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 48f1e6e..8f2a473 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -34,10 +34,12 @@ #include "catalog/objectaccess.h" #include "catalog/toasting.h" #include "commands/cluster.h" +#include "commands/progress.h" #include "commands/tablecmds.h" #include "commands/vacuum.h" #include "miscadmin.h" #include "optimizer/planner.h" +#include "pgstat.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/predicate.h" @@ -105,6 +107,7 @@ static void reform_and_rewrite_tuple(HeapTuple tuple, void cluster(ClusterStmt *stmt, bool isTopLevel) { + if (stmt->relation != NULL) { /* This is the single-relation case. */ @@ -276,6 +279,11 @@ cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose) if (!OldHeap) return; + /* Start progress monitor for cluster command */ + pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid); + /* Set indexOid to column */ + pgstat_progress_update_param(PROGRESS_CLUSTER_SCAN_INDEX_RELID, indexOid); + /* * Since we may open a new transaction for each relation, we have to check * that the relation still is what we think it is. @@ -404,6 +412,8 @@ cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose) rebuild_relation(OldHeap, indexOid, verbose); /* NB: rebuild_relation does heap_close() on OldHeap */ + + pgstat_progress_end_command(); } /* @@ -771,6 +781,9 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, else OldIndex = NULL; + /* Set reltuples to total_tuples */ + pgstat_progress_update_param(PROGRESS_CLUSTER_TOTAL_HEAP_TUPLES, OldHeap->rd_rel->reltuples); + /* * Their tuple descriptors should be exactly alike, but here we only need * assume that they have the same number of columns. @@ -902,12 +915,16 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, */ if (OldIndex != NULL && !use_sort) { + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_SCAN_HEAP_AND_WRITE_NEW_HEAP); + pgstat_progress_update_param(PROGRESS_CLUSTER_SCAN_METHOD, PROGRESS_CLUSTER_METHOD_INDEX_SCAN); heapScan = NULL; indexScan = index_beginscan(OldHeap, OldIndex, SnapshotAny, 0, 0); index_rescan(indexScan, NULL, 0, NULL, 0); } else { + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_SCAN_HEAP); + pgstat_progress_update_param(PROGRESS_CLUSTER_SCAN_METHOD, PROGRESS_CLUSTER_METHOD_SEQ_SCAN); heapScan = heap_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL); indexScan = NULL; } @@ -1039,6 +1056,9 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, oldTupDesc, newTupDesc, values, isnull, NewHeap->rd_rel->relhasoids, rwstate); + + /* Regardless of index scan or seq scan, update tuples_scanned column */ + pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, num_tuples); } if (indexScan != NULL) @@ -1052,8 +1072,15 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, */ if (tuplesort != NULL) { + double num_tuples = 0; + + /* Report that we are now sorting tuples */ + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_SORT_TUPLES); tuplesort_performsort(tuplesort); + /* Report that we are now writing new heap */ + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP); + pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, num_tuples); for (;;) { HeapTuple tuple; @@ -1064,10 +1091,13 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, if (tuple == NULL) break; + num_tuples += 1; reform_and_rewrite_tuple(tuple, oldTupDesc, newTupDesc, values, isnull, NewHeap->rd_rel->relhasoids, rwstate); + + pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, num_tuples); } tuplesort_end(tuplesort); @@ -1480,6 +1510,9 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, int reindex_flags; int i; + /* Report that we are now swapping relation files */ + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES); + /* Zero out possible results from swapped_relation_files */ memset(mapped_tables, 0, sizeof(mapped_tables)); @@ -1514,6 +1547,10 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, * because the new heap won't contain any HOT chains at all, let alone * broken ones, so it can't be necessary to set indcheckxmin. */ + + /* Report that we are now reindexing relations */ + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_REBUILD_INDEX); + reindex_flags = REINDEX_REL_SUPPRESS_INDEX_USE; if (check_constraints) reindex_flags |= REINDEX_REL_CHECK_CONSTRAINTS; @@ -1529,6 +1566,9 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, reindex_relation(OIDOldHeap, reindex_flags, 0); + /* Report that we are now doing clean up */ + pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP); + /* * If the relation being rebuild is pg_class, swap_relation_files() * couldn't update pg_class's own pg_class entry (check comments in diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 20ce48b..90bde85 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -467,6 +467,8 @@ 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; + else if(pg_strcasecmp(cmd, "CLUSTER") == 0) + cmdtype = PROGRESS_COMMAND_CLUSTER; else ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index 9472ecc..28ccf38 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -34,4 +34,24 @@ #define PROGRESS_VACUUM_PHASE_TRUNCATE 5 #define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6 +/* Progress parameters for cluster */ +#define PROGRESS_CLUSTER_PHASE 0 +#define PROGRESS_CLUSTER_SCAN_METHOD 1 +#define PROGRESS_CLUSTER_SCAN_INDEX_RELID 2 +#define PROGRESS_CLUSTER_TOTAL_HEAP_TUPLES 3 +#define PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED 4 + +/* Phases of cluster (as dvertised via PROGRESS_CLUSTER_PHASE) */ +#define PROGRESS_CLUSTER_PHASE_SCAN_HEAP 1 +#define PROGRESS_CLUSTER_PHASE_SORT_TUPLES 2 +#define PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP 3 +#define PROGRESS_CLUSTER_PHASE_SCAN_HEAP_AND_WRITE_NEW_HEAP 4 +#define PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES 5 +#define PROGRESS_CLUSTER_PHASE_REBUILD_INDEX 6 +#define PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP 7 + +/* Scan methods of cluster */ +#define PROGRESS_CLUSTER_METHOD_INDEX_SCAN 0 +#define PROGRESS_CLUSTER_METHOD_SEQ_SCAN 1 + #endif diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 57ac5d4..1c8dd67 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -916,7 +916,8 @@ typedef enum typedef enum ProgressCommandType { PROGRESS_COMMAND_INVALID, - PROGRESS_COMMAND_VACUUM + PROGRESS_COMMAND_VACUUM, + PROGRESS_COMMAND_CLUSTER } ProgressCommandType; #define PGSTAT_NUM_PROGRESS_PARAM 10 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index d582bc9..ede9242 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1819,6 +1819,31 @@ pg_stat_database_conflicts| SELECT d.oid AS datid, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d; +pg_stat_progress_cluster| SELECT s.pid, + s.datid, + d.datname, + s.relid, + CASE s.param1 + WHEN 0 THEN 'initializing'::text + WHEN 1 THEN 'scanning heap'::text + WHEN 2 THEN 'sorting tuples'::text + WHEN 3 THEN 'writing new heap'::text + WHEN 4 THEN 'scan heap and write new heap'::text + WHEN 5 THEN 'swapping relation files'::text + WHEN 6 THEN 'rebuilding index'::text + WHEN 7 THEN 'performing final cleanup'::text + ELSE NULL::text + END AS phase, + CASE s.param2 + WHEN 0 THEN 'index scan'::text + WHEN 1 THEN 'seq scan'::text + ELSE NULL::text + END AS scan_method, + s.param3 AS scan_index_relid, + s.param4 AS heap_tuples_total, + s.param5 AS heap_tuples_scanned + FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10) + LEFT JOIN pg_database d ON ((s.datid = d.oid))); pg_stat_progress_vacuum| SELECT s.pid, s.datid, d.datname, --------------010306050501040408090209 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------010306050501040408090209--