Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Format iostat output into excel graphs to analyse disk io bottlenecks

by abhishes (Friar)
on Aug 30, 2003 at 16:28 UTC ( #287924=CUFP: print w/ replies, xml ) Need Help??

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

Comment on Format iostat output into excel graphs to analyse disk io bottlenecks
Download Code
Re: Format iostat output into excel graphs to analyse disk io bottlenecks
by tachyon (Chancellor) on Aug 31, 2003 at 03:46 UTC

    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

Re: Format iostat output into excel graphs to analyse disk io bottlenecks
by Jamison (Initiate) on Sep 03, 2003 at 00:48 UTC
    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
      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
Re: Format iostat output into excel graphs to analyse disk io bottlenecks
by Anonymous Monk on Nov 30, 2011 at 15:23 UTC
    Thank you !

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://287924]
Approved by phydeauxarff
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2014-09-20 01:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (151 votes), past polls