Feeds:
Posts
Comments

Posts Tagged ‘Charting’

I have updated the PowerShell script, GraphSQLIO.ps1, to include the histogram data that appears in the output file. As you know, the data is something like this:

histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

Now when you run the script, it will save this data too and the charts will show it like this:

FormHist

The updated files are here (save & rename to .zip and extract):GraphSQLIO

Note: The script defaults to using MSChart control assembly that is delivered with .NET 4.0. In other words, it assumes that you are using PowerShell 3.0 and above. If you are using PowerShell 2.0, then make sure that you have:

  • Installed MSChart control from MSDN
  • Commented the line in the script (see below) that uses .NET 4.0 and un-commented that line that use .NET 3.5 lib for MSChart control

Some folks had trouble downloading the file so I have added the PowerShell script here in it’s entirety:

param
(
[string]$OptionsFile= “TestSQLIO.cmd”,
[string]$ResultsFile= “SQLIOResults.xml”,
[switch]$OnlyCharts
)

Add-Type -AssemblyName System.Windows.Forms
#Use this on .NEt 4.0 otherwise comment the line below
Add-Type -AssemblyName System.Windows.Forms.DataVisualization

#Use this on .NEt 3.5
#Add-Type -AssemblyName (‘System.Windows.Forms.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’)

#Script level variable to store Run entries
$Script:entries = @()

function ParseRunEntries
{
$folder = Split-Path (Resolve-Path $OptionsFile) -Parent
$lines = (Get-Content $OptionsFile) -split [Environment]::NewLine | Where {$_.Trim().Length -gt 0 -and $_.StartsWith(“sqlio”)} | foreach {$_.Trim()}

try
{
foreach($line in $lines)
{
$options = $line -split “-” | foreach {$_.Trim()}
$filename = [String]::Empty
if($options.Length -eq 9)
{
$sqlObj = New-Object psobject
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Options” -Value @()
foreach($option in $options[1..$options.Length])
{
$sqlObj.Options += “‘-$option'” #Create parameter array of string type
$value = $option.Substring(1).Trim()
if($option -match “^k(w|W|R|r)”){
$filename += $value.ToUpper()
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Operation” -Value $value
}
elseif($option -match “^s\d+” ) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Duration” -Value ([int]$value)
}
elseif($option -match “^f(random|sequential)”) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Mode” -Value $value
}
elseif($option -match “^o\d+”) {
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Outstanding” -Value ([int]$value)
}
elseif($option -match “^b\d+”) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Size” -Value ([int]$value)
}
elseif($option -match “-F.*\.txt”)
{
Add-Member -InputObject $sqlObj -m NoteProperty -Name “ParamFile” -Value $value
}
}
$filename += “.txt”
Add-Member -InputObject $sqlObj -m NoteProperty -Name “OutputFile” -Value $filename
Add-Member -InputObject $sqlObj -m NoteProperty -Name “CaptureLatency” -Value “‘-LS'”
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Buffering” -Value “‘-BN'”
$Script:entries += $sqlObj
$sqlObj = $null
}
}
}
catch{
write “Error executing script: $Error”
}
}

