Re: Queueing Theory in Oracle
From: Stéphane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 11 Mar 2014 19:34:04 +0100
Message-ID: <531F571C.9090109_at_roughsea.com>
Well, I think that you can use database metrics, it's just a question of making reasonable assumptions that may or may not apply to your case. In a queuing system you basically have servers that provide a service, which requires some time to be provided, and clients that ask for that service and arrive at some rate, according to what is usually assumed to follow Poisson's law (in simple terms, the interval between two requests is random). Trouble starts when requests arrive faster than they can be serviced, because then queues build up. As Jonathan stated, all this started to be seriously studied with telephone exchanges (which were Erlang's object of study).
If your system is mostly CPU-bound, your servers are your processors. CPU time (better than DB time in this specific context IMHO) gives you a global time of service, and number of executions the number of requests you got. Related wait times can probably help refine the picture. Of course you need to work on averages, which may or may not be severely misleading. If, however, the workload has specific patterns at different times of the day, you may probably be able to predict when you'll certainly have issues. Just take your results for what they will be, guidelines and warning flashes. Avoid numerical results with four decimals. I have never had the opportunity to work seriously on this, but I certainly have been tempted.
Date: Tue, 11 Mar 2014 19:34:04 +0100
Message-ID: <531F571C.9090109_at_roughsea.com>
Well, I think that you can use database metrics, it's just a question of making reasonable assumptions that may or may not apply to your case. In a queuing system you basically have servers that provide a service, which requires some time to be provided, and clients that ask for that service and arrive at some rate, according to what is usually assumed to follow Poisson's law (in simple terms, the interval between two requests is random). Trouble starts when requests arrive faster than they can be serviced, because then queues build up. As Jonathan stated, all this started to be seriously studied with telephone exchanges (which were Erlang's object of study).
If your system is mostly CPU-bound, your servers are your processors. CPU time (better than DB time in this specific context IMHO) gives you a global time of service, and number of executions the number of requests you got. Related wait times can probably help refine the picture. Of course you need to work on averages, which may or may not be severely misleading. If, however, the workload has specific patterns at different times of the day, you may probably be able to predict when you'll certainly have issues. Just take your results for what they will be, guidelines and warning flashes. Avoid numerical results with four decimals. I have never had the opportunity to work seriously on this, but I certainly have been tempted.
HTH,
-- Stéphane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> Author, SQL Success <http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>, The Art of SQL <http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>, Refactoring SQL Applications <http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/> On 03/11/2014 05:03 PM, Ls Cheng wrote:Received on Tue Mar 11 2014 - 19:34:04 CET
> 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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto:oracle-l-bounce_at_freelists.org>
> [oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] on behalf of Ls
> Cheng [exriscer_at_gmail.com <mailto: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
> <mailto: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 <http://karlarao.wordpress.com>
> Wiki: karlarao.tiddlyspot.com <http://karlarao.tiddlyspot.com>
> Twitter: _at_karlarao <http://twitter.com/karlarao>
>
>
-- http://www.freelists.org/webpage/oracle-l