In other words, how can I determine if my application is receiving any benefit whatsoever from intra-query parallelism using SQL Anywhere version 11.0.1.2405?

In particular, are there any engine, database or connection properties that will help?

Motivation: max_query_tasks = 1 may be a workaround for a possible problem in the engine.


Possible answer... but what are these engine-level properties really telling me? This is a busy server with millions of row locks at any one time...

'ExchangeTasks','Exchange tasks currently being executed','16'

'ExchangeTasksCompleted','Total number of exchange tasks that have been executed','336'

asked 28 Sep '10, 19:29

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%


By and large the only way to determine (currently) if intra-query parallelism benefits your workload is to run the workload with a series of experiments (with intra-query parallelism enabled and alternately disabled) and determine the difference in overall elapsed times. If there is little difference, then intra-query parallelism didn't gain you anything.

Let me go into a bit more detail.

The ExchangeTasks and ExchangeTasksCompleted counters are the current number of workers executing parallel plans, and the cumulative number of such executions respectively. These counters (particularly the latter for a given workload) are indicative of the number of times the optimizer has chosen a parallel plan for one or more portions of a given query. So, the higher the values, the more times the optimizer has selected a parallel execution plan.

What these counters don't tell you is if the parallel execution paid off - that is, if the parallel plan did indeed execute faster (wall clock) than a serial execution. Execution plans that have to wait (for example, for a lock, or for an I/O operation to complete) will benefit much less from parallelization. Here's an example.

Suppose we had a simple query that scans a base table in parallel. Suppose we have four cores, no other connections, and max_query_tasks = 0, and the optimizer selects a plan that does a 4-way parallel scan. In this case, parallelism will pay off to the greatest extent possible if the I/O subsystem can deliver pages of rows to each of the parallel scans so that the scans are never waiting. If one measures the CPU time taken by the request, it should come close to 4x the elapsed time (if the threads never have to wait). With serial execution, again assuming no waiting, the ratio of CPU to wall clock time should be close to 1. If waiting occurs, the ratio may dip to a value less than 1 (it cannot be less than 0).

By this measure, the closer a workload approaches a CPU time/wall clock time ratio closer to the greater of (number of cores, maximum degree of parallelization), the more intra-query parallelism is paying off.

At the moment, SQL Anywhere doesn't offer a convenient metric to measure that ratio. A problem is that accounting for CPU is expensive, so our internal measurements of CPU time are approximate. However, you could, if desired, bracket individual SQL requests with code to track elapsed time and the ApproximateCPUTime property for the current connection - totaling these values and then computing the ratio will give you an idea of how much intra-query parallelism is buying you. But the simplest method will be to test your workload multiple times, and see if your elapsed time changes and if so by how much.

permanent link

answered 05 Oct '10, 19:13

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Comment Text Removed

IMO approximate CPU times are just fine, especially if bizarre artifacts like rapidly-increasing-cumulative-CPU-time for a blocked connection are eliminated... and even that case, if documented, would be acceptable :)

(05 Oct '10, 20:50) Breck Carter
1

Would it be possible to implement ExchangeTasksCompleted at the connection level?

(05 Oct '10, 20:52) Breck Carter

I don't see why not. We'll discuss it with the server team and get back to you.

(05 Oct '10, 23:59) Glenn Paulley
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:

×242

question asked: 28 Sep '10, 19:29

question was seen: 533 times

last updated: 05 Oct '10, 19:13