Tuning Aurora MySQL for real-world workloads — part 1: Making it observable

Published On: November 5th, 20266.8 min readCategories: Engineering

Tuning Aurora MySQL for real-world workloads — part 1: Making it observable

This is Part 1 of a three-part series on tuning Aurora MySQL for mixed OLTP and reporting workloads. This part focuses on observability — the foundation for any tuning effort.

Amazon Aurora MySQL is a strong default for high-throughput transactional systems: you get managed storage, fast replication, and solid baseline performance without extensive tuning. Although most ideas in this series are language-agnostic, it is written from the perspective of teams building Go services and all concrete examples use Go’s database/sql stack.

As clusters grow and start serving both:

Latency-sensitive OLTP traffic, and
Heavy reporting or ETL workloads that scan large tables.

you often see a familiar pattern:

Reporting jobs drift from minutes to hours.
P95/P99 latency for APIs becomes unpredictable under load.
Buffer cache hit ratios deteriorate and read IOPS climb.
Deadlocks and lock timeouts appear around previously quiet tables.

The first question is always whether a heavy scan is necessary at all. Often the best optimization is eliminating the query entirely, through caching, precomputation, or rethinking the access pattern. This series assumes you’ve already asked that question and are now dealing with workloads that genuinely need to run.

Increasing instance size may provide short-term relief, but the more sustainable gains come from:

Establishing visibility into query and request-level performance.
Tuning a small number of InnoDB and Aurora parameters based on measurements.
Being explicit in Go about writer vs reader routing and connection limits.
Aligning indexes and partitions with actual access patterns.
Refactoring heavy queries into bounded, concurrent, retry-aware batches.
Using transaction isolation and timeouts intentionally.
Introducing summary tables where queries cannot be made efficient enough.

This first part focuses on the critical foundation: making system behavior visible before you start changing anything.

Observability: from engine metrics to code paths

Without good visibility it is easy to make configuration changes that do not address the real bottlenecks. A practical setup combines:

Aurora/RDS metrics in CloudWatch for cluster-level health.
RDS Performance Insights for query-level DB load and wait events.
An APM such as New Relic for application-level traces and DB spans.

Cluster-Level signals: CPU, I/O, buffer pool, locks

At the Aurora/RDS level, you want to understand whether the cluster is primarily:

CPU-bound,
I/O-bound, or
Blocked on locks / concurrency.

Useful high-level indicators include:

CPUUtilization — sustained high usage with moderate I/O often indicates CPU-heavy plans (large sorts, aggregations, JSON, complex expressions).
Read/write IOPS and throughput — high values together with a low buffer cache hit ratio suggest the buffer pool is too small relative to the working set.
Deadlock and rollback metrics, combined with wait-event data from Perf Insights, show whether lock contention is dominant.

Buffer pool efficiency can be approximated using global status:

Press enter or click to view image in full size

The ratio Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests indicates how often InnoDB must read from storage instead of servicing requests from memory. A ratio close to zero is desirable. A rising ratio under load suggests the hot working set does not fit in memory.

You can also use the BufferCacheHitRatio metric in CloudWatch for the RDS instance. Values close to 100 percent indicate good buffer‑pool efficiency (most reads served from memory). Sharp drops correspond to periods where queries are reading more from storage, often when large scans or poorly indexed queries exceed the buffer pool.

Performance Insights: DB load and wait classes

RDS Performance Insights provides a DB-centric view:

DB load (average active sessions),
You can break DB load down by normalized SQL digest, user, host, and wait event class.

For each high-load SQL digest, you want to know:

The normalized SQL text (so you can locate it in code).
The dominant wait class (CPU, I/O, lock, or other).
How its DB load contribution evolves after changes.

A typical workflow:

1. Sort the top SQL list by DB load over a representative time window.

2. For each item, inspect the wait breakdown:

Mostly CPU: review execution plan, indexing, expression complexity, JSON usage.
Mostly I/O: look for full scans or large range scans; check buffer pool behavior.
Mostly locks: examine transaction boundaries, isolation levels, and writer/reader patterns.

3. Revisit Perf Insights after each change to verify the effect.

Perf Insights is also effective at revealing plan regressions when a previously inexpensive query becomes one of the top contributors after schema or data distribution changes.

Query tagging with SQL comments

Normalized SQL text can be difficult to map back to code when multiple call sites generate similar queries. A technique that simplifies this mapping is query tagging using SQL comments.

For example:

Key points:

Aurora preserves these comments, so they appear in Perf Insights.
You can search for query:load_events in the codebase to find the relevant code path.
Tags can encode service name, job name, and a stable query identifier that remains valid even if the SQL text changes slightly.

This gives you a consistent “query identity” visible both in the database and in the application.

Application-Level traces with New Relic

Perf Insights does not know about your services, handlers, or jobs. To connect heavy SQL digests to code, instrument the DB layer so each query generates a New Relic datastore segment.

A minimal wrapper around database/sql might look like:

HTTP handlers and background jobs propagate the transaction context:

With this instrumentation in place, New Relic begins surfacing structured datastore segments within application traces. An example view is shown below:

With the Aurora/RDS integration enabled in New Relic, dashboards can correlate:

Service-level latency and error rates,
Aurora CPU, I/O, connections,
DB load and top queries,
Individual traces including DB spans.

Combined with query tagging, this makes it straightforward to move from “top SQL in Perf Insights” to “specific Go job or handler” and back.

Non-Production analysis: slow query log and performance_schema

In environments where Performance Insights is not enabled or you want finer detail, core MySQL tools remain useful:

The slow query log can be enabled with a relatively low threshold (e.g. 200–500 ms) to capture slow statements in development and staging.
performance_schema views such as events_statements_summary_by_digest aggregate statistics per normalized statement, and table_io_waits_summary_by_index_usage can identify unused or rarely used indexes.

These tools are especially useful for iterative tuning and verification outside production.

What’s next

With observability in place, you now have the data to make informed decisions. In Part 2, we’ll cover the tuning itself: buffer pool sizing, connection architecture, writer/reader routing, and schema optimization.

Tuning Aurora MySQL for real-world workloads — part 1: Making it observable