*** /tmp/skr99/mytop-1.9.1/mytop Tue Feb 14 18:24:52 2012 --- ./mytop Sat Sep 22 02:03:31 2012 *************** *** 15,25 **** use 5.005; use strict; use DBI; - use Getopt::Long; use DBD::mysql; use Socket; ! $main::VERSION = "1.91"; $|=1; $0 = 'mytop'; --- 15,28 ---- use 5.005; use strict; use DBI; use DBD::mysql; + use Getopt::Long; use Socket; + use List::Util qw(min max); + use File::Basename; ! $main::VERSION = "1.91a"; ! my $path_for_script= dirname($0); $|=1; $0 = 'mytop'; *************** *** 95,101 **** slow => 10, # slow query time socket => '', sort => 1, # default or reverse sort ("s") ! user => 'root' ); my %qcache = (); ## The query cache--used for full query info support. --- 98,105 ---- slow => 10, # slow query time socket => '', sort => 1, # default or reverse sort ("s") ! user => 'root', ! fullqueries => 0 ); my %qcache = (); ## The query cache--used for full query info support. *************** *** 112,117 **** --- 116,132 ---- my $RM_RESET = 0; my $RM_NOBLKRD = 3; ## using 4 traps Ctrl-C :-( + # Add options from .my.cnf first + + my $my_print_defaults; + if (!defined($my_print_defaults=my_which("my_print_defaults"))) + { + print "Warning: Can't find my_print_defaults. Please add it to your PATH!\n"; + exit(1); + } + + unshift @ARGV, split "\n", `$my_print_defaults client mytop`; + ## Read the user's config file, if it exists. my $config = "$ENV{HOME}/.mytop"; *************** *** 160,166 **** "long_nums!" => \$config{long_nums}, "mode|m=s" => \$config{mode}, "slow=i" => \$config{slow}, ! "sort=s" => \$config{sort} ); ## User may have put the port with the host. --- 175,182 ---- "long_nums!" => \$config{long_nums}, "mode|m=s" => \$config{mode}, "slow=i" => \$config{slow}, ! "sort=s" => \$config{sort}, ! "fullqueries|L!" => \$config{fullqueries} ); ## User may have put the port with the host. *************** *** 271,276 **** --- 287,293 ---- my $db_version; my $db_release; + my $server="MySQL"; my $have_query_cache; my @variables = Hashes("show variables"); *************** *** 280,286 **** if ($_->{Variable_name} eq "version") { $db_version = $_->{Value}; ! $db_release = int(sprintf("%d",$db_version)); next; } if ($_->{Variable_name} eq "have_query_cache") --- 297,305 ---- if ($_->{Variable_name} eq "version") { $db_version = $_->{Value}; ! $db_version =~ /(\d+)/; ! $db_release= $1; ! $server="MariaDB" if ($db_version =~ /maria/i); next; } if ($_->{Variable_name} eq "have_query_cache") *************** *** 396,402 **** next; } ! ## m - mode swtich to qps if ($key eq 'm') { --- 415,421 ---- next; } ! ## m - mode switch to qps if ($key eq 'm') { *************** *** 406,412 **** next; } ! ## c - mode swtich to command summary if ($key eq 'c') { --- 425,431 ---- next; } ! ## c - mode switch to command summary if ($key eq 'c') { *************** *** 724,733 **** ReadKey(0); } ! if ($key eq 'S') { $config{mode} = 'status'; } } ReadMode($RM_RESET) unless $config{batchmode}; --- 743,773 ---- ReadKey(0); } ! # Switch to show status mode ! ! if ($key eq 'M') { $config{mode} = 'status'; } + + ## L - full queries toggle + + if ($key eq 'L') + { + if ($config{fullqueries}) + { + $config{fullqueries} = 0; + print RED(), "-- full queries OFF --", RESET(); + sleep 1; + } + else + { + $config{fullqueries} = 1; + print RED(), "-- full queries ON --", RESET(); + sleep 1; + } + } + } ReadMode($RM_RESET) unless $config{batchmode}; *************** *** 814,819 **** --- 854,864 ---- %OLD_STATUS = %STATUS; + # Set some status that may not exist in all versions + $STATUS{Handler_tmp_write}= 0; + $STATUS{Handler_tmp_update}= 0; + $STATUS{Rows_tmp_read}= 0; + foreach my $ref (@recs) { my $key = $ref->{Variable_name}; *************** *** 904,922 **** my $current_time = sprintf "[%02d:%02d:%02d]", $t[2], $t[1], $t[0]; ! my $host_width = 75; ! my $up_width = $width - $host_width; ! Clear() unless $config{batchmode}; print RESET(); ! printf "%-${host_width}s%${up_width}s\n", ! "MySQL on $config{host} ($db_version)", ! "load $l up $uptime $current_time"; $lines_left--; ! printf " Queries: %-6s qps: %4.0f Slow: %7s Se/In/Up/De(%%): %02.0f/%02.0f/%02.0f/%02.0f \n", make_short( $STATUS{Questions} ), # q total $STATUS{Questions} / $STATUS{Uptime}, # qps, average make_short( $STATUS{Slow_queries} ), # slow --- 949,967 ---- my $current_time = sprintf "[%02d:%02d:%02d]", $t[2], $t[1], $t[0]; ! my $host_width = 50; ! my $up_width = $width - $host_width - 1; Clear() unless $config{batchmode}; print RESET(); ! printf "%-.${host_width}s %${up_width}s\n", ! "$server on $config{host} ($db_version)", ! "up $uptime $current_time"; ! # "load $l up $uptime $current_time"; $lines_left--; ! printf " Queries: %-6s qps: %4.0f Slow: %7s Se/In/Up/De(%%): %02.0f/%02.0f/%02.0f/%02.0f\n", make_short( $STATUS{Questions} ), # q total $STATUS{Questions} / $STATUS{Uptime}, # qps, average make_short( $STATUS{Slow_queries} ), # slow *************** *** 934,940 **** my $q_diff = ( $STATUS{Questions} - $OLD_STATUS{Questions} ); # print("q_diff: $STATUS{Questions} - $OLD_STATUS{Questions} / $t_delta = $q_diff\n"); ! printf(" Sorts: %5.0f qps now: %4.0f Slow qps: %3.1f Threads: %4.0f (%4.0f/%4.0f) %02.0f/%02.0f/%02.0f/%02.0f \n", ( $STATUS{Sort_rows} - $OLD_STATUS{Sort_rows} ) / $t_delta, ( $STATUS{Questions} - $OLD_STATUS{Questions} ) / $t_delta, ( # slow now (qps) --- 979,985 ---- my $q_diff = ( $STATUS{Questions} - $OLD_STATUS{Questions} ); # print("q_diff: $STATUS{Questions} - $OLD_STATUS{Questions} / $t_delta = $q_diff\n"); ! printf(" Sorts: %5.0f qps now: %4.0f Slow qps: %3.1f Threads: %4.0f (%4.0f/%4.0f) %02.0f/%02.0f/%02.0f/%02.0f\n", ( $STATUS{Sort_rows} - $OLD_STATUS{Sort_rows} ) / $t_delta, ( $STATUS{Questions} - $OLD_STATUS{Questions} ) / $t_delta, ( # slow now (qps) *************** *** 991,997 **** ) || 1) : 0; if ($HAS_COLOR) { ! print GREEN() if ($Ratio_now => 80.0); print YELLOW() if ($Ratio_now < 80.0); print RED() if ($Ratio_now < 50.0); print MAGENTA() if ($Ratio_now < 20.0); --- 1036,1042 ---- ) || 1) : 0; if ($HAS_COLOR) { ! print GREEN() if ($Ratio_now >= 80.0); print YELLOW() if ($Ratio_now < 80.0); print RED() if ($Ratio_now < 50.0); print MAGENTA() if ($Ratio_now < 20.0); *************** *** 1004,1011 **** } $lines_left--; ! printf(" Key Efficiency: %2.1f%% Bps in/out: %5s/%5s ", $cache_hits_percent, make_short($STATUS{Bytes_received} / $STATUS{Uptime} ), make_short($STATUS{Bytes_sent} / $STATUS{Uptime})); --- 1049,1095 ---- } $lines_left--; + if ($t_delta) + { + my $rows_read; + if (defined($STATUS{Rows_read})) + { + $rows_read= $STATUS{Rows_read} - $OLD_STATUS{Rows_read}; + } + else + { + $rows_read= + ($STATUS{Handler_read_first}+$STATUS{Handler_read_key}+ + $STATUS{Handler_read_next}+$STATUS{Handler_read_prev}+ + $STATUS{Handler_read_rnd}+$STATUS{Handler_read_rnd_next} - + $OLD_STATUS{Handler_read_first}-$OLD_STATUS{Handler_read_key}- + $OLD_STATUS{Handler_read_next}-$OLD_STATUS{Handler_read_prev}- + $OLD_STATUS{Handler_read_rnd}- + $OLD_STATUS{Handler_read_rnd_next}); + } + printf(" Handler: (R/W/U/D) %5d/%5d/%5d/%5d Tmp: R/W/U: %5d/%5d/%5d\n", + $rows_read/$t_delta, + ($STATUS{Handler_write} - $OLD_STATUS{Handler_write}) / + $t_delta, + ($STATUS{Handler_update} - $OLD_STATUS{Handler_update}) / + $t_delta, + ($STATUS{Handler_delete} - $OLD_STATUS{Handler_delete}) / + $t_delta, + ($STATUS{Rows_tmp_read} - $OLD_STATUS{Rows_tmp_read}) / + $t_delta, + ($STATUS{Handler_tmp_write} + -$OLD_STATUS{Handler_tmp_write})/$t_delta, + ($STATUS{Handler_tmp_update} - + $OLD_STATUS{Handler_tmp_update})/$t_delta); + } + else + { + print "\n"; + } ! $lines_left--; ! ! printf(" MyISAM Key Efficiency: %2.1f%% Bps in/out: %5s/%5s ", $cache_hits_percent, make_short($STATUS{Bytes_received} / $STATUS{Uptime} ), make_short($STATUS{Bytes_sent} / $STATUS{Uptime})); *************** *** 1025,1031 **** RESET() if ($HAS_COLOR); } ! my($data) = Hashes('SHOW SLAVE STATUS'); if (defined($data->{Master_Host})) { if (defined($data->{Seconds_Behind_Master})) --- 1109,1115 ---- RESET() if ($HAS_COLOR); } ! ($data) = Hashes('SHOW SLAVE STATUS'); if (defined($data->{Master_Host})) { if (defined($data->{Seconds_Behind_Master})) *************** *** 1071,1091 **** ## Threads ## ! #my $sz = $width - 52; ! my @sz = (9, 9, 15, 10, 10, 6, 8); my $used = scalar(@sz) + Sum(@sz); ! my $free = $width - $used; ! print BOLD() if ($HAS_COLOR); ! printf "%9s %9s %15s %10s %10s %6s %8s %-${free}s\n", ! 'Id','User','Host/IP','DB','Time', 'Cmd', 'State', 'Query'; print RESET() if ($HAS_COLOR); ## Id User Host DB ! printf "%9s %9s %15s %10s %10s %6s %8s %-${free}s\n", ! '--','----','-------','--','----', '---', '-----', '----------'; $lines_left -= 2; --- 1155,1182 ---- ## Threads ## ! my @sz = (9, 8, 15, 9, 6, 5, 6, 8); my $used = scalar(@sz) + Sum(@sz); ! my $state= $width <= 80 ? 6 : int(min(6+($width-80)/3, 15)); ! my $free = $width - $used - ($state - 6); ! my $format= "%9s %8s %15s %9s %6s %5s %6s %${state}s %-.${free}s\n"; ! my $format2; ! if ($config{fullqueries}) ! { ! $format2 = "%9d %8.8s %15.15s %9.9s %6d %5.1f %6.6s %${state}.${state}s %-${free}s\n"; ! } else { ! $format2 = "%9d %8.8s %15.15s %9.9s %6d %5.1f %6.6s %${state}.${state}s %-${free}.${free}s\n"; ! } print BOLD() if ($HAS_COLOR); ! printf $format, ! 'Id','User','Host/IP','DB','Time', '%', 'Cmd', 'State', 'Query'; print RESET() if ($HAS_COLOR); ## Id User Host DB ! printf $format, ! '--','----','-------','--','----', '-', '---', '-----', '----------'; $lines_left -= 2; *************** *** 1134,1139 **** --- 1225,1231 ---- $thread->{Command} ||= ''; $thread->{Host} ||= ''; $thread->{State} ||= ""; + $thread->{Progress} ||= 0; ## alter double hyphen comments so they don't break ## the query when newlines are removed - http://freshmeat.net/users/jerjones *************** *** 1198,1212 **** next if ($thread->{Host} !~ $config{filter_host}); next if ($thread->{State} !~ $config{filter_state}); - $thread->{State} = trim(sprintf("%8.8s",$thread->{State})); - # Otherwise, print. my $smInfo; if ($thread->{Info}) { ! $smInfo = substr $thread->{Info}, 0, $free; } # if ($thread->{State}) # { --- 1290,1307 ---- next if ($thread->{Host} !~ $config{filter_host}); next if ($thread->{State} !~ $config{filter_state}); # Otherwise, print. my $smInfo; if ($thread->{Info}) { ! if ($config{fullqueries}) ! { ! $smInfo = $thread->{Info}; ! } else { ! $smInfo = substr $thread->{Info}, 0, $free; ! } } # if ($thread->{State}) # { *************** *** 1226,1234 **** print MAGENTA() if $thread->{Time} > $config{long}; } ! printf "%9d %9.9s %15.15s %10.10s %10d %6.6s %8.8s %-${free}.${free}s\n", $thread->{Id}, $thread->{User}, $thread->{Host}, $thread->{db}, ! $thread->{Time}, $thread->{Command}, $thread->{State}, $smInfo; print RESET() if $HAS_COLOR; --- 1321,1329 ---- print MAGENTA() if $thread->{Time} > $config{long}; } ! printf $format2, $thread->{Id}, $thread->{User}, $thread->{Host}, $thread->{db}, ! $thread->{Time}, $thread->{Progress}, $thread->{Command}, $thread->{State}, $smInfo; print RESET() if $HAS_COLOR; *************** *** 1400,1406 **** Clear() unless $config{batchmode}; my @rows = Hashes("SHOW STATUS"); ! printf "%32s %10s %10s\n", 'Counter', 'Total', 'Change'; printf "%32s %10s %10s\n", '-------', '-----', '------'; for my $row (@rows) --- 1495,1501 ---- Clear() unless $config{batchmode}; my @rows = Hashes("SHOW STATUS"); ! printf "%32s %10s %10s Toggle idle with 'i'\n", 'Counter', 'Total', 'Change'; printf "%32s %10s %10s\n", '-------', '-----', '------'; for my $row (@rows) *************** *** 1638,1643 **** --- 1733,1739 ---- p - pause the display l - change long running queries hightlighing m - switch [mode] to qps (queries/sec) scrolling view + M - switch [mode] to status o - reverse the sort order (toggle) q - quit r - reset the status counters (via FLUSH STATUS on your server) *************** *** 1646,1656 **** S - change slow quiery hightlighting t - switch to thread view (default) u - show only a specific user : - enter a command (not yet implemented) ! - Skip an error that has stopped replications (at your own risk) ! ${GREEN}http://jeremy.zawodny.com/mysql/mytop/${RESET} ! ${GREEN}http://www.mysqlfanboy.com/mytop/${RESET} ]; print $help; --- 1742,1754 ---- S - change slow quiery hightlighting t - switch to thread view (default) u - show only a specific user + V - show variables : - enter a command (not yet implemented) ! - Skip an error that has stopped replications (at your own risk) + L - show full queries (do not strip to terminal width) ! Base version from ${GREEN}http://www.mysqlfanboy.com/mytop${RESET} ! This version comes as part of the ${GREEN}MariaDB${RESET} distribution. ]; print $help; *************** *** 1747,1752 **** --- 1845,1879 ---- return $found; } + #### + #### my_which is used, because we can't assume that every system has the + #### which -command. my_which can take only one argument at a time. + #### Return values: requested system command with the first found path, + #### or undefined, if not found. + #### + + sub my_which + { + my ($command) = @_; + my (@paths, $path); + + return $command if (-f $command && -x $command); + + # Check first if this is a source distribution, then if this binary + # distribution and last in the path + + push @paths, "./extra"; + push @paths, $path_for_script; + push @paths, split(':', $ENV{'PATH'}); + + foreach $path (@paths) + { + $path .= "/$command"; + return $path if (-f $path && -x $path); + } + return undef(); + } + =pod =head1 SYNOPSIS *************** *** 1755,1761 **** =head1 AVAILABILITY ! The latest version of B is available from http://jeremy.zawodny.com/mysql/mytop/ it B also be on CPAN as well. --- 1882,1892 ---- =head1 AVAILABILITY ! Base version from B. ! ! This version comes as part of the B distribution. See B. ! ! And older (the original) version B is available from http://jeremy.zawodny.com/mysql/mytop/ it B also be on CPAN as well. *************** *** 2010,2020 **** =head2 Config File Instead of always using bulky command-line parameters, you can also ! use a config file in your home directory (C<~/.mytop>). If present, ! B will read it automatically. It is read I any of your command-line arguments are processed, so your command-line arguments will override directives in the config file. Here is a sample config file C<~/.mytop> which implements the defaults described above. --- 2141,2155 ---- =head2 Config File Instead of always using bulky command-line parameters, you can also ! use a config files for the default value of your options. ! ! mytop will first read the [client] and [mytop] sections from your ! my.cnf files. After that it will read the (C<~/.mytop>) file from your ! home directory (if present). These are read I any of your command-line arguments are processed, so your command-line arguments will override directives in the config file. + Here is a sample config file C<~/.mytop> which implements the defaults described above. *************** *** 2202,2213 **** way. It is merely a personal idea which happened to be very useful in my job. - =head1 RECRUITING - - If you hack Perl and grok MySQL, come work at Yahoo! Contact me for - details. Or just send me your resume. Er, unless we just had layoffs, - in which case we're not hiring. :-( - =head1 SEE ALSO Please check the MySQL manual if you're not sure where some of the --- 2337,2342 ---- *************** *** 2225,2230 **** --- 2354,2363 ---- =over + =Item Jean Weisbuch + + Added --fullqueries and reading of .my.cnf + =item Mark Grennan (mark@grennan.com) www.linuxfangoy.com Added updates for MySQL 5.x. Added 'S' (slow) highlighting. *************** *** 2312,2317 **** --- 2445,2457 ---- Supplied an excellent patch to tidy up the top display. This includes showing most values in short form, such as 10k rather than 10000. + =item Michael "Monty" Widenius + + Fixed a couple of minor bugs that gave warnings on startup. + Added support for MariaDB (show MariaDB at top and % done). + Cut long server version names to display width. + Made 'State' length dynamic. + =back See the Changes file on the B distribution page for more