Tuning Aurora MySQL for real-world workloads — part 2: Engine, connections, and schema

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

Tuning Aurora MySQL for real-world workloads — part 2: Engine, connections, and schema

This is Part 2 of a three-part series on tuning Aurora MySQL for mixed OLTP and reporting workloads. Part 1 covered observability. This part focuses on the tuning itself: engine parameters, connection architecture, and schema optimization.

With observability in place (see Part 1 — link), you now have the data to identify bottlenecks. The next step is tuning — but with a light touch. Aurora handles many things automatically, so the goal is adjusting a small number of parameters that genuinely matter for your workload.

Engine and parameter tuning: working with InnoDB

Buffer pool sizing — give InnoDB room to breathe

innodb_buffer_pool_size controls how much data and index information InnoDB can keep in memory. For mixed OLTP and reporting workloads on larger Aurora instances, it is often beneficial to allocate a substantial fraction of instance memory to the buffer pool.

A typical iterative process:

1. Measure buffer pool behavior and read I/O under representative load.

2. Increase innodb_buffer_pool_size in the parameter group (subject to Aurora’s constraints and restart requirements).

3. Re-measure:

Buffer pool hit ratio,
Read IOPS and throughput,
Latency for previously I/O-bound queries.

Large buffer pools can benefit from multiple buffer pool instances (innodb_buffer_pool_instances) to reduce internal contention. Aurora generally derives a sensible value, but it is worth checking that recommendations for your engine version and pool size are followed.

As a starting point for larger instances, conceptually in a parameter group, you’re aiming for something like:

For I/O-bound workloads, more buffer pool generally means:

Fewer disk reads,
Higher buffer pool hit ratio,
More stable performance.

Aurora’s distributed storage layer changes the impact of traditional I/O tuning parameters. In practice, you’ll usually get more value from buffer pool sizing and workload/query changes than from tweaking low-level I/O knobs like innodb_io_capacity or innodb_flush_log_at_trx_commit.

Sort/Join buffers and temp tables

When Perf Insights shows high CPU usage and temp-table activity, and EXPLAIN reports Using temporary; Using filesort for large result sets, consider:

First, whether indexing or query shape changes can avoid the temp tables entirely.
For queries that are intentionally aggregating or sorting large amounts of data, moderate increases to sort and join buffers can help, keeping in mind these are typically per-connection.

Changes to these parameters must consider the product of buffer sizes and maximum concurrent connections. A 4MB sort_buffer_size with 500 connections could consume 2GB just for sort buffers.

Press enter or click to view image in full size

This ensures that only dedicated reporting connections use large read buffers, while OLTP connections continue with conservative defaults.

Connection architecture: pools, routing, and Aurora-specific patterns

Beyond engine settings, the way the application uses connections has a significant impact on Aurora behavior.

Connection pooling and concurrency limits

For each role (writer, readers), it is generally preferable to:

Maintain a single shared *sql.DB instance per process,
Configure explicit limits for SetMaxOpenConns, SetMaxIdleConns, and optionally SetConnMaxLifetime.

These should align with:

Aurora instance vCPU count,
max_connections on the cluster,
Expected connection usage from other services.

Concurrency in batch jobs (e.g. errgroup.SetLimit) should be calibrated against these pool limits. For example, if a reader pool is configured with 64 max open connections and a process has only one such pool, spawning 200 concurrent worker goroutines will cause contention and queuing in the client.

A reasonable rule of thumb: keep concurrent DB-using goroutines per process in the same order as the pool size, and ensure the sum across services fits within Aurora’s connection budget.

Writer vs Reader routing in Go

Aurora makes it straightforward to add reader instances, but they only provide value if the application routes traffic appropriately. A simple first step is to introduce a DB wrapper that centralizes routing decisions at the caller site:

Callers specify ConnReader or ConnWriter on each Query, so routing decisions are explicit at the call site rather than inferred from SQL text.

This ensures:

All obvious writes, DDL, and transactional updates go to the writer.
Read-only queries go to readers by default.

Over time, the routing logic can be refined:

