Automating the finding of coefficients for the USL

I got to playing around with R more and I've always found that to learn a language I need to solve problems with the language. I'm sure most everybody else does the same thing. My goal was to write a R function that imported a CSV with performance information to gonkulate against.

In my case, I'm using the basic performance information from "Guerrilla Capacity Planning." I've created a CSV file with the number of procs and the resultant ray trace benchmark from Table 5.1:

   1:  C:\Users\auswipe\Desktop>cat raw_throughput.csv

   2:  p,x

   3:  1,20

   4:  4,78

   5:  8,130

   6:  12,170

   7:  16,190

   8:  20,200

   9:  24,210

  10:  28,230

  11:  32,260

  12:  48,280

  13:  64,310

Then I wrote an R function to crunch the numbers:

   1:  uslCoefficients <- function(dataFile) {

   2:    uslData <- read.csv(dataFile, header=TRUE);

   3:    uslData$c <- uslData$x / uslData$x[1];

   4:    usl <- nls(c ~ p/(1+sigma*(p-1)+kappa*p*(p-1)),

   5:               uslData,

   6:               algorithm="port",

   7:               start=c(sigma=0.0, kappa=0.0),

   8:               lower=c(0,0));

   9:    sigma <- coef(usl)["sigma"];

  10:    kappa <- coef(usl)["kappa"];

  11:    return(list(sigma=sigma, kappa=kappa));

  12:  };

The function uslCoefficient returns a list where I can reference the "sigma" and "kappa" by named index:

   1:  > uslCoef <- uslCoefficients("c:\\Users\\auswipe\\Desktop\\raw_throughput.csv")

   2:  > uslCoef["sigma"]

   3:  $sigma

   4:      sigma 

   5:  0.0497973 

   6:   

   7:  > uslCoef["kappa"]

   8:  $kappa

   9:         kappa 

  10:  1.143404e-05 

  11:   

  12:  > uslCoef

  13:  $sigma

  14:      sigma 

  15:  0.0497973 

  16:   

  17:  $kappa

  18:         kappa 

  19:  1.143404e-05 

R is pretty nifty. I doubt I'll ever make use of all the power that is available but it'll be better than writing my own stat routines.

Using R to calculate coefficients of the Universal Scaling Law with Non-Linear Regression

Ooh! Doesn't that sound fancy?

Several weeks ago I purchased the eBook from O'Reilly called "The Art of Capacity Planning." I've always thought that load testing and capacity planning went hand-in-hand. One is not a replacement for the other but one can assist with the other. Load test helps out capacity planning by applying load to psuedo-production systems and capacity planning helps load testing by verifying results in load test against real world systems.

