Universal Scalability Law in Oracle

In August 2011 Dave Abercrombie wrote an article about the Universal Scalability Law (USL) in an Oracle OLTP database. He used Mathematica for his calculations and wanted to provide a solution based on R later. Currently I am keen to learn more about the Universal Scalability Law and therefore I decided to transform his calculations into R code myself.

You might have heard or read about Amdahl’s Law. It is about the maximum improvement you can expect from a system when you parallelize parts of it. Every system eventually is limited by the parts that cannot be parallelized and this will cause performance to level off. This is known as the point of diminishing returns: adding more processing power to the system yields a much smaller improvement than expected.

Dr. Neil Gunther developed the Universal Scalability Law as a generalization of Amdahl’s Law by including not only concurrency but also coherency in the scalability model. This model predicts that performance will not only level off but actually decrease when parallelization is increased unreasonably. It seems that this model approximates real systems better than Amdahl’s Law does.

The following calculations will follow the steps performed by Dave Abercrombie in his Mathematica notebook. I also used his headlines to make a clear connection to the original calculations.

USL model detailed description

As in the original article I will try to estimate the parameters α and β of the Universal Scalability Law:

\( capacityRatio = \frac{n}{1 + \alpha * (n-1) + \beta * n * (n-1)} \)

The capacityRatio is the normalized throughput per concurrent user. We take the Oracle metric buffer gets to model throughput and Average Active Sessions (AAS) to model concurrent users. The article Concurrency of a connection pooled OLTP database: N=AAS shows that AAS is a reasonable metric to model the number of concurrent users.

Observations from a production database

Dave Abercrombie has provided a CSV file with AWR data from a production database. First we need to read the data into R.

> awrObservations <- read.csv(file="db9-awr.csv")

Then we create a new data frame nThroughPut containing only the relevant columns AAS (derived from DB_TIME_CSPH) and meanThroughPut (derived from BUFFER_GETS_PH). We perform the same sanity check as in the original article and print the first ten rows.

> nThroughPut <- with(
    awrObservations,
    data.frame(AAS=DB_TIME_CSPH / (3600*100),
               meanThroughPut=BUFFER_GETS_PH / (3600*1000)
))

> head(nThroughPut, n=10)
        AAS meanThroughPut
1  1.987661       165.2103
2  1.948708       152.5213
3  2.093981       159.3359
4  2.296250       167.6869
5  2.373292       165.1114
6  2.196808       163.3581
7  2.224908       157.0620
8  1.790683       142.2059
9  1.381636       121.5163
10 1.218381       112.1319

The formatting in R is a bit different compared to Mathematica but the data is the same.

Estimate throughput at N=1

To get the normalized capacityRatio we need to find the throughput for N=1. Therefore we create a subset of the data with AAS less than 1.5 to be used in linear regression.

> nThroughPutSubset <- subset(nThroughPut, AAS < 1.5)

Next we create a scatterplot of the data for a quick sanity check.

> plot(nThroughPutSubset, pch=20)

Scatterplot for AAS<1.5

Now we can use the lm() function to create the linear model and estimate the dependency between AAS and meanThroughPut. The summary() function tells us more about the fitted model.

> nThroughPutSubsetLm <- lm(meanThroughPut ~ AAS,
                            data = nThroughPutSubset)

> summary(nThroughPutSubsetLm)

Call:
lm(formula = meanThroughPut ~ AAS, data = nThroughPutSubset)

Residuals:
    Min      1Q  Median      3Q     Max
-6.0757 -2.7737 -0.7096  1.7995  9.3835

Coefficients:
            Estimate Std. Error t value Pr(>|t|)
(Intercept)   34.602      7.953   4.351 0.000186 ***
AAS           67.018      6.107  10.974 2.96e-11 ***
---
Signif. codes:  0***0.001**0.01*0.05.0.1 ‘ ’ 1

Residual standard error: 3.944 on 26 degrees of freedom
Multiple R-squared: 0.8224,  Adjusted R-squared: 0.8156
F-statistic: 120.4 on 1 and 26 DF,  p-value: 2.957e-11

