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;  
}

[download]

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

In reply to Format iostat output into excel graphs to analyse disk io bottlenecks by abhishes

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.