Question Re: Exchange operator in Technology Q&A

Exchange operator

robert_kratschmann — Fri, 02 Apr 2021 03:55:09 GMT

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

Re: Exchange operator

Vlad — Sat, 03 Apr 2021 15:27:13 GMT

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

Re: Exchange operator

robert_kratschmann — Sun, 04 Apr 2021 08:34:35 GMT

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.

Re: Exchange operator

VolkerBarth — Tue, 06 Apr 2021 04:17:28 GMT

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.

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 06:52:37 GMT

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

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 07:14:20 GMT

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 🙂

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 07:26:24 GMT

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 🙂

Re: Exchange operator

robert_kratschmann — Tue, 06 Apr 2021 11:12:29 GMT

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.

Re: Exchange operator

VolkerBarth — Tue, 06 Apr 2021 11:34:32 GMT

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.

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 11:47:01 GMT

> 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.

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 12:03:55 GMT

> 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 may allow you to deliver better overall performance with the same hardware.".

Re: Exchange operator

Breck_Carter — Tue, 06 Apr 2021 12:07:39 GMT

> 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! 🙂

Re: Exchange operator

robert_kratschmann — Wed, 07 Apr 2021 10:48:42 GMT

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

Re: Exchange operator

robert_kratschmann — Wed, 07 Apr 2021 10:51:21 GMT

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...