AWS recently introduced RDS Optimized Reads and RDS Optimized Writes, which are designed to enhance the performance of MySQL and MariaDB workloads running on RDS. These new functionalities can improve query performances and provide higher write throughput but are available on a limited subset of instances and have multiple prerequisites.
RDS Optimized Reads is a feature that provides faster query processing, moving MySQL temporary tables to local NVMe-based SSD storage. According to the cloud provider, queries involving sorts, hash aggregations, high-load joins, and Common Table Expressions (CTEs) can execute up to 50% faster. The new feature was announced during re:Invent for RDS for MySQL workloads running 8.0.28 and above and it is now available for RDS for MariaDB too.
Only a subset of memory-optimized and general-purpose instances support it and the amount of instance storage available on the instance varies by family and size, from a minimum of 75 GB to a maximum of 3.8 TB. The use of local storage can be monitored with three new CloudWatch metrics: FreeLocalStorage, ReadIOPSLocalStorage, and WriteIOPSLocalStorage.
RDS Optimized Writes is instead a feature that delivers an improvement in write transaction throughput at no extra charge, and with the same level of provisioned IOPS, helping write-heavy workloads that generate lots of concurrent transactions. Jeff Barr, vice president and chief evangelist at AWS, explains:
By default, MySQL uses an on-disk doublewrite buffer that serves as an intermediate stop between memory and the final on-disk storage. Each page of the buffer is 16 KiB but is written to the final on-disk storage in 4 KiB chunks. This extra step maintains data integrity, but also consumes additional I/O bandwidth. (...) Optimized Writes uses uniform 16 KiB database pages, file system blocks, and operating system pages, and writes them to storage atomically (all or nothing), resulting in a performance improvement of up to 2x.
Torn Write Prevention (TWP) ensures 16KiB write operations are not torn in the event of operating system crashes or power loss during write transactions. The feature benefits from the AWS Nitro System and is currently available only if the database was created with a DB engine version and DB instance class that support the feature. A database restored from a snapshot can enable the feature only if it was initially created from a server that supports it. According to AWS, RDS Optimized Writes helps workloads like digital payments, financial trading, and gaming applications.
While some users question the lack of PostgreSQL support, Corey Quinn, chief cloud economist at The Duckbill Group, writes in his newsletter:
Not for nothing, but it’s super hard to square this enhancement with the absolutely relentless "RDS? NO! Use Aurora!" messaging we've gotten from AWS top to bottom for the past few years.
RDS Optimized Reads is currently available on M5d, R5d, M6gd, and R6gd instances only. Initially announced for memory-optimized r5 Intel instances running MySQL 8.0.30 and above only, RDS Optimized Writes is now available for a subset of Graviton instances too.
The re:Invent session covering both new features is available on YouTube.
