Re: System load consideration before spawning parallel workers

On Fri, Jul 29, 2016 at 11:26 AM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com> wrote:
> we observed that spawning the specified number of parallel workers for
> every query that satisfies for parallelism is sometimes leading to
> performance drop compared to improvement during the peak system load
> with other processes. Adding more processes to the system is leading
> to more context switches thus it reducing the performance of other SQL
> operations.
>

Have you consider to tune using max_worker_processes, basically I
think even if you have kept the moderate value for
max_parallel_workers_per_gather, the number of processes might
increase if total number allowed is much bigger.

Are the total number of parallel workers more than number of
CPU's/cores in the system? If yes, I think that might be one reason
for seeing performance degradation.

> In order to avoid this problem, how about adding some kind of system
> load consideration into account before spawning the parallel workers?
>

Hook could be a possibility, but not sure how users are going to
decide the number of parallel workers, there might be other backends
as well which can consume resources. I think we might need some form
of throttling w.r.t assignment of parallel workers to avoid system
overload.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com