Since a long time I am busy using queuing formula’s to be able to calculate cpu queue’s and I/O queues. One of the big problems I was facing that the formula’s I like to use on big data sets with my GAPP analysis were only available in perl. For a long time I was using proximity functions to avoid the perl programmed Erlang-C formula and some other. Last weekend I just had the time to start programming the formula’s in PLSQL, just to have them easily accessible in my database. After finish programming the package I realized that the package can also be very handy for other people, so I decided to create this blog. The created package has the following important functions: Erlang-C, Erlang-B, Response Time in multi-server environments (ErlangR in the package), Queue length in multi-server environments (ErlangQ in the package) and Response Time in multi-queue environments like IO (paratqr in the package).

The formula’s are described in the book “Analyzing Computer System Performance with Perl::PDQ” from Dr. Neil J. Gunther 2005. In the source of the package are the exact locations in the documentation documented.

The code of the package looks like (erlangghh_pkg_v1_0):

CREATE OR REPLACE PACKAGE GHH_PKG AS

/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==

**

** Formula’s from book:

** “Analyzing Computer System Performance with Perl::PDQ”

** by Dr. Neil J. Gunther 2005

**

** Initial creation 10-10-2010 G.Hendriksen v0.1

** Updated formula 11-10-2010 G.Hendriksen v0.2

** Updated formula 12-10-2010 G.Hendriksen v1.0

*//* Function erlangb is used to get the Erlang-B (multiserver queueing system)

** probability that all the servers are busy and calls are dropped completely

** rather than being queued up. The function has input values number of servers

** (m) and utilization (p) — See pag. 83 formula 2.67

*/

function erlangb (m number, p number) return number;/* Function erlangc is used to get the Erlang-C, this is the probability that

** all the servers are busy and therefore calls arriving causing utilization p

** will have to wait for service. The function has input values number of

** servers (m) and utilization (p) — See pag. 81 formula 2.66

*/

function erlangc (m number, p number) return number;/* Function erlangr is used to calculate the exact multiserver residence time.

** The function has input values number of servers (m), utilization (p) and

** service time (s) — See pag. 81 formula 2.64

*/

function erlangr (m number, p number, s number) return number;/* Function erlangq is used to calculate the queue length in a multiserver

** environment. The function has input values number of servers (m) and

** utilization (p) — See pag. 81 formula 2.65

*/

function erlangq (m number, p number) return number;/* Function PDF is used to calculate the Discrete Probability Function of a

** Poison distribution with mean equal to the traffic intensity mp. The function

** has input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.68

*/

function PDF (m number, p number) return number;/* Function CDF is used to calculate the Cumulative Distriburion Function of a

** Poison distribution with mean equal to the traffic intensity mp. The function

** has input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.68

*/

function CDF (m number, p number) return number;/* Function PoisonR is used to calculate the Poison Ratio. The function has

** input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.69

*/

function PoisonR (m number, p number) return number;/* Function Utilp is used to calculate the utilization (p). The function has

** input values calls per time lambda (l), service time (s) and number of queues

** (q) — See pag. 74 formula 2.45

*/

function Utilp (l number, s number, q number) return number;/* Function Paralqr is used to calculate the response time (r) for parallel

** queues, with utilization per queue. The function has input values

** utilization (p) and service time (s) — See pag. 75 formula 2.46

*/

function Paralqr (p number, s number) return number;/* Function Paratqr is used to calculate the response time (r) for parallel

** queues, with utilization over all queues. The function has input values

** number of queues (q), utilization (p) and service time (s) — See pag. 75

** formula 2.46

*/

function Paratqr (q number, p number, s number) return number;/* Function fact is used to get the factorial value with given number

*/

function fact (v number ) return number;END GHH_PKG;

/CREATE OR REPLACE PACKAGE BODY GHH_PKG AS

/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==

**

** Formula’s from book:

** “Analyzing Computer System Performance with Perl::PDQ”

** by Dr. Neil J. Gunther 2005

**

** Initial creation 10-10-2010 G.Hendriksen v0.1

** Updated formula 11-10-2010 G.Hendriksen v0.2

** Updated formula 12-10-2010 G.Hendriksen v1.0

*//* Function erlangb is used to get the Erlang-B (multiserver queueing system)

** probability that all the servers are busy and calls are dropped completely

** rather than being queued up. The function has input values number of servers

** (m) and utilization (p) — See pag. 83 formula 2.67

*/

function erlangb (m number, p number) return number AS

b number;

– Sigma inside formula is calculated here:

function sigm (v_m number, v_p number) return number AS

s number;

begin

s := 0;

for n in 0..(v_m)

loop

s := s + ( power((v_m*v_p),n)/fact(n) );

end loop;

return s;

end sigm;

BEGIN

b:=( power((m*p),m)/fact(m) ) / ( sigm(m,p) );

