On 01/08/16 18:08, Haribabu Kommi wrote:
On Fri, Jul 29, 2016 at 8:48 PM, Amit Kapila <amit.kapil...@gmail.com> wrote:
On Fri, Jul 29, 2016 at 11:26 AM, Haribabu Kommi
<kommi.harib...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
