Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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.



asked 02 Apr '21, 03:55

Robert%20Kratschmann's gravatar image

Robert Krats...
accept rate: 0%

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

(03 Apr '21, 15:27) Vlad
Replies hidden
Comment Text Removed

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.

(04 Apr '21, 08:34) Robert Krats...

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.

(06 Apr '21, 04:17) Volker Barth

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

(06 Apr '21, 06:52) 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 :)

(06 Apr '21, 07:14) 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 :)

(06 Apr '21, 07:26) Breck Carter

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.

(06 Apr '21, 11:12) Robert Krats...

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.

(06 Apr '21, 11:34) Volker Barth

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

(06 Apr '21, 11:47) Breck Carter
(06 Apr '21, 12:03) 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! :)

(06 Apr '21, 12:07) Breck Carter

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

(07 Apr '21, 10:48) Robert Krats...

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

(07 Apr '21, 10:51) Robert Krats...
More comments hidden
showing 4 of 13 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 02 Apr '21, 03:55

question was seen: 707 times

last updated: 07 Apr '21, 10:51