Slow Query in Amazon RDS

Amazon RSD will not give you the SSH access to its database server. That means you don’t have the opportunity to view any of the log files , even the slow query log, for the database. But RDS provides a way to play with the slow queries.

First you have to enable slow query in your ‘parameter-group’ with the slow query time. As you do that, login to your database server through phpMyAdmin or the console and select ‘mysql’ database. You can find a table named ‘slow_log’ there. This table will hold all the queries which are slow depending upon your ‘long_query_time’ parameter.

Now, if your slow queries are many then this table will hold too many queries to handle. You cannot truncate the table or delete any row from there. There is only a way to move all the data from this table to another table named ‘slow_log_backup’, making the slow_log table empty.
To do so you have to run a stored procedure already available in RDS. Select the ‘mysql’ database and you can see some stored procedure listed. Run the procedure named ‘rds_rotate_slow_log’ by running ‘CALL rds_rotate_slow_log’ command.

In the back-end RDS just renames those two tables, it does not actually move the data. Running the command twice will delete the slow log completely from the database server.