The model calculated the coefficents 34.602 and 67.018 which matches the result in the Mathematica notebook. We can also see from the p-value of the model and the t values that we actually have found significant parameters for the linear model.

Then the predict() function is used to estimate the throughput for AAS=1. R returns 101.6202 as the result and again this matches the result calculated with Mathematica.

> throughPutOne <- predict(
    nThroughPutSubsetLm,
    newdata = data.frame(AAS=c(1)))[[1]]

> throughPutOne
[1] 101.6202

A plot of the calculated liner model can be added to the existing scatterplot and we get the same graph as in the original article.

> abline(nThroughPutSubsetLm)

Linear regression for AAS<1.5

Normalize throughput observations to convert to capacityRatio

In order to get the normalized capacity ratio we create a new data frame containing the original observations divided by 101.6202 (the estimated value for AAS=1). A printout of the first ten observations and a scatterplot of the data frame shows we are still following the original calculations.

> nCapacityRatio <- with(
    nThroughPut,
    data.frame(AAS = AAS,
               nThroughPut = meanThroughPut / throughPutOne
))

> head(nCapacityRatio, n=10)
        AAS nThroughPut
1  1.987661    1.625763
2  1.948708    1.500896
3  2.093981    1.567955
4  2.296250    1.650134
5  2.373292    1.624789
6  2.196808    1.607536
7  2.224908    1.545579
8  1.790683    1.399386
9  1.381636    1.195789
10 1.218381    1.103441

> plot(nCapacityRatio, pch=20,
       xlim=c(0,5.6), ylim=c(0,2.5))

Normalized capacity ratio

Fit USL model to observations to estimate parameters α and β

Now I define the function usl() in R to make the following code easier to read.

> usl <- function(n, alpha, beta) {
    n / (1 + (alpha * (n - 1)) + (beta * n * (n - 1)))
}

The R function nls() is used to solve the non-linear function usl() based on the values in the data frame nCapacityRatio. Again the summary() function is used to show details about the result.

> bestFitUSL <- nls(nThroughPut ~ usl(AAS, alpha, beta),
                    data  = nCapacityRatio,
                    start = c(alpha=0.1, beta=0.01))

> summary(bestFitUSL)

Formula: nThroughPut ~ usl(AAS, alpha, beta)

Parameters:
      Estimate Std. Error t value Pr(>|t|)
alpha 0.189005   0.017125  11.037  < 2e-16 ***
beta  0.033517   0.005261   6.371 1.64e-09 ***
---
Signif. codes:  0***0.001**0.01*0.05.0.1 ‘ ’ 1

Residual standard error: 0.1102 on 173 degrees of freedom

Number of iterations to convergence: 5
Achieved convergence tolerance: 5.256e-07

Again the t values show a high significance of the result. We can use coef() if we are only interested in the coefficients.

> coef(bestFitUSL)

     alpha       beta
0.18900524 0.03351659

We come to the same result as in the Mathematica calculations. Plotting the curve using the estimated values for α and β shows the same graph.

> curve(usl(x,
            coef(bestFitUSL)[["alpha"]],
            coef(bestFitUSL)[["beta"]]),
        from=0, to=8, col="lightblue", add=TRUE)

Universal Scalability Law (USL)

Using the formula given by Dr. Neil Gunther we can also calculate the point where the system reaches its maximum scalability.

> sqrt((1 - coef(bestFitUSL)[["alpha"]]) /
       coef(bestFitUSL)[["beta"]])

[1] 4.919026

So the measured data seams to belong to a system that reaches peak scalability with about five concurrent users. Having more than five Average Active Sessions (=concurrent users) seemingly leads to a degradation of throughput where throughput is defined as the number of Oracle buffer gets per hour.

In this article I have illustrated how R can be used to apply the Universal Scalability Law (USL) to Oracle performance metrics. Credits for the math behind this go to Dr. Neil Gunther and Dave Abercrombie; errors in the R implementation are all my fault.