Is dynamic tuning of the MPL no longer safe in SQL Anywhere 17.0.10.6089?

[Breck_Carter]

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.

[VolkerBarth]

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?

[Breck_Carter]

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