Analyses a MySQL general query log, filters queries by structure and presents the most frequently ran query kinds

DBAnalyzer.rb

#!/usr/bin/env ruby

# Example usage
#   DBAnalyzer.rb general-query.log


class DBAnalyzer
  OPERATIONS = %w(SELECT)
  SHOW_TOP   = 10

  class << self
    def analyze(log)
      # Read the log file
      puts "Reading #{log}\n\n"
      queries = File.open(log).read

      # Filter the queries by what operations we concerned about
      puts "Filtering queries by #{OPERATIONS.join(', ')}"
      queries = queries.scan(/^\s*\d+\s*Query\s*(#{OPERATIONS.join('|')}.*)/).flatten
      total_queries = queries.size
      puts "  * Found #{total_queries} matching queries.\n\n"

      # Group queries by their structure
      puts "Grouping similar queries"
      queries = queries.group_by { |q| q.gsub(/IN \([^)]*\)/, 'IN (@)').gsub(/\d+|'[^\']+'/, '@') }
      puts "  * Reduced to #{queries.size} general queries\n\n"

      # Sort groups by query frequency
      puts "Sorting queries by run frequency\n\n"
      queries = queries.sort_by { |k,qs| -qs.size }

      # Show top offenders
      puts "Top #{SHOW_TOP} queries by frequency, showing an individual example of each:\n\n\n\n"
      queries.first(SHOW_TOP).each do |k,qs|
        puts "  #{'%4.01f' % (qs.size.to_f/total_queries*100)}%  #{qs.sample}\n\n"
      end
    end
  end
end

DBAnalyzer.analyze(ARGV.first)