Loading Oracle Part 3: Exponential Inter-arrivals & a bit of queuing theory


Introduction

In part two of the blog post series we have seen how to create a transaction load within the database itself. One of the missing things we have kept till this part is the concept of inter-arrival time. Remember that in previous part, as an example, we’ve scheduled 20 sessions and let them to insert 5000 records at the same time. But can you see the problem about this method?

Having a single 5000 insertion time statistics for each session usually causes a great variation in computations. This is all about what we measure. Remember that we are trying to approximate the response time of Oracle in a concurrent environment. In previous post we have only an average and a variance. Nothing more… But at the end of this model we obtain some mathematical models rather than just numbers. So what was wrong with the precious one? Let’s try to build a correlation with a super-market (more or less both fit into queuing theory facts).

Imagine a market with 20 cash desks (an Oracle instance whose job_queue_processses parameter is set to 20). Market is closed initially and at 09:00 doors are opened. Then 20 customers arrive simultaneously and buy 5000 goods and they pay in different cash desks. We measure the time between arrival and departure of each customer and make some statistics. I think the problem is clearer now. Let me summarize what is wrong:

  • Customers arrive suddenly at the same time just like 20 simultaneous sessions.
  • There are only 20 timing measurements. Modeling a system with 20 points is not so sound.

Tuning the Previous Model

Previous model we have utilized in the second part of the post has those problems. Let’s now see the methods for correcting those errors:

  • We rather use stochastic stationary Poisson arrivals instead of n simultaneous arrivals at a time.
  • We will do statistics over much more than 20 timing measurements.
  • We try to build mathematical stochastic processes.

Poisson Arrivals & Queuing Theory

Let’s first talk about the real characteristics of super market customer arrivals. Assuming that there can be only one customer at exactly the same time seems to be a sound assumption. Next assumption is another sound one. The number of arrivals between t and t+s depends only to s. In other words the expected number of customers between 09:00-09:10, 09:10-09:20 or 11:21-11:31 is equal to each other (Even we will relax this assumption also in next parts of the post). Customers arriving between [t,t+s] and [t+s,t+v] is totally independent. Each customer decides totally free to come without interacting with other customers. That’s all. I think all seem sound. Let me summarize them more formally once more using the words of infamous Law & Kelton adapting to our Oracle scenario:

  1. Sessions are opened one at a time. (It is very rare that session opening timestamp differences between two consecutive Oracle sessions equal to 0).
  2. The distribution of N(t+s) – N(t) is independent of t for all t,s>0
  3. N (t+s) – N(t) (the number of arrivals in the time interval (t, t+s]) is independent of {N(u), 0<u<t}.

If those three assumptions hold, this type of arrivals called stationary Poisson arrivals. Fortunately, most queuing systems follow this distribution. I won’t give formulas on Poisson distribution but let me illustrate what some Poissons look like:

Figure 1

We will be talking about those histograms a bit and try to understand, informally, the characteristics of a Poisson distribution. Logically those distributions are discrete and may take any discrete value in [0, infinite). Mode of the distribution is always somewhere around lambda. There may be other visual characteristics but now I will focus on the meaning of these histograms. What they mean? Take lambda=2 for example. Let’s read the figure:

  1. Given that on the average 2 customers arrive per minute to XYZ-market, what is the probability that in 1 minute exactly 5 customers will arrive?
  2. Or given that on the average 6 customers arrive per minute to UVT-market, what is the probability that less than 4 customers (exclusive) will arrive?

Answers to those questions may be given by just looking to histograms. First question asks the value you read from the 5th green bar. That is ~0.36. The second one asks for the sum of 0th, 1st, 2nd and 3rd purple bars (cumulative distribution, F (3) = P(X<4)). That is ~0.15. Isn’t it amazing that by just knowing that your customers’ arrival satisfies those three conditions, you can estimate anything about their arrival?

Let’s now focus on some other issue. If we can compute the number of customers (sessions) we will have in the next minute, is it possible to compute the time between two consecutive customers (sessions)? Exactly! Once more I won’t deal with mathematical proofs, although I like them very much, but I will put the outcome in one sentence:

