In other words, how can I determine if my application is receiving any benefit whatsoever from intra-query parallelism using SQL Anywhere version 18.104.22.1685?
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
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.
answered 05 Oct '10, 19:13