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