#!/usr/bin/perl -w
#Version 2.0 2010-01-05
#Author: realzyy
#MSN: realzyy@gmail.com

use Getopt::Long;
use warnings;
use strict;

our %op;
our %his_status1;
our %his_status2;
our %dif_status;
our $dif_time;
our $var_num1;
our $var_num2;
my $i;
my $cmd;
my $his_time1;
my $his_time2;
my $rand_num;
my $var_name;

#1. To get cmd options and init
GetOptions(\%op, "user=s", "password:s", "host=s", "Port=s", "socket=s", "interval=i", "local-file-old=s", "local-file-new=s", "time-old=s", "time-new=s", "display-ip=s", "clean", "bar", "help|?");

if(exists $op{'help'})
{
	show_help_and_exit();
}

if(exists $op{'clean'})
{
	clean_and_exit();
}

if(exists $op{'password'})
{
	if($op{'password'} eq '')
	{
		printf("Password for user \"%s\": ", $op{'user'});
		$op{'password'} = <STDIN>;
		chomp($op{'password'});
	}
}

#Fetch status from two status files
if(exists $op{'local-file-old'} && exists $op{'local-file-new'} && exists $op{'interval'})
{
	$dif_time = $op{'interval'};
	if($dif_time<1)
	{
		printf("Eroor: Interval should be bigger than 1\n");
		exit;
	}
}
elsif((!exists $op{'local-file-old'}) && (!exists $op{'local-file-new'}))
{
	if(exists $op{'interval'})
	{
		$dif_time = $op{'interval'};
		if($dif_time<1)
		{
			printf("Eroor: Interval should be bigger than 1\n");
			exit;
		}
	}
	else
	{
		$dif_time = 5;
	}
}
else
{
	show_help_and_exit();
}
#print %op;
#1. End

#2. Generate status files
if((!exists $op{'local-file-old'}) && (!exists $op{'local-file-new'}))
{
	$rand_num = int(rand(99999));
	if(exists $op{'user'} && exists $op{'password'})
	{
		$cmd = "mysql -u".$op{'user'}." -p".$op{'password'}." -e\"show global status\"";
	}
	else
	{
		$cmd = "mysql -uroot -e\"show global status\"";
	}
	
	#2.1 Generate an old status file
	$his_time1 = time();
	$op{'local-file-old'} = "/tmp/MyDoc_" . $rand_num . "_" . $his_time1;
	system($cmd . ">" . $op{'local-file-old'});
	#2.1 End
	
	#2.2 Print a process bar
	if(exists $op{'bar'})
	{
		for($i=1; $i<=$dif_time; $i++)
		{
			sleep(1);
			proc_bar($i, $dif_time);
			select(undef, undef, undef, 0.2);
		}
		printf("\n");
	}
	else
	{
		sleep($dif_time);
	}
	#2.2 End
	
	#2.3 Generate a new status file
	$his_time2 = time();
	$op{'local-file-new'} = "/tmp/MyDoc_" . $rand_num . "_" . $his_time2;
	system($cmd . ">" . $op{'local-file-new'});
	#2.3 End
}
#2. End

#3. Load status from file into hash array
#printf("%s %s", $op{'local-file-old'}, $op{'local-file-new'});
#3.1 Read status from files
$var_num1 = 0;
open(FILE_OLD, "<$op{'local-file-old'}") or die("Can not open $op{'local-file-old'}");
while(my $temp = <FILE_OLD>)
{
	$var_num1++;
	chomp($temp);
	if( $temp =~ m/([^\t]*)\t([^\t]*)/ )
	{
		$his_status1{"$1"} = $2;
	}
}
close(FILE_OLD) or die("Can not close $op{'local-file-old'}");

$var_num2 = 0;
open(FILE_NEW, "<$op{'local-file-new'}") or die("Can not open $op{'local-file-new'}");
while(my $temp = <FILE_NEW>)
{
	$var_num2++;
	chomp($temp);
	if( $temp =~ m/([^\t]*)\t([^\t]*)/ )
	{
		$his_status2{"$1"} = $2;
	}
}
close(FILE_NEW) or die("Can not close $op{'local-file-new'}");
#3.1 End

#3.2 Check status
if($var_num1>$var_num2)
{
	printf("Error: Lines do not match in two files!\n");
	exit;
}
elsif($var_num1<$var_num2)
{
	printf("Warn: Less status number in old file!\n");
}

#printf("\n%d\n", $var_num1);
#print %his_status1;
#printf("\n");
#printf("\n%d\n", $var_num2);
#print %his_status2;
#printf("\n");
#3.2 End
#3. End

