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.

Advertisements

3 Responses to Slow Query in Amazon RDS

  1. Grammy says:

    superb explanation. need to check this now.

  2. Anil says:

    Very Good article.. Its very helpful for beginers with Amazon RDS.. try to post more related topics..

  3. Manoj says:

    Good article!

    Keep it up man!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: