RDS MySQL not using NVMe instance storage - no benefit of "Optimised Reads"

Question

I am load testing various RDS instances trying to optimise db price/performance

I had seen this doc https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-optimized-reads.html and it sounded like a __d instance with local SSD storage would likely improve performance

The r6gd.2xlarge that I tested has 1x474GB NVMe, which is big enough to fit the whole dataset (EBS volume is only 400GB).

However when I check the metrics in Performance Insights the stats for ReadIOPSLocalStorage and ReadThroughputLocalStorage are basically zero. I do see a big chunk of WriteThroughputLocalStorage but it doesn't seem to be reading anything.

And if I test an r6g instance without the NVMe the performance is basically the same.

So it doesn't seem to be doing anything.

The AWS doc says it will automatically do "Optimised Reads" if you deploy one of the __d instances. But I am wondering if some manual config changes are needed to make best use of it?

There is this note in the doc:

Set the internal_tmp_mem_storage_engine parameter to TempTable, and set the temptable_max_mmap parameter to match the size of the available storage on the instance store.

Currently the latter value is 1073741824 i.e. 1 GiB, well short of the NVMe size

I tried bumping this up to 246960619520 but didn't see any more usage or very different performance, and FreeLocalStorage stat shows it's basically all free still.

How do I get benefit from local SSD storage and "Optimised Reads" on my RDS MySQL instance?

(It's recently deployed, on MySQL 8.0.35)

Answer 1

This doc says:

The following use cases are candidates for RDS Optimized Reads:

• ...
• Applications that run on-demand or dynamic reporting queries that involve complex operations, such as queries with GROUP BY and ORDER BY clauses

...which is why I thought it would be useful for my db workload.

However this doc better explains what "Optimised Reads" actually does:

The following temporary objects are stored in the instance store volumes for Optimized Reads: internal temporary files, internal on-disk temporary tables, user-created temp tables, memory map files created by the TempTable engine, and temporary binlog cache files. Offloading the temporary objects to the instance store volume benefits use cases such as queries that involve large temporary objects like temporary tables or sort files. Furthermore, it helps when application queries involve using temporary user-created tables, complex CTEs, derived tables, or sub-queries.

So it only benefits queries which use temporary tables that are too large to fit in memory:

A key simplification for manageability for the TempTable storage engine is that it uses a memory pool for all sessions that use it. The memory limit, set by temptable_max_ram, applies to the sum of memory consumption from concurrent sessions. This is different from the MEMORY storage engine, where the memory limit, set by the smaller tmp_table_size and max_heap_table_size, is per table, specific to a session. The TempTable storage engine also has its own disk overflow mechanism. You can configure it to overflow either to memory-mapped temporary files or to InnoDB on-disk internal temporary tables. A memory-mapped file provides the mapping between a file and memory space that speeds up file read and write operations. In addition to the memory limit, these two separate overflow paths can bring out distinctive aspects of internal temporary table storage engines and directly impact query performance.

So... I may have some queries which use temp tables, but maybe not many, or they mostly don't reach the threshold to spill over to the NVMe storage.

Interesting/frustratingly it looks like the same feature for Postgres probably works more like I was hoping for:

https://aws.amazon.com/blogs/database/new-amazon-aurora-optimized-reads-for-aurora-postgresql-with-up-to-8x-query-latency-improvement-for-i-o-intensive-applications/

Tiered cache – This allows you to extend your DB instance caching capacity by utilizing the local NVMe storage. It automatically caches database pages about to be evicted from the in-memory database buffer pool, offering up to eight times better latency for queries that were previously fetching data from Aurora storage.