1#!@PERL_PATH@ 2 3# Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 4# 5# This program is free software; you can redistribute it and/or 6# modify it under the terms of the GNU Library General Public 7# License as published by the Free Software Foundation; version 2 8# of the License. 9# 10# This program is distributed in the hope that it will be useful, 11# but WITHOUT ANY WARRANTY; without even the implied warranty of 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13# Library General Public License for more details. 14# 15# You should have received a copy of the GNU Library General Public 16# License along with this library; if not, write to the Free 17# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, 18# MA 02110-1335 USA 19 20# mysqldumpslow - parse and summarize the MySQL slow query log 21 22# Original version by Tim Bunce, sometime in 2000. 23# Further changes by Tim Bunce, 8th March 2001. 24# Handling of strings with \ and double '' by Monty 11 Aug 2001. 25 26use strict; 27use Getopt::Long; 28 29# t=time, l=lock time, r=rows, a=rows affected 30# at, al, ar and aa are the corresponding averages 31 32my %opt = ( 33 s => 'at', 34 h => '*', 35); 36 37GetOptions(\%opt, 38 'v|verbose+',# verbose 39 'help+', # write usage info 40 'd|debug+', # debug 41 's=s', # what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t) 42 'r!', # reverse the sort order (largest last instead of first) 43 't=i', # just show the top n queries 44 'a!', # don't abstract all numbers to N and strings to 'S' 45 'n=i', # abstract numbers with at least n digits within names 46 'g=s', # grep: only consider stmts that include this string 47 'h=s', # hostname/basename of db server for *-slow.log filename (can be wildcard) 48 'i=s', # name of server instance (if using mysql.server startup script) 49 'l!', # don't subtract lock time from total time 50) or usage("bad option"); 51 52$opt{'help'} and usage(); 53 54unless (@ARGV) { 55 my $defaults = `my_print_defaults --mysqld`; 56 57 my $datadir = ($defaults =~ m/--datadir=(.*)/g)[-1]; 58 if (!$datadir or $opt{i}) { 59 # determine the datadir from the instances section of /etc/my.cnf, if any 60 my $instances = `my_print_defaults instances`; 61 die "Can't determine datadir from 'my_print_defaults instances' output: $defaults" 62 unless $instances; 63 my @instances = ($instances =~ m/^--(\w+)-/mg); 64 die "No -i 'instance_name' specified to select among known instances: @instances.\n" 65 unless $opt{i}; 66 die "Instance '$opt{i}' is unknown (known instances: @instances)\n" 67 unless grep { $_ eq $opt{i} } @instances; 68 $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/g)[-1] 69 or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances"; 70 warn "datadir=$datadir\n" if $opt{v}; 71 } 72 73 my $slowlog = ($defaults =~ m/--log[-_]slow[-_]queries=(.*)/g)[-1]; 74 if (!$slowlog) 75 { 76 $slowlog = ($defaults =~ m/--slow[-_]query[-_]log[-_]file=(.*)/g)[-1]; 77 } 78 if ( $slowlog ) 79 { 80 @ARGV = ($slowlog); 81 die "Can't find '$slowlog'\n" unless @ARGV; 82 } 83 else 84 { 85 if (!$opt{h}) 86 { 87 $opt{h}= ($defaults =~ m/--log[-_]basename=(.*)/g)[-1]; 88 } 89 @ARGV = <$datadir/$opt{h}-slow.log>; 90 die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV; 91 } 92} 93 94warn "\nReading mysql slow query log from @ARGV\n"; 95 96my @pending; 97my %stmt; 98$/ = ";\n#"; # read entire statements using paragraph mode 99while ( defined($_ = shift @pending) or defined($_ = <>) ) { 100 warn "[[$_]]\n" if $opt{d}; # show raw paragraph being read 101 102 my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m; 103 if (@chunks > 1) { 104 unshift @pending, map { length($_) ? $_ : () } @chunks; 105 warn "<<".join(">>\n<<",@chunks).">>" if $opt{d}; 106 next; 107 } 108 109 s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//; 110 my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('',''); 111 112 s/^# Thread_id: [0-9]+\s+Schema: .*\s+QC_hit:.*[^\n]+\n//; 113 s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//; 114 my ($t, $l, $r, $e) = ($1, $2, $3, $4); 115 s/^# Rows_affected: ([0-9.]+).*\n//; 116 my ($a) = ($1); 117 118 $t -= $l unless $opt{l}; 119 120 # remove fluff that mysqld writes to log when it (re)starts: 121 s!^/.*Version.*started with:.*\n!!mg; 122 s!^Tcp port: \d+ Unix socket: \S+\n!!mg; 123 s!^Time.*Id.*Command.*Argument.*\n!!mg; 124 125 # Remove optimizer info 126 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; 127 s!^# Filesort: \S+\s+Filesort_on_disk: \S+[^\n]+\n!!mg; 128 s!^# Full_scan: \S+\s+Full_join: \S+[^\n]+\n!!mg; 129 130 s/^use \w+;\n//; # not consistently added 131 s/^SET timestamp=\d+;\n//; 132 133 s/^[ ]*\n//mg; # delete blank lines 134 s/^[ ]*/ /mg; # normalize leading whitespace 135 s/\s*;\s*(#\s*)?$//; # remove trailing semicolon(+newline-hash) 136 137 next if $opt{g} and !m/$opt{g}/io; 138 139 unless ($opt{a}) { 140 s/\b\d+\b/N/g; 141 s/\b0x[0-9A-Fa-f]+\b/N/g; 142 s/''/'S'/g; 143 s/""/"S"/g; 144 s/(\\')//g; 145 s/(\\")//g; 146 s/'[^']+'/'S'/g; 147 s/"[^"]+"/"S"/g; 148 # -n=8: turn log_20001231 into log_NNNNNNNN 149 s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n}; 150 # abbreviate massive "in (...)" statements and similar 151 s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg; 152 } 153 154 my $s = $stmt{$_} ||= { users=>{}, hosts=>{} }; 155 $s->{c} += 1; 156 $s->{t} += $t; 157 $s->{l} += $l; 158 $s->{r} += $r; 159 $s->{e} += $e; 160 $s->{a} += $a; 161 $s->{users}->{$user}++ if $user; 162 $s->{hosts}->{$host}++ if $host; 163 164 warn "{{$_}}\n\n" if $opt{d}; # show processed statement string 165} 166 167foreach (keys %stmt) { 168 my $v = $stmt{$_} || die; 169 my ($c, $t, $l, $r, $e, $a) = @{ $v }{qw(c t l r e a)}; 170 $v->{at} = $t / $c; 171 $v->{al} = $l / $c; 172 $v->{ar} = $r / $c; 173 $v->{ae} = $e / $c; 174 $v->{aa} = $a / $c; 175} 176 177my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; 178@sorted = @sorted[0 .. $opt{t}-1] if $opt{t}; 179@sorted = reverse @sorted if $opt{r}; 180 181foreach (@sorted) { 182 my $v = $stmt{$_} || die; 183 my ($c, $t, $at, $l, $al, $r, $ar, $e, $ae, $a, $aa) = @{ $v }{qw(c t at l al r ar e ae a aa)}; 184 my @users = keys %{$v->{users}}; 185 my $user = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users; 186 my @hosts = keys %{$v->{hosts}}; 187 my $host = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts; 188 printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows_sent=%.1f (%d), Rows_examined=%.1f (%d), Rows_affected=%.1f (%d), $user\@$host\n%s\n\n", 189 $c, $at,$t, $al,$l, $ar,$r, $ae, $e, $aa, $a, $_; 190} 191 192sub usage { 193 my $str= shift; 194 my $text= <<HERE; 195Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] 196 197Parse and summarize the MySQL slow query log. Options are 198 199 --verbose verbose 200 --debug debug 201 --help write this text to standard output 202 203 -v verbose 204 -d debug 205 -s ORDER what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t), 'at' is default 206 aa: average rows affected 207 ae: aggregated rows examined 208 al: average lock time 209 ar: average rows sent 210 at: average query time 211 a: rows affected 212 c: count 213 e: rows examined 214 l: lock time 215 r: rows sent 216 t: query time 217 -r reverse the sort order (largest last instead of first) 218 -t NUM just show the top n queries 219 -a don't abstract all numbers to N and strings to 'S' 220 -n NUM abstract numbers with at least n digits within names 221 -g PATTERN grep: only consider stmts that include this string 222 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), 223 default is '*', i.e. match all 224 -i NAME name of server instance (if using mysql.server startup script) 225 -l don't subtract lock time from total time 226 227HERE 228 if ($str) { 229 print STDERR "ERROR: $str\n\n"; 230 print STDERR $text; 231 exit 1; 232 } else { 233 print $text; 234 exit 0; 235 } 236} 237