Tuning Aurora MySQL for real-world workloads — part 3: Shaping Go workloads

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

Tuning Aurora MySQL for real-world workloads — part 3: Shaping Go workloads

This is Part 3 of a three-part series on tuning Aurora MySQL for mixed OLTP and reporting workloads. Part 1 covered observability, Part 2 covered engine tuning and schema optimization. This final part focuses on shaping workloads in Go: execution control, batching, retry logic, transaction isolation, and operational guardrails.

Even with good observability, tuned parameters, and optimized schemas, the way your Go code interacts with the database matters enormously. This part covers patterns that make heavy workloads behave predictably and safely.

Controlling Execution Time: Timeouts and Cancellation

Observability is only helpful if long-running or misconfigured queries can be terminated safely. Heavy reporting or ETL jobs should respect both application-level and server-side execution limits.

Context deadlines in Go

In Go, DB operations should always use QueryContext / ExecContext with a context that carries a timeout:

If the context deadline is reached, the driver should attempt to cancel the underlying query and return promptly. This prevents orphaned work on the database when a caller has already given up.

Server-side caps

In addition to client-side timeouts, MySQL 8 and Aurora MySQL 3 support statement-level limits such as MAX_EXECUTION_TIME:

Press enter or click to view image in full size

For certain reporting sessions, it can be appropriate to set such caps to ensure that mis-sized batches or unexpected data distributions do not result in unbounded server-side work.

Shaping workloads in Go: batching, concurrency, and guardrails

Many of the most problematic queries seen in Perf Insights are long-running operations over large data ranges executed as single statements or transactions. Go makes it comparatively straightforward to restructure these operations into bounded, concurrent, retry-aware workloads.

Windowed processing by time range

Suppose a job processes events in [start, end) using:

Rather than running this over the full range, divide the interval into windows and process them in parallel:

runWindow can:

Set an appropriate isolation level for long-running reads (see below),
Use a context with timeout to bound execution time,
Process rows in a streaming fashion,
Write outputs to staging tables or external sinks.

The SetLimit value should be coordinated with the configured connection pool size and the cluster’s capacity.

Choosing the right chunk size and concurrency

The optimal chunk size depends on your data distribution:

• Too large: individual chunks take too long, reducing parallelism benefits

• Too small: overhead from query setup and connection management dominates

Start with chunks that complete in 1–5 minutes each, then adjust based on observed behavior. Monitor connection pool utilization — if goroutines are frequently waiting for connections, reduce concurrency or increase pool size.

Retry logic for transient errors

Long-running jobs are more likely to experience transient issues such as lock timeouts or deadlocks. It is therefore helpful to centralize retry logic for known retryable MySQL errors:

Exponential backoff implementation

A simple backoff wrapper:

Note: In production, it’s often worth adding jitter to avoid synchronization if many workers retry at once.

This turns many transient failures into bounded delays rather than full job failures.

Batched move to history tables with LIMIT

For archiving data from hot tables to history tables, large INSERT … SELECT statements over multi-month ranges can be replaced with batched operations using an ordered key and LIMIT.

In pseudocode:

Here, insertHistSQL uses WHERE id > ? ORDER BY id LIMIT ?. This constrains the amount of data moved per transaction and allows batch size adjustments without redesigning the process.
If you skip the ORDER BY id LIMIT ? part and just delete huge ranges, you’ll likely see long‑held locks and spikes in replication lag. Batching into predictable chunks allows you to bound the blast radius of each transaction.

Practical considerations for batched archival:

Start with batch sizes around 10,000–100,000 rows depending on row width.
Commit after each batch to release locks promptly.
Add configurable delays between batches if you need to reduce sustained load.
Use feature flags to adjust batch size without redeploying.

Common failure modes this pattern avoids:

Running a single, huge INSERT … SELECT or DELETE over months of data, which holds locks for a long time and can cause large, spiky replication lag.
Retrying a failed large statement from the beginning, effectively doing the same expensive work again.

Precomputed aggregates and summary tables

Some analytical workloads are difficult to optimize adequately on top of OLTP tables, even with indexing and batching. In those cases, introducing summary tables or precomputed aggregates can be highly effective:

Daily or hourly aggregates per key (for example, account, region, or product).
“Latest state” tables that materialize the current state of an entity from an event stream.
Specialized fact tables optimized for specific dashboards or reports.

