Improving write performance with RDS Optimized Writes for MySQL
You can improve the performance of write transactions with RDS Optimized Writes for MySQL. When your RDS for MySQL database uses RDS Optimized Writes, it can achieve up to two times higher write transaction throughput.
Topics
Overview of RDS Optimized Writes
When you turn on RDS Optimized Writes, your RDS for MySQL databases write only once when flushing data to durable storage without the need for the doublewrite buffer. The databases continue to provide ACID property protections for reliable database transactions, along with improved performance.
Relational databases, like MySQL, provide the ACID properties of
atomicity, consistency, isolation, and durability for reliable database transactions. To
help provide these properties, MySQL uses a data storage area called the
doublewrite buffer that prevents partial page write errors.
These errors occur when there is a hardware failure while the database is updating a
page, such as in the case of a power outage. A MySQL database can detect partial page
writes and recover with a copy of the page in the doublewrite buffer. While this
technique provides protection, it also results in extra write operations. For more
information about the MySQL doublewrite buffer, see Doublewrite Buffer
With RDS Optimized Writes turned on, RDS for MySQL databases write only once when flushing data to durable storage without using the doublewrite buffer. RDS Optimized Writes is useful if you run write-heavy workloads on your RDS for MySQL databases. Examples of databases with write-heavy workloads include ones that support digital payments, financial trading, and gaming applications.
These databases run on DB instance classes that use the Amazon Nitro System. Because of the hardware configuration in these systems, the database can write 16-KiB pages directly to data files reliably and durably in one step. The Amazon Nitro System makes RDS Optimized Writes possible.
You can set the new database parameter rds.optimized_writes
to control
the RDS Optimized Writes feature for RDS for MySQL databases. Access this parameter in the DB parameter
groups of RDS for MySQL version 8.0 and RDS for MySQL version 8.4. Set the parameter using the
following values:
-
AUTO
– Turn on RDS Optimized Writes if the database supports it. Turn off RDS Optimized Writes if the database doesn't support it. This setting is the default. -
OFF
– Turn off RDS Optimized Writes even if the database supports it.
If you have an existing database with an engine version, DB instance class, and/or file system format that doesn't support RDS Optimized Writes, you can enable the feature by creating a blue/green deployment. For more information, see Enabling RDS Optimized Writes on an existing database.
If you migrate an RDS for MySQL database that is configured to use RDS Optimized Writes to a DB instance class that doesn't support the feature, RDS automatically turns off RDS Optimized Writes for the database.
When RDS Optimized Writes is turned off, the database uses the MySQL doublewrite buffer.
To determine whether an RDS for MySQL database is using RDS Optimized Writes, view the current value of the
innodb_doublewrite
parameter for the database. If the database is using RDS Optimized Writes, this
parameter is set to FALSE
(0
).
Using RDS Optimized Writes
You can turn on RDS Optimized Writes when you create an RDS for MySQL database with the RDS console, the Amazon CLI, or the RDS API. RDS Optimized Writes is turned on automatically when both of the following conditions apply during database creation:
-
You specify a DB engine version and DB instance class that support RDS Optimized Writes.
-
RDS Optimized Writes is supported for RDS for MySQL version 8.0.30 and higher. For information about RDS for MySQL versions, see MySQL on Amazon RDS versions.
-
RDS Optimized Writes is supported for RDS for MySQL databases that use the following DB instance classes:
-
db.m7g
-
db.m6g
-
db.m6gd
-
db.m6i
-
db.m5
-
db.m5d
-
db.r7g
-
db.r6g
-
db.r6gd
-
db.r6i
-
db.r5
-
db.r5b
-
db.r5d
-
db.x2idn
-
db.x2iedn
For information about DB instance classes, see DB instance classes.
DB instance class availability differs for Amazon Web Services Regions. To determine whether a DB instance class is supported in a specific Amazon Web Services Region, see Determining DB instance class support in Amazon Web Services Regions.
To upgrade your database to a DB instance class that supports RDS Optimized Writes, you can create a blue/green deployment. For more information, see Enabling RDS Optimized Writes on an existing database.
-
-
-
In the parameter group associated with the database, the
rds.optimized_writes
parameter is set toAUTO
. In default parameter groups, this parameter is always set toAUTO
.
If you want to use a DB engine version and DB instance class that support RDS
Optimized Writes, but you don't want to use this feature, then specify a custom
parameter group when you create the database. In this parameter group, set the
rds.optimized_writes
parameter to OFF
. If you want the
database to use RDS Optimized Writes later, you can set the parameter to
AUTO
to turn it on. For information about creating custom parameter
groups and setting parameters, see Parameter groups for Amazon RDS.
For information about creating a DB instance, see Creating an Amazon RDS DB instance.
When you use the RDS console to create an RDS for MySQL database, you can filter for the DB engine versions and DB instance classes that support RDS Optimized Writes. After you turn on the filters, you can choose from the available DB engine versions and DB instance classes.
To choose a DB engine version that supports RDS Optimized Writes, filter for the RDS for MySQL DB engine versions that support it in Engine version, and then choose a version.
In the Instance configuration section, filter for the DB instance classes that support RDS Optimized Writes, and then choose a DB instance class.
After you make these selections, you can choose other settings that meet your requirements and finish creating the RDS for MySQL database with the console.
To create a DB instance by using the Amazon CLI, run the create-db-instance
command. Make sure the --engine-version
and
--db-instance-class
values support RDS Optimized Writes. In addition, make sure
the parameter group associated with the DB instance has the
rds.optimized_writes
parameter set to AUTO
. This
example associates the default parameter group with the DB instance.
Example Creating a DB instance that uses RDS Optimized Writes
For Linux, macOS, or Unix:
aws rds create-db-instance \ --db-instance-identifier
mydbinstance
\ --engine mysql \ --engine-version8.0.30
\ --db-instance-classdb.r5b.large
\ --manage-master-user-password \ --master-usernameadmin
\ --allocated-storage200
For Windows:
aws rds create-db-instance ^ --db-instance-identifier
mydbinstance
^ --engine mysql ^ --engine-version8.0.30
^ --db-instance-classdb.r5b.large
^ --manage-master-user-password ^ --master-usernameadmin
^ --allocated-storage200
You can create a DB instance using the CreateDBInstance
operation. When you use this operation, make sure the EngineVersion
and DBInstanceClass
values support RDS Optimized Writes. In
addition, make sure the parameter group associated with the DB instance has the
rds.optimized_writes
parameter set to AUTO
.
Enabling RDS Optimized Writes on an existing database
In order to modify an existing RDS for MySQL database to turn on RDS Optimized Writes, the database must
have been created with a supported DB engine version and DB instance class. In addition,
the database must have been created after RDS Optimized Writes was
released on November 27, 2022, as the required underlying file system configuration is
incompatible with that of databases created before it was released. If these conditions
are met, you can turn on RDS Optimized Writes by setting the rds.optimized_writes
parameter
to AUTO
.
If your database was not created with a supported engine version, instance class, or file system configuration, you can use RDS Blue/Green Deployments to migrate to a supported configuration. While creating the blue/green deployment, do the following:
-
Select Enable Optimized Writes on green database, then specify an engine version and DB instance class that supports RDS Optimized Writes. For a list of supported engine versions and instance classes, see Using RDS Optimized Writes.
-
Under Storage, choose Upgrade storage file system configuration. This option upgrades the database to a compatible underlying file system configuration.
When you create the blue/green deployment, if the rds.optimized_writes
parameter is set to AUTO
, RDS Optimized Writes will be automatically
enabled on the green environment. You can then switch over the blue/green deployment,
which promotes the green environment to be the new production environment.
For more information, see Creating a blue/green deployment.
Limitations for RDS Optimized Writes
When you're restoring an RDS for MySQL database from a snapshot, you can only turn on RDS Optimized Writes for the database if all of the following conditions apply:
-
The snapshot was created from a database that supports RDS Optimized Writes.
-
The snapshot was created from a database that was created after RDS Optimized Writes was released.
-
The snapshot is restored to a database that supports RDS Optimized Writes.
-
The restored database is associated with a parameter group that has the
rds.optimized_writes
parameter set toAUTO
.