Postgres Pro
Downloads
Home   >   mailing lists

Thread: create index insist on 2 workers only

create index insist on 2 workers only

From
Radoslav Nedyalkov
Date:
Hello everybody,

We 're rebuilding a big table which has set parallel_workers = 6
system has
 max_parallel_maintenance_workers | 6       | /var/lib/pgsql/11/data/postgresql.sumup.conf
 max_parallel_workers             | 16      | /var/lib/pgsql/11/data/postgresql.sumup.conf
 max_parallel_workers_per_gather  | 4       | /var/lib/pgsql/11/data/postgresql.sumup.conf

Also session level on index restore there is 
set max_parallel_maintenance_workers = 6;

Still we get only 2 parallel processes in a free of any other load system.
It is postgres 11.7 

Where to look at ?

Thank You very much

Rado

Re: create index insist on 2 workers only

From
Peter Geoghegan
Date:
On Fri, Apr 24, 2020 at 7:32 AM Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
> We 're rebuilding a big table which has set parallel_workers = 6
> system has
>  max_parallel_maintenance_workers | 6       | /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers             | 16      | /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers_per_gather  | 4       | /var/lib/pgsql/11/data/postgresql.sumup.conf
>
> Also session level on index restore there is
> set max_parallel_maintenance_workers = 6;
>
> Still we get only 2 parallel processes in a free of any other load system.
> It is postgres 11.7

Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.

-- 
Peter Geoghegan

Re: create index insist on 2 workers only

From
Radoslav Nedyalkov
Date:
Just to reply for completeness.
MWM was high enough (4GB) .
Apparently we were having max_worker_processes = 18 and 16 subscriptions running, leaving no room for additional processes. What an oversight.
Once mwp was bumped (to 42 on 64 core machine), everything  started working as expected.

On Sat, Apr 25, 2020 at 5:03 AM Peter Geoghegan <pg@bowt.ie> wrote:
On Fri, Apr 24, 2020 at 7:32 AM Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
> We 're rebuilding a big table which has set parallel_workers = 6
> system has
>  max_parallel_maintenance_workers | 6       | /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers             | 16      | /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers_per_gather  | 4       | /var/lib/pgsql/11/data/postgresql.sumup.conf
>
> Also session level on index restore there is
> set max_parallel_maintenance_workers = 6;
>
> Still we get only 2 parallel processes in a free of any other load system.
> It is postgres 11.7

Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.

--
Peter Geoghegan