This script will help determine IOPS being delivered from ExaData
#!/bin/ksh
#
# cell_iops.sh - a "sort of" end to end Exadata IO monitoring script
# * inspired by http://glennfawcett.wordpress.com/2013/06/18/analyzing-io-at-the-exadata-cell-level-a-simple-tool-for-iops/
# and modified to show end to end breakdown of IOPS, inter-database, consumer groups, and latency across Exadata storage cells
# * you must use this script together with "iostat -xmd" on storage cells on both flash and spinning disk and database IO latency on
# system level (AWR) and session level (Tanel Poder's snapper) for a "real" end to end IO troubleshooting and monitoring
# * the inter-database and consumer groups data is very useful for overall resource management and IORM configuration and troubleshooting
# * check out the sample viz that can be done by mining the data here goo.gl/0Q1Oeo
#
# Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE, OakTable
# http://karlarao.wordpress.com
#
# on any Exadata storage cell node you can run this one time
# ./cell_iops.sh
#
# OR on loop spooling to a file and consume later with Tableau for visualization
# while :; do ./cell_iops.sh >> cell_iops.csv ; egrep "CS,ALL|DB,_OTHER_DATABASE_" cell_iops.csv ; sleep 60; echo "--"; done
#
# Here are the 19 column headers:
#
# TM - the time on each snap
# CATEGORY - CS (cell server - includes IOPS, MBs, R+W breakdown, latency), DB (database - IOPS, MBs), CG (consumer group - IOPS, MBs)
# GROUP - grouping per CATEGORY, it could be databases or consumer groups.. a pretty useful dimension in Tableau to drill down on IO
# DISK_IOPS - (applies to CS, DB, CG) high level spinning disk IOPS
# FLASH_IOPS - (applies to CS, DB, CG) high level flash disk IOPS
# DISK_MBS - (applies to CS, DB, CG) high level spinning disk MB/s (bandwidth)
# FLASH_MBS - (applies to CS, DB, CG) high level flash disk MB/s (bandwidth)
# DISK_IOPS_R - (applies to CS only) IOPS breakdown, spinning disk IOPS read
# FLASH_IOPS_R - (applies to CS only) IOPS breakdown, flash disk IOPS read
# DISK_IOPS_W - (applies to CS only) IOPS breakdown, spinning disk IOPS write
# FLASH_IOPS_W - (applies to CS only) IOPS breakdown, flash disk IOPS write
# DLAT_RLG - (applies to CS only) average latency breakdown, spinning disk large reads
# FLAT_RLG - (applies to CS only) average latency breakdown, flash disk large reads
# DLAT_RSM - (applies to CS only) average latency breakdown, spinning disk small reads
# FLAT_RSM - (applies to CS only) average latency breakdown, flash disk small reads
# DLAT_WLG - (applies to CS only) average latency breakdown, spinning disk large writes
# FLAT_WLG - (applies to CS only) average latency breakdown, flash disk large writes
# DLAT_WSM - (applies to CS only) average latency breakdown, spinning disk small writes
# FLAT_WSM - (applies to CS only) average latency breakdown, flash disk small writes
#
datafile=`echo /tmp/metriccurrentall.txt`
/usr/local/bin/dcli -l root -g /root/cell_group "cellcli -e list metriccurrent" > $datafile
export TM=$(date +%m/%d/%y" "%H:%M:%S)
# Header
print "TM,CATEGORY,GROUP,DISK_IOPS,FLASH_IOPS,DISK_MBS,FLASH_MBS,DISK_IOPS_R,FLASH_IOPS_R,DISK_IOPS_W,FLASH_IOPS_W,DLAT_RLG,FLAT_RLG,DLAT_RSM,FLAT_RSM,DLAT_WLG,FLAT_WLG,DLAT_WSM,FLAT_WSM"
#######################################
# extract IOPS for cells
#######################################
export DRW=`cat $datafile | egrep 'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRW=`cat $datafile | egrep 'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export DRWM=`cat $datafile | egrep 'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRWM=`cat $datafile | egrep 'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export DR=`cat $datafile | egrep 'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FR=`cat $datafile | egrep 'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export DW=`cat $datafile | egrep 'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FW=`cat $datafile | egrep 'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export DLATRLG=`cat $datafile | egrep 'CD_IO_TM_R_LG_RQ' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRLG=`cat $datafile | egrep 'CD_IO_TM_R_LG_RQ' |grep FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export DLATRSM=`cat $datafile | egrep 'CD_IO_TM_R_SM_RQ' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRSM=`cat $datafile | egrep 'CD_IO_TM_R_SM_RQ' |grep FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export DLATWLG=`cat $datafile | egrep 'CD_IO_TM_W_LG_RQ' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWLG=`cat $datafile | egrep 'CD_IO_TM_W_LG_RQ' |grep FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export DLATWSM=`cat $datafile | egrep 'CD_IO_TM_W_SM_RQ' |grep -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWSM=`cat $datafile | egrep 'CD_IO_TM_W_SM_RQ' |grep FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
print "$TM,CS,ALL,$DRW,$FRW,$DRWM,$FRWM,$DR,$FR,$DW,$FW,$DLATRLG,$FLATRLG,$DLATRSM,$FLATRSM,$DLATWLG,$FLATWLG,$DLATWSM,$FLATWSM"
#######################################
# extract IOPS for database
#######################################
export db_str=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
for db_name in `echo $db_str`
do
# Calculate Total IOPS of harddisk
# DB_IO_RQ_LG_SEC
# DB_IO_RQ_SM_SEC
db_drw=`cat $datafile | egrep 'DB_IO_RQ_LG_SEC|DB_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total IOPS of flashdisk
# DB_FD_IO_RQ_LG_SEC
# DB_FD_IO_RQ_SM_SEC
db_frw=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC|DB_FD_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total MB/s of harddisk
# DB_IO_BY_SEC
db_drwm=`cat $datafile | egrep 'DB_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total MB/s of flashdisk
# DB_FC_IO_BY_SEC
# DB_FD_IO_BY_SEC
# DB_FL_IO_BY_SEC
db_frwm=`cat $datafile | egrep 'DB_FC_IO_BY_SEC|DB_FD_IO_BY_SEC|DB_FL_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
print "$TM,DB,$db_name,$db_drw,$db_frw,$db_drwm,$db_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
done
#######################################
# extract IOPS for DBRM consumer groups
#######################################
export cg_str=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
for cg_name in `echo $cg_str`
do
# Calculate Total IOPS of harddisk
# CG_IO_RQ_LG_SEC
# CG_IO_RQ_SM_SEC
cg_drw=`cat $datafile | egrep 'CG_IO_RQ_LG_SEC|CG_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total IOPS of flashdisk
# CG_FD_IO_RQ_LG_SEC
# CG_FD_IO_RQ_SM_SEC
cg_frw=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC|CG_FD_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total MB/s of harddisk
# CG_IO_BY_SEC
cg_drwm=`cat $datafile | egrep 'CG_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
# Calculate Total MB/s of flashdisk
# CG_FC_IO_BY_SEC
# CG_FD_IO_BY_SEC
cg_frwm=`cat $datafile | egrep 'CG_FC_IO_BY_SEC|CG_FD_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
print "$TM,CG,$cg_name,$cg_drw,$cg_frw,$cg_drwm,$cg_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
done