Loading iostat output into Oracle

Alex Gorbachev posted an item on Pythian's blog last week about Basic I/O monitoring on Linux.

That's reminded me to post a couple of scripts here that can be used to load cpu and disk stats from iostat into Oracle tables.

I assume your iostat command line looks something like this:

iostat -x -t [interval] > iostat.log

You can then process iostat.log using this script to make it more SQL*Loader friendly:

cat iostat.log | processiostat > iostat.dat

And here's the processiostat script:

#!/bin/sh
#
# read and process an iostat.out file
# assumption is that it has been collected using -x -t
#
# Format looks like this (excluding leading comment sign
#=======================
#Linux 2.4.21-27.ELsmp (myserver.preferisco.com)        02/05/06
#
#Time: 13:01:58
#avg-cpu:  %user   %nice    %sys %iowait   %idle
#           0.96    0.06    0.49    1.93   96.55
#
#Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
#sda         80.85   0.00 22.62 29.90  388.10  522.72   194.05   261.36    17.34     0.00    0.08   0.02   0.11
#sde         44.04   0.00 12.19  1.78  203.58   14.28   101.79     7.14    15.59     0.01    0.61   0.33   0.46
#sdi         44.41   0.00 11.68  1.87  195.50   15.71    97.75     7.86    15.59     0.01    0.79   0.45   0.61
#
#Time: ...
#=======================
#
# Read the file from stdin
read os version longhost date
# extract hostname - works for rac2-4 at least
# discard empty line
read discard
while read label time
do
    # discard cpu headings
   read discard
   # read cpu figures
   read usercpu nicecpu syscpu iowait idle
   echo CPU ${date}-$time $longhost $usercpu $nicecpu $syscpu $iowait $idle
   # discard blank line
   read discard
   # discard headings
   read discard
   # now read in device stats
   device="START"
   until [ "$device" = "" ]
   do
      read device rrqms wrqms rps wps rsps wsps rkbs wkbs rqsz qusz await svctm util
      if [ "$device" != "" ] 
      then
         echo DISK ${date}-$time $longhost $device $rrqms $wrqms $rps $wps $rsps $wsps $rkbs $wkbs $rqsz $qusz $await $svctm $util
      fi
   done
done

That .dat output can then be loaded using this control file:

-- general ctl format for iostat tables
load data
infile *
into table IOSTAT_CPU REPLACE
WHEN (1:3)="CPU"
fields terminated by whitespace
( TSTAMP position (5) DATE "mm/dd/yy-hh24:mi:ss" 
, HOST
, USERCPU
, NICECPU
, SYSCPU
, IOWAIT
, IDLE
)
into table IOSTAT_DISK REPLACE
WHEN (1:4)="DISK"
fields terminated by whitespace
( TSTAMP position(6) DATE "mm/dd/yy-hh24:mi:ss" 
, HOST
, DEVICE
, RRQMS
, WRQMS
, RPS
, WPS
, RSPS
, WSPS
, RKBS
, WKBS
, RQSZ
, QUSZ
, AWAIT
, SVCTM
, UTIL
)

Then you can do comparisons, trend analysis, even draw pretty graphs using your favourite GUI (SQL*Plus in my case...)

Enjoy...