Format iostat output into excel graphs to analyse disk io bottlenecks

[abhishes]

Hello All

This is the first time I have tried to write a utiltity which I believe can be used by others in the perlmonks community.

Recently I was given the task to study the bottleneck in an application which does file IO very extensively. I was also asked to create a report in presentable format. The system was running on the unix platform.

I gathered the disk usage data by using the command IOSTAT. I ran the IOSTAT for a long time (iostat 5 2500 > iostat.txt). and ran all my test cases. Now I want to study that how was the disk used during the time test cases were being run.

To analyze the output of iostat correctly. I wrote a utiltiy which will format the output from the utiltity (captured in a file called iostat.txt) and create a Excel sheet out of it. It will create a graph for each disk in the system. So that I can know how many byte reads and seeks were happening on the disk against time.

The output of the iostat utiltity looks like:

  device    bps     sps    msps 

 c1t15d0      0     0.0     1.0 
 c3t15d0      0     0.0     1.0 

 c1t15d0      2     1.2     1.0 
 c3t15d0     10     1.2     1.0 

 c1t15d0      0     0.2     1.0 
 c3t15d0      0     0.2     1.0 

 c1t15d0      0     0.2     1.0 
 c3t15d0      0     0.0     1.0 

 c1t15d0      1     0.2     1.0 
 c3t15d0      3     0.4     1.0 

 c1t15d0      0     0.2     1.0 
 c3t15d0      0     0.2     1.0 

 c1t15d0      1     0.6     1.0 
 c3t15d0      0     0.0     1.0 

The text contains all the data but its hard to conclude anything by looking at these numbers. It will be better if we could plot these numbers in a graph to see how the disk is being used.

There is the code of the utiltity please review it for me and let me know if I can improve it. Also let me know if I can make it more useful.

use warnings;
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Cwd;

my @diskNames = GetDiskNames();
# create a new excel object
my $file = "iostat.xls";
unlink $file if (-e $file);
my $excel = new Win32::OLE("Excel.Application", \&QuitExcel) or 
die "could not create Microsoft Excel Application $! \n";
if ($excel)
{
  $excel->{Visible} = 1;
  $excel->{SheetsInNewWorkBook} = 2;
  my $workbook = $excel->WorkBooks->Add() 
    or die "could not create workbook in the excel sheet \n";  
  # create two worksheets in it one for sps data one for bps data;

  # get the bps data for each sheet
  
  my $bpsWorkSheet = $workbook->Worksheets(1);
  $workbook->Worksheets($bpsWorkSheet);
  $bpsWorkSheet->{Name} = "BPS";  
  $bpsWorkSheet->Range("A1")->{Value} = "Bytes Read Per Second";
  
  my $col = "A";  
  my $row;
  foreach(@diskNames)
  {
    $row = 2;
    $bpsWorkSheet->Range("$col$row")->{Value} = $_;
    # create a new column in the excel work sheet for this disk and du
+mp the data in that column
    my @sps = GetColumnForDisk($_, 2);  
    foreach(@sps)
    {
      $bpsWorkSheet->Range($col.++$row)->{Value} = $_;
    }
    $col++;
  }  
  PlotGraph($bpsWorkSheet, $col, $row, "Bytes Per Second Versus Time",
+ "Time", "Bytes Per Second");
  
  my $spsWorkSheet = $workbook->Worksheets(2);
  $workbook->Worksheets($spsWorkSheet);
  $spsWorkSheet->{Name} = "SPS";  
  $spsWorkSheet->Range("A1")->{Value} = "Seeks Per Second";
  
  $col = "A";    
  foreach(@diskNames)
  {
    $row = 2;
    $spsWorkSheet->Range("$col$row")->{Value} = $_;
    # create a new column in the excel work sheet for this disk and du
+mp the data in that column
    my @sps = GetColumnForDisk($_, 3);  
    foreach(@sps)
    {
      $spsWorkSheet->Range($col.++$row)->{Value} = $_;
    }
    $col++;
  }  
  PlotGraph($spsWorkSheet, $col, $row, "Seeks Per Second Versus Time",
+ "Time", "Seeks Per Second");
  
  #save close the excel object
  $workbook->SaveAs(cwd() . "/iostat.xls");  
}

