#!/usr/bin/perl -w
#
# command_summary - Prints a summary of MySQL commands executed
#
# USAGE:        command_summary
#
# FIELDS:
#               Command         MySQL Command name
#               seconds         Time taken by MySQL command 
#               calls           Number of samples
#               errors          Number of errors executing command
#
# Author: Neelakanth Nadgir (Based off hotuser script by Brendan Gregg)
#
# 19-May-2009   Neelakanth Nadgir   Created this.
#
use strict;
use Getopt::Std;
sub cleanupsig {
}
$SIG{INT} = \&cleanupsig;    # Ctrl-C
$SIG{QUIT} = \&cleanupsig;   # Ctrl-\
$SIG{TERM} = \&cleanupsig;   # TERM


# Declare DTrace script
#
my $dtrace = <<ENDDTRACE;
/usr/sbin/dtrace -n '
#pragma D option quiet
string cmd[32];
BEGIN {
	/* sql_parse.cc command_name[] */
	cmd[0] = "Sleep";
	cmd[1] = "Quit";
	cmd[2] = "InitDB";
	cmd[3] = "Query";
	cmd[4] = "FieldList";
	cmd[5] = "CreateDB";
	cmd[6] = "DropDB";
	cmd[7] = "Refresh";
	cmd[8] = "Shutdown";
	cmd[9] = "Statistics";
	cmd[10] = "Processlist";
	cmd[11] = "Connect";
	cmd[12] = "Kill";
	cmd[13] = "Debug";
	cmd[14] = "Ping";
	cmd[15] = "Time";
	cmd[16] = "DelayedInsert";
	cmd[17] = "ChangeUser";
	cmd[18] = "BinlogDump";
	cmd[19] = "TableDump";
	cmd[20] = "ConnectOut";
	cmd[21] = "RegisterSlave";
	cmd[22] = "Prepare";
	cmd[23] = "Execute";
	cmd[24] = "LongData";
	cmd[25] = "CloseStmt";
	cmd[26] = "ResetStmt";
	cmd[27] = "SetOption";
	cmd[28] = "Fetch";
	cmd[29] = "Daemon";
	cmd[30] = "Error";
}
mysqld*::command-start
{
	self->cmd = arg1;
	self->start = timestamp;
}
mysqld*::command-done
/self->cmd/
{
	\@command[cmd[self->cmd]] = count();
	\@elap[cmd[self->cmd]] = sum((timestamp - self->start)/1000000);
	\@status[cmd[self->cmd],arg0] = count();
}
END {
	printa("COUNT %-30s %\@12d\\n", \@command);
	printa("TIME %-30s %\@12d\\n", \@elap);
	printa("STATUS %-30s %d %\@12d\\n", \@status);
}
'
ENDDTRACE

my %count;
my %tme;
my %err;
my $tc = 0;
my $tt = 0;
my $te = 0;
open DTRACE, "$dtrace |" or die "ERROR1: Can't run dtrace (perms?): $!\n";
print "Sampling... Hit Ctrl-C to end.\n";
while (my $line = <DTRACE>) {
    next if $line =~ /^\s*$/;
    my ($tag, $cmd, $val) = split ' ', $line;
    if ($line =~ /^COUNT/) {
      $count{$cmd}=$val;
      $tc += $val;
    }
    if ($line =~ /^TIME/) {
      $tme{$cmd}=$val;
      $tt += $val;
    }
    if ($line =~ /^STATUS/) {
      my ($tag, $cmd, $stat, $val) = split ' ', $line;
      $err{$cmd} += ($stat != 0) ? $val : 0;
      $te += ($stat != 0) ? $val : 0;
    }

}
close DTRACE;

printf("\nCommand                  seconds     calls  errors\n");
foreach my $k (sort { $count{$a} <=> $count{$b} } keys %count) {
	printf("%-19.19s %12.3f %9d %7s\n", $k, 
			$tme{$k}/1000.0, $count{$k}, 
			$err{$k} == 0? "": $err{$k});
}
printf("%-19.19s %12s %9s %7s\n"," ","-------", "-----", "------");
printf("%-19.19s %12s %9s %7s\n", "total:", $tt/1000.0, $tc,$te);