Re: Parallel Seq Scan

On Wed, Apr 8, 2015 at 1:53 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> > If we attempt to do this parallel stuff at plan time, and we
> > happen to plan at some quiet period, or perhaps worse, some
> > application's start-up process happens to PREPARE a load of
> > queries when the database is nice and quite, then quite possibly
> > we'll end up with some highly parallel queries. Then perhaps come
> > the time these queries are actually executed the server is very
> > busy... Things will fall apart quite quickly due to the masses of
> > IPC and context switches that would be going on.
> >
> > I completely understand that this parallel query stuff is all
> > quite new to us all and we're likely still trying to nail down
> > the correct infrastructure for it to work well, so this is why
> > I'm proposing that the planner should know nothing of parallel
> > query, instead I think it should work more along the lines of:
> >
> > * Planner should be completely oblivious to what parallel query
> > is.
> > * Before executor startup the plan is passed to a function which
> > decides if we should parallelise it, and does so if the plan
> > meets the correct requirements. This should likely have a very
> > fast exit path such as:
> > if root node's cost < parallel_query_cost_threshold
> > return; /* the query is not expensive enough to attempt to make
parallel */
> >
> > The above check will allow us to have an almost zero overhead for
> > small low cost queries.
> >
> > This function would likely also have some sort of logic in order
> > to determine if the server has enough spare resource at the
> > current point in time to allow queries to be parallelised
>
> There is a lot to like about this suggestion.
>
> I've seen enough performance crashes due to too many concurrent
> processes (even when each connection can only use a single process)
> to believe that, for a plan which will be saved, it is possible to
> know at planning time whether parallelization will be a nice win or
> a devastating over-saturation of resources during some later
> execution phase.
>
> Another thing to consider is that this is not entirely unrelated to
> the concept of admission control policies. Perhaps this phase
> could be a more general execution start-up admission control phase,
> where parallel processing would be one adjustment that could be
> considered.

I think there is always a chance that resources (like parallel-workers)
won't be available at run-time even if we decide about them at
executor-start phase unless we block it for that node's usage and OTOH
if we block it (by allocating) those resources during executor-start phase
then we might end up blocking it too early or may be they won't even get
used if we decide not to execute that node. On that basis, it seems to
me current strategy is not bad where we decide during planning time and
later during execution time if not all resources (particularly
parallel-workers)
are not available, then we use only the available one's to execute the plan.
Going forward, I think we can improve the same if we decide not to shutdown
parallel workers till postmaster shutdown once they are started and
then just allocate them during executor-start phase.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com