A question that has come up as part of that other one, related to the tuning of the "multiprogramming level" (MPL) in SA 12 and above and the usage of external environments:

Tasks typically service user requests. So setting the maximum MPL to 80 would mean the database server can handle up to 80 active requests concurrently - if there are more requests those will be queued.

To cite from the v16 docs on Threading behavior:

Each database request causes the creation of at least one task, and possibly more if intra-query parallelism is involved. Additionally, the server occasionally schedules tasks to perform internal activities.

What other kind of tasks may use workers from the worker thread pool?

  • Event connections will fall under the "internal activities" category, apparently. They are shown with sa_conn_info(), like parallelized queries, so they are easily noticed.

  • However, IIRC, I have read somewhere in the past (but cannot find the sources) that external environments and proxy connections will use separate threads, as well. If so, are these taken from the same worker pool, which is controlled by the MPL settings, or are they handled separately? (If so, they are harder to track as they do not show up with sa_conn_info()). In case that is documented somewhere, could you provide a link for me?

asked 01 Jun '15, 04:12

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

As to the background:

On a rather idle 12.0.1.3324 server (primarily used for tests) with automatic MPL with default settings, it seems that the system tends to lower the current MPL to 3-5 (since there are usually only 1-3 active requests), however, as soon an event is triggered and calls an external function (or xp_cmdshell?), the console log displays a thread deadlock and increases the current MPL to 14 or 15 - although property('ActiveReq') never seems to make it beyond 3...

After a while, the current MPL is decreased to 5 and then further to 4 and 3 (that seems quite a pattern) before the next increase takes place. (FWIW: One of these few active requests is a ISQL session with a loop that selects the desired properties and writes them to the console log every 30 seconds.)

I'm aware that a loop that queries the state every 30 seconds will not notice each system state, however, the number of unscheduled requests (i.e. property('UnschReq')) seems always 0 in my small test, which would not imply the need for a higer MPL setting...

That observation makes me think that external functions seem to require more threads than "ActiveReq" will tell, or at least that the automatic MPL adjustment increases its settings based on much more information than the server properties seem to reveal.


Note: That behaviour is not a problem to solve, it's just something I'd like to understand.


Just to add: The pattern to decrease and increase the MPL is at least documented with the property "ThreadDeadlocksAvoided" - its value will increase over time.

