# Re: Queueing Theory in Oracle

Date: Tue, 11 Mar 2014 16:34:04 -0400

Message-ID: <CACNsJnf6CxhEvRTCsWFNSCsL=A=HuR59NEbTeC6qDfzwfKEfsw_at_mail.gmail.com>

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*

*>> Exadata v2*

*>> 40-45% CPU util*

*>> 17ms latency*

*>> AAS 9 <-- measure of db time*

*>>*

*>> Exadata x2*

*>> 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*

*>>*

*>> guerrilla-capacity-planning_at_googlegroups.com*

*>> perfviz_at_googlegroups.com*

*>>*

*>>*

*>>*

*>>*

*>> -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*

*>>>>>>>*

*>>>>>>> I hope this is helpful*

*>>>>>>>*

*>>>>>>> PaulH*

*>>>>>>>*

*>>>>>>>*

*>>>>>>*

*>>>>>*

*>>>>*

*>>>>*

*>>>> --*

*>>>> Karl Arao*

*>>>> Blog: karlarao.wordpress.com*

*>>>> Wiki: karlarao.tiddlyspot.com*

*>>>> Twitter: _at_karlarao <http://twitter.com/karlarao>*

*>>>>*

*>>>*

*>>>*

*>>*

*>>*

*>> --*

*>> Karl Arao*

*>> Blog: karlarao.wordpress.com*

*>> Wiki: karlarao.tiddlyspot.com*

*>> Twitter: _at_karlarao <http://twitter.com/karlarao>*

*>>*

*>*

*>*

-- Karl Arao Blog: karlarao.wordpress.com Wiki: karlarao.tiddlyspot.com Twitter: _at_karlarao <http://twitter.com/karlarao> -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 11 2014 - 21:34:04 CET