I have a client who reports that in general, the response time of slow transactions is better when they turn max_query_tasks off, which is the opposite of what we expected to see.
- What particular types of queries benefit from allowing max_query_tasks (and this is apparently not one of them)? - Even if there is no benefit for this particular query, why would allowing intra-query-parallelism make it run more slowly?

They're using SA 11.0.1.2584

asked 24 May '11, 15:12

Margaret%20Kammermayer's gravatar image

Margaret Kam...
3316819
accept rate: 50%

Have you compared the query plans for both options? I would seek the cause for your observation there.

(25 May '11, 02:55) Martin

I assume "they turn max_query_tasks off" means they set it to 1, right?

From the docs: "Setting the max_query_tasks option to 1 disables intra-query parallelism."

(27 May '11, 03:43) Volker Barth

Is the setting of "max-query-tasks" the default of 0? (ie unlimited?) And I assume the setting of optimization-goal is 'all-rows'? and the setting of optimization-level is 9?

There are a number of factors that interplay here that can impact actual performance. Without getting into gory detail, the optimizer makes a cost-based decision between parallel and non-parallel plans. Two important things occur when the optimizer considers parallel plans:

  1. There are, simply, more plans in the space for the optimizer to choose. Consequently, the optimizer may fail to discover better (possibly simpler) plans in the time allotted for it to do so (controlled by the optimization-level option).
  2. The optimizer chooses the degree of parallelism at optimization time; in a server with variable workloads, the number of available workers when the query is executed may not match the desired number. In SQL Anywhere, that's not a really significant problem because even if the number of workers is less than that assumed, the workers that ARE available will get used, and the query won't be starved for resources. On the other hand, if the optimizer made a cost-based decision based on all 4 cores being idle, and it turns out that when executing only one core was available, then the choice of a parallel plan in the first place can be moot.

While our default for max-query-tasks is 0 (unlimited parallelization based on the optimizer's choices) I would be tempted as an administrator to tradeoff higher degrees of parallelism with ensuring that cores/workers are available to handle new connections or other long-running ad-hoc requests that can affect the system's response to peak workloads.

permanent link

answered 26 May '11, 12:34

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

Glenn, I have a question to the situation you point to in number 2:

With v12's automatic tuning of the MPL feature, would a plan that is chosen for more workers than are available when the execution is about to start

  1. increase the number of workers immediately or
  2. after the query is processed or
  3. not at all?

(Assumed the increased number would still be below the maximum level)?

(27 May '11, 03:49) Volker Barth
Replies hidden
1

The correct answer is

  1. If the query is sufficiently long-running, the self-tuning MPL algorithm may decide to add additional workers to the worker pool, which may then be available to run idle sub-plans of a parallel strategy.
(27 May '11, 09:43) Glenn Paulley

Thanks for the clarification - and, yes, I should've known that smart heuristics don't "increase immediately" but "may decide ... under certain conditions":)

(27 May '11, 11:18) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275

question asked: 24 May '11, 15:12

question was seen: 2,044 times

last updated: 27 May '11, 11:18