diff options
Diffstat (limited to 'scripts/mysqldumpslow.sh')
| -rw-r--r-- | scripts/mysqldumpslow.sh | 77 |
1 files changed, 38 insertions, 39 deletions
diff --git a/scripts/mysqldumpslow.sh b/scripts/mysqldumpslow.sh index 9ec3802a2e4..de565678f9b 100644 --- a/scripts/mysqldumpslow.sh +++ b/scripts/mysqldumpslow.sh @@ -1,22 +1,4 @@ #!/usr/bin/perl - -# Copyright (C) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc. -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU Library General Public -# License as published by the Free Software Foundation; version 2 -# of the License. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -# Library General Public License for more details. -# -# You should have received a copy of the GNU Library General Public -# License along with this library; if not, write to the Free -# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, -# MA 02111-1307, USA - # mysqldumpslow - parse and summarize the MySQL slow query log # Original version by Tim Bunce, sometime in 2000. @@ -38,13 +20,13 @@ GetOptions(\%opt, 'v|verbose+',# verbose 'help+', # write usage info 'd|debug+', # debug - 's=s', # what to sort by (al, at, ar, c, t, l, r) + 's=s', # what to sort by (al, at, ar, ae, c, t, l, r, e) 'r!', # reverse the sort order (largest last instead of first) 't=i', # just show the top n queries 'a!', # don't abstract all numbers to N and strings to 'S' 'n=i', # abstract numbers with at least n digits within names 'g=s', # grep: only consider stmts that include this string - 'h=s', # hostname of db server for *-slow.log filename (can be wildcard) + 'h=s', # hostname/basename of db server for *-slow.log filename (can be wildcard) 'i=s', # name of server instance (if using mysql.server startup script) 'l!', # don't subtract lock time from total time ) or usage("bad option"); @@ -52,34 +34,42 @@ GetOptions(\%opt, $opt{'help'} and usage(); unless (@ARGV) { - my $defaults = `my_print_defaults mysqld`; - my $basedir = ($defaults =~ m/--basedir=(.*)/)[0] - or die "Can't determine basedir from 'my_print_defaults mysqld' output: $defaults"; - warn "basedir=$basedir\n" if $opt{v}; + my $defaults = `my_print_defaults mysqld mariadb`; - my $datadir = ($defaults =~ m/--datadir=(.*)/)[0]; - my $slowlog = ($defaults =~ m/--log-slow-queries=(.*)/)[0]; + my $datadir = ($defaults =~ m/--datadir=(.*)/g)[-1]; if (!$datadir or $opt{i}) { # determine the datadir from the instances section of /etc/my.cnf, if any my $instances = `my_print_defaults instances`; - die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults" + die "Can't determine datadir from 'my_print_defaults instances' output: $defaults" unless $instances; my @instances = ($instances =~ m/^--(\w+)-/mg); die "No -i 'instance_name' specified to select among known instances: @instances.\n" unless $opt{i}; die "Instance '$opt{i}' is unknown (known instances: @instances)\n" unless grep { $_ eq $opt{i} } @instances; - $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0] + $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/g)[-1] or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances"; warn "datadir=$datadir\n" if $opt{v}; } - if ( -f $slowlog ) { + my $slowlog = ($defaults =~ m/--log[-_]slow[-_]queries=(.*)/g)[-1]; + if (!$slowlog) + { + $slowlog = ($defaults =~ m/--slow[-_]query[-_]log[-_]file=(.*)/g)[-1]; + } + if ( $slowlog ) + { @ARGV = ($slowlog); die "Can't find '$slowlog'\n" unless @ARGV; - } else { - @ARGV = <$datadir/$opt{h}-slow.log>; - die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV; + } + else + { + if (!$opt{h}) + { + $opt{h}= ($defaults =~ m/--log[-_]basename=(.*)/g)[-1]; + } + @ARGV = <$datadir/$opt{h}-slow.log>; + die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV; } } @@ -101,8 +91,10 @@ while ( defined($_ = shift @pending) or defined($_ = <>) ) { s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//; my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('',''); - s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+).*\n//; - my ($t, $l, $r) = ($1, $2, $3); + s/^# Thread_id: [0-9]+\s+Schema: [^\n]+\n//; + s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//; + my ($t, $l, $r, $e) = ($1, $2, $3, $4); + $t -= $l unless $opt{l}; # remove fluff that mysqld writes to log when it (re)starts: @@ -110,6 +102,11 @@ while ( defined($_ = shift @pending) or defined($_ = <>) ) { s!^Tcp port: \d+ Unix socket: \S+\n!!mg; s!^Time.*Id.*Command.*Argument.*\n!!mg; + # Remove optimizer info + s!^# QC_Hit: \S+\s+Full_scan: \S+\s+Full_join: \S+\s+Tmp_table: \S+\s+Tmp_table_on_disk: \S+[^\n]+\n!!mg; + s!^# Filesort: \S+\s+Filesort_on_disk: \S+[^\n]+\n!!mg; + s!^# Full_scan: \S+\s+Full_join: \S+[^\n]+\n!!mg; + s/^use \w+;\n//; # not consistently added s/^SET timestamp=\d+;\n//; @@ -139,6 +136,7 @@ while ( defined($_ = shift @pending) or defined($_ = <>) ) { $s->{t} += $t; $s->{l} += $l; $s->{r} += $r; + $s->{e} += $e; $s->{users}->{$user}++ if $user; $s->{hosts}->{$host}++ if $host; @@ -147,10 +145,11 @@ while ( defined($_ = shift @pending) or defined($_ = <>) ) { foreach (keys %stmt) { my $v = $stmt{$_} || die; - my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)}; + my ($c, $t, $l, $r, $e) = @{ $v }{qw(c t l r e)}; $v->{at} = $t / $c; $v->{al} = $l / $c; $v->{ar} = $r / $c; + $v->{ae} = $e / $c; } my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; @@ -159,13 +158,13 @@ my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; foreach (@sorted) { my $v = $stmt{$_} || die; - my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)}; + my ($c, $t,$at, $l,$al, $r,$ar,$e, $ae) = @{ $v }{qw(c t at l al r ar e ae)}; my @users = keys %{$v->{users}}; my $user = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users; my @hosts = keys %{$v->{hosts}}; my $host = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts; - printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows=%.1f (%d), $user\@$host\n%s\n\n", - $c, $at,$t, $al,$l, $ar,$r, $_; + printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows_sent=%.1f (%d), Rows_examined=%.1f (%d), $user\@$host\n%s\n\n", + $c, $at,$t, $al,$l, $ar,$r, $ae, $e, $_; } sub usage { @@ -181,7 +180,7 @@ Parse and summarize the MySQL slow query log. Options are -v verbose -d debug - -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default + -s ORDER what to sort by (al, at, ar, ae, c, l, r, e, t), 'at' is default al: average lock time ar: average rows sent at: average query time |