(01 Jun '15, 07:40) Volker Barth

FWIW I do remember this being covered some place before, in a response? ... or a whitepaper? If I find that I will link it in later.

The various -gn* switches controls the number of workers and your question is actually about workers. In contrast sa_conn_info() shows information only about connections (connection contexts for both the external and internal contexts). There is not a 1-to-1 mapping between those concepts. Tasks is what is actually mapped onto workers and it is a 1 to optionally-many relationship at both levels here.

Connections (internal or external) are optionally mapped onto 1 or more Tasks (when there is an active query/operation) and Tasks (when scheduled) are mapped to Workers. Needless to say unscheduled tasks do not currently consume a worker. If a task needs a connection-context there will be a trackable connection for that (I don' know of any exceptions to that rule).

Of course it is workers that are eventually scheduled on actual system threads (in the typical SMP and OS-specific way) running on some core.

As to what consumes the extra workers you are worried about?

When you use external functions/procedures of any kind (Not the old school DLL calls but the newer external environments of any sort) or remote server accesses (remote procedure call or accessing proxy tables) those do involve extra workers (typically 1 per external reference so there could be multiple per query). These execute on the same connection context as the triggering internal/external connection so they do not show up as extra internal connections. For remote server connections those extra connections will show up on the remote server but not the caller system. For the per-database external environments (Java or CLR) there will be an extra connection for the call back mechanism used for that; I'd have to check on per-connection external environments.

All of this is in addition to the workers used for parallelism which do show up as extra internal connection contexts in addition to the extra workers involved (not all of which may be consuming an actual worker if not scheduled).

Not all connections consume a worker at any moment in time. Connections without requests (and possibly internal parallel worker connections that don't execute a parallel sub-plan due to runtime adjustments) do not tie up a worker. But connections can often be associated with multiple workers when doing something complex using multiple features.

Events, cleaner, stats cleaning, ... have their on internal connections (as you've already noted) and tasks and workers when running ...

Of course I haven't yet covered HTTP requests (yep 1 connection and 1 worker) or remote HTTP requests (yep an additional 1 worker on the connection context) and of course HTTP requests operate over queries, functions, procedures, . . . and there can be a fan-out there ... again.

Did I miss anything else?

AFAIK

The basic link for the this "threading model" is in every DCX edition. I don't find any official break down on the various kinds of tasks nor a counter for that. People do track the current level of utilization of workers by monitoring the Unscheduled Requests and Active Requests but those are best used for performance impact/efficiency measurements; not so helpful for breaking out or enumerating the current break down on what those tasks are. Combining those with the sa_conn_activity()/info() information gets you a little closer.

permanent link

answered 01 Jun '15, 11:45

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.9k29100
accept rate: 31%

edited 01 Jun '15, 13:00

Big thanks for the elaborate answer, Nick:)

(I have certainly overseen the HTTPS requests but my according engine does not run a web server nor web client procedures so those do not have influence in my case.)

I'm still puzzled (and that detail seems unanswered) whether tasks for external functions and remote data access will count for the MPL - and based on my observations, they don't seem to be counted for "ActiveReq", right?

(01 Jun '15, 12:14) Volker Barth

I just added the link to active requests before I noticed your latest post but you are correct! According to that DCX article it is only workers assigned to client-side requests contribute to the ActiveReq counter. It is also underscored by this other article too.

They do consume workers and that will affect the way things get optimized (if parallelism is your concern), scheduled, or delayed if unscheduled (but maybe not counted if not from a user request).

permanent link

answered 01 Jun '15, 12:32

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.9k29100
accept rate: 31%

edited 01 Jun '15, 12:33

Going back to your question about MPL in the subject. What are you most concerned about? Are you asking if the extra workers get counted/affect the dynamic-MPL algorithm? Or are you just worried about the tracking the count.

If it is not yet clear the worker count is the total count and these 'other workers' are drawn from the same worker pool (even though that does not show up clearly in the various diagrams and descriptions). There is no 'other workers' pool for that. That said tracking that is the difficult part when using external environments.

Those familiar with the old external function call, that did not take the extra workers the newer external environments do (yep going to correct my original posting on the ext. func. part of that next) can be easily surprised by this ... as the person posting the original question on that other thread was.

And, if it matters, I do believe it will also driver the MPL algorithm.

(01 Jun '15, 12:53) Nick Elson S...
Replies hidden
Comment Text Removed
Comment Text Removed
Comment Text Removed

What are you most concerned about?

As stated in my comment on the question, I'm not about to solve a problem (as there is none) but trying to understand the automatic MPL behaviour.

What I have observed on my test system (see my comment on the question) has been confirmed in the meantime:

The system is basically idle, there are a few almost idle connections - and there are events running regularly, e.g. one running every 30 minutes to backup the log, check for its validity (via the DBTranslate() API) and copy it to a network share via xm_cmdshell. And during the event (which calls several external functions from one DLL loaded in an external C environment), each time the current MPL level is increased from 3-4 to 13-14 (after a thread deadlock is reported) and then shortly after decreased to 3-4. The somewhat funny part is that according to the MPL messages in the console log, the increase does take place when the event is almost finished (and most external calls have been made), so I don't think it will somehow speed up things...


Given the fact (if my understanding of your statements is correct) that

  • these "external" workers are taken from the one and only ("MPL") worker pool,
  • these "external" workers therefore are considered for the MPL adjustment, and
  • property "ActiveReq" seems not to count those "external" workers,

it's still hard to find out why the current MPL is increased that much. But if there is no property that does count all workers in use, I'll take that as given:)

(02 Jun '15, 03:31) Volker Barth

Sorry, I have to correct my observation:

The MPL is not increased during the course of the event but decreased (while the copying to the net share takes place, apparently since this is a somewhat "slow", IO-focussed action) and is then increased again to the typical value. - Still it remains a mystery why that strong decrease from 13-14 to 3-4 happens. But I can live with that...

Nick, in case there is no property that counts these "external" workers, consider my question as answered:)

(02 Jun '15, 04:00) Volker Barth
Your answer
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:

×26
×22
×22
×7

question asked: 01 Jun '15, 04:12

question was seen: 464 times

last updated: 02 Jun '15, 04:00