Logging parallel worker draught

From: Benoit Lobréau <benoit(dot)lobreau(at)dalibo(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Melanie Plageman <melanieplageman(at)gmail(dot)com>
Subject: Logging parallel worker draught
Date: 2023-04-21 13:04:01
Message-ID: b4220d15-2e21-0e98-921b-b9892543cc93@dalibo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

Following my previous mail about adding stats on parallelism[1], this
patch introduces the log_parallel_worker_draught parameter, which
controls whether a log message is produced when a backend attempts to
spawn a parallel worker but fails due to insufficient worker slots. The
shortage can stem from insufficent settings for max_worker_processes,
max_parallel_worker or max_parallel_maintenance_workers. It could also
be caused by another pool (max_logical_replication_workers) or an
extention using bg worker slots. This new parameter can help database
administrators and developers diagnose performance issues related to
parallelism and optimize the configuration of the system accordingly.

Here is a test script:

```
psql << _EOF_

SET log_parallel_worker_draught TO on;

-- 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);

SET max_parallel_workers TO 0;

CREATE INDEX ON test_pql(i);
REINDEX TABLE test_pql;

RESET max_parallel_workers;

-- VACUUM
CREATE INDEX ON test_pql(j);
CREATE INDEX ON test_pql(i,j);
ALTER TABLE test_pql SET (autovacuum_enabled = off);
DELETE FROM test_pql WHERE i BETWEEN 1000 AND 2000;

SET max_parallel_workers TO 1;

VACUUM (PARALLEL 2, VERBOSE) test_pql;

RESET max_parallel_workers;

-- SELECT
SET min_parallel_table_scan_size TO 0;
SET parallel_setup_cost TO 0;
SET max_parallel_workers TO 1;

EXPLAIN (ANALYZE) SELECT i, avg(j) FROM test_pql GROUP BY i;

_EOF_
```

Which produces the following logs:

```
LOG: Parallel Worker draught during statement execution: workers
spawned 0, requested 1
STATEMENT: CREATE INDEX ON test_pql(i);

LOG: Parallel Worker draught during statement execution: workers
spawned 0, requested 1
STATEMENT: REINDEX TABLE test_pql;

LOG: Parallel Worker draught during statement execution: workers
spawned 1, requested 2
CONTEXT: while scanning relation "public.test_pql"
STATEMENT: VACUUM (PARALLEL 2, VERBOSE) test_pql;

LOG: Parallel Worker draught during statement execution: workers
spawned 1, requested 2
STATEMENT: EXPLAIN (ANALYZE) SELECT i, avg(j) FROM test_pql GROUP BY i;
```

[1]
https://www.postgresql.org/message-id/d657df20-c4bf-63f6-e74c-cb85a81d0383@dalibo.com

--
Benoit Lobréau
Consultant
http://dalibo.com

Attachment Content-Type Size
0001-Add-logging-for-exceeded-parallel-worker-slot-limits.patch_v1 text/plain 4.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2023-04-21 13:42:57 base backup vs. concurrent truncation
Previous Message Robert Haas 2023-04-21 12:51:08 Re: Non-superuser subscription owners