#4. Caculate the difference
foreach $var_name(keys %his_status1)
{
	#print $var_name, $his_status1{$var_name};
	if(is_num($his_status1{$var_name}) eq "NUMBER")
	{
		$dif_status{$var_name} = $his_status2{$var_name} - $his_status1{$var_name};
	}
}
#print %dif_status;
#print $his_status1{"Compression"}." ", $his_status2{"Compression"}." ", $dif_status{"Compression"};
#4. End

#5. Begin to analyze
if(exists $op{'time-old'} && exists $op{'time-new'} && $op{'display-ip'})
{
	printf("Statistics below is based on %d seconds, for host %s\n", $dif_time, $op{'display-ip'});
	printf("From %s to %s, generated by MyDoc 2.0\n\n", $op{'time-old'}, $op{'time-new'});
}
else
{
	printf("Statistics below is based on %d seconds, generated by MyDoc\n\n", $dif_time);
}
	
$~ = 'MYSQL_HEAD', write;
$~ = 'MYSQL_NET', write;
$~ = 'MYSQL_TABLE', write;
$~ = 'MYSQL_QCACHE', write;
$~ = 'MYSQL_QUERIES', write;
$~ = 'MYSQL_SORT_SCAN', write;
$~ = 'MYSQL_MYISAM', write;
$~ = 'MYSQL_INNODB', write;

#5. End

#6. Define the output format
format MYSQL_HEAD =
.

format MYSQL_QCACHE =
__ Qcache___________________________________________________________________
Usage:
    Free_Memory   @>>>>>>>>>> -> @<<<<<<<<<<
$his_status1{'Qcache_free_memory'}, $his_status2{'Qcache_free_memory'}
    Free Blocks   @>>>>>>>>>> -> @<<<<<<<<<<
$his_status1{'Qcache_free_blocks'}, $his_status2{'Qcache_free_blocks'}
Queries:
    Insert    @>>>>>>>>>>        DELETE    @>>>>>>>>>>
change_rate($dif_status{'Qcache_inserts'}, $dif_time), change_rate($dif_status{'Qcache_lowmem_prunes'}, $dif_time)
    Hits      @>>>>>>>>>>        Hit Percentage  @>>>>>>
change_rate($dif_status{'Qcache_hits'}, $dif_time), perc($dif_status{'Qcache_hits'},$dif_status{'Qcache_hits'}+$dif_status{'Qcache_not_cached'})

.

format MYSQL_NET =
__ Connections and Related__________________________________________________
Flow:
    Bytes Received @>>>>>>>>>>>>>>> @>>>>>>>>>>>>>>
$dif_status{'Bytes_received'}, change_rate($dif_status{'Bytes_received'}, $dif_time)
    Bytes Sent     @>>>>>>>>>>>>>>> @>>>>>>>>>>>>>>
$dif_status{'Bytes_sent'}, change_rate($dif_status{'Bytes_sent'}, $dif_time)
Connection:
    Clients Aborted @>>>>>>>>>
$dif_status{'Aborted_clients'}
    Failed Attempts @>>>>>>>>> of @<<<<<<  @>>>>>>>
$dif_status{'Aborted_connects'}, $dif_status{'Connections'}, perc($dif_status{'Aborted_connects'}, $dif_status{'Connections'})
Thread:
    Created         @>>>>>>>>> @>>>>>>>>
$dif_status{'Threads_created'}, change_rate($dif_status{'Threads_created'}, $dif_time)
    
.

format MYSQL_QUERIES =
__ Questions and Handler____________________________________________________
Command:
    Select    @>>>>>>>>>>        Insert    @>>>>>>>>>>
change_rate($dif_status{'Com_select'}, $dif_time), change_rate($dif_status{'Com_insert'}, $dif_time)
    Update    @>>>>>>>>>>        Delete    @>>>>>>>>>>
change_rate($dif_status{'Com_update'}, $dif_time), change_rate($dif_status{'Com_delete'}, $dif_time)
    DML       @>>>>>>>>>>        TPS       @>>>>>>>>>>
change_rate($dif_status{'Com_select'}+$dif_status{'Com_insert'}+$dif_status{'Com_update'}+$dif_status{'Com_delete'}, $dif_time), change_rate($dif_status{'Com_insert'}+$dif_status{'Com_update'}+$dif_status{'Com_delete'}, $dif_time)
Handler:
    Han_write         @>>>>>>>>>>        Han_update          @>>>>>>>>>>
change_rate($dif_status{'Handler_write'}, $dif_time), change_rate($dif_status{'Handler_update'}, $dif_time)
    Han_delete        @>>>>>>>>>>        Han_commit          @>>>>>>>>>>
change_rate($dif_status{'Handler_delete'}, $dif_time), change_rate($dif_status{'Handler_commit'}, $dif_time)
    Han_read_first    @>>>>>>>>>>        Han_read_key        @>>>>>>>>>>
change_rate($dif_status{'Handler_read_first'}, $dif_time), change_rate($dif_status{'Handler_read_key'}, $dif_time)
    Han_read_prev     @>>>>>>>>>>        Han_read_next       @>>>>>>>>>>
