Exchange operator

[robert_kratschmann]

The query plan contains an Exchange operator for intra-query parallelism. Whose effect is to merge (union) the results of the parallel computation of each subtree. In our situation seven subtrees will be executed in parallel. All of them are using exact the same tables.

If they are executed in parallel: Does the cache containing the same set of pages seven times - one set of the necessary pages for each subtree? Or does the cache containing one set of pages that is used for all seven subtrees?

If seven sets exists - the query will monopolize the cache during the execution.

Thanks

Robert

[Vlad]

May I ask you, what does "monopolize the cache" might mean for you?

[robert_kratschmann]

Monopolizing means that a large query will overwrite the entire cache with its required pages. All subsequent queries must reload their pages from disk into the cache. This means normaly that the following queries are slow.

[VolkerBarth]

I don't know about "cache monopolization" w.r.t. intra-query parallelism - but AFAIK cache pages are not "linked" to particular threads but can be shared between requests. So I would assume the database server would not need to multiply pages for parallel access.

[Breck_Carter]

If you are asking "Did this guy build SQL Anywhere?" the answer is no.

[Breck_Carter]

If you have an actual performance problem, feel free to ask questions about it.

If you have evidence of bad behavior on the part of SQL Anywhere, PLEASE ASK QUESTIONS ABOUT IT (shouting intended, because everyone is interested in performance)

The important word is "evidence" which is different from "speculation".

FWIW there is no shortage of evidence that intra-query parallelism can cause HORRENDOUS performance problems on busy transactional databases, but this is the first time I've heard cache monopolization mentioned.

Of course, cache monopolization COULD be a thing, I wouldn't know, I'm that guy with the sack of hammers 🙂

[Breck_Carter]

On the subject of evidence, the Graphical Plan With Statistics shows a lot of information about cache-versus-disk usage, so that feature could be used to study the effect of intra-query parallelism on cache usage by other connections. I suggest using GRAPHICAL_PLAN() calls because that gives complete control over the experimental setup... it shouldn't take too much work, probably no more than a few weeks 🙂

[robert_kratschmann]

We are changing max_query_tasks from 0 to 8. This leads to a smaller cpu load (from 70-90% to 30-50%) and the Unscheduled Requests at a time are going down from 20 to 2-3.

My idea was that with the original setting individual queries are taking a lot of the available cache. On ASE I know what to do if a query takes the whole cache. These setting (sp_cachestrategy) didn’t exist in SQL Anywhere. So, I’m interested if the possibility of taking the whole cache by an individual query exists in SQL Anywhere too.

[VolkerBarth]

We are changing max_query_tasks from 0 to 8.

Hm, value 0 means "unlimited" intra-query parallelism - so I'm surprised that the behaviour does change - unless your database server does use more than 8 logical processors, so "8" does impose a limit here.

[Breck_Carter]

> On ASE

You will have nothing but trouble if you apply any of the complex ASE performance tuning techniques to SQL Anywhere... in other words, try to forget everything you learned about ASE. The same applies to Oracle, DB2, and all the other products that require manual tuning that SQL Anywhere does for you.

Questions: Is your database used for a heavy multi-user update transaction workload? Are the queries which benefit from intra-query parallelism part of that workload, or are they part of a different "business intelligence" reporting workload? If the answer is "yes" to both then try setting max_query_tasks to 1. If the BI queries slow down too much then move them to another physical database, or run them overnight, or try max_query_tasks = 2 etcetera.

[Breck_Carter]

> On ASE

Of course, if you really want to apply ASE advice to SQL Anywhere, here's an exhortation that might apply: "Since serial processing is more resource‐efficient than parallel processing,  avoiding parallelism ....

[Breck_Carter]

> so "8" does impose a limit here

Yup, that be my guess too 🙂

( wait, are you still using eight processors? all the Cool Kids are using way more! 🙂

[robert_kratschmann]

The machine has 16 cores and two threads per core. So it really reduces the number of tasks per query 8.

[robert_kratschmann]

No, I don't want to implement ASEs techniques to SA17.

I had such a problem with a query taking most of the cache on ASE and sp_cachestrategy solved the problem...