When you’re troubleshooting performance on Amazon Aurora MySQL Serverless, one of the most useful tools available is the slow query log. By capturing queries that take longer than expected, you can pinpoint inefficiencies and optimize your workload.
Aurora Serverless supports slow query logging much like standard RDS MySQL, but with some Aurora-specific twists. This post walks you through enabling the feature, choosing between TABLE and FILE output, and analyzing your logs in CloudWatch.
Step 1: Understand How Aurora Logs Work
Aurora MySQL Serverless (v1 and v2) uses a DB cluster parameter group to manage configuration. You’ll need to modify (or create) a cluster parameter group to enable slow query logging.
You can log queries to:
- TABLE → stored in
mysql.slow_log, queryable with SQL. - FILE → written to RDS logs and optionally exported to CloudWatch Logs for dashboards and alerts.
Step 2: Configure Parameters
The key parameters are:
| Parameter | Value | Purpose |
|---|---|---|
slow_query_log |
1 |
Enables slow query logging |
long_query_time |
1 |
Threshold in seconds |
log_output |
TABLE or FILE |
Output destination |
log_queries_not_using_indexes |
0 or 1 |
Optional: log unindexed queries |
Example: Enable Slow Query Log (to TABLE)
|
1 2 3 4 5 6 7 |
aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name aurora-slowlog \ --parameters \ "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=log_output,ParameterValue=TABLE,ApplyMethod=immediate" |
Example: Enable Slow Query Log (to FILE + CloudWatch)
|
1 2 3 4 5 6 7 |
aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name aurora-slowlog \ --parameters \ "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=log_output,ParameterValue=FILE,ApplyMethod=immediate" |
Step 3: Apply Parameter Group to Cluster
|
1 2 3 4 5 |
aws rds modify-db-cluster \ --db-cluster-identifier YOUR-CLUSTER-ID \ --db-cluster-parameter-group-name aurora-slowlog \ --apply-immediately |
Aurora Serverless v2 supports dynamic parameter changes, so most settings apply immediately. On v1, some may require a stop/start cycle.
Step 4: View Your Logs
Option A: TABLE Output
Run queries directly against the system table:
|
1 2 3 4 5 |
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100; |
Rotate/clear logs when needed:
|
1 2 |
CALL mysql.rds_rotate_slow_log(); |
Option B: FILE Output + CloudWatch
Logs appear under RDS → Logs & Events or in CloudWatch if you enable export:
|
1 2 3 4 5 |
aws rds modify-db-cluster \ --db-cluster-identifier YOUR-CLUSTER-ID \ --enable-cloudwatch-logs-exports '["slowquery"]' \ --apply-immediately |
In CloudWatch, look under:
|
1 2 |
/aws/rds/cluster/YOUR-CLUSTER-ID/slowquery |
Step 5: Analyze Slow Queries in CloudWatch Logs Insights
With logs in CloudWatch, you can use Logs Insights to quickly spot problematic queries.
Go to CloudWatch → Logs Insights, select your slow query log group (e.g., /aws/rds/cluster/<cluster>/slowquery), paste a query, and run it.
Top 10 slow queries by total execution time
|
1 2 3 4 5 6 7 8 9 10 |
fields @timestamp, @message | parse @message "*# Query_time: * Lock_time: * Rows_sent: * Rows_examined: *" as query_time, lock_time, rows_sent, rows_examined | parse @message "*SET timestamp=*;*" as ts, sql | filter ispresent(query_time) and ispresent(sql) | eval qtime = toNumber(query_time) | eval norm_sql = replace(replace(replace(sql, /'[^']*'/, "'?'"), /\b\d+\b/, "?"), /\s+/, " ") | stats sum(qtime) as total_seconds, avg(qtime) as avg_seconds, count(*) as occurrences by norm_sql | sort by total_seconds desc | limit 10 |
Top 10 by average query time
|
1 2 3 4 5 6 7 8 9 |
fields @timestamp, @message | parse @message "*# Query_time: * Lock_time: * Rows_sent: * Rows_examined: *" as query_time, lock_time, rows_sent, rows_examined | parse @message "*SET timestamp=*;*" as ts, sql | filter ispresent(query_time) and ispresent(sql) | eval qtime = toNumber(query_time) | stats avg(qtime) as avg_seconds, max(qtime) as p99ish_max, count(*) as occurrences by sql | sort by avg_seconds desc | limit 10 |
Hot list of queries over 2 seconds
|
1 2 3 4 5 6 7 8 |
fields @timestamp, @message | parse @message "*# Query_time: * Lock_time: * Rows_sent: * Rows_examined: *" as query_time, lock_time, rows_sent, rows_examined | parse @message "*SET timestamp=*;*" as ts, sql | filter ispresent(query_time) and toNumber(query_time) > 2 | display @timestamp, query_time, rows_examined, sql | sort @timestamp desc | limit 100 |
Step 6: Pin to a CloudWatch Dashboard
You can keep an eye on slow query trends right from a CloudWatch Dashboard. Here’s a sample widget JSON that shows the count of slow queries over time.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
{ "widgets": [ { "type": "log", "x": 0, "y": 0, "width": 24, "height": 6, "properties": { "query": "SOURCE '/aws/rds/cluster/YOUR-CLUSTER-ID/slowquery'\n| parse @message \"*# Query_time: * Lock_time: * Rows_sent: * Rows_examined: *\" as query_time, lock_time, rows_sent, rows_examined\n| stats count() as slow_queries by bin(5m)", "region": "us-east-1", "title": "Aurora Slow Query Count (5m buckets)", "view": "timeSeries", "stacked": false, "yAxis": { "left": { "label": "Slow Queries", "showUnits": false } } } } ] } |
How to use:
- Open CloudWatch → Dashboards.
- Create or edit a dashboard.
- Choose “Add widget” → “Logs” → “Source query” → Switch to JSON.
- Paste the JSON, update
YOUR-CLUSTER-IDandregion. - Save the dashboard.
Now you’ll see a real-time chart of slow query counts, binned by 5 minutes.
Best Practices
- Start with
long_query_time=1and adjust based on workload. - Use
log_queries_not_using_indexes=1sparingly—it can generate a lot of noise. - Prefer TABLE output for one-off troubleshooting and FILE + CloudWatch for ongoing monitoring.
- Rotate logs regularly to keep storage manageable:
12CALL mysql.rds_rotate_slow_log();
Quick Setup Script (All-in-One)
Here’s a simple script that enables logging to FILE and exports to CloudWatch:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CLUSTER_ID="your-cluster" PG_NAME="aurora-slowlog" PG_FAMILY="aurora-mysql8.0" AWS_REGION="us-east-1" aws rds create-db-cluster-parameter-group \ --db-cluster-parameter-group-name "$PG_NAME" \ --db-parameter-group-family "$PG_FAMILY" \ --description "Enable slow query logging" \ --region "$AWS_REGION" aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name "$PG_NAME" \ --parameters \ "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \ "ParameterName=log_output,ParameterValue=FILE,ApplyMethod=immediate" \ --region "$AWS_REGION" aws rds modify-db-cluster \ --db-cluster-identifier "$CLUSTER_ID" \ --db-cluster-parameter-group-name "$PG_NAME" \ --enable-cloudwatch-logs-exports '["slowquery"]' \ --apply-immediately \ --region "$AWS_REGION" |
Conclusion
With the slow query log enabled, you’ll have clear visibility into which queries are dragging down performance in Aurora MySQL Serverless. Whether you choose TABLE for ad-hoc analysis, FILE + CloudWatch for continuous monitoring, or pin results to a CloudWatch Dashboard, this feature is an essential part of your optimization toolkit.
Reliable Penguin helps teams optimize AWS environments and manage Aurora clusters every day. If you’d like expert support tuning your database or improving performance, contact us and let’s talk.
