mysql report

From marc, 3 Years ago, written in Perl, viewed 3'063 times.
URL http://bin.flmt.es/view/mysqlre Embed
Download Paste or View Raw
  1. #!/usr/bin/perl -w
  2.  
  3. # mysqlreport v3.5 Apr 16 2008
  4. # http://hackmysql.com/mysqlreport
  5.  
  6. # mysqlreport makes an easy-to-read report of important MySQL status values.
  7. # Copyright 2006-2008 Daniel Nichter
  8. #
  9. # This program is free software; you can redistribute it and/or
  10. # modify it under the terms of the GNU General Public License
  11. # as published by the Free Software Foundation; either version 2
  12. # of the License, or (at your option) any later version.
  13. #
  14. # This program is distributed in the hope that it will be useful,
  15. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17. # GNU General Public License for more details.
  18. #
  19. # The GNU General Public License is available at:
  20. # http://www.gnu.org/copyleft/gpl.html
  21.  
  22. use strict;
  23. use File::Temp qw(tempfile);
  24. use DBI;
  25. use Getopt::Long;
  26. eval { require Term::ReadKey; };
  27. my $RK = ($@ ? 0 : 1);
  28.  
  29. sub have_op;
  30.  
  31. my $WIN = ($^O eq 'MSWin32' ? 1 : 0);
  32. my %op;
  33. my %mycnf; # ~/.my.cnf
  34. my ($tmpfile_fh, $tmpfile);
  35. my ($stat_name, $stat_val, $stat_label);
  36. my $MySQL_version;
  37. my (%stats, %vars); # SHOW STATUS, SHOW VARIABLES
  38. my (%DMS_vals, %Com_vals, %ib_vals);
  39. my ($dbh, $query);
  40. my ($questions, $key_read_ratio, $key_write_ratio, $dms, $slow_query_t);
  41. my ($key_cache_block_size, $key_buffer_used, $key_buffer_usage);
  42. my ($qc_mem_used, $qc_hi_r, $qc_ip_r); # Query Cache
  43. my $have_innodb_vals;
  44. my ($ib_bp_used, $ib_bp_total, $ib_bp_read_ratio);
  45. my ($relative_live, $relative_infiles);
  46. my $real_uptime;
  47. my (%stats_present, %stats_past); # For relative reports
  48.      
  49. GetOptions (
  50.    \%op,
  51.    "user=s",
  52.    "password:s",
  53.    "host=s",
  54.    "port=s",
  55.    "socket=s",
  56.    "no-mycnf",
  57.    "infile|in=s",
  58.    "outfile=s",
  59.    "flush-status",
  60.    "email=s",
  61.    "r|relative:i",
  62.    "c|report-count=i",
  63.    "detach",
  64.    "help|?",
  65.    "debug"
  66. );
  67.  
  68. show_help_and_exit() if $op{'help'};
  69.  
  70. get_user_mycnf() unless $op{'no-mycnf'};
  71.  
  72. # Command line options override ~/.my.cnf
  73. $mycnf{'host'}   = $op{'host'}   if have_op 'host';
  74. $mycnf{'port'}   = $op{'port'}   if have_op 'port';
  75. $mycnf{'socket'} = $op{'socket'} if have_op 'socket';
  76. $mycnf{'user'}   = $op{'user'}   if have_op 'user';
  77.  
  78. $mycnf{'user'} ||= $ENV{'USER'};
  79.  
  80. if(exists $op{'password'})
  81. {
  82.    if($op{'password'} eq '') # Prompt for password
  83.    {
  84.       Term::ReadKey::ReadMode(2) if $RK;
  85.       print "Password for database user $mycnf{'user'}: ";
  86.       chomp($mycnf{'pass'} = <STDIN>);
  87.       Term::ReadKey::ReadMode(0), print "\n" if $RK;
  88.    }
  89.    else { $mycnf{'pass'} = $op{'password'}; } # Use password given on command line
  90. }
  91.  
  92. $op{'com'} ||= 3;
  93. $op{'c'}   ||= 1; # Used in collect_reports() if --r given integer value
  94.  
  95. $relative_live    = 0;
  96. $relative_infiles = 0;
  97.  
  98. if(defined $op{'r'})
  99. {
  100.    if($op{r}) { $relative_live    = 1; }  # if -r was given an integer value
  101.    else       { $relative_infiles = 1; }
  102. }
  103.  
  104. # The report is written to a tmp file first.
  105. # Later it will be moved to $op{'outfile'} or emailed $op{'email'} if needed.
  106. ($tmpfile_fh, $tmpfile) = tempfile() or die "Cannot open temporary file for writing: $!\n";
  107.  
  108. if($op{'detach'})
  109. {
  110.    $SIG{'TERM'} = 'sig_handler';
  111.  
  112.    if(fork())
  113.    {
  114.       print "mysqlreport has forked and detached.\n";
  115.       print "While running detached, mysqlreport writes reports to '$tmpfile'.\n";
  116.  
  117.       exit;
  118.    }
  119.  
  120.    open(STDIN, "</dev/null");
  121.    open(STDOUT, "> $tmpfile") or die "Cannot dup STDOUT: $!\n";
  122.    open(STDERR, "> $tmpfile") or die "Cannot dup STDERR: $!\n";
  123. }
  124.  
  125. select $tmpfile_fh;
  126. $| = 1 if ($op{'detach'} || $relative_live);
  127.  
  128. print "tmp file: $tmpfile\n" if $op{debug};
  129.  
  130. # Connect to MySQL
  131. if(!$op{'infile'} && !$relative_infiles)
  132. {
  133.    connect_to_MySQL();
  134. }
  135.  
  136. $have_innodb_vals = 1; # This might be set to 0 later in get_MySQL_version()
  137.  
  138. if(defined $op{'r'})
  139. {
  140.    if($relative_live)
  141.    {
  142.       print STDERR "mysqlreport is writing relative reports to '$tmpfile'.\n" unless $op{'detach'};
  143.       get_MySQL_version();
  144.       collect_reports();
  145.    }
  146.  
  147.    if($relative_infiles) { read_relative_infiles(); }
  148. }
  149. else
  150. {
  151.    if(!$op{'infile'})
  152.    {
  153.       get_MySQL_version();
  154.       get_vals();
  155.       get_vars();
  156.    }
  157.    else
  158.    {
  159.       read_infile($op{'infile'});
  160.    }
  161.  
  162.    get_Com_values();
  163.  
  164.    set_myisam_vals();
  165.    set_ib_vals() if $have_innodb_vals;
  166.  
  167.    write_report();
  168. }
  169.  
  170. exit_tasks_and_cleanup();
  171.  
  172.  
  173. #
  174. # Subroutines
  175. #
  176. sub show_help_and_exit
  177. {
  178.    print <<"HELP";
  179. mysqlreport v3.5 Apr 16 2008
  180. mysqlreport makes an easy-to-read report of important MySQL status values.
  181.  
  182. Command line options (abbreviations work):
  183.    --user USER       Connect to MySQL as USER
  184.    --password PASS   Use PASS or prompt for MySQL user's password
  185.    --host ADDRESS    Connect to MySQL at ADDRESS
  186.    --port PORT       Connect to MySQL at PORT
  187.    --socket SOCKET   Connect to MySQL at SOCKET
  188.    --no-mycnf        Don't read ~/.my.cnf
  189.    --infile FILE     Read status values from FILE instead of MySQL
  190.    --outfile FILE    Write report to FILE
  191.    --email ADDRESS   Email report to ADDRESS (doesn't work on Windows)
  192.    --flush-status    Issue FLUSH STATUS; after getting current values
  193.    --relative X      Generate relative reports. If X is an integer,
  194.                      reports are live from the MySQL server X seconds apart.
  195.                      If X is a list of infiles (file1 file2 etc.),
  196.                      reports are generated from the infiles in the order
  197.                      that they are given.
  198.    --report-count N  Collect N number of live relative reports (default 1)
  199.    --detach          Fork and detach from terminal (run in background)
  200.    --help            Prints this
  201.    --debug           Print debugging information
  202.  
  203. Visit http://hackmysql.com/mysqlreport for more information.
  204. HELP
  205.  
  206.    exit;
  207. }
  208.  
  209. sub get_user_mycnf
  210. {
  211.    print "get_user_mycnf\n" if $op{debug};
  212.  
  213.    return if $WIN;
  214.    open MYCNF, "$ENV{HOME}/.my.cnf" or return;
  215.    while(<MYCNF>)
  216.    {
  217.       if(/^(.+?)\s*=\s*"?(.+?)"?\s*$/)
  218.       {
  219.          $mycnf{$1} = $2;
  220.          print "get_user_mycnf: read '$1 = $2'\n" if $op{debug};
  221.       }
  222.    }
  223.    $mycnf{'pass'} ||= $mycnf{'password'} if exists $mycnf{'password'};
  224.    close MYCNF;
  225. }
  226.  
  227. sub connect_to_MySQL
  228. {
  229.    print "connect_to_MySQL\n" if $op{debug};
  230.  
  231.    my $dsn;
  232.  
  233.    if($mycnf{'socket'} && -S $mycnf{'socket'})
  234.    {
  235.       $dsn = "DBI:mysql:mysql_socket=$mycnf{socket}";
  236.    }
  237.    elsif($mycnf{'host'})
  238.    {
  239.       $dsn = "DBI:mysql:host=$mycnf{host}" . ($mycnf{port} ? ";port=$mycnf{port}" : "");
  240.    }
  241.    else
  242.    {
  243.       $dsn = "DBI:mysql:host=localhost";
  244.    }
  245.  
  246.    print "connect_to_MySQL: DBI DSN: $dsn\n" if $op{debug};
  247.  
  248.    $dbh = DBI->connect($dsn, $mycnf{'user'}, $mycnf{'pass'}) or die;
  249. }
  250.  
  251. sub collect_reports
  252. {
  253.    print "collect_reports\n" if $op{debug};
  254.  
  255.    my $i;
  256.  
  257.    get_vals();
  258.    get_vars();
  259.  
  260.    get_Com_values();
  261.  
  262.    %stats_past = %stats;
  263.  
  264.    set_myisam_vals();
  265.    set_ib_vals() if $have_innodb_vals;
  266.  
  267.    print "#\n# Beginning report, 0 0:0:0\n#\n";
  268.  
  269.    write_report();
  270.  
  271.    for($i = 0; $i < $op{'c'}; $i++)
  272.    {
  273.       $dbh->disconnect();
  274.  
  275.       sleep($op{'r'});
  276.  
  277.       connect_to_MySQL();
  278.  
  279.       print "\n#\n# Interval report " , $i + 1 , ", +", sec_to_dhms(($i + 1) * $op{'r'}), "\n#\n";
  280.  
  281.       get_vals();
  282.  
  283.       write_relative_report();
  284.    }
  285. }
  286.  
  287. sub read_relative_infiles
  288. {
  289.    print "read_relative_infiles\n" if $op{debug};
  290.  
  291.    my $slurp;    # Used to check infiles for multiple sets of status values
  292.    my $n_stats;  # Number of multiple sets of status values in an infile
  293.    my $infile;
  294.    my $report_n; # Report number
  295.  
  296.    $report_n = 1;
  297.  
  298.    foreach $infile (@ARGV)
  299.    {
  300.       # Read all of infile into $slurp
  301.       open INFILE, "< $infile" or warn and next;
  302.       $slurp = do { local $/;  <INFILE> };
  303.       close INFILE;
  304.  
  305.       $n_stats = 0;
  306.  
  307.       # Count number of status value sets
  308.       $n_stats++ while $slurp =~ /Aborted_clients/g;
  309.  
  310.       print "read_relative_infiles: found $n_stats sets of status values in file '$infile'\n"
  311.          if $op{debug};
  312.  
  313.       if($n_stats == 1)
  314.       {
  315.          read_infile($infile);
  316.          relative_infile_report($report_n++);
  317.       }
  318.  
  319.       if($n_stats > 1)
  320.       {
  321.          my @tmpfile_fh;
  322.          my @tmpfile_name;
  323.          my $i;
  324.          my $stat_n;  # Status value set number
  325.  
  326.          # Create a tmp file for each set of status values
  327.          for($i = 0; $i < $n_stats; $i++)
  328.          {
  329.             my ($fh, $name) = tempfile()
  330.                or die "read_relative_infiles: cannot open temporary file for writing: $!\n";
  331.  
  332.             push(@tmpfile_fh, $fh);
  333.             push(@tmpfile_name, $name);
  334.  
  335.             print "read_relative_infiles: created tmp file '$name' for set $i\n" if $op{debug};
  336.          }
  337.  
  338.          $i = 0;
  339.          $stat_n = 0;
  340.  
  341.          select $tmpfile_fh[$i];
  342.  
  343.          # Read infile again and copy each set of status values to seperate tmp files
  344.          open INFILE, "< $infile" or warn and next;
  345.          while(<INFILE>)
  346.          {
  347.             next if /^\+/;
  348.             next if /^$/;
  349.  
  350.             # The infile must begin with the system variable values.
  351.             # Therefore, the first occurance of Aborted_clients indicates the beginning
  352.             # of the first set of status values if no sets have occured yet ($stat_n == 0).
  353.             # In this case, the following status values are printed to the current fh,
  354.             # along with the system variable values read thus far, until Aborted_clients
  355.             # occurs again. Then begins the second and subsequent sets of status values.
  356.  
  357.             if(/Aborted_clients/)
  358.             {
  359.                print and next if $stat_n++ == 0;
  360.                select $tmpfile_fh[++$i];
  361.             }
  362.  
  363.             print;
  364.          }
  365.          close INFILE;
  366.  
  367.          # Re-select the main tmp file into which the reports are being written.
  368.          select $tmpfile_fh;
  369.  
  370.          for($i = 0; $i < $n_stats; $i++)
  371.          {
  372.             close $tmpfile_fh[$i];
  373.  
  374.             print "read_relative_infiles: reading set $i tmp file '$tmpfile_name[$i]'\n"
  375.                if $op{debug};
  376.  
  377.             read_infile($tmpfile_name[$i]);
  378.             relative_infile_report($report_n++);
  379.  
  380.             if($WIN) { `del $tmpfile_name[$i]`;   }
  381.             else     { `rm -f $tmpfile_name[$i]`; }
  382.  
  383.             print "read_relative_infiles: deleted set $i tmp file '$tmpfile_name[$i]'\n"
  384.                if $op{debug};
  385.          }
  386.  
  387.       } # if($n_stats > 1)
  388.    } # foreach $infile (@files)
  389. }
  390.  
  391. sub relative_infile_report
  392. {
  393.    print "relative_infile_report\n" if $op{debug};
  394.  
  395.    my $report_n = shift;
  396.  
  397.    if($report_n == 1)
  398.    {
  399.       get_Com_values();
  400.  
  401.       %stats_past = %stats;
  402.  
  403.       set_myisam_vals();
  404.       set_ib_vals() if $have_innodb_vals;
  405.  
  406.       print "#\n# Beginning report, 0 0:0:0\n#\n";
  407.  
  408.       write_report();
  409.    }
  410.    else
  411.    {
  412.       print "\n#\n# Interval report ", $report_n - 1, ", +",
  413.          sec_to_dhms($stats{Uptime} - $stats_past{Uptime}),
  414.          "\n#\n";
  415.  
  416.       write_relative_report();
  417.    }
  418. }
  419.  
  420. sub get_vals
  421. {
  422.    print "get_vals\n" if $op{debug};
  423.  
  424.    my @row;
  425.  
  426.    # Get status values
  427.    if($MySQL_version >= 50002)
  428.    {
  429.       $query = $dbh->prepare("SHOW GLOBAL STATUS;");
  430.    }
  431.    else
  432.    {
  433.       $query = $dbh->prepare("SHOW STATUS;");
  434.    }
  435.    $query->execute();
  436.    while(@row = $query->fetchrow_array()) { $stats{$row[0]} = $row[1]; }
  437.  
  438.    $real_uptime = $stats{'Uptime'};
  439. }
  440.  
  441. sub get_vars
  442. {
  443.    print "get_vars\n" if $op{debug};
  444.  
  445.    my @row;
  446.  
  447.    # Get server system variables
  448.    $query = $dbh->prepare("SHOW VARIABLES;");
  449.    $query->execute();
  450.    while(@row = $query->fetchrow_array()) { $vars{$row[0]} = $row[1]; }
  451.  
  452.    # table_cache was renamed to table_open_cache in MySQL 5.1.3
  453.    if($MySQL_version >= 50103)
  454.    {
  455.       $vars{'table_cache'} = $vars{'table_open_cache'};
  456.    }
  457. }
  458.  
  459. sub read_infile
  460. {
  461.    print "read_infile\n" if $op{debug};
  462.  
  463.    my $infile = shift;
  464.  
  465.    # Default required system variable values if not set in INFILE.
  466.    # As of mysqlreport v3.5 the direct output from SHOW VARIABLES;
  467.    # can be put into INFILE instead. See http://hackmysql.com/mysqlreportdoc
  468.    # for details.
  469.    $vars{'version'} = "0.0.0"         if !exists $vars{'version'};
  470.    $vars{'table_cache'} = 64          if !exists $vars{'table_cache'};
  471.    $vars{'max_connections'} = 100     if !exists $vars{'max_connections'};
  472.    $vars{'key_buffer_size'} = 8388600 if !exists $vars{'key_buffer_size'}; # 8M
  473.    $vars{'thread_cache_size'} = 0     if !exists $vars{'thread_cache_size'};
  474.    $vars{'tmp_table_size'} = 0        if !exists $vars{'tmp_table_size'};
  475.    $vars{'long_query_time'} = '?'     if !exists $vars{'long_query_time'};
  476.    $vars{'log_slow_queries'} = '?'    if !exists $vars{'log_slow_queries'};
  477.  
  478.    # One should also add:
  479.    #    key_cache_block_size
  480.    #    query_cache_size
  481.    # to INFILE if needed.
  482.  
  483.    open INFILE, "< $infile" or die "Cannot open INFILE '$infile': $!\n";
  484.  
  485.    while(<INFILE>)
  486.    {
  487.       last if !defined $_;
  488.  
  489.       next if /^\+/;  # skip divider lines
  490.       next if /^$/;   # skip blank lines
  491.  
  492.       next until /(Aborted_clients|back_log|=)/;
  493.  
  494.       if($1 eq 'Aborted_clients')  # status values
  495.       {
  496.          print "read_infile: start stats\n" if $op{debug};
  497.  
  498.          while($_)
  499.          {
  500.             chomp;
  501.             if(/([A-Za-z_]+)[\s\t|]+(\d+)/)
  502.             {
  503.                $stats{$1} = $2;
  504.                print "read_infile: save $1 = $2\n" if $op{debug};
  505.             }
  506.             else { print "read_infile: ignore '$_'\n" if $op{debug}; }
  507.  
  508.             last if $1 eq 'Uptime';  # exit while() if end of status values
  509.             $_ = <INFILE>; # otherwise, read next line of status values
  510.          }
  511.       }
  512.       elsif($1 eq  'back_log')  # system variable values
  513.       {
  514.          print "read_infile: start vars\n" if $op{debug};
  515.  
  516.          while($_)
  517.          {
  518.             chomp;
  519.             if(/([A-Za-z_]+)[\s\t|]+([\w\.\-]+)/)  # This will exclude some vars
  520.             {                                      # like pid_file which we don't need
  521.                $vars{$1} = $2;
  522.                print "read_infile: save $1 = $2\n" if $op{debug};
  523.             }
  524.             else { print "read_infile: ignore '$_'\n" if $op{debug}; }
  525.  
  526.             last if $1 eq 'wait_timeout';  # exit while() if end of vars
  527.             $_ = <INFILE>; # otherwise, read next line of vars
  528.          }
  529.       }
  530.       elsif($1 eq '=')  # old style, manually added system variable values
  531.       {
  532.          print "read_infile: start old vars\n" if $op{debug};
  533.  
  534.          while($_ && $_ =~ /=/)
  535.          {
  536.             chomp;
  537.             if(/^\s*(\w+)\s*=\s*([0-9.]+)(M*)\s*$/)  # e.g.: key_buffer_size = 128M
  538.             {
  539.                $vars{$1} = ($3 ? $2 * 1024 * 1024 : $2);
  540.                print "read_infile: read '$_' as $1 = $vars{$1}\n" if $op{debug};
  541.             }
  542.             else { print "read_infile: ignore '$_'\n" if $op{debug}; }
  543.  
  544.             $_ = <INFILE>; # otherwise, read next line of old vars
  545.          }
  546.  
  547.          redo;
  548.       }
  549.       else
  550.       {
  551.          print "read_infile: unrecognized line: '$_'\n" if $op{debug};
  552.       }
  553.    }
  554.  
  555.    close INFILE;
  556.  
  557.    $real_uptime = $stats{'Uptime'};
  558.  
  559.    $vars{'table_cache'} = $vars{'table_open_cache'} if exists $vars{'table_open_cache'};
  560.  
  561.    get_MySQL_version();
  562. }
  563.  
  564. sub get_MySQL_version
  565. {
  566.    print "get_MySQL_version\n" if $op{debug};
  567.  
  568.    return if $MySQL_version;
  569.  
  570.    my ($major, $minor, $patch);
  571.  
  572.    if($op{'infile'} || $relative_infiles)
  573.    {
  574.       ($major, $minor, $patch) = ($vars{'version'} =~ /(\d{1,2})\.(\d{1,2})\.(\d{1,2})/);
  575.    }
  576.    else
  577.    {
  578.       my @row;
  579.  
  580.       $query = $dbh->prepare("SHOW VARIABLES LIKE 'version';");
  581.       $query->execute();
  582.       @row = $query->fetchrow_array();
  583.       ($major, $minor, $patch) = ($row[1] =~ /(\d{1,2})\.(\d{1,2})\.(\d{1,2})/);
  584.    }
  585.  
  586.    $MySQL_version = sprintf("%d%02d%02d", $major, $minor, $patch);
  587.  
  588.    # Innodb_ status values were added in 5.0.2
  589.    if($MySQL_version < 50002)
  590.    {
  591.       $have_innodb_vals = 0;
  592.       print "get_MySQL_version: no InnoDB reports because MySQL version is older than 5.0.2\n" if $op{debug};
  593.    }
  594. }
  595.  
  596. sub set_myisam_vals
  597. {
  598.    print "set_myisam_vals\n" if $op{debug};
  599.  
  600.    $questions = $stats{'Questions'};
  601.  
  602.    $key_read_ratio = sprintf "%.2f",
  603.                      ($stats{'Key_read_requests'} ?
  604.                       100 - ($stats{'Key_reads'} / $stats{'Key_read_requests'}) * 100 :
  605.                       0);
  606.  
  607.    $key_write_ratio = sprintf "%.2f",
  608.                       ($stats{'Key_write_requests'} ?
  609.                        100 - ($stats{'Key_writes'} / $stats{'Key_write_requests'}) * 100 :
  610.                        0);
  611.  
  612.    $key_cache_block_size = (defined $vars{'key_cache_block_size'} ?
  613.                             $vars{'key_cache_block_size'} :
  614.                             1024);
  615.  
  616.    $key_buffer_used = $stats{'Key_blocks_used'} * $key_cache_block_size;
  617.  
  618.    if(defined $stats{'Key_blocks_unused'}) # MySQL 4.1.2+
  619.    {
  620.       $key_buffer_usage =  $vars{'key_buffer_size'} -
  621.                            ($stats{'Key_blocks_unused'} * $key_cache_block_size);
  622.    }
  623.    else { $key_buffer_usage = -1; }
  624.  
  625.    # Data Manipulation Statements: http://dev.mysql.com/doc/refman/5.0/en/data-manipulation.html
  626.    %DMS_vals =
  627.    (
  628.       SELECT  => $stats{'Com_select'},
  629.       INSERT  => $stats{'Com_insert'}  + $stats{'Com_insert_select'},
  630.       REPLACE => $stats{'Com_replace'} + $stats{'Com_replace_select'},
  631.       UPDATE  => $stats{'Com_update'}  +
  632.                  (exists $stats{'Com_update_multi'} ? $stats{'Com_update_multi'} : 0),
  633.       DELETE  => $stats{'Com_delete'}  +
  634.                  (exists $stats{'Com_delete_multi'} ? $stats{'Com_delete_multi'} : 0)
  635.    );
  636.  
  637.    $dms = $DMS_vals{SELECT} + $DMS_vals{INSERT} + $DMS_vals{REPLACE} + $DMS_vals{UPDATE} + $DMS_vals{DELETE};
  638.  
  639.    $slow_query_t = format_u_time($vars{long_query_time});
  640.  
  641. }
  642.  
  643. sub set_ib_vals
  644. {
  645.    print "set_ib_vals\n" if $op{debug};
  646.  
  647.    $ib_bp_used  = ($stats{'Innodb_buffer_pool_pages_total'} -
  648.                    $stats{'Innodb_buffer_pool_pages_free'}) *
  649.                    $stats{'Innodb_page_size'};
  650.  
  651.    $ib_bp_total = $stats{'Innodb_buffer_pool_pages_total'} * $stats{'Innodb_page_size'};
  652.  
  653.    $ib_bp_read_ratio = sprintf "%.2f",
  654.                        ($stats{'Innodb_buffer_pool_read_requests'} ?
  655.                         100 - ($stats{'Innodb_buffer_pool_reads'} /
  656.                            $stats{'Innodb_buffer_pool_read_requests'}) * 100 :
  657.                         0);
  658. }
  659.  
  660. sub write_relative_report
  661. {
  662.    print "write_relative_report\n" if $op{debug};
  663.  
  664.    %stats_present = %stats;
  665.  
  666.    for(keys %stats)
  667.    {
  668.       if($stats_past{$_} =~ /\d+/)
  669.       {
  670.          if($stats_present{$_} >= $stats_past{$_}) # Avoid negative values
  671.          {
  672.             $stats{$_} = $stats_present{$_} - $stats_past{$_};
  673.          }
  674.       }
  675.    }
  676.  
  677.    # These values are either "at present" or "high water marks".
  678.    # Therefore, it is more logical to not relativize these values.
  679.    # Doing otherwise causes strange and misleading values.
  680.    $stats{'Key_blocks_used'}      = $stats_present{'Key_blocks_used'};
  681.    $stats{'Open_tables'}          = $stats_present{'Open_tables'};
  682.    $stats{'Max_used_connections'} = $stats_present{'Max_used_connections'};
  683.    $stats{'Threads_running'}      = $stats_present{'Threads_running'};
  684.    $stats{'Threads_connected'}    = $stats_present{'Threads_connected'};
  685.    $stats{'Threads_cached'}       = $stats_present{'Threads_cached'};
  686.    $stats{'Qcache_free_blocks'}   = $stats_present{'Qcache_free_blocks'};
  687.    $stats{'Qcache_total_blocks'}  = $stats_present{'Qcache_total_blocks'};
  688.    $stats{'Qcache_free_memory'}   = $stats_present{'Qcache_free_memory'};
  689.    if($have_innodb_vals)
  690.    {
  691.       $stats{'Innodb_page_size'}                 = $stats_present{'Innodb_page_size'};
  692.       $stats{'Innodb_buffer_pool_pages_data'}    = $stats_present{'Innodb_buffer_pool_pages_data'};
  693.       $stats{'Innodb_buffer_pool_pages_dirty'}   = $stats_present{'Innodb_buffer_pool_pages_dirty'};
  694.       $stats{'Innodb_buffer_pool_pages_free'}    = $stats_present{'Innodb_buffer_pool_pages_free'};
  695.       $stats{'Innodb_buffer_pool_pages_latched'} = $stats_present{'Innodb_buffer_pool_pages_latched'};
  696.       $stats{'Innodb_buffer_pool_pages_misc'}    = $stats_present{'Innodb_buffer_pool_pages_misc'};
  697.       $stats{'Innodb_buffer_pool_pages_total'}   = $stats_present{'Innodb_buffer_pool_pages_total'};
  698.       $stats{'Innodb_data_pending_fsyncs'}       = $stats_present{'Innodb_data_pending_fsyncs'};
  699.       $stats{'Innodb_data_pending_reads'}        = $stats_present{'Innodb_data_pending_reads'};
  700.       $stats{'Innodb_data_pending_writes'}       = $stats_present{'Innodb_data_pending_writes'};
  701.  
  702.       # Innodb_row_lock_ values were added in MySQL 5.0.3
  703.       if($MySQL_version >= 50003)
  704.       {
  705.          $stats{'Innodb_row_lock_current_waits'} = $stats_present{'Innodb_row_lock_current_waits'};
  706.          $stats{'Innodb_row_lock_time_avg'}      = $stats_present{'Innodb_row_lock_time_avg'};
  707.          $stats{'Innodb_row_lock_time_max'}      = $stats_present{'Innodb_row_lock_time_max'};
  708.       }
  709.    }
  710.  
  711.    get_Com_values();
  712.  
  713.    %stats_past = %stats_present;
  714.  
  715.    set_myisam_vals();
  716.    set_ib_vals() if $have_innodb_vals;
  717.  
  718.    write_report();
  719. }
  720.  
  721. sub write_report
  722. {
  723.    print "write_report\n" if $op{debug};
  724.  
  725.    $~ = 'MYSQL_TIME', write;
  726.    $~ = 'KEY_BUFF_MAX', write;
  727.    if($key_buffer_usage != -1) { $~ = 'KEY_BUFF_USAGE', write }
  728.    $~ = 'KEY_RATIOS', write;
  729.    write_DTQ();
  730.    $~ = 'SLOW_DMS', write;
  731.    write_DMS();
  732.    write_Com();
  733.    $~ = 'SAS', write;
  734.    write_qcache();
  735.    $~ = 'REPORT_END', write;
  736.    $~ = 'TAB', write;
  737.  
  738.    write_InnoDB() if $have_innodb_vals;
  739. }
  740.  
  741. sub sec_to_dhms # Seconds to days hours:minutes:seconds
  742. {
  743.    my $s = shift;
  744.    my ($d, $h, $m) = (0, 0, 0);
  745.  
  746.    return '0 0:0:0' if $s <= 0;
  747.  
  748.    if($s >= 86400)
  749.    {
  750.       $d = int $s / 86400;
  751.       $s -= $d * 86400;
  752.    }
  753.  
  754.    if($s >= 3600)
  755.    {
  756.      $h = int $s / 3600;
  757.      $s -= $h * 3600;
  758.    }
  759.    
  760.    $m = int $s / 60;
  761.    $s -= $m * 60;
  762.    
  763.    return "$d $h:$m:$s";
  764. }
  765.  
  766. sub make_short
  767. {
  768.    my ($number, $kb, $d) = @_;
  769.    my $n = 0;
  770.    my $short;
  771.  
  772.    $d ||= 2;
  773.  
  774.    if($kb) { while ($number > 1023) { $number /= 1024; $n++; }; }
  775.    else { while ($number > 999) { $number /= 1000; $n++; }; }
  776.  
  777.    $short = sprintf "%.${d}f%s", $number, ('','k','M','G','T')[$n];
  778.    if($short =~ /^(.+)\.(00)$/) { return $1; } # 12.00 -> 12 but not 12.00k -> 12k
  779.  
  780.    return $short;
  781. }
  782.  
  783. # What began as a simple but great idea has become the new standard:
  784. # long_query_time in microseconds. For MySQL 5.1.21+ and 6.0.4+ this
  785. # is now standard. For 4.1 and 5.0 patches, the architects of this
  786. # idea provide: http://www.mysqlperformanceblog.com/mysql-patches/
  787. # Relevant notes in MySQL manual:
  788. # http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
  789. # http://dev.mysql.com/doc/refman/6.0/en/slow-query-log.html
  790. #
  791. # The format_u_time sub simply beautifies long_query_time.
  792.  
  793. sub format_u_time  # format microsecond (µ) time value
  794. {
  795.    # 0.000000 - 0.000999 = 0 - 999 µ
  796.    # 0.001000 - 0.999999 = 1 ms - 999.999 ms
  797.    # 1.000000 - n.nnnnnn = 1 s - n.nnnnn s
  798.  
  799.    my $t = shift;
  800.    my $f;  # formatted µ time
  801.    my $u = chr(($WIN ? 230 : 181));
  802.  
  803.    $t = 0 if $t < 0;
  804.  
  805.    if($t > 0 && $t <= 0.000999)
  806.    {
  807.       $f = ($t * 1000000) . " $u";
  808.    }
  809.    elsif($t >= 0.001000 && $t <= 0.999999)
  810.    {
  811.       $f = ($t * 1000) . ' ms';
  812.    }
  813.    elsif($t >= 1)
  814.    {
  815.       $f = ($t * 1) . ' s';  # * 1 to remove insignificant zeros
  816.    }
  817.    else
  818.    {
  819.       $f = 0;  # $t should = 0 at this point
  820.    }
  821.  
  822.    return $f;
  823. }
  824.  
  825. sub perc # Percentage
  826. {
  827.    my($is, $of) = @_;
  828.    return sprintf "%.2f", ($is * 100) / ($of ||= 1);
  829. }
  830.  
  831. sub t # Time average per second
  832. {
  833.    my $val = shift;
  834.    return 0 if !$val;
  835.    return(make_short($val / $stats{'Uptime'}, 0, 1));
  836. }
  837.  
  838. sub email_report # Email given report to $op{'email'}
  839. {
  840.    print "email_report\n" if $op{debug};
  841.  
  842.    return if $WIN;
  843.  
  844.    my $report = shift;
  845.  
  846.    open SENDMAIL, "|/usr/sbin/sendmail -t";
  847.    print SENDMAIL "From: mysqlreport\n";
  848.    print SENDMAIL "To: $op{email}\n";
  849.    print SENDMAIL "Subject: MySQL status report on " . ($mycnf{'host'} || 'localhost') . "\n\n";
  850.    print SENDMAIL `cat $report`;
  851.    close SENDMAIL;
  852. }
  853.  
  854. sub cat_report # Print given report to screen
  855. {
  856.    print "cat_report\n" if $op{debug};
  857.  
  858.    my $report = shift;
  859.    my @report;
  860.  
  861.    open REPORT, "< $report";
  862.    @report = <REPORT>;
  863.    close REPORT;
  864.    print @report;
  865. }
  866.  
  867. sub get_Com_values
  868. {
  869.    print "get_Com_values\n" if $op{debug};
  870.  
  871.    %Com_vals = ();
  872.  
  873.    # Make copy of just the Com_ values
  874.    for(keys %stats)
  875.    {
  876.       if(grep /^Com_/, $_ and $stats{$_} > 0)
  877.       {
  878.          /^Com_(.*)/;
  879.          $Com_vals{$1} = $stats{$_};
  880.       }
  881.    }
  882.  
  883.    # Remove DMS values
  884.    delete $Com_vals{'select'};
  885.    delete $Com_vals{'insert'};
  886.    delete $Com_vals{'insert_select'};
  887.    delete $Com_vals{'replace'};
  888.    delete $Com_vals{'replace_select'};
  889.    delete $Com_vals{'update'};
  890.    delete $Com_vals{'update_multi'} if exists $Com_vals{'update_multi'};
  891.    delete $Com_vals{'delete'};
  892.    delete $Com_vals{'delete_multi'} if exists $Com_vals{'delete_multi'};
  893. }
  894.  
  895. sub write_DTQ # Write DTQ report in descending order by values
  896. {
  897.    print "write_DTQ\n" if $op{debug};
  898.  
  899.    $~ = 'DTQ';
  900.  
  901.    my %DTQ;
  902.    my $first = 1;
  903.  
  904.    # Total Com values
  905.    $stat_val = 0;
  906.    for(values %Com_vals) { $stat_val += $_; }
  907.    $DTQ{'Com_'} = $stat_val;
  908.  
  909.    $DTQ{'DMS'}      = $dms;
  910.    $DTQ{'QC Hits'}  = $stats{'Qcache_hits'} if $stats{'Qcache_hits'} != 0;
  911.    $DTQ{'COM_QUIT'} = int (($stats{'Connections'} - 2) - ($stats{'Aborted_clients'} / 2));
  912.  
  913.    $stat_val = 0;
  914.    for(values %DTQ) { $stat_val += $_; }
  915.    if($questions != $stat_val)
  916.    {
  917.       $DTQ{($questions > $stat_val ? '+Unknown' : '-Unknown')} = abs $questions - $stat_val;
  918.    }
  919.  
  920.    for(sort { $DTQ{$b} <=> $DTQ{$a} } keys(%DTQ))
  921.    {
  922.       if($first) { $stat_label = '%Total:'; $first = 0; }
  923.       else       { $stat_label = ''; }
  924.  
  925.       $stat_name = $_;
  926.       $stat_val  = $DTQ{$_};
  927.       write;
  928.    }
  929. }
  930.  
  931. sub write_DMS # Write DMS report in descending order by values
  932. {
  933.    print "write_DMS\n" if $op{debug};
  934.  
  935.    $~ = 'DMS';
  936.  
  937.    for(sort { $DMS_vals{$b} <=> $DMS_vals{$a} } keys(%DMS_vals))
  938.    {
  939.       $stat_name = $_;
  940.       $stat_val  = $DMS_vals{$_};
  941.       write;
  942.    }
  943. }
  944.  
  945. sub write_Com # Write COM report in descending order by values
  946. {
  947.    print "write_Com\n" if $op{debug};
  948.  
  949.    my $i = $op{'com'};
  950.  
  951.    $~ = 'COM_1';
  952.  
  953.    # Total Com values and write first line of COM report
  954.    $stat_label = '%Total:' unless $op{'dtq'};
  955.    $stat_val   = 0;
  956.    for(values %Com_vals) { $stat_val += $_; }
  957.    write;
  958.  
  959.    $~ = 'COM_2';
  960.  
  961.    # Sort remaining Com values, print only the top $op{'com'} number of values
  962.    for(sort { $Com_vals{$b} <=> $Com_vals{$a} } keys(%Com_vals))
  963.    {
  964.       $stat_name = $_;
  965.       $stat_val  = $Com_vals{$_};
  966.       write;
  967.  
  968.       last if !(--$i);
  969.    }
  970. }
  971.  
  972. sub write_qcache
  973. {
  974.    print "write_qcache\n" if $op{debug};
  975.  
  976.    # Query cache was added in 4.0.1, but have_query_cache was added in 4.0.2,
  977.    # ergo this method is slightly more reliable
  978.    return if not exists $vars{'query_cache_size'};
  979.    return if $vars{'query_cache_size'} == 0;
  980.  
  981.    $qc_mem_used = $vars{'query_cache_size'} - $stats{'Qcache_free_memory'};
  982.    $qc_hi_r = sprintf "%.2f", $stats{'Qcache_hits'} / ($stats{'Qcache_inserts'} ||= 1);
  983.    $qc_ip_r = sprintf "%.2f", $stats{'Qcache_inserts'} / ($stats{'Qcache_lowmem_prunes'} ||= 1);
  984.  
  985.    $~ = 'QCACHE';
  986.    write;
  987. }
  988.  
  989. sub write_InnoDB
  990. {
  991.    print "write_InnoDB\n" if $op{debug};
  992.  
  993.    return if not defined $stats{'Innodb_page_size'};
  994.  
  995.    $~ = 'IB';
  996.    write;
  997.  
  998.    # Innodb_row_lock_ values were added in MySQL 5.0.3
  999.    if($MySQL_version >= 50003)
  1000.    {
  1001.       $~ = 'IB_LOCK';
  1002.       write;
  1003.    }
  1004.  
  1005.    # Data, Pages, Rows
  1006.    $~ = 'IB_DPR';
  1007.    write;
  1008. }
  1009.  
  1010. sub have_op
  1011. {
  1012.    my $key = shift;
  1013.    return 1 if (exists $op{$key} && $op{$key} ne '');
  1014.    return 0;
  1015. }
  1016.  
  1017. sub sig_handler
  1018. {
  1019.    print "\nReceived signal at " , scalar localtime , "\n";
  1020.    exit_tasks_and_cleanup();
  1021.    exit;
  1022. }
  1023.  
  1024. sub exit_tasks_and_cleanup
  1025. {
  1026.    print "exit_tasks_and_cleanup\n" if $op{debug};
  1027.  
  1028.    close $tmpfile_fh;
  1029.    select STDOUT unless $op{'detach'};
  1030.  
  1031.    email_report($tmpfile) if $op{'email'};
  1032.  
  1033.    cat_report($tmpfile) unless $op{'detach'};
  1034.  
  1035.    if($op{'outfile'})
  1036.    {
  1037.       if($WIN) { `move $tmpfile $op{outfile}`; }
  1038.       else     { `mv $tmpfile $op{outfile}`;   }
  1039.    }
  1040.    else
  1041.    {
  1042.       if($WIN) { `del $tmpfile`;   }
  1043.       else     { `rm -f $tmpfile`; }
  1044.    }
  1045.  
  1046.    if(!$op{'infile'} && !$relative_infiles)
  1047.    {
  1048.       if($op{'flush-status'})
  1049.       {
  1050.          $query = $dbh->prepare("FLUSH STATUS;");
  1051.          $query->execute();
  1052.       }
  1053.  
  1054.       $query->finish();
  1055.       $dbh->disconnect();
  1056.    }
  1057. }
  1058.  
  1059. #
  1060. # Formats
  1061. #
  1062.  
  1063. format MYSQL_TIME =
  1064. MySQL @<<<<<<<<<<<<<<<<  uptime @<<<<<<<<<<<   @>>>>>>>>>>>>>>>>>>>>>>>>
  1065. $vars{'version'}, sec_to_dhms($real_uptime), (($op{infile} || $relative_infiles) ? '' : scalar localtime)
  1066. .
  1067.  
  1068. format KEY_BUFF_MAX =
  1069.  
  1070. __ Key _________________________________________________________________
  1071. Buffer used   @>>>>>> of @>>>>>>  %Used: @>>>>>
  1072. make_short($key_buffer_used, 1), make_short($vars{'key_buffer_size'}, 1), perc($key_buffer_used, $vars{'key_buffer_size'})
  1073. .
  1074.  
  1075. format KEY_BUFF_USAGE =
  1076.   Current     @>>>>>>            %Usage: @>>>>>
  1077. make_short($key_buffer_usage, 1), perc($key_buffer_usage, $vars{'key_buffer_size'})
  1078. .
  1079.  
  1080. format KEY_RATIOS =
  1081. Write hit     @>>>>>%
  1082. $key_write_ratio
  1083. Read hit      @>>>>>%
  1084. $key_read_ratio
  1085.  
  1086. __ Questions ___________________________________________________________
  1087. Total       @>>>>>>>>  @>>>>>/s
  1088. make_short($questions), t($questions)
  1089. .
  1090.  
  1091. format DTQ =
  1092.   @<<<<<<<  @>>>>>>>>  @>>>>>/s  @>>>>>> @>>>>>
  1093. $stat_name, make_short($stat_val), t($stat_val), $stat_label, perc($stat_val, $questions)
  1094. .
  1095.  
  1096. format SLOW_DMS =
  1097. Slow @<<<<<<< @>>>>>>  @>>>>>/s          @>>>>>  %DMS: @>>>>>  Log: @>>
  1098. $slow_query_t, make_short($stats{'Slow_queries'}), t($stats{'Slow_queries'}), perc($stats{'Slow_queries'}, $questions), perc($stats{'Slow_queries'}, $dms), $vars{'log_slow_queries'}
  1099. DMS         @>>>>>>>>  @>>>>>/s          @>>>>>
  1100. make_short($dms), t($dms), perc($dms, $questions)
  1101. .
  1102.  
  1103. format DMS =
  1104.   @<<<<<<<  @>>>>>>>>  @>>>>>/s          @>>>>>        @>>>>>
  1105. $stat_name, make_short($stat_val), t($stat_val), perc($stat_val, $questions), perc($stat_val, $dms)
  1106. .
  1107.  
  1108. format COM_1 =
  1109. Com_        @>>>>>>>>  @>>>>>/s          @>>>>>
  1110. make_short($stat_val), t($stat_val), perc($stat_val, $questions)
  1111. .
  1112.  
  1113. format COM_2 =
  1114.   @<<<<<<<<<< @>>>>>>  @>>>>>/s          @>>>>>
  1115. $stat_name, make_short($stat_val), t($stat_val), perc($stat_val, $questions)
  1116. .
  1117.  
  1118. format SAS =
  1119.  
  1120. __ SELECT and Sort _____________________________________________________
  1121. Scan          @>>>>>>   @>>>>/s %SELECT: @>>>>>
  1122. make_short($stats{'Select_scan'}), t($stats{'Select_scan'}), perc($stats{'Select_scan'}, $stats{'Com_select'})
  1123. Range         @>>>>>>   @>>>>/s          @>>>>>
  1124. make_short($stats{'Select_range'}), t($stats{'Select_range'}), perc($stats{'Select_range'}, $stats{'Com_select'})
  1125. Full join     @>>>>>>   @>>>>/s          @>>>>>
  1126. make_short($stats{'Select_full_join'}), t($stats{'Select_full_join'}), perc($stats{'Select_full_join'}, $stats{'Com_select'})
  1127. Range check   @>>>>>>   @>>>>/s          @>>>>>
  1128. make_short($stats{'Select_range_check'}), t($stats{'Select_range_check'}), perc($stats{'Select_range_check'}, $stats{'Com_select'})
  1129. Full rng join @>>>>>>   @>>>>/s          @>>>>>
  1130. make_short($stats{'Select_full_range_join'}), t($stats{'Select_full_range_join'}), perc($stats{'Select_full_range_join'}, $stats{'Com_select'})
  1131. Sort scan     @>>>>>>   @>>>>/s
  1132. make_short($stats{'Sort_scan'}), t($stats{'Sort_scan'})
  1133. Sort range    @>>>>>>   @>>>>/s
  1134. make_short($stats{'Sort_range'}), t($stats{'Sort_range'})
  1135. Sort mrg pass @>>>>>>   @>>>>/s
  1136. make_short($stats{'Sort_merge_passes'}), t($stats{'Sort_merge_passes'})
  1137. .
  1138.  
  1139. format QCACHE =
  1140.  
  1141. __ Query Cache _________________________________________________________
  1142. Memory usage  @>>>>>> of @>>>>>>  %Used: @>>>>>
  1143. make_short($qc_mem_used, 1), make_short($vars{'query_cache_size'}, 1), perc($qc_mem_used, $vars{'query_cache_size'})
  1144. Block Fragmnt @>>>>>%
  1145. perc($stats{'Qcache_free_blocks'}, $stats{'Qcache_total_blocks'})
  1146. Hits          @>>>>>>   @>>>>/s
  1147. make_short($stats{'Qcache_hits'}), t($stats{'Qcache_hits'})
  1148. Inserts       @>>>>>>   @>>>>/s
  1149. make_short($stats{'Qcache_inserts'}), t($stats{'Qcache_inserts'})
  1150. Insrt:Prune @>>>>>>:1   @>>>>/s
  1151. make_short($qc_ip_r), t($stats{'Qcache_inserts'} - $stats{'Qcache_lowmem_prunes'})
  1152. Hit:Insert  @>>>>>>:1
  1153. $qc_hi_r, t($qc_hi_r)
  1154. .
  1155.  
  1156. # Not really the end...
  1157. format REPORT_END =
  1158.  
  1159. __ Table Locks _________________________________________________________
  1160. Waited      @>>>>>>>>  @>>>>>/s  %Total: @>>>>>
  1161. make_short($stats{'Table_locks_waited'}), t($stats{'Table_locks_waited'}), perc($stats{'Table_locks_waited'}, $stats{'Table_locks_waited'} + $stats{'Table_locks_immediate'});
  1162. Immediate   @>>>>>>>>  @>>>>>/s
  1163. make_short($stats{'Table_locks_immediate'}), t($stats{'Table_locks_immediate'})
  1164.  
  1165. __ Tables ______________________________________________________________
  1166. Open        @>>>>>>>> of @>>>    %Cache: @>>>>>
  1167. $stats{'Open_tables'}, $vars{'table_cache'}, perc($stats{'Open_tables'}, $vars{'table_cache'})
  1168. Opened      @>>>>>>>>  @>>>>>/s
  1169. make_short($stats{'Opened_tables'}), t($stats{'Opened_tables'})
  1170.  
  1171. __ Connections _________________________________________________________
  1172. Max used    @>>>>>>>> of @>>>      %Max: @>>>>>
  1173. $stats{'Max_used_connections'}, $vars{'max_connections'}, perc($stats{'Max_used_connections'}, $vars{'max_connections'})
  1174. Total       @>>>>>>>>  @>>>>>/s
  1175. make_short($stats{'Connections'}), t($stats{'Connections'})
  1176.  
  1177. __ Created Temp ________________________________________________________
  1178. Disk table  @>>>>>>>>  @>>>>>/s
  1179. make_short($stats{'Created_tmp_disk_tables'}), t($stats{'Created_tmp_disk_tables'})
  1180. Table       @>>>>>>>>  @>>>>>/s    Size: @>>>>>
  1181. make_short($stats{'Created_tmp_tables'}), t($stats{'Created_tmp_tables'}), make_short($vars{'tmp_table_size'}, 1, 1)
  1182. File        @>>>>>>>>  @>>>>>/s
  1183. make_short($stats{'Created_tmp_files'}), t($stats{'Created_tmp_files'})
  1184. .
  1185.  
  1186. format TAB =
  1187.  
  1188. __ Threads _____________________________________________________________
  1189. Running     @>>>>>>>> of @>>>
  1190. $stats{'Threads_running'}, $stats{'Threads_connected'}
  1191. Cached      @>>>>>>>> of @>>>      %Hit: @>>>>>
  1192. $stats{'Threads_cached'}, $vars{'thread_cache_size'}, make_short(100 - perc($stats{'Threads_created'}, $stats{'Connections'}))
  1193. Created     @>>>>>>>>  @>>>>>/s
  1194. make_short($stats{'Threads_created'}), t($stats{'Threads_created'})
  1195. Slow        @>>>>>>>>  @>>>>>/s
  1196. $stats{'Slow_launch_threads'}, t($stats{'Slow_launch_threads'})
  1197.  
  1198. __ Aborted _____________________________________________________________
  1199. Clients     @>>>>>>>>  @>>>>>/s
  1200. make_short($stats{'Aborted_clients'}), t($stats{'Aborted_clients'})
  1201. Connects    @>>>>>>>>  @>>>>>/s
  1202. make_short($stats{'Aborted_connects'}), t($stats{'Aborted_connects'})
  1203.  
  1204. __ Bytes _______________________________________________________________
  1205. Sent        @>>>>>>>>  @>>>>>/s
  1206. make_short($stats{'Bytes_sent'}), t($stats{'Bytes_sent'})
  1207. Received    @>>>>>>>>  @>>>>>/s
  1208. make_short($stats{'Bytes_received'}), t($stats{'Bytes_received'})
  1209. .
  1210.  
  1211. format IB =
  1212.  
  1213. __ InnoDB Buffer Pool __________________________________________________
  1214. Usage         @>>>>>> of @>>>>>>  %Used: @>>>>>
  1215. make_short($ib_bp_used, 1), make_short($ib_bp_total, 1), perc($ib_bp_used, $ib_bp_total)
  1216. Read hit      @>>>>>%
  1217. $ib_bp_read_ratio;
  1218. Pages
  1219.   Free      @>>>>>>>>            %Total: @>>>>>
  1220. make_short($stats{'Innodb_buffer_pool_pages_free'}), perc($stats{'Innodb_buffer_pool_pages_free'}, $stats{'Innodb_buffer_pool_pages_total'})
  1221.   Data      @>>>>>>>>                    @>>>>> %Drty: @>>>>>
  1222. make_short($stats{'Innodb_buffer_pool_pages_data'}), perc($stats{'Innodb_buffer_pool_pages_data'}, $stats{'Innodb_buffer_pool_pages_total'}), perc($stats{'Innodb_buffer_pool_pages_dirty'}, $stats{'Innodb_buffer_pool_pages_data'})
  1223.   Misc      @>>>>>>>>                    @>>>>>
  1224.   $stats{'Innodb_buffer_pool_pages_misc'}, perc($stats{'Innodb_buffer_pool_pages_misc'}, $stats{'Innodb_buffer_pool_pages_total'})
  1225.   Latched   @>>>>>>>>                    @>>>>>
  1226. $stats{'Innodb_buffer_pool_pages_latched'}, perc($stats{'Innodb_buffer_pool_pages_latched'}, $stats{'Innodb_buffer_pool_pages_total'})
  1227. Reads       @>>>>>>>>  @>>>>>/s  
  1228. make_short($stats{'Innodb_buffer_pool_read_requests'}), t($stats{'Innodb_buffer_pool_read_requests'})
  1229.   From file @>>>>>>>>  @>>>>>/s          @>>>>>
  1230. make_short($stats{'Innodb_buffer_pool_reads'}), t($stats{'Innodb_buffer_pool_reads'}), perc($stats{'Innodb_buffer_pool_reads'}, $stats{'Innodb_buffer_pool_read_requests'})
  1231.   Ahead Rnd @>>>>>>>>  @>>>>>/s
  1232. $stats{'Innodb_buffer_pool_read_ahead_rnd'}, t($stats{'Innodb_buffer_pool_read_ahead_rnd'})
  1233.   Ahead Sql @>>>>>>>>  @>>>>>/s
  1234. $stats{'Innodb_buffer_pool_read_ahead_seq'}, t($stats{'Innodb_buffer_pool_read_ahead_seq'})
  1235. Writes      @>>>>>>>>  @>>>>>/s
  1236. make_short($stats{'Innodb_buffer_pool_write_requests'}), t($stats{'Innodb_buffer_pool_write_requests'})
  1237. Flushes     @>>>>>>>>  @>>>>>/s
  1238. make_short($stats{'Innodb_buffer_pool_pages_flushed'}), t($stats{'Innodb_buffer_pool_pages_flushed'})
  1239. Wait Free   @>>>>>>>>  @>>>>>/s
  1240. $stats{'Innodb_buffer_pool_wait_free'}, t($stats{'Innodb_buffer_pool_wait_free'})
  1241. .
  1242.  
  1243. format IB_LOCK =
  1244.  
  1245. __ InnoDB Lock _________________________________________________________
  1246. Waits       @>>>>>>>>  @>>>>>/s
  1247. $stats{'Innodb_row_lock_waits'}, t($stats{'Innodb_row_lock_waits'})
  1248. Current     @>>>>>>>>
  1249. $stats{'Innodb_row_lock_current_waits'}
  1250. Time acquiring
  1251.   Total     @>>>>>>>> ms
  1252. $stats{'Innodb_row_lock_time'}
  1253.   Average   @>>>>>>>> ms
  1254. $stats{'Innodb_row_lock_time_avg'}
  1255.   Max       @>>>>>>>> ms
  1256. $stats{'Innodb_row_lock_time_max'}
  1257. .
  1258.  
  1259. format IB_DPR =
  1260.  
  1261. __ InnoDB Data, Pages, Rows ____________________________________________
  1262. Data
  1263.   Reads     @>>>>>>>>  @>>>>>/s
  1264. make_short($stats{'Innodb_data_reads'}), t($stats{'Innodb_data_reads'})
  1265.   Writes    @>>>>>>>>  @>>>>>/s
  1266. make_short($stats{'Innodb_data_writes'}), t($stats{'Innodb_data_writes'})
  1267.   fsync     @>>>>>>>>  @>>>>>/s
  1268. make_short($stats{'Innodb_data_fsyncs'}), t($stats{'Innodb_data_fsyncs'})
  1269.   Pending
  1270.     Reads   @>>>>>>>>
  1271. $stats{'Innodb_data_pending_reads'}, t($stats{'Innodb_data_pending_reads'})
  1272.     Writes  @>>>>>>>>
  1273. $stats{'Innodb_data_pending_writes'}, t($stats{'Innodb_data_pending_writes'})
  1274.     fsync   @>>>>>>>>
  1275. $stats{'Innodb_data_pending_fsyncs'}, t($stats{'Innodb_data_pending_fsyncs'})
  1276.  
  1277. Pages
  1278.   Created   @>>>>>>>>  @>>>>>/s
  1279. make_short($stats{'Innodb_pages_created'}), t($stats{'Innodb_pages_created'})
  1280.   Read      @>>>>>>>>  @>>>>>/s
  1281. make_short($stats{'Innodb_pages_read'}), t($stats{'Innodb_pages_read'})
  1282.   Written   @>>>>>>>>  @>>>>>/s
  1283. make_short($stats{'Innodb_pages_written'}), t($stats{'Innodb_pages_written'})
  1284.  
  1285. Rows
  1286.   Deleted   @>>>>>>>>  @>>>>>/s
  1287. make_short($stats{'Innodb_rows_deleted'}), t($stats{'Innodb_rows_deleted'})
  1288.   Inserted  @>>>>>>>>  @>>>>>/s
  1289. make_short($stats{'Innodb_rows_inserted'}), t($stats{'Innodb_rows_inserted'})
  1290.   Read      @>>>>>>>>  @>>>>>/s
  1291. make_short($stats{'Innodb_rows_read'}), t($stats{'Innodb_rows_read'})
  1292.   Updated   @>>>>>>>>  @>>>>>/s
  1293. make_short($stats{'Innodb_rows_updated'}), t($stats{'Innodb_rows_updated'})
  1294. .
  1295.  

Reply to "mysql report"

Here you can reply to the paste above