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