change_rate($dif_status{'Handler_read_prev'}, $dif_time), change_rate($dif_status{'Handler_read_next'}, $dif_time)
    Han_read_rnd      @>>>>>>>>>>        Han_read_rnd_next   @>>>>>>>>>>
change_rate($dif_status{'Handler_read_rnd'}, $dif_time), change_rate($dif_status{'Handler_read_rnd_next'}, $dif_time)
    Han_rollback      @>>>>>>>>>>        Han_savepoint       @>>>>>>>>>>
change_rate($dif_status{'Handler_rollback'}, $dif_time), change_rate($dif_status{'Handler_savepoint'}, $dif_time)
    Han_save_rb       @>>>>>>>>>>
change_rate($dif_status{'Handler_savepoint_rollback'}, $dif_time)

.

format MYSQL_SORT_SCAN = 
__ Sort and Scan___________________________________________________________
Sort:
    Sort Scan   @>>>>>>>>>>        Sort Rows       @>>>>>>>>>>
change_rate($dif_status{'Sort_scan'}, $dif_time), change_rate($dif_status{'Sort_rows'}, $dif_time)
    Sort Range  @>>>>>>>>>>        Sort Merge      @>>>>>>>>>>
change_rate($dif_status{'Sort_range'}, $dif_time), change_rate($dif_status{'Sort_merge_passes'}, $dif_time)
Scan:
    Scan        @>>>>>>>>>>        Range           @>>>>>>>>>>
change_rate($dif_status{'Select_scan'}, $dif_time), change_rate($dif_status{'Select_range'}, $dif_time)
    Full Join   @>>>>>>>>>>        Full RANGE join @>>>>>>>>>>
change_rate($dif_status{'Select_full_join'}, $dif_time), change_rate($dif_status{'Select_full_range_join'}, $dif_time)
    Range Check @>>>>>>>>>>
change_rate($dif_status{'Select_range_check'}, $dif_time)

.

format MYSQL_LOG =
__ Log Usage _______________________________________________________________

.

format MYSQL_TABLE =
__ Tables and Files_________________________________________________________
Tables:
    Open            @>>>>>>>>>>>>> @>>>>>>>>>
$dif_status{'Opened_tables'}, change_rate($dif_status{'Opened_tables'}, $dif_time)
Temp table:
    Total created   @>>>>>>>>>>>>> @>>>>>>>>>
$dif_status{'Created_tmp_tables'}, change_rate($dif_status{'Created_tmp_tables'}, $dif_time)
    Create on disk  @>>>>>>>>>>>>> @>>>>>>>>>
$dif_status{'Created_tmp_disk_tables'}, change_rate($dif_status{'Created_tmp_disk_tables'}, $dif_time)
#Files:
#    Open            @>>>>>>>>>>>>> @>>>>>>>>>
#$dif_status{'Opened_files'}, change_rate($dif_status{'Opened_files'}, $dif_time)
Temp files:
    Total created   @>>>>>>>>>>>>> @>>>>>>>>>
$dif_status{'Created_tmp_files'}, change_rate($dif_status{'Created_tmp_files'}, $dif_time)

.

format MYSQL_MYISAM =
__ MyISAM __________________________________________________________________
Buffer Blocks:
    Blocks Unused       @>>>>>>>> -> @<<<<<<<<
$his_status1{'Key_blocks_unused'}, $his_status2{'Key_blocks_unused'}
    Blocks Not Flushed  @>>>>>>>> -> @<<<<<<<<
$his_status1{'Key_blocks_not_flushed'}, $his_status2{'Key_blocks_not_flushed'}
Buffer Hit:
    Phyical/Logical Read   @>>>>>>>> / @<<<<<<<<    Hit Percentage @>>>>>>
$dif_status{'Key_reads'}, $dif_status{'Key_read_requests'}, perc($dif_status{'Key_read_requests'}-$dif_status{'Key_reads'}, $dif_status{'Key_read_requests'})
    Phyical/Logical Write  @>>>>>>>> / @<<<<<<<<    Hit Percentage @>>>>>>
$dif_status{'Key_writes'}, $dif_status{'Key_write_requests'}, perc($dif_status{'Key_write_requests'}-$dif_status{'Key_writes'}, $dif_status{'Key_write_requests'})

.

format MYSQL_INNODB =
__ InnoDB __________________________________________________________________
Rows:
    Read      @>>>>>>>>>>        Inserted    @>>>>>>>>>>
change_rate($dif_status{'Innodb_rows_read'}, $dif_time), change_rate($dif_status{'Innodb_rows_inserted'}, $dif_time)
    Updated   @>>>>>>>>>>        Deleted     @>>>>>>>>>>
