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.
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
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.
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.