Tool to determine 50 slowest & 50 most rows-examined queries in MySQL slowlog

slowlog

#!/usr/bin/env ruby

Entry = Struct.new(:duration, :rows, :data)

def trim(array, limit, &block)
  too_many = array.count - limit
  if too_many > 0
    array.sort_by!(&block)
    array.shift(too_many)
  end
end

in_comment = false
duration = rows = nil
data = []

duration_entries = []
rows_entries = []
count = 0
$stdin.each_line do |line|
  if line =~ /^#/
    if line =~ /Query_time: (\d+\.\d+).*Rows_examined: (\d+) /
      duration = $1.to_f
      rows = $2.to_i
    end

    if duration && !in_comment
      entry = Entry.new(duration, rows, data)
      count += 1
      $stdout.print "\rProcessed #{count}    "
      $stdout.sync

      duration_entries << entry
      trim(duration_entries, 50, &:duration)

      rows_entries << entry
      trim(rows_entries, 50, &:rows)

      duration = rows = nil
      data = []
    end
    in_comment = true
  else
    in_comment = false
  end

  data << line
end
puts

File.open("by_duration.log", "w") do |fh|
  duration_entries.each do |entry|
    fh.puts *entry.data
  end
end

File.open("by_rows.log", "w") do |fh|
  rows_entries.each do |entry|
    fh.puts *entry.data
  end
  fh.puts
end