Question 1: What do all the values in thie SQL Anywhere 17.0.10.6089 message mean?

   I. MPL[curr=80 prev=80] concurrency[213] throughput[curr=5 prev=4573] algorithm=0

It appeared at the beginning of a 46.8 second outage where SQL Anywhere skyrocketed the multiprogramming level from 80 to 196...

Sample             Interval     CPU      Active/MPL/Unsch
2022-05-24 04:17:27  46.8s  22.7% of 12    3 / 196 / - 
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   (48ms) 
2022-05-24 04:17:24  -- I. Thread deadlocked occurred. Adjusted the multiprogramming level to 184   
2022-05-24 04:17:24  -- I. Adjusting the multiprogramming level to 184   (.2s) 
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 5 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 7 to handle intra-query parallelism   (.1s) 
2022-05-24 04:17:24  -- I. Thread deadlocked occurred. Adjusted the multiprogramming level to 162   
2022-05-24 04:17:24  -- I. Adjusting the multiprogramming level to 162   (.2s) 
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 6 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 6 to handle intra-query parallelism   (.1s) 
2022-05-24 04:17:24  -- I. Thread deadlocked occurred. Adjusted the multiprogramming level to 140   
2022-05-24 04:17:24  -- I. Adjusting the multiprogramming level to 140   (.1s) 
2022-05-24 04:17:24  -- I. Thread deadlocked occurred. Adjusted the multiprogramming level to 130   
2022-05-24 04:17:24  -- I. Adjusting the multiprogramming level to 130   (.1s) 
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:24  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   (.1s) 
2022-05-24 04:17:24  -- I. Thread deadlocked occurred. Adjusted the multiprogramming level to 108   
2022-05-24 04:17:24  -- I. Adjusting the multiprogramming level to 108   (.3s) 
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 2 to handle intra-query parallelism   
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 4 to handle intra-query parallelism   (.3s) 
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 3 to handle intra-query parallelism   
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 5 to handle intra-query parallelism   
2022-05-24 04:17:23  -- I. Multiprogramming level increased by 4 to handle intra-query parallelism   (.4s) 
2022-05-24 04:17:23  -- I. Adjusting the multiprogramming level to 80   (15ms) 
2022-05-24 04:17:22  -- I. MPL[curr=80 prev=80] concurrency[213] throughput[curr=5 prev=4573] algorithm=0 Help  (2m 21s) 
Sample             Interval     CPU      Active/MPL/Unsch
2022-05-24 04:16:40  10.2s  22.2% of 12   1 / 80 / - 

Question 2: Why does SQL Anywhere 17.0.10.6089 automatically change the multiprogramming level when a thread deadlock occurs?

   I. Thread deadlocked occurred. Adjusted the multiprogramming level to 108 

Question 3: Is dynamic tuning of the multiprogramming level no longer safe in SQL Anywhere 17.0.10.6089?

A 46-second outage is very bad news on a busy server.

asked 17 Jul, 14:47

Breck%20Carter's gravatar image

Breck Carter
32.5k5377231050
accept rate: 20%

As to your 2nd question: When there's a thread deadlock based on too many blocked workers (in contrast to a cyclic blocking conflict), it seems reasonable that the MPL is increased to have more workers available...

Can't comment on the other topics, and all too often I disable intra-query parallelism because some queries are faster when not parallelized...


BTW: As workers are also required for external environments, could it be the parallelized queries include external calls?

(18 Jul, 03:22) Volker Barth
Replies hidden

Here's what the Help says about thread deadlock: "The design of your application may also cause thread deadlock because of excessive or unintentional contention. In these cases, scaling the application to larger data sets can make the problem worse, and increasing the database server's multiprogramming level may not solve the problem."

(18 Jul, 10:16) Breck Carter
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

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:

×9
×1

question asked: 17 Jul, 14:47

question was seen: 247 times

last updated: 18 Jul, 10:16