Skip to content

AWS Updated 2026-07-04

RDS slow queries: enabling and reading the slow query log on MySQL and Postgres

How to enable the slow query log on RDS MySQL (slow_query_log, long_query_time) and Postgres (log_min_duration_statement), where the logs land, and how to actually read them.

On RDS you enable slow query logging through the DB parameter group, not by editing config files: set slow_query_log = 1 and long_query_time on MySQL, or log_min_duration_statement on Postgres. Both are dynamic parameters, so no reboot is required if the instance already uses a custom parameter group. The logs land under Logs & events on the instance page, and the step most teams skip — exporting them to CloudWatch Logs — is what makes them actually searchable when something is slow at 2 a.m.

What’s happening

RDS does not give you SSH, so everything that would normally be a my.cnf or postgresql.conf edit goes through parameter groups. Instances start life on the default parameter group, which is immutable — if yours is still on default.mysql8.0 or default.postgres16, your first move is creating a custom group and attaching it (attaching a new parameter group requires a reboot for the association itself; the logging parameters afterwards do not). We covered the wider discipline of managing these in parameter group tuning.

On MySQL, the relevant parameters are slow_query_log (0/1), long_query_time (seconds, fractional allowed — production-useful values are 0.5-2, not the ancient default of 10), and optionally log_queries_not_using_indexes. log_output decides whether entries go to FILE (readable and exportable, use this) or TABLE (mysql.slow_log, which quietly eats storage and cannot be exported to CloudWatch).

On Postgres, there is no separate slow log file — slow statements go into the main postgres log when they exceed log_min_duration_statement (milliseconds; 500-2000 is a sane production range; 0 logs everything and will flood a busy instance). Two supporting extensions do the heavy lifting: pg_stat_statements for cumulative statistics (enabled by adding it to shared_preload_libraries — that one is static and needs a reboot) and auto_explain if you want plans logged with the slow statements.

One honest caveat: logging is not free. long_query_time = 0 or log_min_duration_statement = 0 on a high-throughput instance adds measurable I/O and can itself degrade performance. Log the slow tail, not the firehose.

What to check first

  • Which parameter group the instance uses, and whether it is a default group — RDS > Databases > your-instance > Configuration.
  • Whether logging is already on — plenty of instances have it enabled by a predecessor and nobody knew.
  • log_output on MySQL — if it is TABLE, past slow queries are in mysql.slow_log, and storage may be quietly filling.
  • Whether Performance Insights is enabled — for “what is slow right now”, it answers faster than logs; the slow log’s value is the durable, detailed record.

How to verify

Confirm the current values without connecting to the database:

aws rds describe-db-parameters \
  --db-parameter-group-name prod-pg16 \
  --query "Parameters[?ParameterName=='log_min_duration_statement'].[ParameterValue,ApplyType]"

Or from a session:

-- MySQL
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

-- Postgres
SHOW log_min_duration_statement;

List and pull the log files themselves:

aws rds describe-db-log-files --db-instance-identifier prod-db

aws rds download-db-log-file-portion \
  --db-instance-identifier prod-db \
  --log-file-name slowquery/mysql-slowquery.log \
  --output text > slowquery.log

The runbook

  1. Move to a custom parameter group if needed. Create it with aws rds create-db-parameter-group, copy any existing customizations, attach it via modify-db-instance, and reboot in a maintenance window. Do this once; it pays off for every future tuning change.
  2. Set the parameters. MySQL: slow_query_log = 1, long_query_time = 1, log_output = FILE. Postgres: log_min_duration_statement = 1000. Apply with aws rds modify-db-parameter-group --db-parameter-group-name prod-pg16 --parameters "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate". Dynamic parameters take effect within moments; the console shows in-sync when done.
  3. Export to CloudWatch Logs. In RDS > Databases > your-instance > Modify > Log exports, tick the slow query log (MySQL) or postgresql log (Postgres). This is the difference between grepping downloaded files and running a query over weeks of history. CloudWatch Logs ingestion and storage are billed per GB, which is another reason to log the tail, not everything.
  4. Read with tooling, not eyeballs. Aggregate before judging: pt-query-digest slowquery.log for MySQL groups identical statements and ranks by total time; pgbadger does the same for Postgres logs. The query that matters is rarely the single slowest one — it is the 40 ms query executed two million times.
  5. Search in CloudWatch Logs Insights for targeted questions. Example against an exported Postgres log:
fields @timestamp, @message
| filter @message like /duration:/
| parse @message "duration: * ms" as duration_ms
| sort duration_ms desc
| limit 25
  1. Act on the top offenders. EXPLAIN them, index or rewrite, then watch the log volume shrink — the log itself is your before/after measurement.

When this is the wrong move

If the instance is on fire right now, do not start by enabling logs — Performance Insights already shows current top SQL with zero setup lag, and the slow log only captures statements that finish. A query storm of fast statements never appears in a slow log at any threshold; that diagnosis needs pg_stat_statements or performance_schema aggregates. And if you cannot afford the reboot to attach a custom parameter group this week, Performance Insights covers the gap until the maintenance window. The engine-specific parameter references live in the Amazon RDS documentation.

Common mistakes

  • Setting long_query_time or log_min_duration_statement to 0 in production “just to see everything” and creating an I/O problem on top of a latency problem.
  • Leaving log_output = TABLE on MySQL: mysql.slow_log grows inside your allocated storage and never reaches CloudWatch.
  • Enabling the log and never exporting it — RDS rotates local log files, and the evidence for last Tuesday’s incident is gone by Friday.
  • Reading raw logs top-to-bottom instead of aggregating: the slowest single query is usually a weekly report, not your problem.

Prevention

  • Keep slow query logging permanently on at a sane threshold (1s) on every production instance — the cost is small, the forensic value is large.
  • Export to CloudWatch Logs with a retention policy (30-90 days) so cost stays bounded.
  • Run a monthly digest (pt-query-digest / pgbadger) and file tickets for the top three — this catches degradation while it is still cheap.
  • Pair the log with Performance Insights: PI for “now”, the slow log for “last month”.
  • Re-check thresholds after major data growth; a threshold set at 10x typical latency stops catching anything once typical latency triples.

A slow query log that is on, exported, and actually read monthly turns database performance from a recurring emergency into a maintenance task. If nobody on your team truly owns the AWS environment end to end, that is the root of most of these problems — Stack Harbor runs AWS accounts daily under AWS Operations, and you can book an AWS review.