Обсуждение: System load consideration before spawning parallel workers

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.

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

This may not be a problem for some users, so instead of adding the
code into the core for the system load calculation and etc, how about
providing some additional hook in the code? so that user who wants to
consider the system load registers the function and this hooks
provides the number of parallel workers that can be started.

comments?

Regards,
Hari Babu
Fujitsu Australia

On Fri, Jul 29, 2016 at 11:26 AM, Haribabu Kommi
<kommi.haribabu@gmail.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

On Fri, Jul 29, 2016 at 8:48 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Fri, Jul 29, 2016 at 11:26 AM, Haribabu Kommi
> <kommi.haribabu@gmail.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

On 01/08/16 18:08, Haribabu Kommi wrote:
> On Fri, Jul 29, 2016 at 8:48 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> On Fri, Jul 29, 2016 at 11:26 AM, Haribabu Kommi
>> <kommi.haribabu@gmail.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

On 8/1/16 1:08 AM, Haribabu Kommi wrote:
> 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.

I think we need to provide more tools to allow users to control system 
behavior on a more dynamic basis. How many workers to launch is a good 
example. There's more reasons than just CPU that parallel workers can 
help (IO being an obvious one, but possible other things like GPU). 
Another example is allowing users to alter the selection process used by 
autovac workers.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Fri, Aug 5, 2016 at 9:46 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 8/1/16 1:08 AM, Haribabu Kommi wrote:
>>
>> 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.
>
>
> I think we need to provide more tools to allow users to control system
> behavior on a more dynamic basis. How many workers to launch is a good
> example. There's more reasons than just CPU that parallel workers can help
> (IO being an obvious one, but possible other things like GPU). Another
> example is allowing users to alter the selection process used by autovac
> workers.

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.


Regards,
Hari Babu
Fujitsu Australia

On 8/1/16 2:17 AM, Gavin Flower wrote:
> 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...

The problem with that approach is that it takes about a minute for the
load averages figures to be updated, by which time you have already
thrashed your system.

You can try this out by building PostgreSQL this way.  Please save your
work first, because you might have to hard-reboot your system.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.

As I just wrote in another message in this thread, I don't trust system
load metrics very much as a gatekeeper.  They are reasonable for
long-term charting to discover trends, but there are numerous potential
problems for using them for this kind of resource control thing.

All of this seems very platform specific, too.  You have
Windows-specific code, but the rest seems very Linux-specific.  The
dstat tool I had never heard of before.  There is stuff with cgroups,
which I don't know how portable they are across different Linux
installations.  Something about Solaris was mentioned.  What about the
rest?  How can we maintain this in the long term?  How do we know that
these facilities actually work correctly and not cause mysterious problems?

There is a bunch of math in there that is not documented much.  I can't
tell without reverse engineering the code what any of this is supposed
to do.

My suggestion is that we focus on refining the process control numbers
that we already have.  We had extensive discussions about that during
9.6 beta.  We have related patches in the commit fest right now.  Many
ideas have been posted.  System admins are generally able to count their
CPUs and match that to the number of sessions and jobs they need to run.Everything beyond that could be great but seems
prematurebefore we
 
have the basics figured out.

Maybe a couple of hooks could be useful to allow people to experiment
with this.  But the hooks should be more general, as described above.
But I think a few GUC settings that can be adjusted at run time could be
sufficient as well.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> As I just wrote in another message in this thread, I don't trust system
> load metrics very much as a gatekeeper.  They are reasonable for
> long-term charting to discover trends, but there are numerous potential
> problems for using them for this kind of resource control thing.

As a note in support of that, sendmail has a "feature" to suppress service
if system load gets above X, which I have never found to do anything
except result in self-DOSing.  The load spike might not have anything to
do with the service that is trying to un-spike things.  Even if it does,
Peter is correct to note that the response delay is much too long to form
part of a useful feedback loop.  It could be all right for scheduling
activities whose length is comparable to the load average measurement
interval, but not for short-term decisions.
        regards, tom lane

