RDS Optimized Reads and Optimized Writes: What NVMe Instance Storage Actually Buys a Temp-Table-Heavy Workload

Who this is for: anyone running temp-table-heavy reporting or analytics queries on RDS for MySQL, MariaDB, or PostgreSQL who’s hit EBS as the bottleneck and wants real numbers on what NVMe instance storage actually buys you.

Our monthly reconciliation job does a handful of large GROUP BY and ORDER BY queries against a table with a couple hundred million rows, and it had crept from twenty-two minutes to just under an hour over a year of data growth. Performance Insights pointed straight at wait time on temp table I/O, not the query plans themselves. I assumed we’d need a bigger instance class. What we actually needed was a different kind of storage under the temp tables, not more of the same kind.

Why This Matters

RDS Optimized Reads places the temporary tables your engine generates for large sorts, joins, and aggregations onto NVMe SSD storage that’s physically attached to the host, instead of on your regular EBS-backed volume. It’s available for RDS for MySQL, MariaDB, and PostgreSQL, and it’s automatically turned on when you’re running one of the NVMe-backed instance classes (the M5d/R5d/M6gd/R6gd generation, and now the newer Graviton4-based R8gd/M8gd classes, which AWS reports deliver up to 165% better throughput and up to 120% better price-performance than R6g for comparable workloads). RDS Optimized Writes is a separate, narrower feature: currently MySQL and MariaDB only, built on the Nitro System’s Torn Write Prevention, and it improves write transaction throughput by writing 16 KiB data pages atomically in a single step instead of needing the double-write buffer MySQL normally relies on for crash safety. Neither feature costs extra beyond the instance class itself, which makes them an unusually clean win if your workload actually matches the pattern they target.

What we measured moving to an NVMe instance class

We moved our reporting replica from a db.r6i.2xlarge (EBS-only) to a db.r8gd.2xlarge, which enables Optimized Reads automatically since it has local NVMe storage.

			
aws rds modify-db-instance \
  --db-instance-identifier reporting-replica \
  --db-instance-class db.r8gd.2xlarge \
  --apply-immediately
-- Confirm temp table I/O is actually landing on instance storage,
-- and compare with Performance Insights before/after
SELECT event_name, SUM(timer_wait)/1000000000 AS wait_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/file/innodb/innodb_temp_file%'
GROUP BY event_name
ORDER BY wait_sec DESC;

		

The reconciliation job dropped from just under an hour back down to twenty-six minutes, close to where it had been a year earlier before the data grew. The InnoDB temp file wait time in Performance Schema went from being the dominant wait event to barely registering. That matched AWS’s description of the feature almost exactly: this wasn’t a general performance boost, it was specifically the temp-table I/O bottleneck disappearing.

Optimized Writes on a write-heavy MySQL instance

We tested Optimized Writes separately on a MySQL 8.0 write-heavy queue-processing instance, where the win is different: normally MySQL protects against torn pages during a crash with a double-write buffer, which means every page gets written twice. Optimized Writes uses the Nitro System’s Torn Write Prevention to write 16 KiB pages atomically without the double write, which showed up for us as roughly a 40% improvement in sustained insert throughput under a steady queue-drain workload, close to AWS’s published “up to 2x” ceiling for the most write-heavy cases.

Optimized Writes has a narrower instance requirement than Optimized Reads. It needs specific instance classes (r5b and r6i were the ones we tested against) and, in our testing, required creating the instance fresh on a supported class rather than converting an existing one in place; we didn’t find a documented in-place conversion path and treated it as a new-instance decision.

Where This Requires Care

Optimized Reads only helps if your bottleneck is actually temp table I/O. If Performance Insights or Performance Schema shows your wait time elsewhere (lock contention, buffer pool misses, network), moving to an NVMe instance class won’t fix it, and you’ll have paid for a different instance shape for nothing.

Optimized Writes is currently MySQL and MariaDB only. If you’re on RDS for PostgreSQL hoping for the same write-path improvement, it isn’t there yet.

The NVMe instance storage backing Optimized Reads is ephemeral and tied to the instance; it’s used only for temp tables and cache, not for your actual data files, so this isn’t a durability tradeoff, but it does mean the instance storage capacity is a hard ceiling for how much temp table spill it can absorb before falling back to slower paths.

Changing instance class is a reboot on single-AZ and a failover on Multi-AZ. Confirm your application tolerates that before applying it to a primary.

Quick Reference

Confirm temp table I/O is your actual bottleneck (Performance Insights, or InnoDB temp file wait events) before moving to an NVMe instance class for Optimized Reads.
Optimized Reads is automatic on NVMe-backed instance classes: the M5d/R5d/M6gd/R6gd generation, and the newer Graviton4 R8gd/M8gd classes for better price-performance.
Optimized Writes is MySQL/MariaDB only and needs a supported instance class (r5b, r6i in our testing); plan for a fresh instance rather than an in-place conversion.
Neither feature costs extra beyond the instance class price, which makes them worth testing on a replica before ruling them out.
NVMe instance storage is ephemeral and used only for temp data, not your actual tables, so there’s no durability tradeoff to weigh.

My Take

This is one of the more targeted performance features AWS has shipped for RDS: it does exactly one thing, moving temp table I/O off EBS, and it does it well when that’s actually your bottleneck. The mistake I almost made was reaching for a bigger instance class before confirming what was actually slow. Check your wait events first. If it’s temp table I/O, Optimized Reads is close to a free win. If it’s something else, no amount of NVMe storage will move the needle.

Arvind Toorpu