Re: System load consideration before spawning parallel workers

On 01/08/16 18:08, Haribabu Kommi wrote:
> 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
>
>
Possibly look how make does it with the '-l' flag?

'-l 8' don't start more process when load is 8 or greater, works on
Linux at least...

Cheers,
Gavin