“The time between two consecutive Poisson arrivals with parameter lambda follows exponential distribution with parameter 1/lambda

Figure 2

Figure 2 illustrates the inter-arrival rates of two systems with different inter-arrival rates (0.5 sess/min and 1 sess/min). For the smaller arrival rate half of the customers arrive before 1.5 minutes whereas this duration falls to less than 1 minute for the higher arrival rate. You may see this from cumulative distribution drawings in Figure 2(red and purple lines).

Oracle in Action Again

After this much theory let’s load Oracle once more. This time we will use Java for loading Oracle. That’s not because Oracle is not eligible to do it himself but rather people like external tools. What we do is pretty simple.

We create stationary Poisson Oracle sessions at five different arrival rates as follows:

Scenario

Arrival rate

Scenario Description

NoLoad

15 sessions/minute

Java creates 100 Poisson arrivals at given rate, each session (Java thread) inserts 5000 rows into table and measures the service time.

LowLoad

30 sessions/minute

MediumLoad

1 session/second

HeavyLoad

2 sessions/second

ExtremeLoad

10 sessions/second

Table 1

Graphs of inter-arrival rates are like more or less Figure 2. Another, more important, fact is the service time of Oracle. In following figures you will see the behavior of our development system under load.

NoLoad

Figure 3 Beta Model for NoLoad response time

As you may see in Figure 3, response time for NoLoad scenario is almost deterministic. Almost 90% of all insertion requests answered in less 160 than milliseconds. That’s highly due to arrival of sessions are so rare that no session need to wait for others to complete their task. If we include outliers into account, best response time can be modeled by using a Beta distribution (37 + 632*Beta (0.128, 1.2)).

LowLoad

Figure 4 LogNormal Model for LowLoad response time

As we increase session rate to 30 sessions per minute. Oracle response time changes characteristics and response time durations fit to a log-normal model which is a common distribution in modeling duration to complete some task. Notice that tails of the distribution are fatter than the previous model. This illustrates that some sessions wait for disk I/O, latch or lock held by other sessions. Stochastic model may be formalized as 39 + Logn(199, 1100).

MediumLoad & HeavyLoad

 
 

Figure 5 Exponential Model for MediumLoad response time

Figure 6 Exponential Model for HeavyLoad response time

 
 

MediumLoad and HeavyLoad scenarios perfectly represent the most simple queue systems. Meaning that inter-arrival and response times are both exponential and there is only one server to serve. We can model Oracle response time for those session loads as 44+EXPO (388) and 43 + EXPO (2290) respectively. Notice that those exponentials are somewhat different from those we use for inter-arrival times. In fact they are not. There is only a shift parameter 43-44. It statistically only changes the expected value of response time, not the characteristics of the model.

ExtremeLoad

Figure 7 Normal Model for ExtremeLoad response time

Final scenario’s purpose is somewhat different from the previous ones. Rather than modeling the real world, we use this scenario to show a general occurrence in queuing systems. Notice that majority of customer response time shift from left to middle of the histogram. That’s waiting time durations increase. Characteristics of the response time model changes from exponential to a center-moded model. Best model seems to be Normal distribution Norm (3380, 1960). One problem is that this model allows negative response times also .But this can be solved by absolute value function trick by disturbing the real Normal distribution model a little bit.

Conclusion

In this part we model Oracle response time for different arrival rates. Starting from this part of Loading Oracle post we start to make a little bit of statistics. This will go like this. Hopefully this much mathematics is not neither too much nor too informal. If it is so I am looking for your feedbacks.

Source codes

Below link includes the source codes I have used in this part. Be careful about running java code. A random value to arrival rate may cause an Oracle disaster for large values

http://husnu.sensoy.googlepages.com/LoadingOraclePart3.rar

Special Thanks

For the outputs of Arena Input Analyzer, I appreciate my esteemed friend Yasin Alan who is currently a PhD student in Cornell University Department of Operations Research and Information Engineering, New York.

Loading Oracle Part 4: R= S+W and Active Session History(ASH)

About kocakahin

Just a computer engineer

Posted on March 26, 2007, in Oracle. Bookmark the permalink. 2 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: