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
