AWS RDS MySQL / Slow Query Log

Question

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.

Answer 1

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` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `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 ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 1 row in set (0.01 sec) mysql> 

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

• slow_query_log
• log_output

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) mysql> 

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.

CAVEAT

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 ENGINE=MyISAM; 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' mysql> 

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

Give it a Try !!!

Answer 2

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.