On 02/09/16 04:44, Peter Eisentraut wrote:
> On 8/1/16 2:17 AM, Gavin Flower wrote:
>> 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...
> The problem with that approach is that it takes about a minute for the
> load averages figures to be updated, by which time you have already
> thrashed your system.
>
> You can try this out by building PostgreSQL this way.  Please save your
> work first, because you might have to hard-reboot your system.
>
Hmm...  I've built several versions of pg this way, without any obvious 
problems!

Looking at top, suggests that the load averages never go much above 8, 
and are usually less.

This is the bash script I use:


#!/bin/bash
# postgresql-build.sh


VERSION='9.5.0'

TAR_FILE="postgresql-$VERSION.tar.bz2"
echo 'TAR_FILE['$TAR_FILE']'
tar xvf $TAR_FILE

PORT='--with-pgport=5433'  ############################ std is 5432

BASE_DIR="postgresql-$VERSION"
echo 'BASE_DIR['$BASE_DIR']'
cd $BASE_DIR

PREFIX="--prefix=/usr/local/lib/postgres-$VERSION"
echo 'PREFIX['$PREFIX']'

LANGUAGES='--with-python'
echo 'LANGUAGES['$LANGUAGES']'

SECURITY='--with-openssl --with-pam --with-ldap'
echo 'PREFIX['$PREFIX']'

XML='--with-libxml --with-libxslt'
echo 'SECURITY['$SECURITY']'

TZDATA='--with-system-tzdata=/usr/share/zoneinfo'
echo 'TZDATA['$TZDATA']'

##DEBUG='--enable-debug'
##echo 'DEBUG['$DEBUG']'


./configure $PREFIX $LANGUAGES $SECURITY $XML $TZDATA $DEBUG

time make -j7 -l8 && time make -j7 -l8 check


Cheers,
Gavin

On 02/09/16 05:01, Peter Eisentraut wrote:
> On 8/16/16 3:39 AM, Haribabu Kommi wrote:
[...]

>> All of this seems very platform specific, too.  You have
>> Windows-specific code, but the rest seems very Linux-specific.  The
>> dstat tool I had never heard of before.  There is stuff with cgroups,
>> which I don't know how portable they are across different Linux
>> installations.  Something about Solaris was mentioned.  What about the
>> rest?  How can we maintain this in the long term?  How do we know that
>> these facilities actually work correctly and not cause mysterious problems?
[...]
I think that we should not hobble pg in Linux, because of limitations of 
other O/S's like those from Microsoft!

On the safe side, if a feature has insufficient evidence of working in a 
particular O/S, then it should not be default enabled for that O/S.

If a feature is useful in Linux, but not elsewhere: then pg should still 
run in the other O/S's but the documentation should reflect that.


Cheers,.
Gavin

Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> On 02/09/16 04:44, Peter Eisentraut wrote:
>> You can try this out by building PostgreSQL this way.  Please save your
>> work first, because you might have to hard-reboot your system.

> Hmm...  I've built several versions of pg this way, without any obvious 
> problems!

I'm a little skeptical of that too.  However, I'd note that with a "make"
you're not likely to care, or possibly even notice, if the thing does
something like go completely to sleep for a little while, or if some
sub-jobs proceed well while others do not.  The fact that "-l 8" works
okay for make doesn't necessarily translate to more-interactive use cases.
        regards, tom lane

On Thu, Sep  1, 2016 at 01:01:35PM -0400, Peter Eisentraut wrote:
> Maybe a couple of hooks could be useful to allow people to experiment
> with this.  But the hooks should be more general, as described above.
> But I think a few GUC settings that can be adjusted at run time could be
> sufficient as well.

Couldn't SQL sessions call a PL/Perl function that could query the OS
and set max_parallel_workers_per_gather appropriately?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

On 9/2/16 4:07 PM, Bruce Momjian wrote:
> Couldn't SQL sessions call a PL/Perl function that could query the OS
> and set max_parallel_workers_per_gather appropriately?

I'd certainly like to see a greater ability to utilize "hooks" without 
resorting to C. "hooks" in quotes because while some hooks need to be in 
C to be of practical use, others (such as a parallelization limit or 
controlling autovacuum) might not.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Thu, Sep 1, 2016 at 10:01 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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

It seems clear that this patch design is not favored by the community,
so I'm setting the patch as rejected in the CF app.

I think there is interest in managing system resources better, but I
don't know what that would look like.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services