Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1heOe2-0005oO-QU for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Jun 2019 18:52:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1heOdz-0004W8-QG for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Jun 2019 18:52:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1heOdy-0004W1-Ut for pgsql-hackers@lists.postgresql.org; Fri, 21 Jun 2019 18:52:15 +0000 Received: from mail-qt1-x835.google.com ([2607:f8b0:4864:20::835]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1heOdv-0007Li-1V for pgsql-hackers@lists.postgresql.org; Fri, 21 Jun 2019 18:52:13 +0000 Received: by mail-qt1-x835.google.com with SMTP id x2so8016759qtr.0 for ; Fri, 21 Jun 2019 11:52:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:mime-version:content-disposition :content-transfer-encoding:user-agent; bh=NENYniwDEBR4znkugC0xThDhBExBPjesAbTb9Han1t0=; b=BxCIEjNYaJJYdMnMzXkJ0DPY1WzNqrpTTl3+b0AekhkPfVeOD2+jdJJGgBHiemqLHj +gea2BraBEsI0koAcZ7WaHATxX/POQs2430nIyAIRXZ7KBZAGhA+ySTOmhb8oTFqIVrJ 52IsgiXKaljFmqodNdFu+DoMDPhPh4S3yIoMqPW65QOW7WD1RPFVe9GzFnkJLC6AjXz3 TeHEL2cE3eCabCai71sX3x6ZBkyApZJNu2bLBGGgX41Upsk1U0Cho0nKg/ezsO7CZDVP 2ZSf2ggduXIsDRykp7SVbX7Ryk7ogsH811InXbiPCI6wG/KVoYrx0fGqM4s0WB2PAb8z XCog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:mime-version :content-disposition:content-transfer-encoding:user-agent; bh=NENYniwDEBR4znkugC0xThDhBExBPjesAbTb9Han1t0=; b=cdk6v/eUNLWEcfuHX2x96wG0NdF7ymprhyvThDbHXw88pe970AyXIdOdXdJqqOftVG XLb7DWiu1QCiY+/isToKj0b9/idzHXG2ZgwkKTeafYawnXc35SUYLg/AwaPZ1bl1jjrl FQ1GpBez4Zl330uJ6w0CIUkl5eKZSOaUhkA133OuHMoziygar2AQ31O+Z6/5tJwt8NXK oZQumaE+cMrIpfGgHISBuRcV7LoFphgCRqa9MdRmaP1XkZhe6GXrLW63H4o23Z9U2v4S KdeXO+4kaafHZoz2mTCYHImetWvh843p3LwgebO60CZtQII/XP6KCCCxvZoWsYxKBewC +tiQ== X-Gm-Message-State: APjAAAVQEa280f7ZOzcIyN+g+0J35aTRCgp4t5MmkTaVIw/P0QzDEHw5 km3sRNUFtt4eqz+3H3jZBD+GD4JspC7rMA== X-Google-Smtp-Source: APXvYqwCOH3Jpb1KkskvJEdTiIelKNg41P7zsXBtiySABblTtdfzNCdm7ITn+4+fZ6dhNyIlSjFsUQ== X-Received: by 2002:aed:21f0:: with SMTP id m45mr104169810qtc.391.1561143129810; Fri, 21 Jun 2019 11:52:09 -0700 (PDT) Received: from nimloth.alvh.no-ip.org ([190.121.29.3]) by smtp.gmail.com with ESMTPSA id w51sm3152014qth.18.2019.06.21.11.52.08 (version=TLS1_3 cipher=AEAD-AES256-GCM-SHA384 bits=256/256); Fri, 21 Jun 2019 11:52:09 -0700 (PDT) Received: by nimloth.alvh.no-ip.org (Postfix, from userid 1000) id 244A6120804; Fri, 21 Jun 2019 14:52:07 -0400 (-04) Date: Fri, 21 Jun 2019 14:52:07 -0400 From: Alvaro Herrera To: Pg Hackers Cc: Tatsuro Yamada Subject: progress report for ANALYZE Message-ID: <20190621185207.GA27929@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="FCuugMFkClbJLl1L" Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --FCuugMFkClbJLl1L Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit Hello Here's a patch that implements progress reporting for ANALYZE. Thanks -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --FCuugMFkClbJLl1L Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v1-0001-Report-progress-for-ANALYZE.patch" From 572fea555bc1c9d3512f7806aed6b97a374199eb Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 21 Jun 2019 13:35:13 -0400 Subject: [PATCH v1] Report progress for ANALYZE --- doc/src/sgml/monitoring.sgml | 129 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 15 ++++ src/backend/commands/analyze.c | 55 +++++++++++- src/backend/utils/adt/pgstatfuncs.c | 2 + src/backend/utils/misc/sampling.c | 6 +- src/include/commands/progress.h | 14 +++ src/include/pgstat.h | 1 + src/include/utils/sampling.h | 2 +- src/test/regress/expected/rules.out | 17 ++++ 9 files changed, 237 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index bf72d0c303..a924ab3b06 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -360,6 +360,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + pg_stat_progress_analyzepg_stat_progress_analyze + One row for each backend (including autovacuum worker processes) running + ANALYZE, showing current progress. + See . + + + pg_stat_progress_clusterpg_stat_progress_cluster One row for each backend running @@ -3927,6 +3935,127 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, + + ANALYZE Progress Reporting + + + Whenever ANALYZE is running, the + pg_stat_progress_analyze view will contain a + row for ech backend that is currently running that command. The tables + below describe the information that will be reported and provide + information about how to interpret it. + + + + <structname>pg_stat_progress_analyze</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 analyzed. + + + include_children + boolean + Whether the current scan includes legacy inheritance children. + + + scanning_table + oid + + The table being scanned (differs from relid + only when processing partitions or inheritance children). + + + + phase + text + Current processing phase. See + + + heap_blks_total + bigint + + Total number of heap blocks to scan in the current table. + + + + heap_blks_scanned + bigint + + Number of heap blocks scanned. + + + + +
+ + + ANALYZE phases + + + + Phase + Description + + + + + initializing + + The command is preparing to begin scanning the heap. This phase is + expected to be very brief. + + + + scanning table + + The command is currently scanning the table. + + + + analyzing sample + + ANALYZE is currently extracting statistical data + from the sample obtained. + + + + analyzing sample (extended) + + ANALYZE is currently extracting statistical data + for extended statistics from the sample obtained. + + + + +
+ + CLUSTER Progress Reporting diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ea4c85e395..e497f59790 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -964,6 +964,21 @@ 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_analyze AS + SELECT + S.pid AS pid, S.datid AS datid, D.datname AS datname, + CAST(S.relid AS oid) AS relid, + CAST(CAST(S.param2 AS int) AS boolean) AS include_children, + CAST(S.param3 AS oid) AS scanning_table, + CASE S.param1 WHEN 0 THEN 'initializing' + WHEN 1 THEN 'scanning table' + WHEN 2 THEN 'analyzing sample' + WHEN 3 THEN 'analyzing sample (extended stats)' + END AS phase, + S.param4 AS heap_blks_total, S.param5 AS heap_blks_scanned + FROM pg_stat_get_progress_info('ANALYZE') AS S + LEFT JOIN pg_database D ON S.datid = D.oid; + CREATE VIEW pg_stat_progress_cluster AS SELECT S.pid AS pid, diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 6cb545c126..0a75549440 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -35,6 +35,7 @@ #include "catalog/pg_namespace.h" #include "catalog/pg_statistic_ext.h" #include "commands/dbcommands.h" +#include "commands/progress.h" #include "commands/tablecmds.h" #include "commands/vacuum.h" #include "executor/executor.h" @@ -251,6 +252,8 @@ analyze_rel(Oid relid, RangeVar *relation, LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); MyPgXact->vacuumFlags |= PROC_IN_ANALYZE; LWLockRelease(ProcArrayLock); + pgstat_progress_start_command(PROGRESS_COMMAND_ANALYZE, + RelationGetRelid(onerel)); /* * Do the normal non-recursive ANALYZE. We can skip this for partitioned @@ -275,6 +278,8 @@ analyze_rel(Oid relid, RangeVar *relation, */ relation_close(onerel, NoLock); + pgstat_progress_end_command(); + /* * Reset my PGXACT flag. Note: we need this here, and not in vacuum_rel, * because the vacuum flag is cleared by the end-of-xact code. @@ -493,6 +498,18 @@ do_analyze_rel(Relation onerel, VacuumParams *params, /* * Acquire the sample rows */ + { + const int index[] = { + PROGRESS_ANALYZE_PHASE, + PROGRESS_ANALYZE_INH + }; + const int64 val[] = { + PROGRESS_ANALYZE_PHASE_SCAN_TABLE, + inh + }; + + pgstat_progress_update_multi_param(2, index, val); + } rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple)); if (inh) numrows = acquire_inherited_sample_rows(onerel, elevel, @@ -512,7 +529,18 @@ do_analyze_rel(Relation onerel, VacuumParams *params, if (numrows > 0) { MemoryContext col_context, - old_context; + old_context; + const int index[] = { + PROGRESS_ANALYZE_PHASE, + PROGRESS_ANALYZE_TOTAL_BLOCKS, + PROGRESS_ANALYZE_BLOCKS_DONE + }; + const int64 val[] = { + PROGRESS_ANALYZE_PHASE_ANALYSIS, + 0, 0 + }; + + pgstat_progress_update_multi_param(3, index, val); col_context = AllocSetContextCreate(anl_context, "Analyze Column", @@ -574,10 +602,15 @@ do_analyze_rel(Relation onerel, VacuumParams *params, } /* Build extended statistics (if there are any). */ + pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE, + PROGRESS_ANALYZE_PHASE_ANALYSIS_EXTENDED); BuildRelationExtStatistics(onerel, totalrows, numrows, rows, attr_cnt, vacattrstats); } + pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE, + PROGRESS_ANALYZE_PHASE_COMPLETE); + /* * Update pages/tuples stats in pg_class ... but not if we're doing * inherited stats. @@ -1016,6 +1049,8 @@ acquire_sample_rows(Relation onerel, int elevel, ReservoirStateData rstate; TupleTableSlot *slot; TableScanDesc scan; + BlockNumber nblocks; + double blksdone = 0; Assert(targrows > 0); @@ -1025,7 +1060,20 @@ acquire_sample_rows(Relation onerel, int elevel, OldestXmin = GetOldestXmin(onerel, PROCARRAY_FLAGS_VACUUM); /* Prepare for sampling block numbers */ - BlockSampler_Init(&bs, totalblocks, targrows, random()); + nblocks = BlockSampler_Init(&bs, totalblocks, targrows, random()); + { + const int index[] = { + PROGRESS_ANALYZE_TOTAL_BLOCKS, + PROGRESS_ANALYZE_SCANREL + }; + const int64 val[] = { + nblocks, + RelationGetRelid(onerel) + }; + + pgstat_progress_update_multi_param(2, index, val); + } + /* Prepare for sampling rows */ reservoir_init_selection_state(&rstate, targrows); @@ -1086,6 +1134,9 @@ acquire_sample_rows(Relation onerel, int elevel, samplerows += 1; } + + pgstat_progress_update_param(PROGRESS_ANALYZE_BLOCKS_DONE, + ++blksdone); } ExecDropSingleTupleTableSlot(slot); diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 05240bfd14..98b01e54fa 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -469,6 +469,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; + if (pg_strcasecmp(cmd, "ANALYZE") == 0) + cmdtype = PROGRESS_COMMAND_ANALYZE; else if (pg_strcasecmp(cmd, "CLUSTER") == 0) cmdtype = PROGRESS_COMMAND_CLUSTER; else if (pg_strcasecmp(cmd, "CREATE INDEX") == 0) diff --git a/src/backend/utils/misc/sampling.c b/src/backend/utils/misc/sampling.c index d2a1537979..f7daece5ee 100644 --- a/src/backend/utils/misc/sampling.c +++ b/src/backend/utils/misc/sampling.c @@ -32,8 +32,10 @@ * Since we know the total number of blocks in advance, we can use the * straightforward Algorithm S from Knuth 3.4.2, rather than Vitter's * algorithm. + * + * Returns the number of blocks that BlockSampler_Next will return. */ -void +BlockNumber BlockSampler_Init(BlockSampler bs, BlockNumber nblocks, int samplesize, long randseed) { @@ -48,6 +50,8 @@ BlockSampler_Init(BlockSampler bs, BlockNumber nblocks, int samplesize, bs->m = 0; /* blocks selected so far */ sampler_random_init_state(randseed, bs->randstate); + + return Min(bs->n, bs->N); } bool diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index acd1313cb3..3007351922 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -34,6 +34,20 @@ #define PROGRESS_VACUUM_PHASE_TRUNCATE 5 #define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6 +/* Progress parameters for analyze */ +#define PROGRESS_ANALYZE_PHASE 0 +#define PROGRESS_ANALYZE_INH 1 +#define PROGRESS_ANALYZE_SCANREL 2 +#define PROGRESS_ANALYZE_TOTAL_BLOCKS 3 +#define PROGRESS_ANALYZE_BLOCKS_DONE 4 + +/* Phases of analyze (as advertised via PROGRESS_ANALYZE_PHASE) */ +#define PROGRESS_ANALYZE_PHASE_SCAN_TABLE 1 +#define PROGRESS_ANALYZE_PHASE_ANALYSIS 2 +#define PROGRESS_ANALYZE_PHASE_ANALYSIS_EXTENDED 3 +#define PROGRESS_ANALYZE_PHASE_COMPLETE 4 + + /* Progress parameters for cluster */ #define PROGRESS_CLUSTER_COMMAND 0 #define PROGRESS_CLUSTER_PHASE 1 diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 0a3ad3a188..9344654b69 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -956,6 +956,7 @@ typedef enum ProgressCommandType { PROGRESS_COMMAND_INVALID, PROGRESS_COMMAND_VACUUM, + PROGRESS_COMMAND_ANALYZE, PROGRESS_COMMAND_CLUSTER, PROGRESS_COMMAND_CREATE_INDEX } ProgressCommandType; diff --git a/src/include/utils/sampling.h b/src/include/utils/sampling.h index 541b507fb5..76d31dc126 100644 --- a/src/include/utils/sampling.h +++ b/src/include/utils/sampling.h @@ -37,7 +37,7 @@ typedef struct typedef BlockSamplerData *BlockSampler; -extern void BlockSampler_Init(BlockSampler bs, BlockNumber nblocks, +extern BlockNumber BlockSampler_Init(BlockSampler bs, BlockNumber nblocks, int samplesize, long randseed); extern bool BlockSampler_HasMore(BlockSampler bs); extern BlockNumber BlockSampler_Next(BlockSampler bs); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 210e9cd146..dd3191563d 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1846,6 +1846,23 @@ pg_stat_gssapi| SELECT s.pid, s.gss_princ AS principal, s.gss_enc AS encrypted FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc); +pg_stat_progress_analyze| SELECT s.pid, + s.datid, + d.datname, + s.relid, + ((s.param2)::integer)::boolean AS include_children, + (s.param3)::oid AS scanning_table, + CASE s.param1 + WHEN 0 THEN 'initializing'::text + WHEN 1 THEN 'scanning table'::text + WHEN 2 THEN 'analyzing sample'::text + WHEN 3 THEN 'analyzing sample (extended stats)'::text + ELSE NULL::text + END AS phase, + s.param4 AS heap_blks_total, + s.param5 AS heap_blks_scanned + FROM (pg_stat_get_progress_info('ANALYZE'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) + LEFT JOIN pg_database d ON ((s.datid = d.oid))); pg_stat_progress_cluster| SELECT s.pid, s.datid, d.datname, -- 2.17.1 --FCuugMFkClbJLl1L--