323
342
select $tmpfile_fh[$i];
325
# Read infile again and copy each set of status values
326
# to seperate tmp files
344
# Read infile again and copy each set of status values to seperate tmp files
327
345
open INFILE, "< $infile" or warn and next;
351
# The infile must begin with the system variable values.
352
# Therefore, the first occurance of Aborted_clients indicates the beginning
353
# of the first set of status values if no sets have occured yet ($stat_n == 0).
354
# In this case, the following status values are printed to the current fh,
355
# along with the system variable values read thus far, until Aborted_clients
356
# occurs again. Then begins the second and subsequent sets of status values.
335
358
if(/Aborted_clients/)
337
# The infile should begin with manually inserted system
338
# var values (like key_buffer_size = 128M). Therefore,
339
# the first occurance of /Aborted_clients/ indicates
340
# the first set of status values only if no sets have
341
# occured yet ($stat_n = 0). In this case, the following
342
# status values are printed to the current fh along with
343
# the system var values read so far until /Aborted_clients/
344
# occurs again. Then begins the second and subsequent sets
346
next if $stat_n++ == 0;
360
print and next if $stat_n++ == 0;
348
361
select $tmpfile_fh[++$i];
450
479
# One should also add:
451
480
# key_cache_block_size
452
481
# query_cache_size
453
# to their infile if needed.
455
open INFILE, "< $infile" or warn;
482
# to INFILE if needed.
484
open INFILE, "< $infile" or die "Cannot open INFILE '$infile': $!\n";
463
if(/([A-Za-z_]+)[\s\t|]+(\d+)/)
469
# Explicit var = val (e.g. key_buffer_size = 128M)
470
if( /^\s*(\w+)\s*=\s*([0-9.]+)(M*)\s*$/ )
472
$vars{$1} = ($3 ? $2 * 1024 * 1024 : $2);
473
print "read_infile: read '$_' as $1 = $vars{$1}\n" if $op{debug};
490
next if /^\+/; # skip divider lines
491
next if /^$/; # skip blank lines
493
next until /(Aborted_clients|back_log|=)/;
495
if($1 eq 'Aborted_clients') # status values
497
print "read_infile: start stats\n" if $op{debug};
502
if(/([A-Za-z_]+)[\s\t|]+(\d+)/)
505
print "read_infile: save $1 = $2\n" if $op{debug};
507
else { print "read_infile: ignore '$_'\n" if $op{debug}; }
509
last if $1 eq 'Uptime'; # exit while() if end of status values
510
$_ = <INFILE>; # otherwise, read next line of status values
513
elsif($1 eq 'back_log') # system variable values
515
print "read_infile: start vars\n" if $op{debug};
520
if(/([A-Za-z_]+)[\s\t|]+([\w\.\-]+)/) # This will exclude some vars
521
{ # like pid_file which we don't need
523
print "read_infile: save $1 = $2\n" if $op{debug};
525
else { print "read_infile: ignore '$_'\n" if $op{debug}; }
527
last if $1 eq 'wait_timeout'; # exit while() if end of vars
528
$_ = <INFILE>; # otherwise, read next line of vars
531
elsif($1 eq '=') # old style, manually added system variable values
533
print "read_infile: start old vars\n" if $op{debug};
535
while($_ && $_ =~ /=/)
538
if(/^\s*(\w+)\s*=\s*([0-9.]+)(M*)\s*$/) # e.g.: key_buffer_size = 128M
540
$vars{$1} = ($3 ? $2 * 1024 * 1024 : $2);
541
print "read_infile: read '$_' as $1 = $vars{$1}\n" if $op{debug};
543
else { print "read_infile: ignore '$_'\n" if $op{debug}; }
545
$_ = <INFILE>; # otherwise, read next line of old vars
478
552
print "read_infile: unrecognized line: '$_'\n" if $op{debug};
483
558
$real_uptime = $stats{'Uptime'};
576
661
sub write_relative_report
578
%stats_present = %stats;
582
if($stats_past{$_} =~ /\d+/)
584
if($stats_present{$_} >= $stats_past{$_}) # Avoid negative values
586
$stats{$_} = $stats_present{$_} - $stats_past{$_};
591
# These values are either "at present" or "high water marks".
592
# Therefore, it is more logical to not relativize these values.
593
# Doing otherwise causes strange and misleading values.
594
$stats{'Key_blocks_used'} = $stats_present{'Key_blocks_used'};
595
$stats{'Open_tables'} = $stats_present{'Open_tables'};
596
$stats{'Max_used_connections'} = $stats_present{'Max_used_connections'};
597
$stats{'Threads_running'} = $stats_present{'Threads_running'};
598
$stats{'Threads_connected'} = $stats_present{'Threads_connected'};
599
$stats{'Threads_cached'} = $stats_present{'Threads_cached'};
600
$stats{'Qcache_free_blocks'} = $stats_present{'Qcache_free_blocks'};
601
$stats{'Qcache_total_blocks'} = $stats_present{'Qcache_total_blocks'};
602
$stats{'Qcache_free_memory'} = $stats_present{'Qcache_free_memory'};
603
if($have_innodb_vals)
605
$stats{'Innodb_page_size'} = $stats_present{'Innodb_page_size'};
606
$stats{'Innodb_buffer_pool_pages_data'} = $stats_present{'Innodb_buffer_pool_pages_data'};
607
$stats{'Innodb_buffer_pool_pages_dirty'} = $stats_present{'Innodb_buffer_pool_pages_dirty'};
608
$stats{'Innodb_buffer_pool_pages_free'} = $stats_present{'Innodb_buffer_pool_pages_free'};
609
$stats{'Innodb_buffer_pool_pages_latched'} = $stats_present{'Innodb_buffer_pool_pages_latched'};
610
$stats{'Innodb_buffer_pool_pages_misc'} = $stats_present{'Innodb_buffer_pool_pages_misc'};
611
$stats{'Innodb_buffer_pool_pages_total'} = $stats_present{'Innodb_buffer_pool_pages_total'};
612
$stats{'Innodb_data_pending_fsyncs'} = $stats_present{'Innodb_data_pending_fsyncs'};
613
$stats{'Innodb_data_pending_reads'} = $stats_present{'Innodb_data_pending_reads'};
614
$stats{'Innodb_data_pending_writes'} = $stats_present{'Innodb_data_pending_writes'};
616
# Innodb_row_lock_ values were added in MySQL 5.0.3
617
if((($minor == 0 && $patch >= 3) || $minor > 0))
619
$stats{'Innodb_row_lock_current_waits'} = $stats_present{'Innodb_row_lock_current_waits'};
620
$stats{'Innodb_row_lock_time_avg'} = $stats_present{'Innodb_row_lock_time_avg'};
621
$stats{'Innodb_row_lock_time_max'} = $stats_present{'Innodb_row_lock_time_max'};
627
%stats_past = %stats_present;
630
set_ib_vals() if $have_innodb_vals;
663
print "write_relative_report\n" if $op{debug};
665
%stats_present = %stats;
669
if($stats_past{$_} =~ /\d+/)
671
if($stats_present{$_} >= $stats_past{$_}) # Avoid negative values
673
$stats{$_} = $stats_present{$_} - $stats_past{$_};
678
# These values are either "at present" or "high water marks".
679
# Therefore, it is more logical to not relativize these values.
680
# Doing otherwise causes strange and misleading values.
681
$stats{'Key_blocks_used'} = $stats_present{'Key_blocks_used'};
682
$stats{'Open_tables'} = $stats_present{'Open_tables'};
683
$stats{'Max_used_connections'} = $stats_present{'Max_used_connections'};
684
$stats{'Threads_running'} = $stats_present{'Threads_running'};
685
$stats{'Threads_connected'} = $stats_present{'Threads_connected'};
686
$stats{'Threads_cached'} = $stats_present{'Threads_cached'};
687
$stats{'Qcache_free_blocks'} = $stats_present{'Qcache_free_blocks'};
688
$stats{'Qcache_total_blocks'} = $stats_present{'Qcache_total_blocks'};
689
$stats{'Qcache_free_memory'} = $stats_present{'Qcache_free_memory'};
690
if($have_innodb_vals)
692
$stats{'Innodb_page_size'} = $stats_present{'Innodb_page_size'};
693
$stats{'Innodb_buffer_pool_pages_data'} = $stats_present{'Innodb_buffer_pool_pages_data'};
694
$stats{'Innodb_buffer_pool_pages_dirty'} = $stats_present{'Innodb_buffer_pool_pages_dirty'};
695
$stats{'Innodb_buffer_pool_pages_free'} = $stats_present{'Innodb_buffer_pool_pages_free'};
696
$stats{'Innodb_buffer_pool_pages_latched'} = $stats_present{'Innodb_buffer_pool_pages_latched'};
697
$stats{'Innodb_buffer_pool_pages_misc'} = $stats_present{'Innodb_buffer_pool_pages_misc'};
698
$stats{'Innodb_buffer_pool_pages_total'} = $stats_present{'Innodb_buffer_pool_pages_total'};
699
$stats{'Innodb_data_pending_fsyncs'} = $stats_present{'Innodb_data_pending_fsyncs'};
700
$stats{'Innodb_data_pending_reads'} = $stats_present{'Innodb_data_pending_reads'};
701
$stats{'Innodb_data_pending_writes'} = $stats_present{'Innodb_data_pending_writes'};
703
# Innodb_row_lock_ values were added in MySQL 5.0.3
704
if($MySQL_version >= 50003)
706
$stats{'Innodb_row_lock_current_waits'} = $stats_present{'Innodb_row_lock_current_waits'};
707
$stats{'Innodb_row_lock_time_avg'} = $stats_present{'Innodb_row_lock_time_avg'};
708
$stats{'Innodb_row_lock_time_max'} = $stats_present{'Innodb_row_lock_time_max'};
714
%stats_past = %stats_present;
717
set_ib_vals() if $have_innodb_vals;
724
print "write_report\n" if $op{debug};
637
726
$~ = 'MYSQL_TIME', write;
638
727
$~ = 'KEY_BUFF_MAX', write;
639
728
if($key_buffer_usage != -1) { $~ = 'KEY_BUFF_USAGE', write }
784
# What began as a simple but great idea has become the new standard:
785
# long_query_time in microseconds. For MySQL 5.1.21+ and 6.0.4+ this
786
# is now standard. For 4.1 and 5.0 patches, the architects of this
787
# idea provide: http://www.mysqlperformanceblog.com/mysql-patches/
788
# Relevant notes in MySQL manual:
789
# http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
790
# http://dev.mysql.com/doc/refman/6.0/en/slow-query-log.html
792
# The format_u_time sub simply beautifies long_query_time.
794
sub format_u_time # format microsecond (�) time value
796
# 0.000000 - 0.000999 = 0 - 999 �
797
# 0.001000 - 0.999999 = 1 ms - 999.999 ms
798
# 1.000000 - n.nnnnnn = 1 s - n.nnnnn s
801
my $f; # formatted � time
802
my $u = chr(($WIN ? 230 : 181));
806
if($t > 0 && $t <= 0.000999)
808
$f = ($t * 1000000) . " $u";
810
elsif($t >= 0.001000 && $t <= 0.999999)
812
$f = ($t * 1000) . ' ms';
816
$f = ($t * 1) . ' s'; # * 1 to remove insignificant zeros
820
$f = 0; # $t should = 0 at this point
695
826
sub perc # Percentage
697
828
my($is, $of) = @_;
829
$is = 0 if (not defined $is);
698
830
return sprintf "%.2f", ($is * 100) / ($of ||= 1);