#!/usr/bin/bash
# Print Innodb logical IO statistics. For more description use innodb_iostat -h
# = Usage
# ruby innodb_iostat.rb [-h] [-d] [-p MYSQL_PID] interval
#         -h : Help
#         -p : Process ID of mysql (optional)
#         -d : dump the dtrace script being used
# = Output
#   * r/s     - Physical (Actual) writes per sec
#   * w/s     - Physical (Actual) writes per sec
#   * r/s     - Reads issued by Innodb per sec
#   * w/s     - Writes issued by Innodb per sec
#   * data    - Regular Reads issued by Innodb to the datafiles per sec
#   * pre     - Prefetch Reads issued by Innodb to the datafiles per sec
#   * log     - Log writes issued by Innodb per sec
#   * dblbuf  - Double buffer writes per sec
#   * dflush  - Writes due to flushing of Innodb buffers to disk.
# More information can be found at 
# http://blogs.sun.com/realneel/inniostat_innodb_io_statistics
# 
# = Note
# This script works with MySQL 5.1, MySQL 5.4 and MySQL 6.0
# This script makes assumptions regarding several Innodb constants
# and hence may not be accurate for future versions. Be sure to check
# http://blogs.sun.com/realneel for the latest version           
# == Version/Changelog/Author
# 0.01 (May 12 2009) Initial Version (neel AT gnu.org) 
# 0.02 (May 28 2009) Exit when mysqld exits (neel AT gnu.org)

# This script is released under the CDDL License. For details see
# http://www.opensolaris.org/os/licensing

# These values are hardcoded from Innodb sources.
OS_FILE_READ=10
OS_FILE_WRITE=11
OS_FILE_LOG=256
OS_AIO_SIMULATED_WAKE_LATER=512
opt_d=0

usage() { 
	echo "Usage: inniostat [-h] [-d] [-p pid] [interval]"
	echo "                 -h : Print this message"
	echo "                 -p : MySQL PID"
	echo "                 -d : Dump dtrace script being used"
	echo ""
	echo "View script source for more details"
	exit
}

pid=$(pgrep -n mysqld)
interval=1
while getopts p:hd name
do
	case $name in
	d)	opt_d=1 ;;
	p)	pid=$OPTARG ;;
	h|?)	usage ;;	
	esac
done
shift `expr $OPTIND - 1`
if [[ "$1" > 0 ]] ; then
	interval=$1; shift
fi
dtrace=' 
#pragma D option destructive
dtrace:::BEGIN {
	reads = 0; writes = 0; wlog = 0; wdbl = 0; wother = 0;
	rprefetch = 0; rnormal = 0; calc_read = 0; rrs = 0; rws = 0;
	to_print = 0;
}
proc:::exit /execname == "mysqld"/{printf("MySQL exited\n");exit(0);}
pid'$pid'::fil_io:entry
{
	self->is_log = arg0 & '$OS_FILE_LOG';
        self->type=arg0 & ~'$OS_AIO_SIMULATED_WAKE_LATER' & ~'$OS_FILE_LOG';
        self->read = (self->type == '$OS_FILE_READ') ? 1 : 0;
        self->write = (self->type == '$OS_FILE_WRITE') ? 1 : 0;
        reads += self->read ? 1 : 0;
        writes += self->write ? 1 : 0;
        wlog += (self->is_log && self->write) ? 1 : 0;
        wdbl += (self->write && !self->is_log && (arg1 != 0)) ? 1 : 0;
        wother += (self->write && !self->is_log && (arg1 == 0)) ? 1 : 0;
        rprefetch += (self->read && (arg1 == 0)) ? 1 : 0;
        rnormal += (self->read && (arg1 != 0)) ? 1 : 0;
}
io:::start {
	rrs += args[0]->b_flags & B_READ ? 1 : 0;
	rws += args[0]->b_flags & B_WRITE ? 1 : 0;
}
tick-1sec/to_print == 0/ {
	printf("%13s %13s %12s  %20s\n", "__physical__", "___Innodb___",
		"____read____", "______write______");
	printf("%6s %6s %6s %6s %6s %6s %6s %6s %6s %8s\n",
		"r/s", "w/s", "r/s", "w/s", "data", "pre", "log", "dblbuf",
		"dflush", "Time");
	to_print +=1;
}
tick-'$interval'sec
{
	printf("%6d %6d %6d %6d %6d %6d %6d %6d %6d ",
		rrs/'$interval', rws/'$interval',
		reads/'$interval', writes/'$interval', 
		rnormal/'$interval', rprefetch/'$interval',
		wlog/'$interval', wdbl/'$interval', wother/'$interval');
	system("date '+%%H:%%M:%%S'");
	reads = 0; writes = 0; wlog = 0; wdbl = 0; wother = 0;
	rprefetch = 0; rnormal = 0; calc_read = 0; rrs = 0; rws = 0;
	to_print +=1;
	to_print = (to_print > 20) ? 0 : to_print; 
}
'
if [ $opt_d -eq 1 ] ; then
	printf "%s" "$dtrace"; exit;
fi
# Now execute the script
/usr/sbin/dtrace -qn "$dtrace" >&2