John Hawthorn

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.