Slow Query in Amazon RDS
August 30, 2010 3 Comments
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.