Microsecond Precision with MySQL on Amazon RDS

Running MySQL in the cloud offers many benefits, including extremely fast provisioning and deployment, managed security, and (mostly) easy operations. But nothing comes for free, and as with all things on this wonderful planet, those benefits come with certain limitations.

Until recently, one of the significant drawbacks of MySQL on Amazon RDS was limitation of the precision associated with slow query logs, as the query logging facility could only report query execution times in whole seconds. To log and analyze queries that completed faster than one second, you had to enable full query logging. On a heavily loaded system, full query logging can lead to IO saturation and production impact. Another drawback was that you couldn’t really know exactly how much time was consumed for a particular query, as RDS reported an execution time of 00:00:00 for all queries that executed in less than one second.

The RDS team has done a great job improving MySQL functionality, and they recently announced long-awaited microsecond precision support for MySQL 5.5 and 5.6. Even better, you don’t need to fetch all of the slow query data as a CSV table and reconstruct the slow query log to create a pt-query-digest report. Now you’re able to access both the mysql error log and mysql slow query log via both the RDS console and RDS command line tools (just make sure you are running the updated version).

To access the MySQL slow query log on an Amazon RDS instance, perform the following:

1. Set your MySQL parameter group option log_output to FILE
2. Set slow_query_time option to any desired value including those that less than second
3. Enable query logging by setting slow_query_log to 1

Once you set those values via the RDS console (use the parameter group interface section to apply them) or with RDS tools, you are ready to go. A MySQL restart is not needed to activate these (specific) parameters. Please note that if you are running a highly loaded MySQL instance on non-PIOPS storage, you may need to be careful when setting slow_query_time to low values to prevent your IO subsystem from overloading.

Now you’re ready to fetch slow query logs without accessing the MySQL instance directly, by using an RDS tools call.

rds-download-db-logfile <RDS_host> --log-file-name slowquery/mysql-slowquery.log

Please make sure you’re using the most recent version (RDSCli-1.13.002 worked well for me).

Now you are all set to use pt-query-digest to analyze the log. You can also contact our Blackbird team (contact AT blackbirdit.com) to help you with slow queries elimination and performance tuning.

Happy slow queries hunting!