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