function RunEntries
{
$validRuns = @()
if($Matches) { $Matches.Clear()}
foreach($obj in $Script:entries)
{
$sqlioPath = Resolve-Path sqlio.exe
$results = Invoke-Expression -Command “$sqlioPath $($obj.Options)” | Out-String
$results | Set-Content $obj.OutputFile

#Add the results to each run object
Add-Member -InputObject $obj -m NoteProperty -Name “Results” -Value $results

if($results.Contains(“histogram:”))
{
$lines = $obj.Results -split [Environment]::NewLine | Where {$_.Trim().Length -gt 0} | foreach {$_.Trim()}
foreach($line in $lines)
{
$outputValue = ParseOutputValue $line
if($outputValue -is [double])
{
if($line -match “IOs/sec.*”)
{
Add-Member -InputObject $obj -MemberType NoteProperty -Name “IOsPerSec” -Value $outputValue
}
elseif($line -match “MBs.*”)
{
Add-Member -InputObject $obj -MemberType NoteProperty -Name “MBsPerSec” -Value $outputValue
}
elseif($line -match “Avg_Latency.*”)
{
Add-Member -InputObject $obj -MemberType NoteProperty -Name “AvgLatencyMs” -Value $outputValue
}
elseif($line -match “using (specified|current) size:”)
{
Add-Member -InputObject $obj -MemberType NoteProperty -Name “TestFilesize” -Value $outputValue
}
}
elseif($line -match “(?\d+)\s+thread[s]?\s+(reading|writing).*file\s+(?[a-zA-Z]?[:\\]?.*)”)
{
Add-Member -InputObject $obj -MemberType NoteProperty -Name “Threads” -Value ([int]$Matches.th)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “TestFile” -Value $Matches.TestFile.Trim()
}
elseif($line -match “^ms:\s+\d+”)
{
#ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
$lineWithValues = $line -split {$_ -eq “m” -or $_ -eq “s” -or $_ -eq “:” -or $_ -eq “+”} | where {$_.Trim().Length -gt 0} | foreach {$_.Trim()}
$values = $lineWithValues -split “\s+” | foreach {[int]$_}
Add-Member -InputObject $obj -MemberType NoteProperty -Name “LatencyValues” -Value $values
}
elseif($line -match “^%:\s+\d+”)
{
#%: 0 0 0 0 3 4 5 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85
$lineLatency = $line -split {$_ -eq “%” -or $_ -eq “:”} | where{$_.Trim().Length -gt 0} | foreach{$_.Trim()}
$percentValues = $lineLatency -split “\s+” | foreach {[int]$_}
Add-Member -InputObject $obj -MemberType NoteProperty -Name “LatencyPercent” -Value $percentValues
}
}
$validRuns += $obj
}
}
$validRuns | Export-Clixml -Path $ResultsFile
}

function ParseOutputValue($linevalue)
{
$value = [String]::Empty
if($linevalue -match “([^(ms:|%:\d+)].*:\s+)(?\d+)”)
{
$value = [double] $Matches.no
}
$value
}

function GetChartArea($chart, $curTicks)
{
$chartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
$chartArea.AxisY.MajorGrid.LineColor = “Blue”
#$chartArea.Area3DStyle = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea3DStyle `
# -Property @{Enable3D=$true; IsRightAngleAxes=$false; Rotation=20; Inclination=20; PointDepth=100; PointGapDepth=200}

$chartAreaName = “CA$curTicks”
$chartArea.Name = $chartAreaName
$chartArea.BackSecondaryColor = [System.Drawing.Color]::LightSteelBlue
$chartArea.BackGradientStyle = [System.Windows.Forms.DataVisualization.Charting.GradientStyle]::DiagonalRight
$chartArea.AxisX.Title = “Block Size”
$chartArea.Area3DStyle.Enable3D = $false
$chart.ChartAreas.Add($chartArea) | Out-Null
$chartArea
}

function SetSeries($chart, $chartArea, $seriesName, $chartType=”Column”, $seriesType=”Cylinder”)
{
$chart.Series.Add($seriesName) | Out-Null
$chart.Series[$seriesName].ChartArea =$chartArea.Name
$chart.Series[$seriesName].BorderWidth = 2
$chart.Series[$seriesName].ChartType = $chartType
$chart.Series[$seriesName].LabelForeColor = [System.Drawing.Color]::DarkGreen
$chart.Series[$seriesName].LabelBackColor = [System.Drawing.Color]::LightGreen
$chart.Series[$seriesName][“DrawingStyle”] = $seriesType
$chart.Series[$seriesName][“PointWidth”] = “0.5”
}

