Re: Parallel workers stats in pg_stat_database

Hi,

This is a new patch which:

* fixes some typos
* changes the execgather / execgathermerge code so that the stats are 
accumulated in EState and inserted in pg_stat_database only once, during 
ExecutorEnd
* adds tests (very ugly, but I could get the parallel plan to be stable 
across make check executions.)


On 8/28/24 17:10, Benoit Lobréau wrote:
> 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

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