Обсуждение: max_parallel_workers can't actually be set?

Поиск
Список
Период
Сортировка

max_parallel_workers can't actually be set?

От
Tom Lane
Дата:
Try this:
alter system set max_parallel_workers = 20;
and restart the system.

max_parallel_workers is still 8, according to both SHOW and
pg_controldata, nor can you launch more than 8 workers.

Even odder, if you just do

regression=# set max_parallel_workers = 200;
SET
regression=# show max_parallel_workers;      
 max_parallel_workers 
----------------------
 200
(1 row)

which should certainly not happen for a PGC_POSTMASTER parameter.

We seem to have an awful lot of mechanism that's concerned with
adjustments of max_parallel_workers, for something that apparently
might as well be a compile-time #define ... so I assume it's supposed
to be changeable at restart and somebody broke it.  But it's not
working as I'd expect in any branch from 10 onwards.

            regards, tom lane

Re: max_parallel_workers can't actually be set?

От
Darafei "Komяpa" Praliaskouski
Дата:
Hi,

On my PG11 I have set it to 64 upon setup and it propogated to postgresql.auto.conf and is set after restart. I've upgraded to PG12 since then, and parameter is read from postgresql.auto.conf correctly and is displayed via SHOW (just checked on 12beta3).

I also spent some time trying to get a plan that will give me 32 workers. Largest I ever got without taking a hammer was 16, which is half of available cores, or all non-HT ones. I still haven't found a way to set costs and limits to load all the system with a query.

ALTER TABLE ... SET (parallel_workers=32); is currently my most favorite hammer. I set max_worker_processes to 512 and letting OS scheduler resolve the hours when four queries run 128 CPU-bound processes on 32-core machine, it's not as good as if the limits were adjusted dynamically after the query start but much better than running a second query with just 1 worker even after first one finishes.

On Sat, Aug 17, 2019 at 8:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Try this:
alter system set max_parallel_workers = 20;
and restart the system.

max_parallel_workers is still 8, according to both SHOW and
pg_controldata, nor can you launch more than 8 workers.

Even odder, if you just do

regression=# set max_parallel_workers = 200;
SET
regression=# show max_parallel_workers;     
 max_parallel_workers
----------------------
 200
(1 row)

which should certainly not happen for a PGC_POSTMASTER parameter.

We seem to have an awful lot of mechanism that's concerned with
adjustments of max_parallel_workers, for something that apparently
might as well be a compile-time #define ... so I assume it's supposed
to be changeable at restart and somebody broke it.  But it's not
working as I'd expect in any branch from 10 onwards.

                        regards, tom lane




--
Darafei Praliaskouski

Re: max_parallel_workers can't actually be set?

От
Sergei Kornilov
Дата:
Hello

> Try this:
> alter system set max_parallel_workers = 20;
> and restart the system.
> max_parallel_workers is still 8

Hmm, I got 20 on my local 11.5 and on HEAD.

> which should certainly not happen for a PGC_POSTMASTER parameter.

But max_parallel_workers is PGC_USERSET and this behavior seems be documented:

> Also, note that a setting for this value which is higher than max_worker_processes will have no effect, since
parallelworkers are taken from the pool of worker processes established by that setting.
 

regards, Sergei

Re: max_parallel_workers can't actually be set?

От
Ibrar Ahmed
Дата:

On Sat, Aug 17, 2019 at 10:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Try this:
alter system set max_parallel_workers = 20;
and restart the system.

max_parallel_workers is still 8, according to both SHOW and
pg_controldata, nor can you launch more than 8 workers.

Even odder, if you just do

regression=# set max_parallel_workers = 200;
SET
regression=# show max_parallel_workers;     
 max_parallel_workers
----------------------
 200
(1 row)

which should certainly not happen for a PGC_POSTMASTER parameter.

We seem to have an awful lot of mechanism that's concerned with
adjustments of max_parallel_workers, for something that apparently
might as well be a compile-time #define ... so I assume it's supposed
to be changeable at restart and somebody broke it.  But it's not
working as I'd expect in any branch from 10 onwards.

                        regards, tom lane



If I understand that correctly it works for me.

postgres=# alter system set max_parallel_workers = 1;
$ pg_ctl restart -l log
$ psql postgres
psql (13devel)
postgres=# explain analyze select * from test where b > 1;                                                      

                                                               QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..98294.84 rows=1 width=8) (actual time=1635.959..1636.028 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 1
   ->  Parallel Seq Scan on test  (cost=0.00..97294.74 rows=1 width=8) (actual time=1632.239..1632.239 rows=0 loops=2)
         Filter: (b > 1)
         Rows Removed by Filter: 5050000

 Planning Time: 0.533 ms
 Execution Time: 1636.080 ms
(8 rows)



postgres=# alter system set max_parallel_workers = 2;
ALTER SYSTEM
postgres=# \q
vagrant@vagrant:~/percona/postgresql$ pg_ctl restart -l log
vagrant@vagrant:~/percona/postgresql$ psql postgres
psql (13devel)
Type "help" for help.
postgres=# explain analyze select * from test where b > 1;
                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..98294.84 rows=1 width=8) (actual time=1622.210..1622.274 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..97294.74 rows=1 width=8) (actual time=1616.000..1616.000 rows=0 loops=3)
         Filter: (b > 1)
         Rows Removed by Filter: 3366667
 Planning Time: 0.699 ms
 Execution Time: 1622.325 ms
(8 rows)

 


--
Ibrar Ahmed

Re: max_parallel_workers can't actually be set?

От
Tom Lane
Дата:
Sergei Kornilov <sk@zsrv.org> writes:
>> which should certainly not happen for a PGC_POSTMASTER parameter.

> But max_parallel_workers is PGC_USERSET and this behavior seems be documented:

Argh!  I was looking at max_worker_processes in one window and
max_parallel_workers in another, and failed to see the discrepancy.

Those GUCs are too confusingly named :-(.

            regards, tom lane

Re: max_parallel_workers can't actually be set?

От
Andres Freund
Дата:
Hi,

On 2019-08-17 13:41:18 -0400, Tom Lane wrote:
> Try this:
> alter system set max_parallel_workers = 20;
> and restart the system.
> 
> max_parallel_workers is still 8, according to both SHOW and
> pg_controldata, nor can you launch more than 8 workers.

Hm. I can't reproduce that. I do get whatever number I configure.  Note
also that pg_controldata shows max_worker_processes, not
max_parallel_workers.


> Even odder, if you just do
> 
> regression=# set max_parallel_workers = 200;
> SET
> regression=# show max_parallel_workers;      
>  max_parallel_workers 
> ----------------------
>  200
> (1 row)
> 
> which should certainly not happen for a PGC_POSTMASTER parameter.

It's not PGC_POSTMASTER. That's max_worker_processes. The max_parallel_*
GUCs just control how many of max_worker_processes are used for $task.

Greetings,

Andres Freund