I finished "The Art of Capacity Planning" and wanted to read more on the subject and picked up a copy of "Guerrilla Capacity Planning" which has a lot more math than "The Art of Capacity Planning." One of the concepts is the Universal Scaling Law based on Amdhal's Law. Dr. Neil J Gunther is a smart cookie. He even has a Ph.D in Theoretical Physics which makes him closer to Gordon Freeman than I'll ever be! (Side question: Do Ph.D's in Theoretical Physics get crowbars at graduation?)

Anyhoo, in section 5.6.1 one of the methods in the book is to use Excel to do second degree polynomial regression for the calculation of two necessary coefficients, sigma and kappa. But when I tried to use Excel I was getting a negative value for sigma and one of the rules of the Universal Scaling Law is that the coefficients can never, ever, ever be negative. I just figured that I fat fingered something and tried it again and once again, got mismatching results.

I scratched my noggin, tried to figure out where I err'd and did some Googling and came across this entry of Dr. Gunther's blog:

Negative Scalability Coefficients in Excel

Because in Excel (and some other packages, like my TI-89) you can't put a constraint on the lower limits of the coefficient, you might from time to time get negative coefficients. But from reading the blog entry I see that other people are using R with success.

This is the first time that I've ever messed around with R for statistical purposes. In the past I've written some stat routines (years ago!) in C# for comparing before/after load testing results.

Here is how I used R from start to finish to gonkulate the coefficients.

Using the data from Section 5.3 I did the following in R:

First I defined my p array, which in the book is the number of processors used for ray tracing:

   1:  p <- c(1, 4, 8, 12, 16, 20, 24, 28, 32, 48, 64)

Then I defined my c array, which is the relative capacity for the number of processors used for ray tracing:

   1:  c <- c(1.0, 3.9, 6.5, 8.5, 9.5, 10.0, 10.5, 11.5, 13.0, 14.0, 15.5)

I combined both arrays into a data frame for later use.

   1:  df <- data.frame(p, c)

And when I check out the contents of df I get:

   1:  df

   2:      p    c

   3:  1   1  1.0

   4:  2   4  3.9

   5:  3   8  6.5

   6:  4  12  8.5

   7:  5  16  9.5

   8:  6  20 10.0

   9:  7  24 10.5

  10:  8  28 11.5

  11:  9  32 13.0

  12:  10 48 14.0

  13:  11 64 15.5

I can now use a non-linear regression routine with my data frame that I entered above.

   1:  usl <- nls(c ~ p/(1+sigma*(p-1)+kappa*p*(p-1)), df, algorithm="port", start=c(sigma=0.0, kappa=0.0), lower=c(0,0))

I can then access the coefficients by named index:

   1:  sigma <- coef(usl)["sigma"]

   2:  kappa <- coef(usl)["kappa"]

   3:   

   4:  sigma

   5:      sigma 

   6:  0.0497973 

   7:   

   8:  kappa

   9:         kappa 

  10:  1.143404e-05 

  11:   

Huzzah!

I can now interpolate the relative capacity based upon the USL and the coefficients that were previously gonkulated and add that to my current data frame, df, that I defined earlier. I do have to note that I was a slackard and did not apply the significant digits rules as outlined in Chapter 3 of "Guerrilla Capacity Planning."

   1:  df$proj_c <- p/(1 + sigma * (p - 1) + kappa * p * (p - 1))

There are the projected relative capacities. Yay!

   1:  df

   2:      p    c    proj_c

   3:  1   1  1.0  1.000000

   4:  2   4  3.9  3.479686

   5:  3   8  6.5  5.929346

   6:  4  12  8.5  7.745536

   7:  5  16  9.5  9.144406

   8:  6  20 10.0 10.253815

   9:  7  24 10.5 11.154233

  10:  8  28 11.5 11.898837

  11:  9  32 13.0 12.524174

  12:  10 48 14.0 14.259114

  13:  11 64 15.5 15.298811

And here I will make a simple little graph of the actual versus projected relative capacity:

   1:  plot(p, c)

   2:  lines(p, proj_c)

And here is the graph that is generated:



Kinda nifty, eh?

I can see myself using R more in the future. I'd rather write routines for automagic analysis of data with R than write my own routines from the ground up.

Extracting Perfmon data from the VS2008 Load Test Repository

Ok. You are performing load tests with VS2008 TSTE and you've enabled the logging of perfmon metrics in your test by tweaking the "Timing Details Storage" property of the Run Settings in your load test. Every 5 seconds the perfmon metrics you specified are being stored to the load test repository and after the run you get some metrics. That's great and all but what if you want more detailed statistics such as average AND standard deviation.

Or better yet! You want at that data to generate graphs of the metrics over the duration of the test. Sure, you can do this by firing up good 'ol Perfmon and logging the values or you can just get them from your load test repository with some SQL. Why have Perfmon logging when VS2008 already does this for you?

I have a need for just such an action and I've been working on the SQL statements to extract the data I need. It's not complete, but this is a major step forward to allowing me to finish up the SPs I want to collect the data dynamically for consumption by managers and other QA folks.

Looking at the handy dandy schema of the load test repository (located here for all to gander) I have created some SQL statements that take care of the leg work for me.

   1:  SELECT LoadTestPerformanceCounterInstance.InstanceId, 

   2:         LoadTestPerformanceCounterCategory.CategoryName, 

   3:         LoadTestPerformanceCounter.CounterName, 

   4:         LoadTestPerformanceCounterInstance.InstanceName, LoadTestPerformanceCounterCategory.MachineName, 

   5:         AVG(LoadTestPerformanceCounterSample.ComputedValue) AS AverageValue,

   6:         stdev(LoadTestPerformanceCounterSample.ComputedValue) as StdDev,

   7:         count(LoadTestPerformanceCounterSample.ComputedValue) as Count

   8:  FROM  LoadTestPerformanceCounterCategory INNER JOIN

   9:                 LoadTestPerformanceCounter INNER JOIN

  10:                 LoadTestPerformanceCounterInstance ON LoadTestPerformanceCounter.LoadTestRunId = LoadTestPerformanceCounterInstance.LoadTestRunId AND 

  11:                 LoadTestPerformanceCounter.CounterId = LoadTestPerformanceCounterInstance.CounterId ON 

  12:                 LoadTestPerformanceCounterCategory.CounterCategoryId = LoadTestPerformanceCounter.CounterCategoryId AND 

  13:                 LoadTestPerformanceCounterCategory.LoadTestRunId = LoadTestPerformanceCounter.LoadTestRunId INNER JOIN

  14:                 LoadTestPerformanceCounterSample ON LoadTestPerformanceCounterInstance.LoadTestRunId = LoadTestPerformanceCounterSample.LoadTestRunId AND 

  15:                 LoadTestPerformanceCounterInstance.InstanceId = LoadTestPerformanceCounterSample.InstanceId

  16:  WHERE (LoadTestPerformanceCounter.LoadTestRunId = 83)

  17:  GROUP BY LoadTestPerformanceCounterCategory.CategoryName, LoadTestPerformanceCounter.CounterName, LoadTestPerformanceCounterInstance.InstanceName, 

  18:                 LoadTestPerformanceCounterInstance.InstanceId, LoadTestPerformanceCounterCategory.MachineName

  19:  ORDER BY LoadTestPerformanceCounterInstance.InstanceId

In the above example, the reference to LoadTestRunId is hard coded to a previous run that I have in my results repository. For all my other SPs I use the GUID of the test and will convert the above SQL statement to reference GUID all in good time. The query above returns results like this:

   1:  0     .NET CLR Memory     Gen 0 heap size                             w3wp     10.0.0.11     944824777.209431     185400315.203616     721

   2:  1     .NET CLR Memory     Large Object Heap size                      w3wp     10.0.0.11     86994000.5298197     29625345.9051784     721

   3:  2     .NET CLR Memory     # Gen 0 Collections                         w3wp     10.0.0.11     132.608876560333     69.5129454689118     721

   4:  3     .NET CLR Memory     Gen 2 heap size                             w3wp     10.0.0.11     197119053.001387     78824416.0154383     721

   5:  4     .NET CLR Memory     Allocated Bytes/sec                         w3wp     10.0.0.11     63521791.4160888     91335026.9218956     721

   6:  5     .NET CLR Memory     # Gen 2 Collections                         w3wp     10.0.0.11     29.5104022191401     12.1351287581502     721

   7:  6     .NET CLR Memory     Promoted Memory from Gen 0                  w3wp     10.0.0.11     42540355.2815534     13356693.0175676     721

   8:  7     .NET CLR Memory     # Induced GC                                w3wp     10.0.0.11     0                    0                    721

   9:  8     .NET CLR Memory     Gen 0 Promoted Bytes/Sec                    w3wp     10.0.0.11     418557.084366331     1120132.98732717     721

  10:  9     .NET CLR Memory     Promoted Memory from Gen 1                  w3wp     10.0.0.11     21740368.0721221     25126733.8606902     721

  11:  10    .NET CLR Memory     # GC Handles                                w3wp     10.0.0.11     3750.73370319001     216.191892870829     721

  12:  11    .NET CLR Memory     # Gen 1 Collections                         w3wp     10.0.0.11     70.0693481276005     35.4698332819129     721

  13:  12    .NET CLR Memory     Gen 1 heap size                             w3wp     10.0.0.11     62394980.9237171     31034211.4233198     721

  14:  13    .NET CLR Memory     Finalization Survivors                      w3wp     10.0.0.11     8217.1040221914      3024.02383909951     721

  15:  14    .NET CLR Memory     Promoted Finalization-Memory from Gen 0     w3wp     10.0.0.11     11415345.6768377     11434973.2174913     721

  16:  15    .NET CLR Memory     % Time in GC                                w3wp     10.0.0.11     3.48545611573799     6.73310861817717     721

The first column is the InstanceId of the perfmon counter and the information for the counter is split up into three separate columns (CategoryName, CounterName and InstanceName) but the column that ties them all together is the InstanceId.

Ok. That's fine and dandy but what about graphing the metrics over the duration of the load test?

Well, that's another SQL query I wrote that is in the alpha stage of development. In the above example I displayed 15 out of 466 metrics that were logged. Let's choose to graph the .NET CLR Memory Gen 2 heap size for w3wp. This particular metric happens to be InstanceId #3 this time around and we can use it in the SQL statement below to extract the data that can eventually be used to graph the results.

   1:  SELECT LoadTestPerformanceCounterInstance.InstanceName,

   2:         LoadTestPerformanceCounterSample.ComputedValue

   3:  FROM  LoadTestPerformanceCounterInstance INNER JOIN

   4:        LoadTestPerformanceCounterSample ON LoadTestPerformanceCounterInstance.LoadTestRunId = LoadTestPerformanceCounterSample.LoadTestRunId AND 

   5:        LoadTestPerformanceCounterInstance.InstanceId = LoadTestPerformanceCounterSample.InstanceId

   6:  WHERE (LoadTestPerformanceCounterInstance.LoadTestRunId = 83) AND (LoadTestPerformanceCounterInstance.InstanceId = 3)

   7:  ORDER BY LoadTestPerformanceCounterSample.TestRunIntervalId

This query yields the results of:

   1:  w3wp    0

   2:  w3wp    0

   3:  w3wp    96

   4:  w3wp    3231264

   5:  w3wp    2.170597E+07

   6:  w3wp    2.170597E+07

   7:  w3wp    2.170597E+07

   8:  w3wp    2.976022E+07

   9:  w3wp    2.976022E+07

  10:  w3wp    2.976022E+07

  11:  w3wp    2.976022E+07

  12:  w3wp    4.127677E+07

  13:  w3wp    4.127677E+07

  14:  w3wp    4.127677E+07

and so on and so on until all 722 metrics are displayed. We can take those 722 metrics and copypasta into Excel and generate a quick and dirty graph:



Viola! Quick and dirty graph from Excel!

I'll need to change the column output to show more details on the metrics to display all three columns to better identify the perfmon metric and also display the time that the metric was taken.

After I do all that I'll need to write some code to generate graphs on the fly. I'm thinking that this project on SourceForge will fit the bill nicely and there seems to be a bunch of examples out on the 'net how to use ZedGraph to it's fullest.

Oh yeah! Almost forgot. I need to double check the results between the first query and the individual information for InstanceId 3 metric (the .NET CLR Gen 2 heap size for w3wp). The first query output says the average value is 197119053.001387. Take the average of output that I did the copypasta into Excel gonkulates to an average of 197119053.4. The difference between the two? A measly 0.355. I'm thinking that is good 'nuff for government work.

Now I just need to finish up the queries and get them into SPs and integrate them with the other code that I've written that does automagic before/after comparison of transaction response times (more on that at a later date).

Extracting Transaction Response Time by Transaction Name per Load Test Run

Despite being annoyed that think time is included in the VS2008 web test response time I would like to be able to graph the response times for a given transaction name. I load my Load Test History database by the unique GUID and forgo the integer test ID to prevent possible duplicates in the future.

I created a query that takes transaction name and test GUID to pull all the transaction times.

   1:  SELECT LoadTestTransactionDetail.TimeStamp, LoadTestTransactionDetail.ElapsedTime

   2:  FROM  LoadTest.dbo.LoadTestRun INNER JOIN

   3:                 LoadTest.dbo.WebLoadTestTransaction ON LoadTestRun.LoadTestRunId = WebLoadTestTransaction.LoadTestRunId INNER JOIN

   4:                 LoadTest.dbo.LoadTestTransactionDetail ON WebLoadTestTransaction.TransactionId = LoadTestTransactionDetail.TransactionId AND 

   5:                 LoadTest.dbo.LoadTestRun.LoadTestRunId = LoadTestTransactionDetail.LoadTestRunId

   6:  WHERE (WebLoadTestTransaction.TransactionName = 'Some_Transaction_Name') AND (LoadTestRun.RunId = '987cce52-7608-40a7-ac85-96714af1ac6d')

   7:  ORDER BY LoadTestTransactionDetail.TimeStamp

   8:   

   9:   

There we go! All the transaction times for "Some_Transaction_Name:"

   1:  2008-07-29 15:58:04.467    7.944

   2:  2008-07-29 15:58:37.633    6.138

   3:  2008-07-29 15:58:55.930    5.243

   4:  2008-07-29 15:59:04.977    4.615

   5:  2008-07-29 15:59:25.607    6.461

   6:  2008-07-29 15:59:36.493    5.026

   7:  2008-07-29 15:59:40.820    5.256

   8:  2008-07-29 16:00:00.507    5.573

   9:  2008-07-29 16:00:04.940    5.457

  10:  2008-07-29 16:00:08.977    5.577

  11:  2008-07-29 16:00:16.547    5.506

  12:  2008-07-29 16:00:34.303    5.758

Uh-Oh. This isn't good.

I found out something today that I really don't like with VS2008 for load testing. I created my virtual user and setup think time ala WebTestRequest.ThinkTime with reasonable think times. After the testing was done I moved the results over to my LoadTestHistory database with a SP that I wrote about earlier in my blog. When I examined the results it turned out that the think time was included in the transaction response time. Nope, I don't like that. I dislike that about as much as I don't like having a failed counter for transactions.

I guess I can't be really surprised as the Begin/End transaction is wrapped around the WebTestRequest object and the think time is handled by the WebTestRequest object and there is no communication between the transaction begin/end and the WTR object (AFAIK).

I looked in the schema for the Load Test Repository and didn't see any fields where response time alone (without think time) would be recorded but there is definitely response time without sleep time recorded during the execution of the VS2008 loadtest. I figured there would be a field in the database with this value but I didn't see it.

Luckily, though, this is pretty simple to get around. If you aren't going to be using variable think times you can simple perform a Thread.Sleep(new TimeSpan(0, 0, 0, 5, 0)) which is similar to the LoadRunner idea behind lr_think_time(). Don't forget to add the "using System.Threading;" to your code to reference the Thread object since it is not referenced by default.

If you want to use variable think time then you'll have to come up with your own mechanism for varying the randomness of the think time. It's not that difficult, it is just a pain.

Transaction Transaction, what's your action?

I noticed something this morning as I was going over my SP that I wrote on Thursday night. I realized that I needed to also gonkulate how many transactions had failed. Seems simple enough, right? If a page (or dependent request) fails inside a transaction the whole transaction gets marked as a failure, right? That's the way that LoadRunner handles the transaction issue.

In LoadRunner I would normally leave the determination of the status of the transaction by using LR_AUTO. There were a few times that I would explicitly call EndTransaction with a LR_FAIL.

I realized looking over the schema of the Load Test Repository Store that there does not appear to be any logging of failures on the transaction level. The page level? Sure. Not a problem. LoadTestMessage table contains those goodies for us but I cannot find a way to link LoadTestMessage with WebLoadTestRequestMap. Going back over some of the online documentation for running VS2008 load tests it appears that the tool itself does not count transaction failures and only counts failed pages.

Yeah... I'm not too happy about that. With my analysis I want to see what transactions failed as I might have multiple page hits to a single transaction. Hopefully this gets fixed in a future version of the MS LT tool.