This is a rebased version.
I have split queries, vacuum and index creation in different patches.
I have also split the declartion that are in common with the
pg_stat_database patch.
--
Benoit Lobréau
Consultant
http://dalibo.com
From cfa4263333a080ca2d0c484ac8f5201800bd6434 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Fri, 11 Oct 2024 23:59:15 +0200
Subject: [PATCH 5/5] Implements logging for parallel worker usage in queries
---
src/backend/executor/execMain.c | 12 ++++++++++++
src/backend/executor/execUtils.c | 3 +++
src/backend/executor/nodeGather.c | 5 +++++
src/backend/executor/nodeGatherMerge.c | 5 +++++
src/include/nodes/execnodes.h | 4 ++++
5 files changed, 29 insertions(+)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index cc9a594cba..6663fea7b1 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -483,6 +483,18 @@ standard_ExecutorEnd(QueryDesc *queryDesc)
Assert(estate != NULL);
+ if ((log_parallel_workers == LOG_PARALLEL_WORKERS_ALL &&
+ estate->es_parallel_workers_to_launch > 0) ||
+ (log_parallel_workers == LOG_PARALLEL_WORKERS_FAILURE &&
+ estate->es_parallel_workers_to_launch != estate->es_parallel_workers_launched))
+ elog(LOG, "%i parallel nodes planned (%i obtained all their workers, %i obtained none), "
+ "%i workers planned to be launched (%i workers launched)",
+ estate->es_parallel_nodes,
+ estate->es_parallel_nodes_success,
+ estate->es_parallel_nodes_no_workers,
+ estate->es_parallel_workers_to_launch,
+ estate->es_parallel_workers_launched);
+
/*
* Check that ExecutorFinish was called, unless in EXPLAIN-only mode. This
* Assert is needed because ExecutorFinish is new as of 9.1, and callers
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 6712302ec8..fd463f7e4c 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -160,6 +160,9 @@ CreateExecutorState(void)
estate->es_use_parallel_mode = false;
estate->es_parallel_workers_to_launch = 0;
estate->es_parallel_workers_launched = 0;
+ estate->es_parallel_nodes = 0;
+ estate->es_parallel_nodes_success = 0;
+ estate->es_parallel_nodes_no_workers = 0;
estate->es_jit_flags = 0;
estate->es_jit = NULL;
diff --git a/src/backend/executor/nodeGather.c b/src/backend/executor/nodeGather.c
index 7f7edc7f9f..49a96f7cbf 100644
--- a/src/backend/executor/nodeGather.c
+++ b/src/backend/executor/nodeGather.c
@@ -188,6 +188,10 @@ ExecGather(PlanState *pstate)
*/
estate->es_parallel_workers_to_launch += pcxt->nworkers_to_launch;
estate->es_parallel_workers_launched += pcxt->nworkers_launched;
+ estate->es_parallel_nodes += 1;
+
+ if (pcxt->nworkers_to_launch == pcxt->nworkers_launched)
+ estate->es_parallel_nodes_success += 1;
/* Set up tuple queue readers to read the results. */
if (pcxt->nworkers_launched > 0)
@@ -205,6 +209,7 @@ ExecGather(PlanState *pstate)
/* No workers? Then never mind. */
node->nreaders = 0;
node->reader = NULL;
+ estate->es_parallel_nodes_no_workers += 1;
}
node->nextreader = 0;
}
diff --git a/src/backend/executor/nodeGatherMerge.c b/src/backend/executor/nodeGatherMerge.c
index bc99c0b448..f3aa7ee14f 100644
--- a/src/backend/executor/nodeGatherMerge.c
+++ b/src/backend/executor/nodeGatherMerge.c
@@ -229,6 +229,10 @@ ExecGatherMerge(PlanState *pstate)
*/
estate->es_parallel_workers_to_launch += pcxt->nworkers_to_launch;
estate->es_parallel_workers_launched += pcxt->nworkers_launched;
+ estate->es_parallel_nodes += 1;
+
+ if (pcxt->nworkers_to_launch == pcxt->nworkers_launched)
+ estate->es_parallel_nodes_success += 1;
/* Set up tuple queue readers to read the results. */
if (pcxt->nworkers_launched > 0)
@@ -246,6 +250,7 @@ ExecGatherMerge(PlanState *pstate)
/* No workers? Then never mind. */
node->nreaders = 0;
node->reader = NULL;
+ estate->es_parallel_nodes_no_workers += 1;
}
}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e4698a28c4..d87af53853 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -713,6 +713,10 @@ typedef struct EState
int es_parallel_workers_launched; /* number of workers actually
* launched. */
+ int es_parallel_nodes;
+ int es_parallel_nodes_success;
+ int es_parallel_nodes_no_workers;
+
/* The per-query shared memory area to use for parallel execution. */
struct dsa_area *es_query_dsa;
--
2.46.2
From c6dfd6d7751337a9e7a435efe742c4aea9d831e3 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Fri, 11 Oct 2024 23:58:40 +0200
Subject: [PATCH 4/5] Implements logging for parallel worker usage in vacuums
---
src/backend/commands/vacuumparallel.c | 8 ++++++++
1 file changed, 8 insertions(+)
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 77679e8df6..e207a6b7d9 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -443,6 +443,14 @@ parallel_vacuum_end(ParallelVacuumState *pvs, IndexBulkDeleteResult **istats)
{
Assert(!IsParallelWorker());
+ if ((log_parallel_workers == LOG_PARALLEL_WORKERS_ALL &&
+ pvs->nworkers_to_launch > 0) ||
+ (log_parallel_workers == LOG_PARALLEL_WORKERS_FAILURE &&
+ pvs->nworkers_to_launch > pvs->nworkers_launched))
+ elog(LOG, "%i workers planned to be launched for index cleanup and bulkdelete (%i workers launched)",
+ pvs->nworkers_to_launch,
+ pvs->nworkers_launched);
+
/* Copy the updated statistics */
for (int i = 0; i < pvs->nindexes; i++)
{
--
2.46.2
From 4cc5b301c9d0cc3f86dd84fe30eac7cfa2abbf25 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Fri, 11 Oct 2024 23:56:23 +0200
Subject: [PATCH 3/5] Setup counters for parallel vacuums
This is used by the logging and pg_stat_database patches.
---
src/backend/commands/vacuumparallel.c | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 4fd6574e12..77679e8df6 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -208,6 +208,9 @@ struct ParallelVacuumState
int nindexes_parallel_cleanup;
int nindexes_parallel_condcleanup;
+ int nworkers_to_launch;
+ int nworkers_launched;
+
/* Buffer access strategy used by leader process */
BufferAccessStrategy bstrategy;
@@ -362,6 +365,9 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
if ((vacoptions & VACUUM_OPTION_PARALLEL_COND_CLEANUP) != 0)
pvs->nindexes_parallel_condcleanup++;
}
+ pvs->nworkers_to_launch = 0;
+ pvs->nworkers_launched = 0;
+
shm_toc_insert(pcxt->toc, PARALLEL_VACUUM_KEY_INDEX_STATS, indstats);
pvs->indstats = indstats;
@@ -739,6 +745,9 @@ parallel_vacuum_process_all_indexes(ParallelVacuumState *pvs, int num_index_scan
for (int i = 0; i < pvs->pcxt->nworkers_launched; i++)
InstrAccumParallelQuery(&pvs->buffer_usage[i], &pvs->wal_usage[i]);
+
+ pvs->nworkers_to_launch += pvs->pcxt->nworkers_to_launch;
+ pvs->nworkers_launched += pvs->pcxt->nworkers_launched;
}
/*
--
2.46.2
From 367b163e135011290125fc4db7586f6603e09270 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Tue, 8 Oct 2024 12:45:03 +0200
Subject: [PATCH 2/5] Implements logging for parallel worker usage in index
builds
Two types of index are concerned: brin and btree.
---
src/backend/access/brin/brin.c | 8 ++++++++
src/backend/access/nbtree/nbtsort.c | 8 ++++++++
2 files changed, 16 insertions(+)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index c0b978119a..fdfbbc7dc4 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -2544,6 +2544,14 @@ _brin_end_parallel(BrinLeader *brinleader, BrinBuildState *state)
/* Shutdown worker processes */
WaitForParallelWorkersToFinish(brinleader->pcxt);
+ if ((log_parallel_workers == LOG_PARALLEL_WORKERS_ALL &&
+ brinleader->pcxt->nworkers_to_launch > 0) ||
+ (log_parallel_workers == LOG_PARALLEL_WORKERS_FAILURE &&
+ brinleader->pcxt->nworkers_to_launch > brinleader->pcxt->nworkers_launched))
+ elog(LOG, "%i workers planned (%i workers launched)",
+ brinleader->pcxt->nworkers_to_launch,
+ brinleader->pcxt->nworkers_launched);
+
/*
* Next, accumulate WAL usage. (This must wait for the workers to finish,
* or we might get incomplete data.)
diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c
index 5cca0d4f52..e31bd8e223 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -1615,6 +1615,14 @@ _bt_end_parallel(BTLeader *btleader)
/* Shutdown worker processes */
WaitForParallelWorkersToFinish(btleader->pcxt);
+ if ((log_parallel_workers == LOG_PARALLEL_WORKERS_ALL &&
+ btleader->pcxt->nworkers_to_launch > 0) ||
+ (log_parallel_workers == LOG_PARALLEL_WORKERS_FAILURE &&
+ btleader->pcxt->nworkers_to_launch > btleader->pcxt->nworkers_launched))
+ elog(LOG, "%i workers planned (%i workers launched)",
+ btleader->pcxt->nworkers_to_launch,
+ btleader->pcxt->nworkers_launched);
+
/*
* Next, accumulate WAL usage. (This must wait for the workers to finish,
* or we might get incomplete data.)
--
2.46.2
From 676fbaf183639b37c382e784c89558a98ff7af9c Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Tue, 8 Oct 2024 12:39:41 +0200
Subject: [PATCH 1/5] Add a guc for parallel worker logging
The new guc log_parallel_workers controls whether a log message is
produced to display information on the number of workers spawned when a
parallel query or utility is executed.
The default value is `none` which disables logging. `all` displays
information for all parallel queries, whereas `failures` displays
information only when the number of workers launched is lower than the
number of planned workers.
This new parameter can help database administrators and developers
diagnose performance issues related to parallelism and optimize the
configuration of the system accordingly.
---
doc/src/sgml/config.sgml | 18 ++++++++++++++++++
src/backend/utils/misc/guc_tables.c | 12 ++++++++++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/access/parallel.h | 15 +++++++++++++++
4 files changed, 46 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 9707d5238d..5c04a27eb6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7696,6 +7696,24 @@ log_line_prefix = '%m [%p] %q%u@%d/%a '
</listitem>
</varlistentry>
+ <varlistentry id="guc-log-parallel-workers" xreflabel="log_parallel_workers">
+ <term><varname>log_parallel_workers</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>log_parallel_workers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether a log message is produced to display information on the number of
+ workers spawned when a parallel query or utility is executed. The default value is
+ <literal>none</literal> which disables logging. <literal>all</literal> displays
+ information for all parallel queries, whereas <literal>failures</literal> displays
+ information only when the number of workers launched is lower than the number of
+ planned workers.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-parameter-max-length" xreflabel="log_parameter_max_length">
<term><varname>log_parameter_max_length</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 686309db58..1f9e2b1a33 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -482,6 +482,7 @@ extern const struct config_enum_entry archive_mode_options[];
extern const struct config_enum_entry recovery_target_action_options[];
extern const struct config_enum_entry wal_sync_method_options[];
extern const struct config_enum_entry dynamic_shared_memory_options[];
+extern const struct config_enum_entry log_parallel_workers_options[];
/*
* GUC option variables that are exported from this module
@@ -526,6 +527,7 @@ int log_min_duration_statement = -1;
int log_parameter_max_length = -1;
int log_parameter_max_length_on_error = 0;
int log_temp_files = -1;
+int log_parallel_workers = LOG_PARALLEL_WORKERS_NONE;
double log_statement_sample_rate = 1.0;
double log_xact_sample_rate = 0;
char *backtrace_functions;
@@ -5196,6 +5198,16 @@ struct config_enum ConfigureNamesEnum[] =
NULL, NULL, NULL
},
+ {
+ {"log_parallel_workers", PGC_SUSET, LOGGING_WHAT,
+ gettext_noop("Log information about parallel worker usage"),
+ NULL
+ },
+ &log_parallel_workers,
+ LOG_PARALLEL_WORKERS_NONE, log_parallel_workers_options,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 667e0dc40a..7cd9c0f3f8 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -619,6 +619,7 @@
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
+#log_parallel_workers = none # none, all, failure
#log_timezone = 'GMT'
# - Process Title -
diff --git a/src/include/access/parallel.h b/src/include/access/parallel.h
index 69ffe5498f..ed33e545d7 100644
--- a/src/include/access/parallel.h
+++ b/src/include/access/parallel.h
@@ -19,6 +19,20 @@
#include "postmaster/bgworker.h"
#include "storage/shm_mq.h"
#include "storage/shm_toc.h"
+#include "utils/guc.h"
+
+typedef enum log_parallel_workers_option_list {
+ LOG_PARALLEL_WORKERS_NONE=0,
+ LOG_PARALLEL_WORKERS_ALL,
+ LOG_PARALLEL_WORKERS_FAILURE,
+} log_parallel_workers_option_list;
+
+static const struct config_enum_entry log_parallel_workers_options[] = {
+ {"none", LOG_PARALLEL_WORKERS_NONE, false},
+ {"all", LOG_PARALLEL_WORKERS_ALL, false},
+ {"failure", LOG_PARALLEL_WORKERS_FAILURE, false},
+ {NULL, 0, false}
+};
typedef void (*parallel_worker_main_type) (dsm_segment *seg, shm_toc *toc);
@@ -56,6 +70,7 @@ typedef struct ParallelWorkerContext
extern PGDLLIMPORT volatile sig_atomic_t ParallelMessagePending;
extern PGDLLIMPORT int ParallelWorkerNumber;
extern PGDLLIMPORT bool InitializingParallelWorker;
+extern PGDLLEXPORT int log_parallel_workers;
#define IsParallelWorker() (ParallelWorkerNumber >= 0)
--
2.46.2
