home *** CD-ROM | disk | FTP | other *** search
/ Internet Magazine 2003 September / INTERNET107.ISO / pc / software / windows / building / mysql / data1.cab / Development / scripts / mysql_explain_log.sh < prev    next >
Encoding:
Text File  |  2003-05-17  |  9.9 KB  |  384 lines
  1. #!@PERL@ -w
  2. use strict;
  3. use DBI;
  4.  
  5. use Getopt::Long;
  6. $Getopt::Long::ignorecase=0;
  7.  
  8. print "explain_log    provided by http://www.mobile.de\n";
  9. print "===========    ================================\n";
  10.  
  11. my $Param={};
  12.  
  13. $Param->{host}='';
  14. $Param->{user}='';
  15. $Param->{password}='';
  16. $Param->{PrintError}=0;
  17.  
  18. if (!GetOptions ('date|d:i' => \$Param->{ViewDate},
  19.          'host|h:s' => \$Param->{host},
  20.          'user|u:s' => \$Param->{user},
  21.          'password|p:s' => \$Param->{password},
  22.          'printerror|e:s' => \$Param->{PrintError},
  23.         )) {
  24.   ShowOptions();
  25. }
  26. else {
  27.   $Param->{UpdateCount} = 0;
  28.   $Param->{SelectCount} = 0;
  29.   $Param->{IdxUseCount} = 0;
  30.   $Param->{LineCount} = 0;
  31.  
  32.   $Param->{Init} = 0;
  33.   $Param->{Field} = 0;
  34.   $Param->{Refresh} = 0;
  35.   $Param->{QueryCount} = 0;
  36.   $Param->{Statistics} =0;
  37.  
  38.   $Param->{Query} = undef;
  39.   $Param->{ALL} = undef ;
  40.   $Param->{Comment} = undef ;
  41.  
  42.   @{$Param->{Rows}} = (qw|possible_keys key type|);
  43.  
  44.   if ($Param->{ViewDate}) {
  45.     $Param->{View} = 0;
  46.   }
  47.   else {
  48.     $Param->{View} = 1;
  49.   }
  50.  
  51.   #print "Date=$Param->{ViewDate}, host=$Param->{host}, user=$Param->{user}, password=$Param->{password}\n";
  52.  
  53.   $Param->{dbh}=DBI->connect("DBI:mysql:host=$Param->{host}",$Param->{user},$Param->{password},{PrintError=>0});
  54.   if (DBI::err()) {
  55.     print "Error: " . DBI::errstr() . "\n";
  56.   }
  57.   else {
  58.     $Param->{Start} = time;
  59.     while(<STDIN>) {
  60.       $Param->{LineCount} ++ ;
  61.  
  62.       if ($Param->{ViewDate} ) {
  63.     if (m/^(\d{6})\s+\d{1,2}:\d\d:\d\d\s.*$/) { # get date
  64.       #print "# $1 #\n";
  65.       if ($1 == $Param->{ViewDate}) {
  66.         $Param->{View} = 1;
  67.       }
  68.       else {
  69.         $Param->{View} = 0;
  70.       }
  71.     }
  72.       }
  73.       if ($Param->{View} ) {
  74.     #print "->>>$_";
  75.  
  76.     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)
  77.       #print "C-$1--$2--$3------\n";
  78.       RunQuery($Param);
  79.       if (defined $3) {
  80.         $Param->{CID}->{$2} = $3 ;
  81.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  82.       }
  83.     }
  84.  
  85.     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)
  86.       #print "\n <<<<<<<<<<<<<<<<<<----------------------------<<<<<<<<<<<<<<<< \n";
  87.       #print "Connect \n";
  88.       RunQuery($Param);
  89.     }
  90.     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)
  91.       #print "C-$1--$2--$3------\n";
  92.       RunQuery($Param);
  93.       if (defined $3) {
  94.         $Param->{CID}->{$2} = $3 ;
  95.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  96.       }
  97.     }
  98.  
  99.     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
  100.       #print "Q-$1--$2--------\n";
  101.       RunQuery($Param);
  102.       delete $Param->{CID}->{$2} ;
  103.     }
  104.  
  105.     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
  106.       #print "S1-$1--$2--$3------\n";
  107.       RunQuery($Param);
  108.       unless ($Param->{CID}->{$2}) {
  109.         #print "Error: No Database for Handle: $2 found\n";
  110.       }
  111.       else {
  112.         $Param->{DB}=$Param->{CID}->{$2};
  113.  
  114.         my $s = "$3";
  115.         $s =~ s/from\s/from $Param->{DB}./i;
  116.         $Param->{Query}="EXPLAIN $s";
  117.  
  118.         #$s =~ m/from\s+(\w+[.]\w+)/i;
  119.         #$Param->{tab} =$1;
  120.         #print "-- $Param->{tab} -- $s --\n";
  121.       }
  122.     }
  123.  
  124.     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
  125.       #print "S2--$1--$2--$3------\n";
  126.       RunQuery($Param);
  127.       unless ($Param->{CID}->{$2}) {
  128.         #print "Error: No Database for Handle: $2 found\n";
  129.       }
  130.       else {
  131.         $Param->{DB}=$Param->{CID}->{$2};
  132.  
  133.         my $ud = $3;
  134.         $ud =~ m/^update\s+(\w+).+(where.+)$/i;
  135.         $Param->{Query} ="EXPLAIN SELECT * FROM $1 $2";
  136.         $Param->{Query} =~ s/from\s/from $Param->{DB}./i;
  137.  
  138.         #$Param->{Query} =~ m/from\s+(\w+[.]\w+)/i;
  139.         #$Param->{tab} =$1;
  140.       }
  141.     }
  142.  
  143.     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?
  144.       $Param->{Statistics} ++;
  145.       #print "Statistics--$1--$2--$3------\n";
  146.       RunQuery($Param);
  147.     }
  148.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(.+)$/i) { # get connection ID($2)
  149.       $Param->{QueryCount} ++;
  150.       #print "Query-NULL $3\n";
  151.       RunQuery($Param);
  152.     }
  153.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Refresh\s+(.+)$/i) { # get connection ID($2)
  154.       $Param->{Refresh} ++;
  155.       #print "Refresh\n";
  156.       RunQuery($Param);
  157.     }
  158.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Init\s+(.+)$/i) { # get connection ID($2)
  159.       $Param->{Init} ++;
  160.       #print "Init $3\n";
  161.       RunQuery($Param);
  162.     }
  163.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Field\s+(.+)$/i) { # get connection ID($2)
  164.       $Param->{Field} ++;
  165.       #print "Field $3\n";
  166.       RunQuery($Param);
  167.     }
  168.  
  169.     elsif (m/^\s+(.+)$/ ) { # command could be some lines ...
  170.       #print "multi-lined ($1)\n";
  171.       my ($A)=$1;
  172.        chomp $A;
  173.       $Param->{Query} .= " $1";
  174.       #print "multi-lined ($1)<<$Param->{Query}>>\n";
  175.     }
  176.  
  177.  
  178.       }
  179.  
  180.     }
  181.  
  182.     $Param->{dbh}->disconnect();
  183.  
  184.     if (1 == 0) {
  185.       print "\nunclosed handles----------------------------------------\n";
  186.       my $count=0;
  187.       foreach (sort keys %{$Param->{CID}}) {
  188.     print "$count | $_ : $Param->{CID}->{$_} \n";
  189.     $count ++;
  190.       }
  191.     }
  192.  
  193.     print "\nIndex usage ------------------------------------\n";
  194.     foreach my $t (sort keys %{$Param->{Data}}) {
  195.       print "\nTable\t$t: ---\n";
  196.       foreach my $k (sort keys %{$Param->{Data}->{$t}}) {
  197.     print " count\t$k:\n";
  198.     my %h = %{$Param->{Data}->{$t}->{$k}};
  199.       foreach (sort {$h{$a} <=> $h{$b}} keys %h) {
  200.       print "  $Param->{Data}->{$t}->{$k}->{$_}\t$_\n";
  201.     }
  202.       }
  203.     }
  204.  
  205.     $Param->{AllCount}=0;
  206.     print "\nQueries causing table scans -------------------\n\n";
  207.     foreach (@{$Param->{ALL}}) {
  208.       $Param->{AllCount} ++;
  209.       print "$_\n";
  210.     }
  211.     print "Sum: $Param->{AllCount} table scans\n";
  212.  
  213.     print "\nSummary ---------------------------------------\n\n";
  214.     print "Select: \t$Param->{SelectCount} queries\n";
  215.     print "Update: \t$Param->{UpdateCount} queries\n";
  216.     print "\n";
  217.  
  218.     print "Init:   \t$Param->{Init} times\n";
  219.     print "Field:  \t$Param->{Field} times\n";
  220.     print "Refresh: \t$Param->{Refresh} times\n";
  221.     print "Query:  \t$Param->{QueryCount} times\n";
  222.     print "Statistics:\t$Param->{Statistics} times\n";
  223.     print "\n";
  224.  
  225.     print "Logfile: \t$Param->{LineCount} lines\n";
  226.     print "Started:  \t".localtime($Param->{Start})."\n";
  227.     print "Finished:   \t".localtime(time)."\n";
  228.  
  229.   }
  230. }
  231.  
  232.  
  233. ###########################################################################
  234. #
  235. #
  236. #
  237. sub RunQuery {
  238.   my $Param = shift ;
  239.  
  240.   if (defined $Param->{Query}) {
  241.     if (defined $Param->{DB} ) {
  242.  
  243.       $Param->{Query} =~ m/from\s+(\w+[.]\w+|\w+)/i;
  244.       $Param->{tab} =$1;
  245.       #print "||$Param->{tab} -- $Param->{Query}\n";
  246.  
  247.       my $sth=$Param->{dbh}->prepare("USE $Param->{DB}");
  248.       if (DBI::err()) {
  249.     if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  250.       }
  251.       else {
  252.     $sth->execute();
  253.     if (DBI::err()) {
  254.       if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  255.     }
  256.     else {
  257.       $sth->finish();
  258.  
  259.       $sth=$Param->{dbh}->prepare($Param->{Query});
  260.       if (DBI::err()) {
  261.         if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  262.       }
  263.       else {
  264.         #print "$Param->{Query}\n";
  265.         $sth->execute();
  266.         if (DBI::err()) {
  267.           if ($Param->{PrintError}) {print "[$Param->{LineCount}]<<$Param->{Query}>>\n";}
  268.           if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  269.         }
  270.         else {
  271.           my $row = undef;
  272.           while ($row = $sth->fetchrow_hashref()) {
  273.         $Param->{SelectCount} ++;
  274.  
  275.         if (defined $row->{Comment}) {
  276.           push (@{$Param->{Comment}}, "$row->{Comment}; $_; $Param->{DB}; $Param->{Query}");
  277.         }
  278.         foreach (@{$Param->{Rows}}) {
  279.           if (defined $row->{$_}) {
  280.             #if (($_ eq 'type' ) and ($row->{$_} eq 'ALL')) {
  281.             if ($row->{type} eq 'ALL') {
  282.               push (@{$Param->{ALL}}, "$Param->{Query}");
  283.               #print ">> $row->{$_} $_ $Param->{DB} $Param->{Query}\n";
  284.             }
  285.             $Param->{IdxUseCount} ++;
  286.             $Param->{Data}->{$Param->{tab}}->{$_}->{$row->{$_}} ++;
  287.           }
  288.         }
  289.           }
  290.         }
  291.       }
  292.     }
  293.       }
  294.       $sth->finish();
  295.     }
  296.     $Param->{Query} = undef ;
  297.   }
  298. }
  299.  
  300. ###########################################################################
  301. #
  302. #
  303. #
  304. sub ShowOptions {
  305.   print <<EOF;
  306. Usage: $0 [OPTIONS] < LOGFILE
  307.  
  308. --date=YYMMDD       select only entrys of date
  309. -d=YYMMDD
  310. --host=HOSTNAME     db-host to ask
  311. -h=HOSTNAME
  312. --user=USERNAME     db-user
  313. -u=USERNAME
  314. --password=PASSWORD password of db-user
  315. -p=PASSWORD
  316.  
  317. Read logfile from STDIN an try to EXPLAIN all SELECT statements. All UPDATE statements are rewritten to an EXPLAIN SELECT statement. The results of the EXPLAIN statement are collected and counted. All results with type=ALL are collected in an separete list. Results are printed to STDOUT.
  318.  
  319. EOF
  320. }
  321.  
  322. 1;
  323.  
  324. __END__
  325.  
  326. =pod
  327.  
  328. =head1 NAME
  329.  
  330. explain_log.pl
  331.  
  332. Feed a mysqld general logfile (created with mysqld --log) back into mysql
  333. and collect statistics about index usage with EXPLAIN.
  334.  
  335. =head1 DISCUSSION
  336.  
  337. To optimize your indices, you have to know which ones are actually
  338. used and what kind of queries are causing table scans. Especially
  339. if you are generating your queries dynamically and you have a huge
  340. amount of queries going on, this isn't easy.
  341.  
  342. Use this tool to take a look at the effects of your real life queries.
  343. Then add indices to avoid table scans and remove those which aren't used.
  344.  
  345. =head1 USAGE
  346.  
  347. explain_log.pl [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] < logfile
  348.  
  349. --date=YYMMDD       select only entrys of date
  350.  
  351. -d=YYMMDD
  352.  
  353. --host=HOSTNAME     db-host to ask
  354.  
  355. -h=HOSTNAME
  356.  
  357. --user=USERNAME     db-user
  358.  
  359. -u=USERNAME
  360.  
  361. --password=PASSWORD password of db-user
  362.  
  363. -p=PASSWORD
  364.  
  365. =head1 EXAMPLE
  366.  
  367. explain_log.pl --host=localhost --user=foo --password=bar < /var/lib/mysql/mobile.log
  368.  
  369. =head1 AUTHOR
  370.  
  371.   Stefan Nitz
  372.   Jan Willamowius <jan@mobile.de>, http://www.mobile.de
  373.  
  374. =head1 RECRUITING
  375.  
  376. If you are looking for a MySQL or Perl job, take a look at http://www.mobile.de
  377. and send me an email with your resume (you must be speaking German!).
  378.  
  379. =head1 SEE ALSO
  380.  
  381. mysql documentation
  382.  
  383. =cut
  384.