RETURN b;

END erlangb;/* Function erlangc is used to get the Erlang-C, this is the probability that

** all the servers are busy and therefore calls arriving causing utilization p

** will have to wait for service. The function has input values number of

** servers (m) and utilization (p) — See pag. 81 formula 2.66

*/

function erlangc (m number, p number) return number AS

c number;

function sigm (v_m number, v_p number) return number AS

s number;

begin

s := 0;

for n in 0..(v_m-1)

loop

s := s + ( power((v_m*v_p),n)/fact(n) );

end loop;

return s;

end sigm;

BEGIN

c:=( power((m*p),m)/fact(m) ) /( ( (1-p)* sigm(m,p)) + ( (power((m*p),m)/fact(m)) ) );

RETURN c;

END erlangc;/* Function erlangr is used to calculate the exact multiserver residence time.

** The function has input values number of servers (m), utilization (p) and

** service time (s) — See pag. 81 formula 2.64

*/

function erlangr (m number, p number, s number) return number AS

r number;

BEGIN

r := ( ( erlangc(m,p)*s ) / ( m*(1-p) ) ) + s;

RETURN r;

END erlangr;/* Function erlangq is used to calculate the queue length in a multiserver

** environment. The function has input values number of servers (m) and

** utilization (p) — See pag. 81 formula 2.65

*/

function erlangq (m number, p number) return number AS

q number;

BEGIN

q := ( ( p*erlangc(m,p) ) / ( m*(1-p) ) ) + (m*p);

RETURN q;

END erlangq;/* Function PDF is used to calculate the Discrete Probability Function of a

** Poison distribution with mean equal to the traffic intensity mp. The function

** has input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.68

*/

function PDF (m number, p number) return number AS

pdf number;

BEGIN

pdf := ( exp(-m*p)* ( power((m*p),m) / fact(m) ) );

RETURN pdf;

END pdf;/* Function CDF is used to calculate the Cumulative Distriburion Function of a

** Poison distribution with mean equal to the traffic intensity mp. The function

** has input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.68

*/

function CDF (m number, p number) return number AS

cdf number;

function sigm (v_m number, v_p number) return number AS

s number;

begin

s := 0;

for n in 0..(v_m)

loop

s := s + ( power((v_m*v_p),n)/fact(n) );

end loop;

return s;

end sigm;

BEGIN

cdf := ( exp(-m*p)*(sigm(m,p)) );

RETURN cdf;

END CDF;/* Function PoisonR is used to calculate the Poison Ratio. The function has

** input values number of servers (m) and utilization (p) — See pag. 83

** formula 2.69

*/

function PoisonR (m number, p number) return number AS

r number;

BEGIN

r := ( 1 – (pdf(m,p)/cdf(m,p)) );

RETURN r;

END PoisonR;/* Function Utilp is used to calculate the utilization (p). The function has

** input values calls per time lambda (l), service time (s) and number of queues

** (q) — See pag. 74 formula 2.45

*/

function Utilp (l number, s number, q number) return number AS

p number;

BEGIN

p := ( (l*s)/q );

RETURN p;

END Utilp;/* Function Paralqr is used to calculate the response time (r) for parallel

** queues, with utilization per queue. The function has input values

** utilization (p) and service time (s) — See pag. 75 formula 2.46

*/

function Paralqr (p number, s number) return number AS

r number;

BEGIN

r := ( s / (1-p) );

RETURN r;

END Paralqr;/* Function Paratqr is used to calculate the response time (r) for parallel

** queues, with utilization over all queues. The function has input values

** number of queues (q), utilization (p) and service time (s) — See pag. 75

** formula 2.46

*/

function Paratqr (q number, p number, s number) return number AS

r number;

BEGIN

r := ( s / (1-(p/q)) );

RETURN r;

END Paratqr;/* Function fact is used to get the factorial value with given number

*/

function fact (v number ) return number AS

min1 number;

prod number;

begin

min1 := v – 1;

if ( min1 > 0 )

then

prod := v*fact(min1);

return prod;

end if;

– important correction factorial value of zero is one.

if v=0 then return 1;

else return v;

end if;

END fact;END GHH_PKG;

/

All the formula’s have been checked and the output is provide in this excel file (erlangpacktest) . When checking the output of the functions, the response time in multiple servers situation will look like the picture:

I hope I made some people happy by programming the functions in PLSQL. If something is wrong with them, please comment on the blog or of course for any suggestion. I will probably enhance the package in future, if so I will keep you posted.

Regards, Gerwin

Hi Gerwin, do you think the entire PDQ stuff could be translated into PL/SQL ?

Thanks

Olivier

Hi Olivier, I guess that must be possible too. Although it will of course some work. I didn’t do it yet but it would be worth to investigate how easy it is.

Regards,

Gerwin