Re: Queueing Theory in Oracle
Date: Tue, 11 Mar 2014 22:08:53 +0100
Message-ID: <CAJ2-Qb_F7pryutdnXJ8prxZfcEc60iPtxuPhoKin-qeVjGqFYA_at_mail.gmail.com>
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>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 11 2014 - 22:08:53 CET