home *** CD-ROM | disk | FTP | other *** search
/ Cricao de Sites - 650 Layouts Prontos / WebMasters.iso / Servidores / xampp-win32-1.6.7-installer.exe / mysql / scripts / mysql_explain_log < prev    next >
Encoding:
Text File  |  2008-04-17  |  11.3 KB  |  414 lines
  1. #!@PERL@
  2. # Copyright (C) 2001-2003, 2006 MySQL AB
  3. # 
  4. # This program is free software; you can redistribute it and/or modify
  5. # it under the terms of the GNU General Public License as published by
  6. # the Free Software Foundation; version 2 of the License.
  7. # 
  8. # This program is distributed in the hope that it will be useful,
  9. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  10. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11. # GNU General Public License for more details.
  12. # 
  13. # You should have received a copy of the GNU General Public License
  14. # along with this program; if not, write to the Free Software
  15. # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  16.  
  17. use strict;
  18. use warnings;
  19. use DBI;
  20.  
  21. use Getopt::Long;
  22. $Getopt::Long::ignorecase=0;
  23.  
  24. my $Param={};
  25.  
  26. $Param->{host}='';
  27. $Param->{user}='';
  28. $Param->{password}='';
  29. $Param->{PrintError}=0;
  30. $Param->{socket}='';
  31.  
  32. my $help;
  33.  
  34. if (!GetOptions (
  35.          'date|d:i' => \$Param->{ViewDate},
  36.          'host|h:s' => \$Param->{host},
  37.          'user|u:s' => \$Param->{user},
  38.          'password|p:s' => \$Param->{password},
  39.          'printerror|e:s' => \$Param->{PrintError},
  40.          'socket|s:s' => \$Param->{socket},
  41.          'help|h' => \$help,
  42.         )) {
  43.   ShowOptions();
  44.   exit(0);
  45. }
  46. if (defined ($help)) {
  47.   ShowOptions();
  48.   exit(0);
  49. }
  50.  
  51.   print "explain_log    provided by http://www.mobile.de\n";
  52.   print "===========    ================================\n";
  53.  
  54.   $Param->{UpdateCount} = 0;
  55.   $Param->{SelectCount} = 0;
  56.   $Param->{IdxUseCount} = 0;
  57.   $Param->{LineCount} = 0;
  58.  
  59.   $Param->{Init} = 0;
  60.   $Param->{Field} = 0;
  61.   $Param->{Refresh} = 0;
  62.   $Param->{QueryCount} = 0;
  63.   $Param->{Statistics} =0;
  64.  
  65.   $Param->{Query} = undef;
  66.   $Param->{ALL} = undef ;
  67.   $Param->{Comment} = undef ;
  68.  
  69.   @{$Param->{Rows}} = (qw|possible_keys key type|);
  70.  
  71.   if ($Param->{ViewDate}) {
  72.     $Param->{View} = 0;
  73.   }
  74.   else {
  75.     $Param->{View} = 1;
  76.   }
  77.  
  78.   #print "Date=$Param->{ViewDate}, host=$Param->{host}, user=$Param->{user}, password=$Param->{password}\n";
  79.  
  80.   $Param->{dbh}=DBI->connect("DBI:mysql:host=$Param->{host}".($Param->{socket}?";mysql_socket=$Param->{socket}":""),$Param->{user},$Param->{password},{PrintError=>0});
  81.   if (DBI::err()) {
  82.     print "Error: " . DBI::errstr() . "\n";
  83.   }
  84.   else {
  85.     $Param->{Start} = time;
  86.     while(<>) {
  87.       $Param->{LineCount} ++ ;
  88.  
  89.       if ($Param->{ViewDate} ) {
  90.     if (m/^(\d{6})\s+\d{1,2}:\d\d:\d\d\s.*$/) { # get date
  91.       #print "# $1 #\n";
  92.       if ($1 == $Param->{ViewDate}) {
  93.         $Param->{View} = 1;
  94.       }
  95.       else {
  96.         $Param->{View} = 0;
  97.       }
  98.     }
  99.       }
  100.       if ($Param->{View} ) {
  101.     #print "->>>$_";
  102.  
  103.     if (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
  104.       #print "C-$1--$2--$3------\n";
  105.       RunQuery($Param);
  106.       if (defined $3) {
  107.         $Param->{CID}->{$2} = $3 ;
  108.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  109.       }
  110.     }
  111.  
  112.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+$/i) { # get connection ID($2) and database($3)
  113.       #print "\n <<<<<<<<<<<<<<<<<<----------------------------<<<<<<<<<<<<<<<< \n";
  114.       #print "Connect \n";
  115.       RunQuery($Param);
  116.     }
  117.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Change user .*\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
  118.       #print "C-$1--$2--$3------\n";
  119.       RunQuery($Param);
  120.       if (defined $3) {
  121.         $Param->{CID}->{$2} = $3 ;
  122.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  123.       }
  124.     }
  125.  
  126.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Quit\s+$/i) { # remove connection ID($2) and querystring
  127.       #print "Q-$1--$2--------\n";
  128.       RunQuery($Param);
  129.       delete $Param->{CID}->{$2} ;
  130.     }
  131.  
  132.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(select.+)$/i) { # get connection ID($2) and querystring
  133.       #print "S1-$1--$2--$3------\n";
  134.       RunQuery($Param);
  135.       unless ($Param->{CID}->{$2}) {
  136.         #print "Error: No Database for Handle: $2 found\n";
  137.       }
  138.       else {
  139.         $Param->{DB}=$Param->{CID}->{$2};
  140.  
  141.         my $s = "$3";
  142.         $s =~ s/from\s/from $Param->{DB}./i;
  143.         $Param->{Query}="EXPLAIN $s";
  144.  
  145.         #$s =~ m/from\s+(\w+[.]\w+)/i;
  146.         #$Param->{tab} =$1;
  147.         #print "-- $Param->{tab} -- $s --\n";
  148.       }
  149.     }
  150.  
  151.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(update.+)$/i) { # get connection ID($2) and querystring
  152.       #print "S2--$1--$2--$3------\n";
  153.       RunQuery($Param);
  154.       unless ($Param->{CID}->{$2}) {
  155.         #print "Error: No Database for Handle: $2 found\n";
  156.       }
  157.       else {
  158.         $Param->{DB}=$Param->{CID}->{$2};
  159.  
  160.         my $ud = $3;
  161.         $ud =~ m/^update\s+(\w+).+(where.+)$/i;
  162.         $Param->{Query} ="EXPLAIN SELECT * FROM $1 $2";
  163.         $Param->{Query} =~ s/from\s/from $Param->{DB}./i;
  164.  
  165.         #$Param->{Query} =~ m/from\s+(\w+[.]\w+)/i;
  166.         #$Param->{tab} =$1;
  167.       }
  168.     }
  169.  
  170.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Statistics\s+(.*)$/i) { # get connection ID($2) and info?
  171.       $Param->{Statistics} ++;
  172.       #print "Statistics--$1--$2--$3------\n";
  173.       RunQuery($Param);
  174.     }
  175.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(.+)$/i) { # get connection ID($2)
  176.       $Param->{QueryCount} ++;
  177.       #print "Query-NULL $3\n";
  178.       RunQuery($Param);
  179.     }
  180.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Refresh\s+(.+)$/i) { # get connection ID($2)
  181.       $Param->{Refresh} ++;
  182.       #print "Refresh\n";
  183.       RunQuery($Param);
  184.     }
  185.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Init\s+(.+)$/i) { # get connection ID($2)
  186.       $Param->{Init} ++;
  187.       #print "Init $3\n";
  188.       RunQuery($Param);
  189.     }
  190.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Field\s+(.+)$/i) { # get connection ID($2)
  191.       $Param->{Field} ++;
  192.       #print "Field $3\n";
  193.       RunQuery($Param);
  194.     }
  195.  
  196.     elsif (m/^\s+(.+)$/ ) { # command could be some lines ...
  197.       #print "multi-lined ($1)\n";
  198.       my ($A)=$1;
  199.        chomp $A;
  200.       $Param->{Query} .= " $1";
  201.       #print "multi-lined ($1)<<$Param->{Query}>>\n";
  202.     }
  203.  
  204.  
  205.       }
  206.  
  207.     }
  208.  
  209.     $Param->{dbh}->disconnect();
  210.  
  211.     if (1 == 0) {
  212.       print "\nunclosed handles----------------------------------------\n";
  213.       my $count=0;
  214.       foreach (sort keys %{$Param->{CID}}) {
  215.     print "$count | $_ : $Param->{CID}->{$_} \n";
  216.     $count ++;
  217.       }
  218.     }
  219.  
  220.     print "\nIndex usage ------------------------------------\n";
  221.     foreach my $t (sort keys %{$Param->{Data}}) {
  222.       print "\nTable\t$t: ---\n";
  223.       foreach my $k (sort keys %{$Param->{Data}->{$t}}) {
  224.     print " count\t$k:\n";
  225.     my %h = %{$Param->{Data}->{$t}->{$k}};
  226.       foreach (sort {$h{$a} <=> $h{$b}} keys %h) {
  227.       print "  $Param->{Data}->{$t}->{$k}->{$_}\t$_\n";
  228.     }
  229.       }
  230.     }
  231.  
  232.     $Param->{AllCount}=0;
  233.     print "\nQueries causing table scans -------------------\n\n";
  234.     foreach (@{$Param->{ALL}}) {
  235.       $Param->{AllCount} ++;
  236.       print "$_\n";
  237.     }
  238.     print "Sum: $Param->{AllCount} table scans\n";
  239.  
  240.     print "\nSummary ---------------------------------------\n\n";
  241.     print "Select: \t$Param->{SelectCount} queries\n";
  242.     print "Update: \t$Param->{UpdateCount} queries\n";
  243.     print "\n";
  244.  
  245.     print "Init:   \t$Param->{Init} times\n";
  246.     print "Field:  \t$Param->{Field} times\n";
  247.     print "Refresh: \t$Param->{Refresh} times\n";
  248.     print "Query:  \t$Param->{QueryCount} times\n";
  249.     print "Statistics:\t$Param->{Statistics} times\n";
  250.     print "\n";
  251.  
  252.     print "Logfile: \t$Param->{LineCount} lines\n";
  253.     print "Started:  \t".localtime($Param->{Start})."\n";
  254.     print "Finished:   \t".localtime(time)."\n";
  255.  
  256.   }
  257.  
  258.  
  259. ###########################################################################
  260. #
  261. #
  262. #
  263. sub RunQuery {
  264.   my $Param = shift ;
  265.  
  266.   if (defined $Param->{Query}) {
  267.     if (defined $Param->{DB} ) {
  268.  
  269.       $Param->{Query} =~ m/from\s+(\w+[.]\w+|\w+)/i;
  270.       $Param->{tab} =$1;
  271.       #print "||$Param->{tab} -- $Param->{Query}\n";
  272.  
  273.       my $sth=$Param->{dbh}->prepare("USE $Param->{DB}");
  274.       if (DBI::err()) {
  275.     if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  276.       }
  277.       else {
  278.     $sth->execute();
  279.     if (DBI::err()) {
  280.       if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  281.     }
  282.     else {
  283.       $sth->finish();
  284.  
  285.       $sth=$Param->{dbh}->prepare($Param->{Query});
  286.       if (DBI::err()) {
  287.         if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  288.       }
  289.       else {
  290.         #print "$Param->{Query}\n";
  291.         $sth->execute();
  292.         if (DBI::err()) {
  293.           if ($Param->{PrintError}) {print "[$Param->{LineCount}]<<$Param->{Query}>>\n";}
  294.           if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  295.         }
  296.         else {
  297.           my $row = undef;
  298.           while ($row = $sth->fetchrow_hashref()) {
  299.         $Param->{SelectCount} ++;
  300.  
  301.         if (defined $row->{Comment}) {
  302.           push (@{$Param->{Comment}}, "$row->{Comment}; $_; $Param->{DB}; $Param->{Query}");
  303.         }
  304.         foreach (@{$Param->{Rows}}) {
  305.           if (defined $row->{$_}) {
  306.             #if (($_ eq 'type' ) and ($row->{$_} eq 'ALL')) {
  307.             if ($row->{type} eq 'ALL') {
  308.               push (@{$Param->{ALL}}, "$Param->{Query}");
  309.               #print ">> $row->{$_} $_ $Param->{DB} $Param->{Query}\n";
  310.             }
  311.             $Param->{IdxUseCount} ++;
  312.             $Param->{Data}->{$Param->{tab}}->{$_}->{$row->{$_}} ++;
  313.           }
  314.         }
  315.           }
  316.         }
  317.       }
  318.     }
  319.       }
  320.       $sth->finish();
  321.     }
  322.     $Param->{Query} = undef ;
  323.   }
  324. }
  325.  
  326. ###########################################################################
  327. #
  328. #
  329. #
  330. sub ShowOptions {
  331.   print <<EOF;
  332. Usage: $0 [OPTIONS] < LOGFILE
  333.  
  334. --help, -h
  335.     Display this help message
  336. --date=YYMMDD, -d=YYMMDD
  337.     Select entries from the log only for the given date
  338. --host=HOSTNAME, -h=HOSTNAME
  339.     Connect to the MySQL server on the given host
  340. --user=USERNAME, -u=USERNAME
  341.     The MySQL username to use when connecting to the server
  342. --password=PASSWORD, -p=PASSWORD
  343.     The password to use when connecting to the server
  344. --socket=SOCKET, -s=SOCKET
  345.     The socket file to use when connecting to the server
  346. --printerror=1, -e 1
  347.     Enable error output
  348.  
  349. mysql_explain_log reads its standard input for query log contents. It
  350. uses EXPLAIN to analyze SELECT statements found in the input. UPDATE
  351. statements are rewritten to SELECT statements and also analyzed with
  352. EXPLAIN. mysql_explain_log then displays a summary of its results.
  353. Results are printed to the standard output.
  354. EOF
  355. }
  356.  
  357. 1;
  358.  
  359. __END__
  360.  
  361. =pod
  362.  
  363. =head1 NAME
  364.  
  365. mysql_explain_log
  366.  
  367. Feed a mysqld general query logfile (created with mysqld --log) back
  368. into mysql and collect statistics about index usage with EXPLAIN.
  369.  
  370. =head1 DISCUSSION
  371.  
  372. To optimize your indexes, you must know which ones are actually used
  373. and what kind of queries are causing table scans. This may not be easy,
  374. especially if you are generating your queries dynamically and you have
  375. a huge number of queries being executed.
  376.  
  377. Use this tool to take a look at the effects of your real life queries.
  378. Then add indexes to avoid table scans and remove indexes that aren't used.
  379.  
  380. =head1 USAGE
  381.  
  382. mysql_explain_log [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] [--socket=/path/to/socket] [--printerror=1] < logfile
  383.  
  384. --help, -h
  385.     Display this help message
  386. --date=YYMMDD, -d=YYMMDD
  387.     Select entries from the log only for the given date
  388. --host=HOSTNAME, -h=HOSTNAME
  389.     Connect to the MySQL server on the given host
  390. --user=USERNAME, -u=USERNAME
  391.     The MySQL username to use when connecting to the server
  392. --password=PASSWORD, -p=PASSWORD
  393.     The password to use when connecting to the server
  394. --socket=SOCKET, -s=SOCKET
  395.     The socket file to use when connecting to the server
  396. --printerror=1, -e 1
  397.     Enable error output
  398.  
  399. =head1 EXAMPLE
  400.  
  401. mysql_explain_log --host=localhost --user=foo --password=bar < /var/lib/mysql/mobile.log
  402.  
  403. =head1 AUTHORS
  404.  
  405.   Stefan Nitz
  406.   Jan Willamowius <jan@willamowius.de>, http://www.willamowius.de
  407.   Dennis Haney <davh@davh.dk> (Added socket support)
  408.  
  409. =head1 SEE ALSO
  410.  
  411. mysql documentation
  412.  
  413. =cut
  414.