Dealing with the MySQL slow log on Amazon RDS

Amazon RDS is a web service offered by amazon which provides users with MySQL databases in the cloud. It’s an attractive service due to its convenient backups and easy replication.

Amazon doesn’t provide shell access to RDS instances. For this reason, the slow query log, which is an invaluable tool for anyone with a somewhat large or complex data set, is not available in the traditional way. Amazon, however, does provide a method for enabling and retrieving the log.

Setup

To enable the slow query log on RDS, you should create or modify a DB parameter group which ~~can be done from the AWS management console~~ must be done through the developer tools. There are two attributes that should be changed, slow_query_log, which should be set to 1 to enable, and long_query_time, which should be set to the shortest length to be considered a slow query.

The slow query log is stored in the table mysql.slow_log. Rotating the slow query log is done by calling the stored procedure CALL rds_rotate_slow_log.

Accessing

Update: this script is now available as a gem: rds_slow_log

I’ve written a little ruby script to print out the slow log in the usual format. From this it can be read, or analyzed with tools like request-log-analyzer.

require 'rubygems'
require 'active_record'

class SlowLog < ActiveRecord::Base
  set_table_name 'slow_log'

  def query_time
    time_parse query_time_before_type_cast
  end
  def lock_time
    time_parse lock_time_before_type_cast
  end
  def to_s
      <<EOS
# Time: #{start_time.strftime('%y%m%d %H:%M:%S')}
# User@Host: #{user_host}
# Query_time: #{query_time}  Lock_time: #{lock_time}  Rows_sent: #{rows_sent}  Rows_examined: #{rows_examined}
#{sql_text};
EOS
  end

  def self.print_all
    find(:all, :order => :start_time).each do |query|
      puts query
    end
  end

  private
  def time_parse string
    hours, minutes, seconds = string.to_s.split(/:/)
    hours.to_i * 60 * 60 + minutes.to_i * 60 + seconds.to_i
  end
end

ActiveRecord::Base.establish_connection :adapter  => 'mysql',
                                        :database => 'mysql',
                                        :username => 'username',
                                        :password => 'password'
SlowLog.print_all

This was written to be compatible with both activerecord 2.3 and 3.0. If compatability with 2.3 were to be dropped, the time_parse hacks could be removed.