2

I would like to enable/disable slow query logging on an Amazon RDS instance without having to restart. I'm aware that I can do this by changing the slow_query_log parameter in the instance's parameter group, but this requires me to restart.

On the other hand, I've seen that this is possible with SET GLOBAL slow_query_log = 'ON'; in MySQL, but this requires super privileges, which are restricted to the rdsadmin user on Amazon RDS.

I've also found this resource: How do I enable functions, procedures, and triggers for my Amazon RDS MySQL instance? but the description says "when binary logging is enabled", which is not what I need.

2

The slow_query_log variable is a dynamic variable.

Dynamic variables can be updated without a restart:

When you change a dynamic parameter and save the DB parameter group, the change is applied immediately regardless of the Apply Immediately setting.

So just change it and you should see the effects immediately.

  • Yes, I saw that it's a dynamic variable, but still, after I change it, the instance's state is applying, and then it's pending-reboot :/ – Zoltán Nov 24 '16 at 9:34
  • 1
    Have slow queries started/stopped being logged? long_query_time (dev.mysql.com/doc/refman/5.7/en/…) is also a dynamic variable. Set it to a very small number (or even 0) and it should log every single query. Slow logs are written and downloadable in real time, so you should be able to immediately see the effects. We've changed these variables at my work before without doing a reboot, so I know from experience it should work. – Willem Renzema Nov 24 '16 at 9:40
  • I just tried switching between parameter groups where the only difference is the slow query log setting. After applying, the instance status said "modifying", but the instance was still available during that time. – Sean Fahey Jan 23 '18 at 20:53
0

This is all documented on the AWS documentation site, here.

You can monitor the MySQL error log, slow query log, and the general log. The MySQL error log is generated by default; you can generate the slow query and general logs by setting parameters in your DB parameter group.

  • Thanks, but the emphasis of the question was how to do it without having to restart the RDS instance. I want to be able to switch it on and off on production. Changing the parameter group, as you suggest, does require a restart in this case. – Zoltán Nov 24 '16 at 9:17
  • I should probably learn to read – Philᵀᴹ Nov 24 '16 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.