Hello again,

Sometimes our customer has problems with performance when users run requests at normal priority and there are other running requests (long running SELECTs) at background priority. All normal priority requests perform very slow when there are ~10 active long running background priority queries. All these normal priority requests are short running when there are no active long running queries. According to Mark's answers in this and this questions, I would expect that normal priority requests were given more processing "time slices" than background priority queries. But the practice shows that it has no visible effect. Or I am misunderstanding something. Are there any other options to configure that such slow background priority queries (even if there are 10-20 of them) would not visibly impact performance of normal priority requests?

Does SA12 or SA16 behave differently in such situations?

-gn server option is set to 200. The server uses 2 physical (4 logical) CPUs.

SA version: 11.0.1.2913. Platform: Windows.

Thanks.

asked 29 Jan '14, 02:54

Arthoor's gravatar image

Arthoor
1.3k355266
accept rate: 11%

edited 29 Jan '14, 02:57


I have been discussing this issue with coworkers and have added a backlog item to reevaluate and change the algorithm used so that the number of lower priority tasks do not affect the percentage of time that higher priority tasks get.

We have also discussed implementing priority IO queues in the past and this task is already on the backlog (of a very long list of feature requests).

As always, I can not say when either of the above changes will show up in the product. Thank you for your input - we always appreciate everyone's suggestions!

permanent link

answered 21 Feb '14, 12:54

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Comment Text Removed

Thank You, I'll wait for this showing up. :)

(12 Mar '14, 03:36) Arthoor

As I describe in my answer to this question the background priority option simply changes the number of time slices that the worker gets. As the number of background workers increase the proportional amount of time that a single non-background worker will get decreases. In your example with 10 background workers and a single normal worker, the normal priority worker will get 8 time slices and then the 10 background workers will each get a time slice (for a total of 10 time slices)... so the normal priority worker is getting less than half of its 'normal' time. (This explanation is not exactly correct due to the 4 logical CPUs involved).

Also note that all workers, regardless of their priority, will wait exactly the same if they are needing pages read from disk into the cache. I.e. all reads (and writes) from (to) disk are sequentially queued on a first-come-first-queued basis. This means that if all of the requests are needing I/O in order to complete their requests then all of the workers will effectively be working (or waiting) for the I/O at the same rate.

Finally, I'll mention that a high -gn value is not necessarily going to give you a better response/throughput. In fact in our experiments we find that often lowering the -gn value actually gives better throughput of requests. This is one of the reasons that automatic multiprogramming level was implemented in v12.

permanent link

answered 29 Jan '14, 09:11

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 29 Jan '14, 09:40

@Arthoor: So now the question is:

Does Mark's detailed explanation fit your expectation of "work[ing] as desired"?

(29 Jan '14, 09:28) Volker Barth
Replies hidden

I've made a little experiment (as I understand, no I/O is needed here). I've started a server with -gtc 1 option (to use only 1 logical CPU). The code:

set temporary option priority = 'normal'; // 'background' in other instances of dbisql
begin
    declare @begin_time timestamp;
    declare @end_time timestamp;
    while 1 = 1 loop
        set @begin_time = current timestamp;
        for lp as curs cursor for
            select row_num from sa_rowgenerator(1, 10000000)
        do
        end for;
        set @end_time = current timestamp;
        message datediff(millisecond, @begin_time, @end_time) / 1000.0 to client;
    end loop;
end

Firstly I launch one instance (1st) of this code at normal priority. It shows 5.8-6 seconds. Then I launch additional instance (2nd) at background priority. The 1st instance processing time increases to 7.3-7.5 seconds, the 2nd shows ~31.2 seconds. The difference is quite big but I'd like that the 1st instance time increased less (of course, the 2nd instance time then would be higher). It would be great, if I could somehow configure the difference of given "time slices" between different priorities (maybe this could be product suggestion?). It would also be great if priority option (or even better - another option) affected not only CPU time but also disk read/write priority (of course, not having to wait for ages for lower priorities - I don't know if it is even possible, if it's not - you can explain, why :) ). When I launch 2 more instances at background priority (3rd and 4th), the processing time of the 1st instance increases to ~10.5 seconds.

And about -gn - a high -gn value is needed because sometimes there can be lots of specific requests with sleeps (WAITFOR...).

(30 Jan '14, 02:43) Arthoor
Replies hidden

No, not fully. :) See my comment to Mark's answer.

(30 Jan '14, 02:50) Arthoor

Besides, I've repeated the test in SA16 on the same PC (with SA11 tests NOT running at the same time). The result seems to be quite different than SA11. When only the 1st instance at normal priority is running, it shows similar results as in SA11 - 5.8-6 seconds. When the 2nd instance is running at background priority at the same time, the 1st instance shows 6.9-7.2 seconds (a bit less than 7.3-7.5 seconds in SA11) and the 2nd shows ~45 seconds (much more than ~31 seconds in SA11). So new questions arise here:

1) are priorities being processed somehow differently in SA16?
2) why is processing time difference in the 1st instance only 0.4 seconds (decreased) but whole 14 seconds in the 2nd instance (increased)?
3) maybe this difference is associated with my testing method or with sa_rowgenerator differences?

I've repeated these tests a few times - in SA11, in SA16, then again in SA11, then again in SA16. The results and differences are quite the same as I've written above.

(30 Jan '14, 05:17) Arthoor
1

Another suggestion (an alternative to what I wrote above) would be that the lowest priority queues (when there are active higher priority requests) would share some common "time slice". The more workers in such queue appear, the less time they each get. Then the performance of higher priority queries would not depend on the number of lowest priority queries (assuming there are no disk reads or writes in lowest priority requests).

(30 Jan '14, 06:20) Arthoor
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:

×275
×143
×3

question asked: 29 Jan '14, 02:54

question was seen: 2,676 times

last updated: 12 Mar '14, 03:36