function GenerateCharts
{
if(-not (Test-Path $ResultsFile))
{
throw “Invalid file specified”
}

$Script:entries = Import-Clixml $ResultsFile
$groupEntries = $Script:entries | Group-Object -Property Operation, Mode -AsHashTable -AsString

$form = New-Object System.Windows.Forms.Form
$form.Text = “SQLIO CHARTS”
$form.Width = 700
$form.Height = 700

$tabHost = New-Object System.Windows.Forms.TabControl
$tabHost.Dock = “Fill”
$form.Controls.Add($tabHost)
$runThreads = ($Script:entries | select -First 1).Threads
$runOutstanding = ($Script:entries | select -First 1).Outstanding
$runDuration = ($Script:entries | select -First 1).Duration
$runFileSize = ($Script:entries | select -First 1).TestFilesize
$runFilePath = ($Script:entries | select -First 1).TestFile
$form.Text = “SQLIO-$runDuration sec run with $runThreads threads, $runOutstanding pending IOs & $runFileSize(MB) $runFilePath file”

foreach($grpKey in $groupEntries.Keys)
{
#Create tab page to host group Chart
$chartName = ($grpKey -replace “,”, “-“) -replace ” ” , “”
$tabPage = New-Object System.Windows.Forms.TabPage -ArgumentList “Run-$($chartName)”

#create chart host
$chart = New-Object System.Windows.Forms.DataVisualization.Charting.Chart
$chart.BackColor = [System.Drawing.Color]::Transparent
$chart.Dock = “fill”

if($grpKey -match “random”)
{
$chart.Titles.Add(“Note: IOPS & Avg.Latency”) | Out-Null
}
else
{
$chart.Titles.Add(“Note: MBs/sec & Avg.Latency”) | Out-Null
}

$chart.Titles[0].Font = New-Object System.Drawing.Font(“Arial”,11, [System.Drawing.FontStyle]::Bold)

#Create chart areas
$grpEntry = $groupEntries[$grpKey]
AddChartAreas $chart $grpEntry

#Add chart to tab page
$tabPage.Controls.Add($chart)

#Add tab page to tab control
$tabHost.TabPages.Add($tabPage)
}

$form.Add_Shown({$form.Activate()})
$form.ShowDialog() | Out-Null
}

function AddChartAreas($chart, $currentGroup)
{
for($i=0; $i -lt 4; $i++)
{
$curTicks = (Get-Date).Ticks
#Get a chartarea and add it to chart
$chartArea = GetChartArea $chart $curTicks

if($i -lt 3)
{
#Get and set a series
$seriesName = “SE-$curTicks”

SetSeries $chart $chartArea $seriesName
$chart.Series[$seriesName].IsValueShownAsLabel = $true
$chart.Series[$seriesName].IsXValueIndexed = $true

#Add series data
if($i -eq 0)
{
$chartarea.AxisY.Title = “Avg. Latency(ms)”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.AvgLatencyMs)} | Out-Null
}
elseif($i -eq 1)
{
$chartarea.AxisY.Title = “MBs/sec”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.MBsPerSec)} | Out-Null
}
elseif($i -eq 2)
{
$chartarea.AxisY.Title = “IOPS”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.IOsPerSec)}| Out-Null
}
}
else
{
$chartArea.AxisY.Title = “Percentage”
$chartArea.AxisX.Title = “Latency (ms)”
$chartArea.AxisY.IsMarginVisible = $true
#Add series data
foreach($grpEntry in $currentGroup)
{
$seriesName = “SE-” + (Get-Date).Ticks
SetSeries $chart $chartArea $seriesName -seriesType “Default”

for($i=0; $i -lt $grpEntry.LatencyPercent.Count; $i++)
{
$chart.Series[$seriesName].Points.AddY($grpEntry.LatencyPercent[$i]) | Out-Null
}
}
}
}
}

Clear-Host
if($OnlyCharts)
{
GenerateCharts
}
else
{
ParseRunEntries
RunEntries
GenerateCharts
}
#$Script:entries

 

Advertisements

Read Full Post »

If you haven’t used SQLIO, it’s a tool available from Microsoft to determine the I/O capacity of a given hardware configuration before deploying SQL Server in production. I won’t go into details of using SQLIO since there are bunch of articles on the net on how to go about using/configuring it.

I’ve published the script & supporting files on GitHub:

https://github.com/sunitjoshi/GraphSQLIO

What I want to show you here is how to automate SQLIO runs and parse the subsequent output to easily create charts that can help in analyzing the output data.

Something like this:

Form2

The “Note” is to basically reminds me that, I need to look at IOPS & Latency for random IOs and MBs/sec & Latency for sequential IOs.

My TestSQLIO.cmd file has this:

sqlio -kR -s300 -frandom -o8 -b8 -LS -BN -Fparam.txt

sqlio -kR -s300 -frandom -o8 -b64 -LS -BN -Fparam.txt

sqlio -kR -s300 -frandom -o8 -b512 -LS -BN -Fparam.txt

sqlio -kW -s300 -frandom -o8 -b8 -LS -BN -Fparam.txt

sqlio -kW -s300 -frandom -o8 -b64 -LS -BN -Fparam.txt

sqlio -kW -s300 -fsequential -o8 -b8 -LS -BN -Fparam.txt