sub GetColumnForDisk
{  
  my $diskName = shift;
  my $columnNumber = shift;
  my @col;  
  open FILE, "iostat.txt";
  my @lines = <FILE>;  
  foreach my $line (@lines) 
  {
    if (defined($line) && length($line) > 0)
    {
      $line =~ m/\s*(\S+)\s*(\S+)\s*(\S+)\s*(\S+)\s*/;
      if (defined($1) && defined($2) && defined($3))
      {           
        if ($diskName eq $1)
        { 
          if ($columnNumber == 2)
          {
            push @col, $2 if (defined($2));          
          }
          elsif ($columnNumber == 3)
          {
            push @col, $3 if (defined($3));          
          }
        }
      }
    }
  }
  
  close FILE;  
  return @col;
}


sub GetDiskNames
{
  open FILE, "iostat.txt";
  my @lines = <FILE>;
  my @diskNames;
  foreach my $line (@lines) 
  {    
    $line =~ m/\s*(\S+)\s*(\S+)\s*(\S+)\s*(\S+)\s*/;    
    if (defined($1))
    {
      my $diskName = $1;
      if ($diskName =~ m/device/)
      {
        next;
      }
      my $diskExists = CheckElementExists(\@diskNames, $diskName);    
      if ($diskExists == 0)
      {
        push @diskNames, $diskName;
      }
    }
  }
  close FILE;
  return @diskNames;
}

sub CheckElementExists
{
  my ($diskList, $diskName) = @_;    
  my $retVal = 0;  
  foreach(@$diskList)
  {
    if ($diskName eq $_)
    {
      $retVal = 1;
    }
  }
  return $retVal;
}

sub QuitExcel
{
  my $comobject = $_[0];
  my $name = $comobject->{Name};
  print "Quitting $name \n";
  $comobject->Quit();
}

sub PlotGraph
{ 
  my $worksheet = shift;
  my $col = shift;
  my $row = shift;
  my $chartTitle = shift;
  my $xAxisTitle = shift;
  my $yAxisTitle = shift;
  my $MRange = $worksheet->Range("A3:$col$row");
  # insert chart in the bps sheet 
  my $chart = $excel->Charts->Add;
  $chart->{Name} = $worksheet->{Name} . " Chart";
  $chart->{ChartType} = xlLine;
  $chart->SetSourceData({Source=>$MRange, PlotBy=>xlColumns});
  $chart->{HasTitle}=1;
  $chart->ChartTitle->{Text} = $chartTitle;
  $chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
  $chart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = $xAxisTitle
+;
  $chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
  $chart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = $yAxisTitle;  
}

So now you run your application and don't forget to capture the data from the iostat utiltity into a file for the entire duration for which the test case is running. When the test has concluded and its time for data analysis run this utility and it will give you a graph of eask disk usage on the system. If you see very high Byte read rate or Seek Rate, then you will know that your application is bottle necking at file IO.

Hope everyone finds this utility useful.

regards, Abhishek.

janitored by ybiC: balanced <readmore> tags

[tachyon]

This:

$line =~ m/\s*(\S+)\s*(\S+)\s*(\S+)\s*(\S+)\s*/;

is a really bad way to split a line on whitespace. It will do all sorts of things you don't want in edge case situations. See this Re: ('Re: ' x 6) How to remove the $1 hard coding and its parent thread.

my ($device, $bps, $sps, $msps ) = split ' ', $line;

Is far more robust, self documenting, and also faster way to split a file like the one you have into fields.

Makes no difference to the functionality but:

sub PlotGraph
{ 
  my $worksheet = shift;
  my $col = shift;
  my $row = shift;
  my $chartTitle = shift;
  my $xAxisTitle = shift;
  my $yAxisTitle = shift;

# reads better to my eye and makes it easier to see the function(args)
+ 
# (also a bit faster FWIW)

sub PlotGraph
{ 
  my ( $worksheet, $col, $row, $chartTitle, $xAxisTitle, $yAxisTitle )
+ = @_;

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[Jamison]

Is there a way I could use this to read a simple tab delimited text file and convert it into excel that could be read by the browser? Jamison

[Anonymous Monk]

Using the following line: print "Content-type: application/vnd.ms-excel", "\n\n"; will force anything viewed in IE to open via Excel. It can be used like so:

 

#!/usr/bin/perl

open TABBED_FILE,"your_tabbed_file.txt";# or die "cant open tabbed fil
+e $!";
@data = <TABBED_FILE>;
close TABBED_FILE;

 print "Content-type: application/vnd.ms-excel", "\n\n";
 print "Col A\tCol B\Col C\n";

foreach $line(@data)
{ my ($thingA,$thingB,$thingC)=split(/\t/,$line);
  print "$thingA\t$thingB\t$thingC\n";
}

======================== JIC

[Anonymous Monk]

Thank you !