Monday, September 25, 2006

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...

2 comments:

Unknown said...

Thanks, very useful. I added support for long device names -- statistics are shown below in that case.

if [ "$device" != "" ]
then

# sometimes the device name takes 1 whole line and its info is below.
if [ "$rrqms" = "" ]
then
read rrqms wrqms rps wps rsps wsps rkbs wkbs rqsz qusz await svctm util
fi

Nigel said...

Thanks Stuart - always good to see stuff being used and improved!

Regards Nigel