Examining MySQL Slow Query Logs With AWK and PERL

To pull out the information for the slow queries use:

awk '$2 ~ /Query_time/' slow-log

To print out just the times of each query:

awk '{if ($2 ~ /Query_time/) { print $3; }}' slow-log

To print out each query as "time - query":

#!/usr/bin/perl

# takes in a mysql slow query log and spits out a ranked list of query time

# and queries.

open FILE, "

my @times;

my %times_to_sql;



while ($line = ) {

  if ($line =~ m/Query_time: (\d+\.\d+)/) { # looking for query time

    my $time = $1;

    # building offending query

    $sql = "";

    while ($line_sql =  and ($line_sql !~ m/^#/) and 1) {

      chomp($line_sql);

      $sql .= $line_sql . " ";

    }

    $times_to_sql{$time} = $sql;

    push @times, $time;

  }

}



close $FILE;



open FILE, ">slow-log-ranked" or die $!;



@sorted_times = sort { $b <=> $a } @times;

foreach my $time (@sorted_times) {

  print FILE "$time  - ".$times_to_sql{$time}."\n";

}

close FILE;