Amazon does not give you direct access to its database server to view any log files (like slow query log files).. But since slow query logs are very much useful in production systems it does give you a view of them in mysql's slow_log table.
Here are some steps you will have to follow to enable Slow Query Logging on RDS (By default it is not enabled):
1. Modify the DB Parameter group your RDS Instance is using. Enable "slow_query_log" parameter.
2. Modify long_query_time to few seconds. Basically it tells to log queries which takes more than this many seconds.
3. Wait till your RDS Instance tells you that it is syncing. Once the DB Parameter group and RDS Instance are in sync (takes around 1 min), you can see that mysql's, slow_log tables shows you detailed view of various queries and how much time those are taking. This will give you a better picture of you various queries you application is firing and you can improve them.
Some points to remember:
1. Queries are logging for new connections.. For existing connections you would see that the queries are not logged.
2. If you want to clear this table, call stored procedure rds_rotate_slow_log. e.g., CALL rds_rotate_slow_log();.
3. When the above stored procedure is called, the existing data in slow_log is dumped to slow_log_backup. You can now dimply dump that table for futher analysis.
4. When you run rds_rotate_slow_log() again the data from slow_log_backup is cleared and fresh data from slow_log is dumped into it.
5. If you are working to improve performance of your application, you would also be interested in turning on "log_queries_not_using_index". This will basically dump queries which are not using index. If you index them you will notice performance benefits.
6. I could not find millisecond precision on RDS.. which means I cannot log queries which are taking less than 1 seconds which is kind of weird. I was more interested to know queries which are taking less time but their frequency is much more high... taking care of such queries would have been more beneficial to me..
Anyways,, I hope my learnings above will help you guys to some extent....
Here are some steps you will have to follow to enable Slow Query Logging on RDS (By default it is not enabled):
1. Modify the DB Parameter group your RDS Instance is using. Enable "slow_query_log" parameter.
2. Modify long_query_time to few seconds. Basically it tells to log queries which takes more than this many seconds.
3. Wait till your RDS Instance tells you that it is syncing. Once the DB Parameter group and RDS Instance are in sync (takes around 1 min), you can see that mysql's, slow_log tables shows you detailed view of various queries and how much time those are taking. This will give you a better picture of you various queries you application is firing and you can improve them.
Some points to remember:
1. Queries are logging for new connections.. For existing connections you would see that the queries are not logged.
2. If you want to clear this table, call stored procedure rds_rotate_slow_log. e.g., CALL rds_rotate_slow_log();.
3. When the above stored procedure is called, the existing data in slow_log is dumped to slow_log_backup. You can now dimply dump that table for futher analysis.
4. When you run rds_rotate_slow_log() again the data from slow_log_backup is cleared and fresh data from slow_log is dumped into it.
5. If you are working to improve performance of your application, you would also be interested in turning on "log_queries_not_using_index". This will basically dump queries which are not using index. If you index them you will notice performance benefits.
6. I could not find millisecond precision on RDS.. which means I cannot log queries which are taking less than 1 seconds which is kind of weird. I was more interested to know queries which are taking less time but their frequency is much more high... taking care of such queries would have been more beneficial to me..
Anyways,, I hope my learnings above will help you guys to some extent....
No comments:
Post a Comment