Re: Queueing Theory in Oracle

Thanks for the comments.

I understand we have to measure the arrival time from each request and that is why I think with database metrics is complicated to model using the queueing theory because database metrics does not give time for every single request, this is probably why Jonathan talked about sampling a higher rate, more granularity.

M/M/n is pessimistic? Anywhere I can look info for that?

I guess to use M/M/n we just have to assume certain database metrics are exponentially distributed such as user transactions per sec, an average number of transactions per sec but the interval is random and service time for each of them is mostly short.

Thanks

On Wed, Mar 12, 2014 at 10:21 AM, Chris Dunscombe <cdunscombe_at_yahoo.com>wrote:

> 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
> 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>
>
>
>
>
>
> --
> Karl Arao
> Blog: karlarao.wordpress.com
> Wiki: karlarao.tiddlyspot.com
> Twitter: _at_karlarao <http://twitter.com/karlarao>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l