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(at)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