cancel
Showing results for 
Search instead for 
Did you mean: 

PRIORITY option - which types of statements are affected?

3,561

Hello,
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.
Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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.

HTH

VolkerBarth
Contributor
0 Kudos

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

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

MarkCulp
Participant

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.

0 Kudos

Thank you.

Answers (0)