针对mysql慢查询脚本的优化

  1 #!/usr/bin/perl
  2 
  3 # Copyright (C) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
  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., 59 Temple Place - Suite 330, Boston,
 18 # MA 02111-1307, 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 
 26 use strict;
 27 use Getopt::Long;
 28 
 29 use DBI qw(:sql_types);
 30 use POSIX qw(strftime);
 31 
 32 
 33 # t=time, l=lock time, r=rows
 34 # at, al, and ar are the corresponding averages
 35 
 36 my %opt = (
 37     s => ‘at‘,
 38     h => ‘*‘,
 39 );
 40 
 41 GetOptions(\%opt,
 42     ‘v|verbose+‘,# verbose
 43     ‘help+‘,    # write usage info
 44     ‘d|debug+‘,    # debug
 45     ‘s=s‘,    # what to sort by (al, at, ar, c, t, l, r)
 46     ‘r!‘,    # reverse the sort order (largest last instead of first)
 47     ‘t=i‘,    # just show the top n queries
 48     ‘a!‘,    # don‘t abstract all numbers to N and strings to ‘S‘
 49     ‘n=i‘,    # abstract numbers with at least n digits within names
 50     ‘g=s‘,    # grep: only consider stmts that include this string
 51     ‘h=s‘,    # hostname of db server for *-slow.log filename (can be wildcard)
 52     ‘i=s‘,    # name of server instance (if using mysql.server startup script)
 53     ‘l!‘,    # don‘t subtract lock time from total time
 54     ‘db=s‘,    # create sql3 database
 55 ) or usage("bad option");
 56 
 57 $opt{‘help‘} and usage();
 58 
 59 unless (@ARGV) {
 60     my $defaults   = `my_print_defaults mysqld`;
 61     my $basedir = ($defaults =~ m/--basedir=(.*)/)[0]
 62     or die "Can‘t determine basedir from ‘my_print_defaults mysqld‘ output: $defaults";
 63     warn "basedir=$basedir\n" if $opt{v};
 64 
 65     my $datadir = ($defaults =~ m/--datadir=(.*)/)[0];
 66     my $slowlog = ($defaults =~ m/--log-slow-queries=(.*)/)[0];
 67     if (!$datadir or $opt{i}) {
 68     # determine the datadir from the instances section of /etc/my.cnf, if any
 69     my $instances  = `my_print_defaults instances`;
 70     die "Can‘t determine datadir from ‘my_print_defaults mysqld‘ output: $defaults"
 71         unless $instances;
 72     my @instances = ($instances =~ m/^--(\w+)-/mg);
 73     die "No -i ‘instance_name‘ specified to select among known instances: @instances.\n"
 74         unless $opt{i};
 75     die "Instance ‘$opt{i}‘ is unknown (known instances: @instances)\n"
 76         unless grep { $_ eq $opt{i} } @instances;
 77     $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0]
 78         or die "Can‘t determine --$opt{i}-datadir from ‘my_print_defaults instances‘ output: $instances";
 79     warn "datadir=$datadir\n" if $opt{v};
 80     }
 81 
 82     if ( -f $slowlog ) {
 83         @ARGV = ($slowlog);
 84         die "Can‘t find ‘$slowlog‘\n" unless @ARGV;
 85     } else {
 86         @ARGV = <$datadir/$opt{h}-slow.log>;
 87         die "Can‘t find ‘$datadir/$opt{h}-slow.log‘\n" unless @ARGV;
 88     }
 89 }
 90 
 91 
 92 
 93 warn "\nReading mysql slow query log from @ARGV\n";
 94 
 95 
 96 
 97 my $dbh;
 98 my $sth;
 99 if ($opt{db}){
100 my $dbfile = $opt{db};
101 $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
102 
103 my $sql = qq{ CREATE TABLE IF NOT EXISTS slowinfo (
104         source    VARCHAR(1024),
105         sourceTempel    VARCHAR(1024),
106         Query_time    NUMERIC,
107         Lock_time    NUMERIC,
108         Rows_sent     NUMERIC,
109         Rows_examined NUMERIC,
110         user     VARCHAR(255),
111         host     VARCHAR(255),
112         timestamp NUMERIC,
113         time VARCHAR(255)
114         ) };
115 
116 $dbh->do($sql);
117 
118 if ($dbh){
119         $sth = $dbh->prepare("
120         insert into  slowinfo values(?,?,?,?,?,?,?,?,?,?);
121         ");
122 }
123 }
124 
125 
126 my @pending;
127 my %stmt;
128 if ($dbh){
129 $dbh->do(qq{ BEGIN; });
130 }
131 
132 $/ = ";\n#";        # read entire statements using paragraph mode
133 while ( defined($_ = shift @pending) or defined($_ = <>) ) {
134     warn "[[$_]]\n" if $opt{d};    # show raw paragraph being read
135 
136     my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
137     if (@chunks > 1) {
138     unshift @pending, map { length($_) ? $_ : () } @chunks;
139     warn "<<".join(">>\n<<",@chunks).">>" if $opt{d};
140     next;
141     }
142 
143     s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
144     my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : (‘‘,‘‘);
145 
146     s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//;
147     my ($t, $l, $r, $re) = ($1, $2, $3, $4);
148     $t -= $l unless $opt{l};
149 
150     # remove fluff that mysqld writes to log when it (re)starts:
151     s!^/.*Version.*started with:.*\n!!mg;
152     s!^Tcp port: \d+  Unix socket: \S+\n!!mg;
153     s!^Time.*Id.*Command.*Argument.*\n!!mg;
154 
155     s/^use \w+;\n//;    # not consistently added
156     s/^SET timestamp=(\d+);\n//;
157     my ($timestamp) = ($1);
158 
159     s/^[     ]*\n//mg;    # delete blank lines
160     s/^[     ]*/  /mg;    # normalize leading whitespace
161     s/\s*;\s*(#\s*)?$//;    # remove trailing semicolon(+newline-hash)
162 
163     next if $opt{g} and !m/$opt{g}/io;
164 
165     my $source = $_;
166     my $sourceTemp;
167 
168     if ($opt{a}){
169 
170             s/\b\d+\b/N/g;
171             s/\b0x[0-9A-Fa-f]+\b/N/g;
172                 s/‘‘/‘S‘/g;
173                 s/""/"S"/g;
174                 s/(\\‘)//g;
175                 s/(\\")//g;
176                 s/‘[^‘]+‘/‘S‘/g;
177                 s/"[^"]+"/"S"/g;
178             # -n=8: turn log_20001231 into log_NNNNNNNN
179             s/([a-z_]+)(\d{$opt{n},})/$1.(‘N‘ x length($2))/ieg if $opt{n};
180             # abbreviate massive "in (...)" statements and similar
181             s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
182     }
183     $sourceTemp = $_;
184     
185     if($sth)
186     {
187               
188           my $timeStr1 = strftime "%Y-%m-%d %H:%M:%S", localtime($timestamp);
189           
190           $sth->bind_param(1, $source, SQL_VARCHAR);
191           $sth->bind_param(2, $sourceTemp, SQL_VARCHAR);
192           $sth->bind_param(3, $t, SQL_DOUBLE);
193           $sth->bind_param(4, $l, SQL_DOUBLE);
194           $sth->bind_param(5, $r, SQL_INTEGER);
195           $sth->bind_param(6, $re, SQL_INTEGER);
196           $sth->bind_param(7, $user, SQL_VARCHAR);
197           $sth->bind_param(8, $host, SQL_VARCHAR);
198           $sth->bind_param(9, $timestamp, SQL_INTEGER);
199           $sth->bind_param(10, $timeStr1, SQL_VARCHAR);
200 
201           $sth->execute();
202     }
203 
204 
205 
206     my $s = $stmt{$_} ||= { users=>{}, hosts=>{} };
207     $s->{c} += 1;
208     $s->{t} += $t;
209     $s->{l} += $l;
210     $s->{r} += $r;
211     $s->{users}->{$user}++ if $user;
212     $s->{hosts}->{$host}++ if $host;
213 
214     warn "{{$_}}\n\n" if $opt{d};    # show processed statement string
215     #printf "string sql %s\r\n", $_;
216 }
217 if ($dbh){
218 $dbh->do(qq{ COMMIT; });
219 }
220 
221 foreach (keys %stmt) {
222     my $v = $stmt{$_} || die;
223     my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)};
224     $v->{at} = $t / $c;
225     $v->{al} = $l / $c;
226     $v->{ar} = $r / $c;
227 }
228 
229 my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;
230 @sorted = @sorted[0 .. $opt{t}-1] if $opt{t};
231 @sorted = reverse @sorted         if $opt{r};
232 
233 my @rows;
234 
235 foreach (@sorted) {
236     my $v = $stmt{$_} || die;
237     my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};
238     my @users = keys %{$v->{users}};
239     my $user  = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;
240     my @hosts = keys %{$v->{hosts}};
241     my $host  = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;
242     my $laststring  = sprintf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",
243         $c, $at,$t, $al,$l, $ar,$r, $_;
244     if ($dbh)
245     {
246     
247     }
248     else
249     {
250         print STDOUT $laststring;
251     }
252 }
253 
254 
255 if ($dbh){
256 $dbh->disconnect();
257 }
258 
259 
260 sub usage {
261     my $str= shift;
262     my $text= <<HERE;
263 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
264 
265 Parse and summarize the MySQL slow query log. Options are
266 
267   --verbose    verbose
268   --debug      debug
269   --help       write this text to standard output
270 
271   -v           verbose
272   -d           debug
273   -s ORDER     what to sort by (al, at, ar, c, l, r, t), ‘at‘ is default
274                 al: average lock time
275                 ar: average rows sent
276                 at: average query time
277                  c: count
278                  l: lock time
279                  r: rows sent
280                  t: query time  
281   -r           reverse the sort order (largest last instead of first)
282   -t NUM       just show the top n queries
283   -a           don‘t abstract all numbers to N and strings to ‘S‘
284   -n NUM       abstract numbers with at least n digits within names
285   -g PATTERN   grep: only consider stmts that include this string
286   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
287                default is ‘*‘, i.e. match all
288   -i NAME      name of server instance (if using mysql.server startup script)
289   -l           don‘t subtract lock time from total time
290 
291 HERE
292     if ($str) {
293       print STDERR "ERROR: $str\n\n";
294       print STDERR $text;
295       exit 1;
296     } else {
297       print $text;
298       exit 0;
299     }
300 }