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