Re: Queueing Theory in Oracle
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Tue, 11 Mar 2014 20:43:16 +0100
I think, there is a big difference between a simple M/M/m model and Oracle
RDBMS:
In Queueing theory there is a number of queues where requests wait, and a
number of processors where they are served afterwards. - Oracle is quite
different: requests never (yes, seldom they do) queue, they start
processing when arrived. So I don't think the whole DB can be modelled as a
simple M/M/m system. But when you start to modell sub-queues and
sub-processors in the DB, it's getting tremendous complex.
Also all metrics in the DB are an aggregate of ServiceTime+QueueTime - "ON
CPU" does not tell us if the process is really doing something in the CPU,
or in an OS-runqueue, or even on SWAP. Maybe some wait events about
latches/mutex/... can be seen as pure QueueTime, but even there the
corresponding ServiceTime is not available easily.
In the past I used Neil Gunthers "Universal Scalability Law" [1] to predict
performance of databases (and even sub components) with acceptable
precision. Maybe this helps more than pure M/M/m queues.
hth,
Martin
[1]
http://www.perfdynamics.com/Manifesto/USLscalability.html
http://www.perfdynamics.com/iBook/gcap.html
http://aberdave.blogspot.co.at/2011/08/universal-scalability-law-usl-in-oracle.html
On Tue, Mar 11, 2014 at 7:34 PM, Stéphane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
> 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:
>
> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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
>>>> @jloracle
>>>> ------------------------------
>>>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]
>>>> on behalf of Ls Cheng [exriscer@xxxxxxxxx]
>>>> *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@xxxxxxxxxxxxxxx> 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
>>>>>
>>>>>
Other related posts:
