Optimized Writes
Applications expect relational databases to guarantee “ACID” properties— atomicity, consistency, isolation, and durability—for database transactions. Since MySQL and MariaDB are written to run on a variety of hardware systems, they are designed conservatively to maintain these ACID properties in the face of a wide range of possible failure scenarios. For example, most storage systems provide atomicity of either 512 bytes or 4KiB, which is less than the 16KiB page size used by MySQL and MariaDB. In these cases, the risk of storage corruption arises when an unexpected failure, such as a loss of power, occurs in the middle of writing a page. The corresponding data loss can lead to incomplete or torn page writes to the database systems. InnoDB, MySQL’s default storage engine, protects against these failure scenarios by writing each 16KiB page twice to durable storage—first to a storage area called the “doublewrite buffer” and then to a data file. During crash recovery, the doublewrite buffer provides an intact copy even if the data file is corrupted. However, there is an additional I/O overhead associated with writing to the doublewrite buffer that impacts database performance and application latency as it reduces the possible number of transactions processed per second.
To support 16KiB writes, the Linux filesystem must support 16KiB pages. This can be achieved by using the cluster-based allocation provided by ext4’s bigalloc feature in the ext4 subsystem. This is enabled by default on database instances available on Optimized Writes, such as db.r6i, db.r6g, db.r5b, db.x2iedn, and db.x2idn. With Optimized Writes, relational databases that use InnoDB as the database engine will benefit from torn write prevention, a feature of the AWS Nitro System, to reliably and durably write to table storage in one step. Torn write prevention writes data to storage in all-or-nothing write transactions, which eliminates the need for using the doublewrite buffer. This prevents partial, or torn, data from being written to storage in the event of an operating system crash or power loss during write transactions. Optimized Writes can help increase the number of transactions processed per second and decrease write latency by up to 2x without compromising the data resiliency of your workloads. Optimized Writes is available for RDS for MySQL versions 8.0.30 and higher and RDS for MariaDB versions 10.6.10 and higher.
While using Optimized Writes, you have full control of the feature through DB parameter groups. You can configure the database parameter rds.optimized_writes to set AUTO or OFF for your databases. The default value for this parameter is AUTO. This enables Optimized Writes on your Amazon RDS instances if your database engine version and instance class support it. If you would like to turn off Optimized Writes, you can simply configure it from AUTO to OFF. As a result, upon rebooting the DB instance, your database will begin using the doublewrite buffer for durability.
To determine if an Amazon RDS database uses Optimized Writes, view the current value of the innodb_doublewrite parameter for the database. If the database is using Optimized Writes, this parameter is set to OFF.
mysql> show variables like 'innodb_doublewrite';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_doublewrite | OFF |
+--------------------+-------+
1 row in set (0.00 sec)
What Use Cases can Benefit from Optimized Reads and Optimized Writes?
The Optimized Reads feature is particularly useful for analytical applications that frequently use Common Table Expressions (CTEs), derived tables, and grouping operations. It is also beneficial for on-demand or dynamic reporting queries that involve complex operations, such as queries with GROUP BY and ORDER BY clauses, which may not always be able to use appropriate indexes. Optimized Reads is helpful for workloads that involve creating large temporary tables, either directly or in procedures, to store intermediate results due to their monolithic nature. If you see a lot of queries that require disk access for temporary processing, an Optimized Reads instance might be a good fit. One way you can evaluate this is by restoring a snapshot of your Amazon RDS instance and replaying your workload. Refer to the restoring from a snapshot documentation for more information.
If your workload aligns with one or more of these use cases, the following factors can further influence the benefit of using local storage:
- Concurrency – Because the data transfer for Amazon EBS occurs via network, diverting your data to a local storage helps you to avoid network I/O latencies. If your workload has highly concurrent read/write temporary processing, this greatly helps improve the overall performance of your Amazon RDS instance.
- Use of Amazon RDS Multi-AZ – The difference in performance improvement may also depend on whether your Amazon RDS instance is using Multi-AZ. Both Multi-AZ with one standby and Multi-AZ with two readable standby instances with local storage provide better performance than Amazon EBS-only backed Multi-AZ instances. This is because in an Amazon EBS-only backed Multi-AZ instance, the whole EBS volume is replicated to a secondary instance that also includes temporary data. Because the replication occurs at the storage layer, you are unable to exclude certain datasets from replication. With Optimized Reads, you avoid this completely because the local storage disk is not part of the replication and is not needed for recovery or failover.
By enabling Optimized Writes, RDS for MySQL and RDS for MariaDB databases can write data to durable storage without using the doublewrite buffer, resulting in a single write operation. By more efficiently writing your data storage, you can more affordably scale your applications and accommodate future business growth. Optimized Writes is particularly useful for applications with write-heavy and concurrent connections, such as those used for digital payments, financial trading, and gaming. Additionally, applications that frequently perform data loading operations (LOAD DATA LOCAL INFILE) or bulk inserts into tables can benefit from this feature. These applications can access the extended storage I/O headroom provided by Optimized Writes without requiring additional provisioned capacity.
Next Steps
If you're a developer looking to enhance your RDS for MySQL, RDS for MariaDB, or RDS for PostgreSQL databases' performance, you can take advantage of Amazon RDS Optimized Reads. Users of RDS for MySQL and MariaDB can also utilize Amazon RDS Optimized Writes. Amazon RDS Optimized Reads can improve query processing on supported instances by up to 2x compared to previous generation instances for read workloads, while Amazon RDS Optimized Writes increases write transaction throughput by up to 2x. Both of these features are available for no extra cost. Consider benchmarking your workload or queries on Optimized Read-enabled and Optimized Write-enabled instances to understand your potential performance improvements.