These can be maintained via:

Scheduled batch jobs using the batching patterns above, or
Change data capture or streaming pipelines that apply updates in near real time.

Pointing heavy dashboards and reports at these tables instead of the underlying OLTP tables reduces contention and simplifies indexing.

Operational guardrails and kill switches

Even well-designed jobs can encounter unexpected data volumes or external constraints. It is therefore helpful to add explicit guardrails:

Feature flags or configuration to:

Enable/disable specific jobs quickly,
Adjust batch sizes and concurrency without redeploying.

Safety checks within jobs, such as:

Aborting if runtime exceeds a configured threshold,
Pausing or reducing concurrency if DB load, CPU, or connection counts exceed predetermined levels.

Example: Runtime Circuit Breaker

These mechanisms help ensure that batch or reporting workloads can be quickly de-risked if they begin to affect OLTP performance.

Transaction isolation: using the right level for each workload

MySQL’s default REPEATABLE READ isolation is generally appropriate for OLTP workloads, providing a consistent view within a transaction at the cost of additional locking in some patterns. For long-running readers over hot tables, however, it can increase the likelihood of deadlocks and prolonged lock waits.

Using READ COMMITTED for long-running readers

For large, read-only queries that can tolerate slightly weaker isolation, running under READ COMMITTED can reduce contention. Rather than changing the global isolation level, it is safer to adjust it per session for specific jobs.

In Go, a helper can encapsulate this:

Note: In a shared connection pool, remember that session-level settings like isolation persist on that physical connection. In production you may want either a dedicated pool for READ COMMITTED workloads or a matching reset of the isolation level once the job completes.

Long-running reporting or ETL transactions that only read data and can accept READ COMMITTED semantics can use this helper. Short OLTP transactions and write-heavy flows remain on the default level.

When to Use READ COMMITTED:

Long-running read-only reports that scan large portions of tables
ETL jobs that read data for export or transformation
Queries where seeing the latest committed data (rather than a snapshot) is acceptable

When to Stick with REPEATABLE READ:

Transactions that read and then write based on what was read
Flows where consistency within the transaction is critical
Short OLTP operations (the overhead difference is negligible)

As always, changes to isolation levels should be driven by:

A clear understanding of the consistency requirements of each workload, and

Observations from Perf Insights and application traces showing that lock waits or deadlocks are a significant contributor to DB load.

Example: Composing the patterns in a batch job

Here is a minimal sketch of how these patterns come together in a daily export job:

runWithCircuitBreaker bounds total runtime.
errgroup processes daily windows in parallel, with a fixed concurrency limit.
withRetry handles transient lock errors.
withReadCommittedTx runs long reads in READ COMMITTED.

Here, min returns the earlier of two timestamps, and exportWindow encapsulates the export logic for a single [from, to) window, typically using the batched INSERT … SELECT pattern described above.

Putting it all together

Optimizing Aurora MySQL for mixed OLTP and reporting workloads is a multi-layered exercise:

Observe carefully:

Use CloudWatch and engine status to understand CPU, I/O, and buffer behavior.
Use Performance Insights, combined with query tagging, to determine which SQL statements dominate DB load and what they wait on.
Use application-level tracing in New Relic to map heavy queries back to services, handlers, and jobs.

Shape queries and workloads:

Improve indexing and, where appropriate, introduce partitioning so the optimizer has efficient plans available.
Refactor monolithic operations into windowed, batched, concurrent, and retry-aware jobs in Go, with explicit timeouts and guardrails.
Introduce summary tables where direct queries on OLTP tables cannot be made efficient enough.

Use the platform effectively:

Size the buffer pool and other key parameters to fit the working set.
Manage connection pools and concurrency limits in line with cluster capacity.
Route appropriate queries to readers, and consider dedicated reporting clusters or fast clones when workloads diverge significantly.
Apply transaction isolation levels deliberately, using READ COMMITTED where it reduces contention for long-running readers.

Applied iteratively to one high-impact workload at a time, this approach can turn an Aurora cluster that appears “too slow for its size” into one that behaves predictably and efficiently under mixed load.

Tuning Aurora MySQL for real-world workloads — part 3: Shaping Go workloads