Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to analyze slow queries on my system and I read that I should enable the "Slow Query Log'. I curious how I can I view this log when I'm using an instance of MySQL hosted on Amazon Web Service's RDS.

share|improve this question
Did my answer address your question ??? – RolandoMySQLDBA Dec 31 '12 at 23:32
Yes. Thanks so much – Philip Isaacs Jan 2 '13 at 16:45
Please accept my answer by clicking on the check mark. – RolandoMySQLDBA Jan 2 '13 at 16:49
Sorry. I'm new to all this. Happy New Year – Philip Isaacs Jan 2 '13 at 16:51
Thank you for accepting my answer. There is a number to the left of my answer with an uparrow above the number and a downarrow below the number. Would you please upvote my answer? – RolandoMySQLDBA Jan 2 '13 at 18:33
up vote 8 down vote accepted

Did you know there is slow log table? It is called mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
1 row in set (0.01 sec)


There are two variables you must activate to start recording slow queries in mysql.slow_log:

Here is the situation

mysql> show variables like 'log_output';
| Variable_name | Value |
| log_output    | TABLE |
1 row in set (0.01 sec)

mysql> show variables like 'slow_query_log';
| Variable_name  | Value |
| slow_query_log | OFF   |
1 row in set (0.01 sec)


log_output is already defaulted to TABLE. You will need to enable slow_query_log. Here is what you must do:

If your RDS Instance has no DB Parameter Group...

  1. Spin up an AmazonRDS MySQL Instance that uses a DB Parameter Group.
  2. Edit the DB Parameter Group by setting the slow_query_log to 1
  3. Restart the DB Instance

If your RDS Instance has DB Parameter Group...

  1. Edit the DB Parameter Group by setting the slow_query_log to 1
  2. Restart the DB Instance

Once the Instance has been restarted, login to mysql and run this query

mysql> SELECT SLEEP(15);

Since the default for long_query_time is 10 seconds, the query SELECT SLEEP(15); should show up in mysql.slow_log. Read the RDS Documentation to see if there are any limits on the table.


Here is a fair warning. Notice mysql.slow_log is a CSV file. The table has no key. While you can query the table, it will do full table scans. It would be great if you can do the following:

ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Then, you can query very fast for date and time ranges. I tried to run this and I get this:

mysql> ALTER TABLE mysql.slow_log ENGINE=MyISAM;
ERROR 1044 (42000): Access denied for user 'lwdba'@'%' to database 'mysql'

Check the RDS Documentation to see if Amazon can do this for you.

Give it a Try !!!

share|improve this answer
You get the access denied because RDS doesn't give you SUPER privileges. – ceejayoz Nov 6 '12 at 23:58
Wow, thanks so much this totally answer my questions. – Philip Isaacs Nov 7 '12 at 14:46

You can also copy the table to a new one in a different db and make the new one MyISAM:

create table my_slow_logs as select * from mysql.slow_log;
alter table my_slow_logs engine=MyISAM;

(then select from my_slow_logs...)

Amazon RDS allows this as long as you do this outside the MySQL database.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.