Re: System load consideration before spawning parallel workers

On Fri, Jul 29, 2016 at 8:48 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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.

Tuning max_worker_processes may work. But the problem here is, During the
peak load test, it is observed that setting parallel is leading to
drop in performance.

The main point here is, even if user set all the configurations properly to use
only the free resources as part of parallel query, in case if a sudden
load increase
can cause some performance problems.

>> 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.

There are some utilities and functions that are available to calculate the
current system load, based on the available resources and system load,
the module can allow the number of parallel workers that can start. In my
observation, adding this calculation will add some overhead for simple
queries. Because of this reason, i feel this can be hook function, only for
the users who want it, can be loaded.

Regards,
Hari Babu
Fujitsu Australia