~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#!/usr/bin/perl
2
# mysqldumpslow - parse and summarize the MySQL slow query log
3
4
# Original version by Tim Bunce, sometime in 2000.
5
# Further changes by Tim Bunce, 8th March 2001.
6
# Handling of strings with \ and double '' by Monty 11 Aug 2001.
7
8
use strict;
9
use Getopt::Long;
10
11
# t=time, l=lock time, r=rows
12
# at, al, and ar are the corresponding averages
13
14
my %opt = (
15
    s => 'at',
16
    h => '*',
17
);
18
19
GetOptions(\%opt,
20
    'verbose|v+',# verbose
21
    'help+',	# write usage info
22
    'debug|d+',	# debug
23
    's=s',	# what to sort by (t, at, l, al, r, ar etc)
24
    'r!',	# reverse the sort order (largest last instead of first)
25
    't=i',	# just show the top n queries
26
    'a!',	# don't abstract all numbers to N and strings to 'S'
27
    'n=i',	# abstract numbers with at least n digits within names
28
    'g=s',	# grep: only consider stmts that include this string
29
    'h=s',	# hostname of db server for *-slow.log filename (can be wildcard)
30
    'i=s',	# name of server instance (if using mysql.server startup script)
31
    'l!',	# don't subtract lock time from total time
32
) or usage("bad option");
33
34
$opt{'help'} and usage();
35
36
unless (@ARGV) {
37
    my $defaults   = `my_print_defaults mysqld`;
38
    my $basedir = ($defaults =~ m/--basedir=(.*)/)[0]
39
	or die "Can't determine basedir from 'my_print_defaults mysqld' output: $defaults";
40
    warn "basedir=$basedir\n" if $opt{v};
41
42
    my $datadir = ($defaults =~ m/--datadir=(.*)/)[0];
43
    my $slowlog = ($defaults =~ m/--log-slow-queries=(.*)/)[0];
44
    if (!$datadir or $opt{i}) {
45
	# determine the datadir from the instances section of /etc/my.cnf, if any
46
	my $instances  = `my_print_defaults instances`;
47
	die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults"
48
	    unless $instances;
49
	my @instances = ($instances =~ m/^--(\w+)-/mg);
50
	die "No -i 'instance_name' specified to select among known instances: @instances.\n"
51
	    unless $opt{i};
52
	die "Instance '$opt{i}' is unknown (known instances: @instances)\n"
53
	    unless grep { $_ eq $opt{i} } @instances;
54
	$datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0]
55
	    or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances";
56
	warn "datadir=$datadir\n" if $opt{v};
57
    }
58
59
    if ( -f $slowlog ) {
60
        @ARGV = ($slowlog);
61
        die "Can't find '$slowlog'\n" unless @ARGV;
62
    } else {
63
        @ARGV = <$datadir/$opt{h}-slow.log>;
64
        die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV;
65
    }
66
}
67
68
warn "\nReading mysql slow query log from @ARGV\n";
69
70
my @pending;
71
my %stmt;
72
$/ = ";\n#";		# read entire statements using paragraph mode
73
while ( defined($_ = shift @pending) or defined($_ = <>) ) {
74
    warn "[[$_]]\n" if $opt{d};	# show raw paragraph being read
75
76
    my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
77
    if (@chunks > 1) {
78
	unshift @pending, map { length($_) ? $_ : () } @chunks;
79
	warn "<<".join(">>\n<<",@chunks).">>" if $opt{d};
80
	next;
81
    }
82
83
    s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
84
    my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('','');
85
86
    s/^# Query_time: (\d+)  Lock_time: (\d+)  Rows_sent: (\d+).*\n//;
87
    my ($t, $l, $r) = ($1, $2, $3);
88
    $t -= $l unless $opt{l};
89
90
    # remove fluff that mysqld writes to log when it (re)starts:
91
    s!^/.*Version.*started with:.*\n!!mg;
92
    s!^Tcp port: \d+  Unix socket: \S+\n!!mg;
93
    s!^Time.*Id.*Command.*Argument.*\n!!mg;
94
95
    s/^use \w+;\n//;	# not consistently added
96
    s/^SET timestamp=\d+;\n//;
97
98
    s/^[ 	]*\n//mg;	# delete blank lines
99
    s/^[ 	]*/  /mg;	# normalize leading whitespace
100
    s/\s*;\s*(#\s*)?$//;	# remove trailing semicolon(+newline-hash)
101
102
    next if $opt{g} and !m/$opt{g}/io;
103
104
    unless ($opt{a}) {
105
	s/\b\d+\b/N/g;
106
	s/\b0x[0-9A-Fa-f]+\b/N/g;
107
        s/''/'S'/g;
108
        s/""/"S"/g;
109
        s/(\\')//g;
110
        s/(\\")//g;
111
        s/'[^']+'/'S'/g;
112
        s/"[^"]+"/"S"/g;
113
	# -n=8: turn log_20001231 into log_NNNNNNNN
114
	s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n};
115
	# abbreviate massive "in (...)" statements and similar
116
	s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
117
    }
118
119
    my $s = $stmt{$_} ||= { users=>{}, hosts=>{} };
120
    $s->{c} += 1;
121
    $s->{t} += $t;
122
    $s->{l} += $l;
123
    $s->{r} += $r;
124
    $s->{users}->{$user}++ if $user;
125
    $s->{hosts}->{$host}++ if $host;
126
127
    warn "{{$_}}\n\n" if $opt{d};	# show processed statement string
128
}
129
130
foreach (keys %stmt) {
131
    my $v = $stmt{$_} || die;
132
    my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)};
133
    $v->{at} = $t / $c;
134
    $v->{al} = $l / $c;
135
    $v->{ar} = $r / $c;
136
}
137
138
my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;
139
@sorted = @sorted[0 .. $opt{t}-1] if $opt{t};
140
@sorted = reverse @sorted         if $opt{r};
141
142
foreach (@sorted) {
143
    my $v = $stmt{$_} || die;
144
    my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};
145
    my @users = keys %{$v->{users}};
146
    my $user  = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;
147
    my @hosts = keys %{$v->{hosts}};
148
    my $host  = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;
149
    printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",
150
	    $c, $at,$t, $al,$l, $ar,$r, $_;
151
}
152
153
sub usage {
154
    my $str= shift;
155
    my $text= <<HERE;
156
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
157
158
Parse and summarize the MySQL slow query log. Options are
159
160
  --verbose    verbose
161
  --debug      debug
162
  --help       write this text to standard output
163
164
  -v           verbose
165
  -d           debug
166
  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default
167
  -r           reverse the sort order (largest last instead of first)
168
  -t NUM       just show the top n queries
169
  -a           don't abstract all numbers to N and strings to 'S'
170
  -n NUM       abstract numbers with at least n digits within names
171
  -g PATTERN   grep: only consider stmts that include this string
172
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
173
               default is '*', i.e. match all
174
  -i NAME      name of server instance (if using mysql.server startup script)
175
  -l           don't subtract lock time from total time
176
177
HERE
178
    if ($str) {
179
      print STDERR "ERROR: $str\n\n";
180
      print STDERR $text;
181
      exit 1;
182
    } else {
183
      print $text;
184
      exit 0;
185
    }
186
}