Does a PRIORITY option affect INSERT/DELETE/UPDATE statements? Or the only affected statement is SELECT?
Versions: 10.0.1, 11.0.1, 12.0.1.

asked 09 Dec '11, 07:00

Arthoor's gravatar image

accept rate: 0%

edited 09 Dec '11, 09:40

Volker%20Barth's gravatar image

Volker Barth

The engine worker PRIORITY option was added in 11.0.x, not to be confused with the process PRIORITY command line option (-gb) which existed in 10.0.x (and prior version). Note the -gb option sets the entire server process OS priority setting and as such affects all operations that the server performs - the amount of CPU that the server gets is controlled by the OS.

The engine worker PRIORITY option controls how much CPU time a request gets relative to the other concurrent requests (and their priorities). Here is basically how it works: each request that has been assigned a worker thread within the server gets put into a queue associated with its current priority setting. In a busy server there is likely to be many such worker/request pairs in each priority queue. The server will pick an entry out of one of the queues and let that request run for a preset amount of time (a "time slice") at which point the request will go back into its assigned queue and wait for it to be picked again. The "magic" of the priorities is in how the server picks which queue entry to run next.

The server picks which worker/request entry in the priority queues in (very roughly) the following way: each entry in the highest non-empty priority queue each get a number of time slices (currently set to 8 I believe) and then the next lower non-empty priority queue each get a time slice after which the server will go back up to the highest priority queue and cycle through them again. This back-and-forth between highest priority queue and the second highest priority queue continues until the second highest queue's entries has had a number of runs at which point the next lower (third highest non-empty) queue gets a run.

Note I say "very roughly" above because what I have described is not exactly what the server does - it is much more complicated. Complicating factors include: requests that have to wait for I/O or blocks on lock, new requests that come into a queue, and requests that leave the queue (when they finish their work). Add to that the fact that today's computers have multiple cores that allow many concurrent worker/requests to run concurrently.

Readers: please do not confuse the above mention of "priority queues" with the "request queue" that is used by the server to dispatch in-coming requests to a worker. The two sets of queues are completely different and work in complete different ways within the server.

Now to answer your question: hopefully from the above details the answer is obvious - all requests, regardless of what the request is, is affected by the priority option.


permanent link

answered 09 Dec '11, 09:11

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 09 Dec '11, 09:15

Does the older "background_priority" option behave in a similar way?

(Except that it groups all requests into just two priorities - "normal" and "background".)

(09 Dec '11, 09:19) Volker Barth
Replies hidden

No, not quite. Prior to v11 (when the PRIORITY option was added) requests ran in a simple round-robin fashion and those that were given "background_priority" ran when there was nothing else to run (with some fudging to ensure that they did not get starved).

Since v11 background_priority is a synonym for setting the PRIORITY to BACKGROUND (which is lower than LOW) and the rest works the same - workers/requests with BACKGROUND priority get time slices when the scheduler picks them out of the background priority queue.

(09 Dec '11, 09:37) Mark Culp

Thank you.

(13 Dec '11, 01:14) Arthoor
Your answer
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](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:


question asked: 09 Dec '11, 07:00

question was seen: 1,029 times

last updated: 13 Dec '11, 01:14