sqlio -kW -s300 -fsequential -o8 -b64 -LS -BN -Fparam.txt

And my param.txt has this:

C:\sqltest.dat 4 0x0 32768

This runs the test using 8, 64, 512 block sizes and 8 pending IOs. These are values that I’m using and you can surely expand this for your needs.

You can see that I don’t specify the output file in the runs above since my PowerShell script concocts one for me. The PowerShell script runs the TestSQLIO.cmd (default input file) and outputs data to SQLIOResults.xml (default file).

I choose to create a PSObject in code that relates to an individual run with properties like Operation, Mode, Duration, Size, etc. so that I can serialize the object collection using Export-Clixml CmdLet. The reason I did this is, I didn’t want to worry about all the other artifacts, like the TestSQLIO.cmd and param.txt later, to query information on threads used, duration the test was run for, size of the testfile, etc. If I dump everything in an xm file, then that’s all I need later. Again this is just my preference and may or may not work for you.

With that said, this how the resultant xml looks like:

 <Obj RefId="0">
    <TN RefId="0">
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <Obj N="Options" RefId="1">
        <TN RefId="1">
          <T>System.Object[]</T>
          <T>System.Array</T>
          <T>System.Object</T>
        </TN>
        <LST>
          <S>'-kR'</S>
          <S>'-s10'</S>
          <S>'-frandom'</S>
          <S>'-o8'</S>
          <S>'-b8'</S>
          <S>'-LS'</S>
          <S>'-BN'</S>
          <S>'-Fparam.txt'</S>
        </LST>
      </Obj>
      <S N="Operation">R</S>
      <I32 N="Duration">10</I32>
      <S N="Mode">random</S>
      <I32 N="Outstanding">8</I32>
      <I32 N="Size">8</I32>
      <S N="OutputFile">Rs10frandomb8.txt</S>
      <S N="CaptureLatency">'-LS'</S>
      <S N="Buffering">'-BN'</S>
      <Obj N="Results" RefId="2">
        <TNRef RefId="1" />
        <LST>
          <S>sqlio v1.5.SG</S>
          <S>using system counter for latency timings, 2530840 counts per second</S>
          <S>parameter file used: param.txt</S>
          <S>_x0009_file C:\sqltest.dat with 4 threads (0-3) using mask 0x0 (0)</S>
          <S>4 threads reading for 10 secs from file C:\sqltest.dat</S>
          <S>_x0009_using 8KB random IOs</S>
          <S>_x0009_enabling multiple I/Os per thread with 8 outstanding</S>
          <S>_x0009_buffering set to not use file nor disk caches (as is SQL Server)</S>
          <S>using specified size: 1024 MB for file: C:\sqltest.dat</S>
          <S>initialization done</S>
          <S>CUMULATIVE DATA:</S>
          <S>throughput metrics:</S>
          <S>IOs/sec:   235.04</S>
          <S>MBs/sec:     1.83</S>
          <S>latency metrics:</S>
          <S>Min_Latency(ms): 1</S>
          <S>Avg_Latency(ms): 134</S>
          <S>Max_Latency(ms): 972</S>
          <S>histogram:</S>
          <S>ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+</S>
          <S>%:  0  0  0  0  0  0  0  3  1  0  0  1  2  0  1  1  0  1  1  1  1  1  1  0 85</S>
        </LST>
      </Obj>
      <I32 N="Threads">4</I32>
      <Db N="TestFilesize">1024</Db>
      <Db N="IOsPerSec">235.04</Db>
      <Db N="MBsPerSec">1.83</Db>
      <Db N="AvgLatencyMs">134</Db>
    </MS>
  </Obj>

Also once I have just this file, I can quickly “graph it” using the –OnlyCharts option and create tab based charts as shown above.

I used a a tabbed interface since I group my test runs based on Operation (R/W) and mode (sequential/random) and create a Hashtable:

Group-Object -Property Operation, Mode -AsHashTable –AsString

The Hashtable keys serve as individual tabpage that are added to the main tab hosted in the form.

To create the charts, I do this:

  • Create a form host with a tab-control that “fills” the form area  (GenerateCharts function)
  • Group the entries by Operation and Mode (GenerateCharts function)
  • Cycle through group keys by:
    • First creating a Chart object (GenerateCharts function)
    • Then creating a Chart Area and it’s Series for the current key’s values (AddChartAreas function)

That should be it!

 

Read Full Post »