Hi hackers,
This patch introduces four new columns in pg_stat_database:
* parallel_worker_planned
* parallel_worker_launched
* parallel_maint_worker_planned
* parallel_maint_worker_launched
The intent is to help administrators evaluate the usage of parallel
workers in their databases and help sizing max_worker_processes,
max_parallel_workers or max_parallel_maintenance_workers).
Here is a test script:
psql << _EOF_
-- Index creation
DROP TABLE IF EXISTS test_pql;
CREATE TABLE test_pql(i int, j int);
INSERT INTO test_pql SELECT x,x FROM generate_series(1,1000000) as F(x);
-- 0 planned / 0 launched
EXPLAIN (ANALYZE)
SELECT 1;
-- 2 planned / 2 launched
EXPLAIN (ANALYZE)
SELECT i, avg(j) FROM test_pql GROUP BY i;
SET max_parallel_workers TO 1;
-- 4 planned / 1 launched
EXPLAIN (ANALYZE)
SELECT i, avg(j) FROM test_pql GROUP BY i
UNION
SELECT i, avg(j) FROM test_pql GROUP BY i;
RESET max_parallel_workers;
-- 1 planned / 1 launched
CREATE INDEX ON test_pql(i);
SET max_parallel_workers TO 0;
-- 1 planned / 0 launched
CREATE INDEX ON test_pql(j);
-- 1 planned / 0 launched
CREATE INDEX ON test_pql(i, j);
SET maintenance_work_mem TO '96MB';
RESET max_parallel_workers;
-- 2 planned / 2 launched
VACUUM (VERBOSE) test_pql;
SET max_parallel_workers TO 1;
-- 2 planned / 1 launched
VACUUM (VERBOSE) test_pql;
-- TOTAL: parallel workers: 6 planned / 3 launched
-- TOTAL: parallel maint workers: 7 planned / 4 launched
_EOF_
And the output in pg_stat_database a fresh server without any
configuration change except thoses in the script:
[local]:5445 postgres@postgres=# SELECT datname,
parallel_workers_planned, parallel_workers_launched,
parallel_maint_workers_planned, parallel_maint_workers_launched FROM pg
_stat_database WHERE datname = 'postgres' \gx
-[ RECORD 1 ]-------------------+---------
datname | postgres
parallel_workers_planned | 6
parallel_workers_launched | 3
parallel_maint_workers_planned | 7
parallel_maint_workers_launched | 4
Thanks to: Jehan-Guillaume de Rorthais, Guillaume Lelarge and Franck
Boudehen for the help and motivation boost.
---
Benoit Lobréau
Consultant
http://dalibo.com
From cabe5e8ed2e88d9cf219161394396ebacb33a5a0 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Wed, 28 Aug 2024 02:27:13 +0200
Subject: [PATCH] Adds four parallel workers stat columns to pg_stat_database
* parallel_workers_planned
* parallel_workers_launched
* parallel_maint_workers_planned
* parallel_maint_workers_launched
---
doc/src/sgml/monitoring.sgml | 36 ++++++++++++++++++++
src/backend/access/brin/brin.c | 4 +++
src/backend/access/nbtree/nbtsort.c | 4 +++
src/backend/catalog/system_views.sql | 4 +++
src/backend/commands/vacuumparallel.c | 5 +++
src/backend/executor/nodeGather.c | 5 +++
src/backend/executor/nodeGatherMerge.c | 5 +++
src/backend/utils/activity/pgstat_database.c | 36 ++++++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 12 +++++++
src/include/catalog/pg_proc.dat | 20 +++++++++++
src/include/pgstat.h | 7 ++++
src/test/regress/expected/rules.out | 4 +++
12 files changed, 142 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 55417a6fa9..8c4b11c11d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3508,6 +3508,42 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_workers_planned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of parallel workers planned by queries on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_workers_launched</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of parallel workers obtained by queries on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_maint_workers_planned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of parallel workers planned by utilities on this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_maint_workers_launched</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of parallel workers obtained by utilities on this database
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 6467bed604..9eceb87b52 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -2540,6 +2540,10 @@ _brin_end_parallel(BrinLeader *brinleader, BrinBuildState *state)
/* Shutdown worker processes */
WaitForParallelWorkersToFinish(brinleader->pcxt);
+ pgstat_update_parallel_maint_workers_stats(
+ (PgStat_Counter) brinleader->pcxt->nworkers_to_launch,
+ (PgStat_Counter) 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 f5d7b3b0c3..232e1a0942 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -1611,6 +1611,10 @@ _bt_end_parallel(BTLeader *btleader)
/* Shutdown worker processes */
WaitForParallelWorkersToFinish(btleader->pcxt);
+ pgstat_update_parallel_maint_workers_stats(
+ (PgStat_Counter) btleader->pcxt->nworkers_to_launch,
+ (PgStat_Counter) btleader->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/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 19cabc9a47..48bf9e5535 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1073,6 +1073,10 @@ CREATE VIEW pg_stat_database AS
pg_stat_get_db_sessions_abandoned(D.oid) AS sessions_abandoned,
pg_stat_get_db_sessions_fatal(D.oid) AS sessions_fatal,
pg_stat_get_db_sessions_killed(D.oid) AS sessions_killed,
+ pg_stat_get_db_parallel_workers_planned(D.oid) as parallel_workers_planned,
+ pg_stat_get_db_parallel_workers_launched(D.oid) as parallel_workers_launched,
+ pg_stat_get_db_parallel_maint_workers_planned(D.oid) as parallel_maint_workers_planned,
+ pg_stat_get_db_parallel_maint_workers_launched(D.oid) as parallel_maint_workers_launched,
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM (
SELECT 0 AS oid, NULL::name AS datname
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 22c057fe61..f7603a0863 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -435,6 +435,11 @@ parallel_vacuum_end(ParallelVacuumState *pvs, IndexBulkDeleteResult **istats)
{
Assert(!IsParallelWorker());
+ pgstat_update_parallel_maint_workers_stats(
+ (PgStat_Counter) pvs->pcxt->nworkers_to_launch,
+ (PgStat_Counter) pvs->pcxt->nworkers_launched
+ );
+
/* Copy the updated statistics */
for (int i = 0; i < pvs->nindexes; i++)
{
diff --git a/src/backend/executor/nodeGather.c b/src/backend/executor/nodeGather.c
index 5d4ffe989c..2cfcb1411e 100644
--- a/src/backend/executor/nodeGather.c
+++ b/src/backend/executor/nodeGather.c
@@ -36,6 +36,7 @@
#include "executor/tqueue.h"
#include "miscadmin.h"
#include "optimizer/optimizer.h"
+#include "pgstat.h"
#include "utils/wait_event.h"
@@ -182,6 +183,10 @@ ExecGather(PlanState *pstate)
/* We save # workers launched for the benefit of EXPLAIN */
node->nworkers_launched = pcxt->nworkers_launched;
+ pgstat_update_parallel_workers_stats(
+ (PgStat_Counter) pcxt->nworkers_to_launch,
+ (PgStat_Counter) pcxt->nworkers_launched);
+
/* Set up tuple queue readers to read the results. */
if (pcxt->nworkers_launched > 0)
{
diff --git a/src/backend/executor/nodeGatherMerge.c b/src/backend/executor/nodeGatherMerge.c
index 45f6017c29..3dc3231511 100644
--- a/src/backend/executor/nodeGatherMerge.c
+++ b/src/backend/executor/nodeGatherMerge.c
@@ -21,6 +21,7 @@
#include "lib/binaryheap.h"
#include "miscadmin.h"
#include "optimizer/optimizer.h"
+#include "pgstat.h"
/*
* When we read tuples from workers, it's a good idea to read several at once
@@ -223,6 +224,10 @@ ExecGatherMerge(PlanState *pstate)
/* We save # workers launched for the benefit of EXPLAIN */
node->nworkers_launched = pcxt->nworkers_launched;
+ pgstat_update_parallel_workers_stats(
+ (PgStat_Counter) pcxt->nworkers_to_launch,
+ (PgStat_Counter) pcxt->nworkers_launched);
+
/* Set up tuple queue readers to read the results. */
if (pcxt->nworkers_launched > 0)
{
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 29bc090974..9e72c286b2 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -262,6 +262,38 @@ AtEOXact_PgStat_Database(bool isCommit, bool parallel)
}
}
+/*
+ * reports parallel_workers_planned and parallel_workers_launched into
+ * PgStat_StatDBEntry
+ */
+void
+pgstat_update_parallel_workers_stats(PgStat_Counter parallel_workers_planned, PgStat_Counter parallel_workers_launched)
+{
+ PgStat_StatDBEntry *dbentry;
+ if (!OidIsValid(MyDatabaseId))
+ return;
+
+ dbentry = pgstat_prep_database_pending(MyDatabaseId);
+ dbentry->parallel_workers_planned += parallel_workers_planned;
+ dbentry->parallel_workers_launched += parallel_workers_launched;
+}
+
+/*
+ * reports parallel_maint_workers_planned and parallel_maint_workers_launched into
+ * PgStat_StatDBEntry
+ */
+void
+pgstat_update_parallel_maint_workers_stats(PgStat_Counter parallel_maint_workers_planned, PgStat_Counter parallel_maint_workers_launched)
+{
+ PgStat_StatDBEntry *dbentry;
+ if (!OidIsValid(MyDatabaseId))
+ return;
+
+ dbentry = pgstat_prep_database_pending(MyDatabaseId);
+ dbentry->parallel_maint_workers_planned += parallel_maint_workers_planned;
+ dbentry->parallel_maint_workers_launched += parallel_maint_workers_launched;
+}
+
/*
* Subroutine for pgstat_report_stat(): Handle xact commit/rollback and I/O
* timings.
@@ -425,6 +457,10 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
PGSTAT_ACCUM_DBCOUNT(sessions_abandoned);
PGSTAT_ACCUM_DBCOUNT(sessions_fatal);
PGSTAT_ACCUM_DBCOUNT(sessions_killed);
+ PGSTAT_ACCUM_DBCOUNT(parallel_workers_planned);
+ PGSTAT_ACCUM_DBCOUNT(parallel_workers_launched);
+ PGSTAT_ACCUM_DBCOUNT(parallel_maint_workers_planned);
+ PGSTAT_ACCUM_DBCOUNT(parallel_maint_workers_launched);
#undef PGSTAT_ACCUM_DBCOUNT
pgstat_unlock_entry(entry_ref);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 3221137123..377a0f6453 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1039,6 +1039,18 @@ PG_STAT_GET_DBENTRY_INT64(sessions_fatal)
/* pg_stat_get_db_sessions_killed */
PG_STAT_GET_DBENTRY_INT64(sessions_killed)
+/* pg_stat_get_db_parallel_workers_planned*/
+PG_STAT_GET_DBENTRY_INT64(parallel_workers_planned)
+
+/* pg_stat_get_db_parallel_workers_launched*/
+PG_STAT_GET_DBENTRY_INT64(parallel_workers_launched)
+
+/* pg_stat_get_db_parallel_maint_workers_planned*/
+PG_STAT_GET_DBENTRY_INT64(parallel_maint_workers_planned)
+
+/* pg_stat_get_db_parallel_maint_workers_launched*/
+PG_STAT_GET_DBENTRY_INT64(parallel_maint_workers_launched)
+
/* pg_stat_get_db_temp_bytes */
PG_STAT_GET_DBENTRY_INT64(temp_bytes)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4abc6d9526..b1cd4fa1b0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5751,6 +5751,26 @@
proname => 'pg_stat_get_db_sessions_killed', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_db_sessions_killed' },
+{ oid => '8403',
+ descr => 'statistics: number of parallel workers planned for queries',
+ proname => 'pg_stat_get_db_parallel_workers_planned', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_parallel_workers_planned' },
+{ oid => '8404',
+ descr => 'statistics: number of parallel workers effectively launched for queries',
+ proname => 'pg_stat_get_db_parallel_workers_launched', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_parallel_workers_launched' },
+{ oid => '8405',
+ descr => 'statistics: number of parallel workers planned for utilities',
+ proname => 'pg_stat_get_db_parallel_maint_workers_planned', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_parallel_maint_workers_planned' },
+{ oid => '8406',
+ descr => 'statistics: number of parallel workers effectively launched for utilities',
+ proname => 'pg_stat_get_db_parallel_maint_workers_launched', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_parallel_maint_workers_launched' },
{ oid => '3195', descr => 'statistics: information about WAL archiver',
proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f63159c55c..3bb3e045d1 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -383,6 +383,11 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter sessions_fatal;
PgStat_Counter sessions_killed;
+ PgStat_Counter parallel_workers_planned;
+ PgStat_Counter parallel_workers_launched;
+ PgStat_Counter parallel_maint_workers_planned;
+ PgStat_Counter parallel_maint_workers_launched;
+
TimestampTz stat_reset_timestamp;
} PgStat_StatDBEntry;
@@ -578,6 +583,8 @@ extern void pgstat_report_deadlock(void);
extern void pgstat_report_checksum_failures_in_db(Oid dboid, int failurecount);
extern void pgstat_report_checksum_failure(void);
extern void pgstat_report_connect(Oid dboid);
+extern void pgstat_update_parallel_workers_stats(PgStat_Counter parallel_maint_workers_planned, PgStat_Counter parallel_maint_workers_launched);
+extern void pgstat_update_parallel_maint_workers_stats(PgStat_Counter parallel_maint_workers_planned, PgStat_Counter parallel_maint_workers_launched);
#define pgstat_count_buffer_read_time(n) \
(pgStatBlockReadTime += (n))
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 862433ee52..e8a4453cd5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1861,6 +1861,10 @@ pg_stat_database| SELECT oid AS datid,
pg_stat_get_db_sessions_abandoned(oid) AS sessions_abandoned,
pg_stat_get_db_sessions_fatal(oid) AS sessions_fatal,
pg_stat_get_db_sessions_killed(oid) AS sessions_killed,
+ pg_stat_get_db_parallel_workers_planned(oid) AS parallel_workers_planned,
+ pg_stat_get_db_parallel_workers_launched(oid) AS parallel_workers_launched,
+ pg_stat_get_db_parallel_maint_workers_planned(oid) AS parallel_maint_workers_planned,
+ pg_stat_get_db_parallel_maint_workers_launched(oid) AS parallel_maint_workers_launched,
pg_stat_get_db_stat_reset_time(oid) AS stats_reset
FROM ( SELECT 0 AS oid,
NULL::name AS datname
--
2.45.2
