UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
This post covers usage of my SLOB.R script, used to analyze SLOB results from repeated test runs. The contents of the SLOB.R script are at the bottom of this post, but first I will show the contents of a sample SLOB.R session, followed by how you can produce similar results.
A Sample SLOB.R Session
The first step is to start R. On Windows you’ll double-click an icon, on Linux you’ll just type R and hit enter. Once R has started, you will be in the interactive R interface and it will display some introductory text standard in R.
R version 2.15.2 (2012-10-26) -- "Trick or Treat" Copyright (C) 2012 The R Foundation for Statistical Computing ISBN 3-900051-07-0 Platform: i386-w64-mingw32/i386 (32-bit) R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details. Natural language support but running in an English locale R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications. Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R. >
Once you are in R, you need to load the SLOB.R script and then load your SLOB data. SLOB data is produced by running SLOB repeatedly, saving the awr.txt output in between each run, then running the SLOB/misc/awr_info.sh script to summarize the collected AWR reports. I have saved the SLOB.R script in my Cygwin home directory, and saved the awr_info.sh output in a file called “awrinfo” in the same directory.
> source('C:/cygwin/home/brian/SLOB.R')
> SLOB <- loadSLOB('C:/cygwin/home/brian/awrinfo')
Now you have the contents of your awr_info.sh output in an R variable called SLOB. You can call this variable anything you wish, but I used SLOB.
To use SLOB.R you need to tell it a little bit about your AWR data: specifically which variables you are trying to test and how many sessions you used. In this example I am comparing performance of the XFS and EXT3 filesystems using the SLOB physical read, redo, and physical write models. The variables to be tested (EXT3 and XFS) are embedded in the filenames I used when saving the awr.txt report between SLOB runs, and the session counts are the numbers I used when running SLOB’s runit.sh script to put load on the database. We tell SLOB.R about this by setting a few R variables containing this data. You can call them anything you wish, you just need to know their names.
> SLOB.sessions <- c(8, 16, 32)
> SLOB.read.vars <- c('XFS', 'EXT3')
> SLOB.redo.vars <- c('XFS', 'EXT3')
> SLOB.readwrite.vars <- c('XFS', 'EXT3')
As you can see, variable assignment in R uses the <- operator. I’m using the R built-in c() function (concatenate) to create vector variables that contain multiple values (you can think of them as like an array for now). The SLOB.sessions variable contains three integer values: 8, 16, and 32; the other three variables each contain two string values: ‘XFS’ and ‘EXT3’. For this demo I am only including two variables but it works fine with many more than that. I have been using about 7 of them. I am comparing filesystems, but you might be comparing storage vendor 1 vs storage vendor 2, or fibre channel vs dNFS, or Oracle 11.2.0.1 vs Oracle 11.2.0.3. As long as the variables are identifiable from your AWR filenames, they will work.
You can view the contents of any R variable just by typing its name.
> SLOB.sessions [1] 8 16 32 > SLOB.read.vars [1] "XFS" "EXT3"
With these variables set up you can now use the main SLOB.R driver functions to summarize the contents of your SLOB AWR reports. First I’ll call the SLOBreads() function to summarize physical read performance. This function summarizes the PREADS column from awr_info.sh output, by variable and session count. To produce a better average it discards the lowest value and highest value from each combination of variable and session count. Other SLOB.R driver functions are SLOBredo() and SLOBreadwrite().
> SLOBreads(SLOB, SLOB.reads.vars, SLOB.sessions)
8 16 32 Overall
XFS 27223.89 46248.05 61667.21 44886.22
EXT3 30076.77 49302.59 59113.00 46094.39
So this indicates that for 8 reader sessions, the XFS filesystem gave me an average of 27,223.89 physical reads per second, while EXT3 gave me 30,076.77 physical reads per second. The columns for 16 and 32 sessions have the same meaning as the 8 session column. The ‘Overall’ column is an average of ALL the data points, regardless of session count.
You don’t have to use variables when calling the SLOB.R driver functions. You can specify the variables or session counts directly in the call. This is part of how R works. Another example, showing how you can receive the same output by calling it with a variable or not:
> SLOBredo(SLOB, SLOB.redo.vars, SLOB.sessions)
8 16 32 Overall
XFS 326480426 336665385 321823426 326425272
EXT3 304188997 325307026 326618609 317991362
> SLOBredo(SLOB, c('XFS', 'EXT3'), c(8, 16, 32))
8 16 32 Overall
XFS 326480426 336665385 321823426 326425272
EXT3 304188997 325307026 326618609 317991362
The numbers above would indicate that when storing my redo logs on XFS, SLOB could push 326,480,426 bytes of redo per second with 8 sessions. On EXT3 with 8 sessions I saw 304,188,977 bytes of redo per second. The 16, 32 and Overall columns have meanings similar to what I showed before.
The SLOBreadwrite() function reports the sum of physical reads and physical writes, with the columns all meaning the same as they do for the other functions.
> SLOBreadwrite(SLOB, SLOB.readwrite.vars, SLOB.sessions) 8 16 32 Overall XFS 18520.44 20535.41 20728.07 19823.37 EXT3 19568.04 21730.94 22641.14 21203.78
How To Create Output For SLOB.R
SLOB.R is smart enough to figure out which of your runs are testing reads, which are testing redo, and which are testing readwrite performance. But for this to work you have to follow the naming convention defined in the SLOB/misc/README file when renaming your awr.txt files for processing by awr_info.sh: [whatever].[number of SLOB writers].[number of SLOB readers] — SLOB.R expects your variables to be uniquely identifiable strings in the ‘whatever‘ part.
I recommend using scripts to run SLOB repeatedly and save the awr.txt output in between. I provided some scripts in a prior post, but you can use your own scripts as long as your filenames match the required format.
Once you have your AWR output collected, run SLOB/misc/awr_info.sh on all the files, and save its output. This is the file you will load into R.
SLOB.R Script (v0.6)
Save this as SLOB.R. You may find it easier to use the pastebin version.
# SLOB.R version 0.6
# BJP 2013 - Twitter @BrianPardy - https://pardydba.wordpress.com/
# See http://wp.me/p2Jp2m-4i for more information
#
#
# Assumes three possible SLOB test models: READS, REDO, WRITES
# READS are readers-only
# REDO and WRITES are writers only, differing in size of buffer_cache (small = REDO, large = WRITES)
#
# Reports PREADS in SLOB.R READS model
# Reports REDO in SLOB.R REDO model
# Reports PWRITES in SLOB.R WRITES model
# Use SLOB.R meta-model READWRITE to report PREADS+PWRITES in WRITES model
#
# Setup:
# Run SLOB as usual, with at least three runs for each variable tested for each model
# Save awr.txt between runs in filename matching [something]-variable.writers.readers
# Example: AWR-FCSAN-run1.8.0, AWR-FCSAN-run2.8.0 (...) AWR-FCSAN-run10.8.0
# AWR-ISCSINAS-run1.8.0, AWR-ISCSINAS-run2.8.0 (...) AWR-ISCSINAS-run10.8.0
# (In this case, the variables would be "FCSCAN" and "ISCSINAS", comparing fibrechannel SAN to NAS)
# Run awr_info.sh from SLOB distribution against all AWR files at the end and save the output
# Load awr_info.sh output into R with: SLOB <- loadSLOB("filename")
#
# Hints:
# Best results achieved with more SLOB runs - myavg() drops high and low values per set, averages remaining
#
# Detailed example usage:
# Testing SLOB read, redo and readwrite models 10 times each with 8, 16, and 32 sessions on EXT3 vs EXT4
# Used a tablespace on EXT3 for EXT3 testing and a tablespace on EXT4 for EXT4 testing
# Used redo logs on EXT3 for EXT3 REDO testing and redo logs on EXT4 for EXT4 REDO testing
# Ran SLOB/misc/awr_info.sh on all awr.txt reports generated from these 60 SLOB runs
# Saved awr_info.sh output as filename "awrinfo"
#
# (Start R)
# > source("SLOB.R")
# > SLOB <- loadSLOB("awrinfo")
# > SLOB.sesscounts < c(8, 16, 32) # Specify the number of sessions used in tests
# > SLOB.read.vars <- c('EXT3', 'EXT4') # Specify the variables for READ testing: EXT3 vs EXT4
# > SLOB.redo.vars <- SLOB.read.vars # Same variables for REDO testing as for READ testing
# > SLOB.readwrite.vars <- SLOB.read.vars # Same variables for READWRITEtesting as for READ testing
# > SLOB.reads <- SLOBreads(SLOB, SLOB.reads.vars, SLOB.sesscounts)
# > SLOB.redo <- SLOBredo(SLOB, SLOB.redo.vars, SLOB.sesscounts)
# > SLOB.readwrite <- SLOBreadwrite(SLOB, SLOB.readwrite.vars, SLOB.sesscounts)
### Previous three lines populate SLOB.reads, SLOB.redo and SLOB.readwrite variables with AWR results
### You can then interrogate those variables by typing their names
# > SLOB.reads
# 8 16 32 Overall
# XFS 27223.89 46248.05 61667.21 44886.22
# EXT3 30076.77 49302.59 59113.00 46094.39
#
#
# Usage variants for more detailed investigation. Consider this advanced usage.
# Most people should just use SLOBreads(), SLOBredo() and SLOBreadwrite()
#
#
# Get average REDO bytes for variable 'foo' across all sessions:
# avg(SLOB, 'REDO', 'foo')
# redoavg(SLOB, 'foo')
#
# Get average REDO bytes for variable 'foo' with 8 sessions:
# avg(SLOB, 'REDO', 'foo', 8)
# redoavg(SLOB, 'foo', 8)
#
# Get average PREADS (physical read) for variable 'foo' across all sessions:
# avg(SLOB, 'READS', 'foo')
# readavg(SLOB, 'foo')
#
# Get average PWRITES (physical writes) for variable 'foo' with 16 sessions:
# avg(SLOB, 'WRITES', 'foo', 16)
# writeavg(SLOB, 'foo', 16)
#
# Get sum of PREADS and PWRITES for variable 'foo' with 32 sessions:
# avg(SLOB, 'READWRITE', 'foo', 32)
# readwriteavg(SLOB, 'foo', 32)
#
# Get average REDO bytes for multiple variables ('foo' and 'bar') across all sessions:
# sapply(c('foo', 'bar'), redoavg, dat=SLOB)
# or for 16 sessions:
# sapply(c('foo', 'bar'), redoavg, dat=SLOB, sessioncount=16)
# alternate: sapply(c('foo', 'bar'), avg, dat=SLOB, sessioncount=16, model='READS')
# (Note: This returns separate results for each variable, it does not combine and average them)
#
# Get sum of PREADS and PWRITES for multiple variables ('XFS' and 'EXT3') across 16 sessions:
# sapply(c('XFS', 'EXT3'), avg, dat=SLOB, sessioncount=16, model='READWRITE')
#
# View the top waits in XFS READ model with 16 sessions:
# waits(SLOB, str='XFS', model='READS', sessioncount=16)
#
# View all data for a particular model with a specific session count:
# SLOB[intersect(REDO(SLOB), sessions(16, SLOB)),]
#
#
getruns <- function(dat, model, str, sessioncount) {
tmp <- intersect(grep(str, dat$FILE), eval(parse(text=paste(model, '(dat)', sep=''))))
if(missing(sessioncount)) { return(tmp)}
else { intersect(tmp, sessions(sessioncount, dat))}
}
loadSLOB <- function(filename) {
read.table(file=filename, sep="|", header=TRUE)
}
# heuristically identify REDO model runs - if this sucks for you, comment it out
# and uncomment the alternate REDO function below. It expects your filenames to include
# the string '-REDO-' when testing REDO performance.
REDO <- function(dat) {
setdiff(which(dat$REDO/(dat$PWRITES*8192) > 2), READS(dat))
}
#REDO <- function(dat) {
# grep('-REDO-', dat$FILE)
#}
WRITES <- function(dat) {
setdiff(which(dat$WRITERS > 0), REDO(dat))
}
READS <- function(dat) {
which(dat$READERS > 0)
}
READWRITE <- function(dat) {
WRITES(dat)
}
sessions <- function(n, dat) {
union(which(dat$WRITERS == n), which(dat$READERS == n))
}
myavg <- function(z) {
mean(z[!z %in% c(min(z), max(z))])
}
getavg <- function(mode, dat) {
myavg(eval(parse(text=paste('dat$', mode, sep=''))))
}
redoavg <- function(dat, ...) {
getavg('REDO', dat[getruns(dat, 'REDO', ...),])
}
readavg <- function(dat, ...) {
getavg('PREADS', dat[getruns(dat, 'READS', ...),])
}
writeavg <- function(dat, ...) {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),])
}
readwriteavg <- function(dat, ...) {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),]) + getavg('PREADS', dat[getruns(dat, 'WRITES', ...),])
}
avg <- function(dat, model, ...) {
if(model=='REDO') {
getavg('REDO', dat[getruns(dat, 'REDO', ...),])
} else if(model=='READS') {
getavg('PREADS', dat[getruns(dat, 'READS', ...),])
} else if (model=='WRITES') {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),])
} else if (model=='READWRITE') {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),]) + getavg('PREADS', dat[getruns(dat, 'WRITES', ...),])
}
}
waits <- function(dat, ...) {
as.character(dat[getruns(dat, ...), 'TOP.WAIT'])
}
testdata <- function(dat, model, str, ...) {
if(model=='REDO') {
sapply(str, avg, dat=dat, model='REDO', ...)
} else if(model=='READS') {
sapply(str, avg, dat=dat, model='READS', ...)
} else if(model=='WRITES') {
sapply(str, avg, dat=dat, model='WRITES', ...)
} else if (model=='READWRITE') {
sapply(str, avg, dat=dat, model='READWRITE', ...)
}
}
readdata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='READS', ...)
}
redodata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='REDO', ...)
}
readwritedata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='READWRITE', ...)
}
SLOBreads <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, readdata(dat, strs, i))
}
z <- rbind(z, readdata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
SLOBredo <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, redodata(dat, strs, i))
}
z <- rbind(z, redodata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
SLOBreadwrite <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, readwritedata(dat, strs, i))
}
z <- rbind(z, readwritedata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
That’s it for now. I welcome comments and questions.
Pardy DBA 