# Re: Queueing Theory in Oracle

From: Chris Dunscombe <cdunscombe_at_yahoo.com>
Date: Wed, 12 Mar 2014 09:21:15 +0000 (GMT)
Message-ID: <1394616075.9043.YahooMailNeo_at_web28706.mail.ir2.yahoo.com>

```Hi,

I've had a couple of thoughts regarding your issue with the arrival rate distribution. Firstly the standard m/m/n queuing model is more pessimistic than a normally distributed arrival rate, hence it's a conservative approach which is generally good. Also in terms of accurately measuring the arrival rate distribution you need to measure the arrival time of each request, whether that be a user transaction or sql statement etc. If you've not done that and the arrival rate is many requests per second then it's quite possible that you've not captured enough detail and the real arrival rate distribution is getting lost.

Also it's worth remembering that the arrival rate distribution is only half the story the other is the service time distribution. Finally a model will always be "wrong" it's just a case of how far wrong and also how accurate do you need it to be for whatever exercise you're doing.

Thanks,

Chris

On Tuesday, 11 March 2014, 21:10, Ls Cheng <exriscer_at_gmail.com> wrote:

ah right you were basically using the ratio modelling, correct?

In my 300 vs 420 user test, a 40% increase in workload the logical reads average jumped from 9958 to 14520 (if I used ratio modelling it would have been 13941) and cpu usage from 16.65% to 22.18% (if I used ratio modelling it would have been 23.31). That is really basic modelling, I then tried to use the Queueing Theory but got stucked because I cannot find a exponential distribution

On Tue, Mar 11, 2014 at 10:02 PM, Karl Arao <karlarao_at_gmail.com> wrote:

No ErlangC involved, the calculations are on page 9, just simple math but works and scales very well. That's what we use for sizing databases we are migrating to Exadata or even non-Exadata. Of course I did my due diligence by running a lot of benchmarks and doing a lot of what if cases just to validate the idea.
>
>
>
>
>-Karl
>
>
>
>On Tue, Mar 11, 2014 at 4:47 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>Hi Karl
>>
>>Did you use ErlangC in your calculation? I dont think the calculation is in http://www.slideshare.net/karlarao/where-didmycpugo, is it?
>>
>>Thanks
>>
>>
>>
>>
>>On Tue, Mar 11, 2014 at 9:34 PM, Karl Arao <karlarao_at_gmail.com> wrote:
>>
>>From the paper, I did the simple math to estimate the end CPU requirement and utilization without doing the migration.
>>>Then I did the actual migration of workload and the numbers were about the same that I got from the math.
>>>
>>>
>>>-Karl
>>>
>>>
>>>
>>>On Tue, Mar 11, 2014 at 4:01 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>
>>>Hi Karl
>>>>
>>>>One of my main concerns is how can we show in this case, TPS as exponentially distributed because as Cary points in his book in order to use M/M/n the distribution must be be exponential.
>>>>
>>>>I ran last week a couple of TPC load with 300 and 420 users then I used both transaction per second and logical reads per second metric and both showed normal data distribution and that is why I have doubts of how to use queueing theory in Oracle.
>>>>
>>>>From your paper was you able to predict the change from v1 to x2 without run the actual test? Then run the test and validate the prediction?
>>>>
>>>>
>>>>Thanks
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>On Tue, Mar 11, 2014 at 8:40 PM, Karl Arao <karlarao_at_gmail.com> wrote:
>>>>
>>>>
>>>>>
>>>>>
>>>>>
>>>>>The queueing theory knowledge is still applicable. So here on the page 8 of this paper http://www.slideshare.net/karlarao/where-didmycpugo
>>>>>I'm migrating a workload from 16CPUs to 24CPUs.. you can say they are the "servers"
>>>>>then I have this 1205 TPS (transactions/sec) .. you can say this is the "arrival rate"
>>>>>
>>>>>
>>>>>then I have this other numbers comparing the V2 and X2 running the 1205 TPS
>>>>>40-45% CPU util
>>>>>17ms latency
>>>>>AAS 9         <-- measure of db time
>>>>>
>>>>>
>>>>>15-20% CPU util
>>>>>10ms latency
>>>>>AAS 6         <-- measure of db time
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>So using the numbers above, when migrating from v2 to x2. 1 CPU of V2 is equivalent to .58 CPU of X2 which means you have a faster processor (or server in queueing theory)
>>>>>
>>>>>
>>>>>= 16*.45*.58
>>>>>= 4.2 this is how many CPUs you need on the X2 if you are using 7.2 CPUs (16*.45) on the V2.. so 16 CPUs of V2 is equal to 9.4 CPUs of X2
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>Applying the queueing theory concepts mentioned on the Chapter 5 of the book:
>>>>>
>>>>>
>>>>>* so.. on the same rate of TPS, the faster CPU translates to faster per execution resulting to lower CPU utilization
>>>>>* so the extra CPU capacity on the faster CPU can be used for additional transactions or workload growth.. resulting to more work being done
>>>>>* now if you do it the other way.. faster to slower.. each SQL will execute longer and in turn will cause higher CPU.. The important thing here is to have the CPU equivalent of the source # of CPUs taking into consideration the chip efficiency factor (speed) on the destination to accommodate the processing power it needs and not anymore aggravate the speed down (faster to slower) effect. So you'll still be able to achieve the same TPS but the response times will be longer and CPU util higher.
>>>>>
>>>>>
>>>>>even on slower to faster CPUs, the TPS may not even change.. because it's the same amount of workers that's doing the same amount of work as you move across platforms. the change you'll be seeing is just lower resource utilization and the gains you'll have is being able to put more work on the workers resulting to more transaction rate
>>>>>
>>>>>
>>>>>So when observing change of workloads when moving to slower-faster with no workload change do the following: check the load profile (TPS).. it should be the same.. then check on per SQL if per exec remained the same (it should be the same).. then check on CPU usage it should be lower
>>>>>
>>>>>
>>>>>And when observing change of workloads when moving to slower-faster with workload change do the following: check the load profile (TPS).. it should be higher.. then check on per SQL if per exec remained the same (it should be higher).. then check on CPU usage it should be higher
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>Now, if the question is when do I run out of gas? then I'll do the regression analysis on it.
>>>>>
>>>>>
>>>>>BTW, if you are interested on this kind of stuff, I have subscribed on the following google groups owned by Neil Gunther and from time to time there are a lot of discussions about this area of performance
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>-Karl
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>On Tue, Mar 11, 2014 at 12:03 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>>
>>>>>Hi Karl
>>>>>>
>>>>>>So I guess you agree that Queueing Theory is not applicable using database metrics, which is basically I am looking for
>>>>>>
>>>>>>I've got the statistics without tears book as well, it is good about normal distribution.... :-)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>On Tue, Mar 11, 2014 at 4:51 PM, Karl Arao <karlarao_at_gmail.com> wrote:
>>>>>>
>>>>>>I've read both of the books, and I love them. On this link are my notes - http://goo.gl/b8aNOj
>>>>>>>Although queueing theory is pretty cool, I find the chapter on regression analysis more suited for real world but the queueing theory chapter builds a nice foundation of ResponseTime=ServiceTime+QueueTime which is from a practical use case point of view the Performance Page you've got all of that components in a nice pretty dashboard broken down into wait class with CPU as your service time and everything else is QueueTime.
>>>>>>>
>>>>>>>
>>>>>>>For the regression analysis on the link (r2project), what I did is rank the independent values (x) that has the high correlation coefficient and make use of that to forecast the dependent value (y). Also the page 8 of this paper http://www.slideshare.net/karlarao/where-didmycpugo also gives you more insights about the "more CPUs, faster CPUs, more and faster CPUs" section of the book, I think it's page 32.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>-Karl
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>On Tue, Mar 11, 2014 at 9:46 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>>>>
>>>>>>>Hi
>>>>>>>>
>>>>>>>>I have been reading Craig's book and Cary's book (chapter 9) for the last 2 weeks, the theory in the books look great but when I tried to start using in the real world it the questions started to appear.
>>>>>>>>
>>>>>>>>For example, arrival rate, what arrival rate in Oracle is exponentially distributed......? Cary says logical reads in his book but I just dont see how can that be possible by using the database metric (for example system metric such as Logical Reads Per Sec or Logical Reads Per User Call). Craig's book I dont even mention a useful database metric (I havent finished the book yet so I might have missed if he has said so), the book just uses all the time the work unit transaction per second.
>>>>>>>>
>>>>>>>>Both book provide a queueing theory workbook but they are useless from database metric point of view since no metric is poisson or exponential distributed (again I am not able to see it, if someone can please advice).
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>But Jonathan you just mention "buffer gets per user call" which is similar to Logical Reads Per User Call from v\$sysmetric, why do you think that is exponentially distributed :-?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>On Tue, Mar 11, 2014 at 3:30 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>>
It's an interesting question - and I don't think you can find a current metric that would help unless you started doing something a little clever with ASH.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>In an OLTP system something like 'buffer gets per user call" would probably be a reasonable fit - but there's no capture at that granularity. Similarly disc I/O requests per call might be appropriate.  Then there are things like disk I/O requests per disc per second.  But every possibility I think of requires too fine a level of granularity unless you can find a way to construct a valid model from the samples in v\$active_session_history.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Regards
>>>>>>>>>Jonathan Lewis
>>>>>>>>>http://jonathanlewis.wordpress.com
>>>>>>>>>_at_jloracle
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>________________________________
>>>>>>>>>
>>>>>>>>>From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ls Cheng [exriscer_at_gmail.com]
>>>>>>>>>Sent: 11 March 2014 14:20
>>>>>>>>>To: Paul Houghton
>>>>>>>>>Cc: Oracle Mailinglist
>>>>>>>>>Subject: Re: Queueing Theory in Oracle
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Hi
>>>>>>>>>
>>>>>>>>>I have had a quick read, I think the link you posted talks about queue time but not about queueing theory such as a M/M/n model. The problem is I am not able to find a database metric that is exponential distributed which allows us to use the M/M/n queueing
theory.
>>>>>>>>>
>>>>>>>>>
Thanks
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>On Tue, Mar 11, 2014 at 3:16 PM, Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk> wrote:
>>>>>>>>>
>>>>>>>>>Craig Shallahamer talks about queuing theory in the following blog post.
>>>>>>>>>>
>>>>>>>>>>http://shallahamer-orapub.blogspot.co.uk/2011/08/why-tuning-oracle-works-and-modeling-it.html
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>PaulH
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>--
>>>>>>>Karl Arao
>>>>>>>Blog: karlarao.wordpress.com
>>>>>>>Wiki: karlarao.tiddlyspot.com
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>--
>>>>>Karl Arao
>>>>>Blog: karlarao.wordpress.com
>>>>>Wiki: karlarao.tiddlyspot.com
>>>>
>>>
>>>
>>>
>>>--
>>>Karl Arao
>>>Blog: karlarao.wordpress.com
>>>Wiki: karlarao.tiddlyspot.com
>>
>
>
>
>--
>Karl Arao
>Blog: karlarao.wordpress.com
>Wiki: karlarao.tiddlyspot.com
```--