Re: Queueing Theory in Oracle
Date: Tue, 11 Mar 2014 17:32:13 -0400
Message-ID: <CACNsJneqY-AC9ynf52o2bzoifBM=6X7Dyfh0tfp+H5dcarhu9w_at_mail.gmail.com>
A bit different from a straight ratio and accounting the speed differences of both CPUs.
-Karl
On Tue, Mar 11, 2014 at 5:08 PM, 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
>>>>>> 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-didmycpugoalso 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>
>>>>
>>>
>>>
>>
>>
>> --
>> 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 - 22:32:13 CET