Re: System load consideration before spawning parallel workers

On Thu, Sep 1, 2016 at 10:01 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 8/16/16 3:39 AM, Haribabu Kommi wrote:
>> Yes, we need to consider many parameters as a system load, not just only
>> the CPU. Here I attached a POC patch that implements the CPU load
>> calculation and decide the number of workers based on the available CPU
>> load. The load calculation code is not an optimized one, there are many ways
>> that can used to calculate the system load. This is just for an example.
>
> I see a number of discussion points here:
>
> We don't yet have enough field experience with the parallel query
> facilities to know what kind of use patterns there are and what systems
> for load management we need. So I think building a highly specific
> system like this seems premature. We have settings to limit process
> numbers, which seems OK as a start, and those knobs have worked
> reasonably well in other areas (e.g., max connections, autovacuum). We
> might well want to enhance this area, but we'll need more experience and
> information.
>
> If we think that checking the CPU load is a useful way to manage process
> resources, why not apply this to more kinds of processes? I could
> imagine that limiting connections by load could be useful. Parallel
> workers is only one specific niche of this problem.

+1 to all of this, particularly the point about parallel workers being
one niche aspect of an overall problem.

What I'd like to see in this area first is our moving away from the
work_mem model. I think it makes a lot of sense to manage memory
currently capped by the catch-all work_mem setting as a shared
resource, to be dynamically doled out among backends according to
availability, priority, and possibly other considerations. I see the
9.6 work on external sort as a building piece for that, as it removed
the one thing that was sensitive to work_mem in a surprising,
unpredictable way.

--
Peter Geoghegan