Certain read paths that require the latest committed data can be forced to the writer even if they are SELECTs.
Specific reporting or ETL jobs can be configured to use particular reader endpoints.
Feature flags and configuration can control routing for specific call sites without code changes.

With routing in place, Perf Insights and New Relic should show:

OLTP-type load primarily on the writer,
Reporting and batch load primarily on readers.

Replica lag metrics then inform whether any read paths need to be kept on the writer for correctness.

Aurora-specific scaling patterns: Reporting clusters and fast clones

When mixed workloads become too demanding for a single cluster, Aurora offers a few scaling options beyond additional readers:

Dedicated reporting cluster:

Create a separate Aurora cluster that replicates from the primary and route heavy analytics/reporting traffic exclusively to that cluster. This isolates OLTP performance from fluctuations in reporting load.

Fast clones for ad-hoc processing:

Use Aurora’s fast database cloning to create a temporary cluster from an existing snapshot for ad-hoc analysis or backfills, perform heavy processing there, and then discard the clone. This offloads extremely intensive workloads from the main cluster.

These patterns are useful when, even with careful tuning, certain reporting or ETL tasks are fundamentally incompatible with the latency and availability requirements of OLTP traffic on the same cluster.

Schema and Indexing: giving the planner good options

Engine tuning and connection management only help if the optimizer has reasonable execution plans available.

Reading execution plans

Before making schema changes, examine EXPLAIN output for the queries highlighted by Perf Insights. Warning signs include:

type: ALL on large tables (full table scan),
Very large rows estimates,
Extra: Using temporary; Using filesort on large result sets where an index could satisfy the sort.

For example, consider:

A non-ideal plan might use an index only on account_id and still require a filesort. Introducing a composite index:

often changes the plan to a narrower range scan that both filters and orders efficiently.

Before adding the composite index, EXPLAIN might show something like:

This indicates a full scan with an explicit filesort over a large number of rows.

After introducing the composite index on (account_id, created_at), the plan becomes more selective:

The engine optimizer can now use a range scan on the new index to both filter and order results, reducing the number of rows read and eliminating the expensive filesort.

Covering indexes and trade-offs

For queries that are particularly hot and latency-sensitive, a covering index can avoid additional table reads. Extending the previous example to:

allows the engine to answer the query from the index alone in many cases. This improves read performance at the cost of index size and write overhead, and should therefore be reserved for the most heavily used patterns.

Partitioning and pruning

When a table grows to billions of rows and queries naturally use a date range, range partitioning by date can help:

• Queries constrained by date can be resolved by scanning only a subset of partitions.

• Retention can be implemented as partition drops instead of large delete operations.

• Maintenance such as index rebuilds can be localized to individual partitions.

Partitioning does not replace indexing — it narrows the data set on which indexes operate. Queries must consistently include predicates on the partitioning key for pruning to be effective.

Deploying Schema and Index Changes Safely

On large production tables, schema and index changes must be applied carefully:

• Use ALTER TABLE … ALGORITHM=INPLACE, LOCK=NONE (where supported by your Aurora MySQL version) to minimize locking during index creation.

• When supported, use invisible indexes to stage rollouts: add new indexes as INVISIBLE so they can build and warm up, test their impact on execution plans, then flip to VISIBLE once you’re confident. Similarly, mark old indexes as INVISIBLE before dropping them so you can revert quickly if needed.

• For changes that cannot be performed online, or when behavior is uncertain, consider external tools that implement online schema changes with triggers and shadow tables, thoroughly tested in non-production first.

• Monitor Perf Insights and CloudWatch during the migration window to ensure that lock waits, replication lag, and query latencies remain within acceptable bounds.

Planning index and schema changes in stages, with clear rollback options, is essential for safety on busy clusters.

What’s Next

With engine tuning, connection architecture, and schema optimization in place, Part 3 will focus on shaping workloads in Go: batching, concurrency control, retry logic, transaction isolation, and operational guardrails.

Tuning Aurora MySQL for real-world workloads — part 2: Engine, connections, and schema

Tuning Aurora MySQL for real-world workloads — part 2: Engine, connections, and schema