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.

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 in the MySQL documentation.

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 AWS 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 AWS 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. 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 AWS 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 AWS Regions. To determine whether a DB instance class is supported in a specific AWS Region, see Determining DB instance class support in AWS 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 to AUTO. In default parameter groups, this parameter is always set to AUTO.

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 Working with parameter groups.

For information about creating a DB instance, see Creating an Amazon RDS DB instance.

Console

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.

AWS CLI

To create a DB instance by using the AWS CLI, use 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-version 8.0.30 \
    --db-instance-class db.r5b.large \
    --manage-master-user-password \
    --master-username admin \
    --allocated-storage 200

For Windows:

aws rds create-db-instance ^
    --db-instance-identifier mydbinstance ^
    --engine mysql ^
    --engine-version 8.0.30 ^
    --db-instance-class db.r5b.large ^
    --manage-master-user-password ^
    --master-username admin ^
    --allocated-storage 200

RDS API

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 to AUTO.