change_rate($dif_status{'Innodb_rows_updated'}, $dif_time), change_rate($dif_status{'Innodb_rows_deleted'}, $dif_time)
Locks:
    Waits     @>>>>>>>>>>
change_rate($dif_status{'Innodb_row_lock_waits'}, $dif_time)
    Max/Agv   @>>>>>>>>>> / @<<<<<<<<<< -> @>>>>>>>> / @<<<<<<<<
$his_status1{'Innodb_row_lock_time_max'}, $his_status1{'Innodb_row_lock_time_avg'}, $his_status2{'Innodb_row_lock_time_max'}, $his_status2{'Innodb_row_lock_time_avg'}
Pages:
    Created   @>>>>>>>>>>    Read   @>>>>>>>>>>    Written   @>>>>>>>>>>
change_rate($dif_status{'Innodb_pages_created'}, $dif_time), change_rate($dif_status{'Innodb_pages_read'}, $dif_time), change_rate($dif_status{'Innodb_pages_written'}, $dif_time)
Buffer Pool Hit:
    Phyical/Logical Read   @>>>>>>>> / @<<<<<<<<    Hit Percentage @>>>>>>
$dif_status{'Innodb_buffer_pool_reads'}, $dif_status{'Innodb_buffer_pool_read_requests'}, perc($dif_status{'Innodb_buffer_pool_read_requests'}-$dif_status{'Innodb_buffer_pool_reads'}, $dif_status{'Innodb_buffer_pool_read_requests'})
    Phyical/Logical Write  @>>>>>>>> / @<<<<<<<<    Hit Percentage @>>>>>>
$dif_status{'Innodb_buffer_pool_pages_flushed'}, $dif_status{'Innodb_buffer_pool_write_requests'}, perc($dif_status{'Innodb_buffer_pool_write_requests'}-$dif_status{'Innodb_buffer_pool_pages_flushed'}, $dif_status{'Innodb_buffer_pool_write_requests'})
Logs:
    InnoDB Phyical/Logical Write  @>>>>>>>> / @<<<<<<<<
$dif_status{'Innodb_log_writes'}, $dif_status{'Innodb_log_write_requests'}
    Waits     @>>>>>>>>>>        Bytes Written    @>>>>>>>>>>
change_rate($dif_status{'Innodb_log_waits'}, $dif_time), change_rate($dif_status{'Innodb_os_log_written'}, $dif_time)

.
#6. End

#7. Define the functions
sub is_num
{
	if(!exists $_[0])
	{
		return "UNDEF";
	}
	elsif($_[0] =~ /^\d+$/)
	{
		return "NUMBER";
	}
	else
	{
		return "NOT_NUMBER";
	}
}

sub perc
{
	my $numerator = $_[0];
	my $denominator = $_[1];
	my $res;
	
	if($denominator == 0)
	{
		$res = sprintf("100.00") . "%";
	}
	else
	{
		$res = sprintf("%5.2f", 100*$numerator/$denominator) . "%";
	}
	
	return $res;
}

sub change_rate
{
	my $change = $_[0];
	my $t_diff = $_[1];
	my $res;
	
	if($t_diff == 0)
	{
		$res = sprintf("Error: Time interval=0, cannot caculate rate\n");
	}
	else
	{
		$res = sprintf("%.2f", $change/$t_diff) . "/S";
	}
	
	return $res;
}

sub proc_bar
{
	local $| = 1;
	my $i = $_[0] || return 0;

	my $n = $_[1] || return 0;
	print   "\r\033[36m[\033[33m".("#" x int(($i/$n)*50)).(" " x (50 - int(($i/$n)*50)))."\033[36m]";
	printf("%2.1f%%\033[0m",$i/$n*100);
	local $| = 0;
}

sub clean_and_exit
{
	system("rm -rf /tmp/MyDoc_*");
	exit;
}

sub show_help_and_exit
{
	print<<"EOF";

MyDoc 2.0 2010-01-05 written by realzyy
MyDoc is a perl script to help you fetch MySQL runtime status.

Command line options (abbreviations work):
   --user USER                  Connect to MySQL as USER
   --password PASS              Use PASS or prompt for MySQL user password
   --host ADDRESS               Connect to MySQL at ADDRESS
   --Port PORT                  Connect to MySQL at PORT
   --socket SOCKET              Connect to MySQL at SOCKET
   --interval INVERVAL          Get a report from two status between INTERVAL seconds, required by local-file option
   --local-file-old FILENAME    The first localfile file for fetching status
   --local-file-new FILENAME    The second localfile file for fetching status
   --time-old                   The time for status in local file old
   --time-new                   The time for status in local file new
   --display-ip                 The ip display in the report
   --clean                      Clean the temp files in /tmp
   --bar                        Prints a process bar when waiting
   --help                       Prints this

EOF

	exit;
}
#7. End