How do I troubleshoot incompatible RDS Optimized Writes for my Amazon RDS for MySQL or MariaDB database instance?

Short description

The storage configuration, instance type, and engine version for your database instance must support atomic writes. If you restart your database instance, and your configuration, instance type, or engine version doesn't support atomic writes, then you receive the following error message:

"The database is using the doublewrite buffer. RDS Optimized Writes is incompatible with the storage configuration."

Note: When you turn on RDS Optimized Writes, the default value for rds.optimized_writes parameter is set to AUTO.

If you created your database instance before AWS released RDS Optimized Writes, then the underlying file system configuration isn't compatible with RDS Optimized Writes. To resolve this issue, check if the innodb_doublewrite parameter is on or off. Then, complete one of the following tasks:

• To use RDS Optimized Writes for your DB instance, create a blue/green deployment and migrate your data.
• To use the doublewrite buffer instead of RDS Optimized Writes, turn off the rds.optimized_writes parameter for the associated database.

To troubleshoot supported engine versions and RB instance classes, see Using RDS Optimized Writes for MySQL or Using RDS Optimized Writes for MariaDB.

Resolution

Check the innodb_doublewriter parameter

To check the innodb_doublewrite parameter, run the following query:

SHOW global variables like "innodb_doublewrite";

The output displays whether the parameter is set to ON or OFF.

Use RDS Optimized Writes

If your innodb_doublewrite parameter is set to ON, then use Amazon RDS Blue/Green deployments to migrate your data to a supported configuration. When you create the blue/green deployment, you can turn on RDS Optimized Writes, and then upgrade your engine version and DB instance class.

To create a blue/green deployment and migrate your data, complete the following steps:

1. Prepare for the blue/green deployment.
   Note: Review best practices for blue/green deployments.
2. Create the blue/green deployment.
3. For Engine configurations, choose a supported engine version.
4. For Instance configuration, choose Enable Optimized Writes for green database.
5. Select a DB instance class that supports RDS Optimized Writes.
6. For Storage, choose Upgrade storage file system configuration.
   Note: This option upgrades the database to a compatible underlying file system configuration.

Use doublewrite buffer

To use the doublewrite buffer, then set the rds.optimized_writes parameter to OFF in the parameter group associated with the database.

Note: You can't change values in a default parameter group.

To modify the rds.optimized_writes parameter, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases, and then select your database instance.
3. In the Configuration tab, select the parameter group associated with your database instance, and then choose Edit.
4. For Modifiable Parameters, search for the rds.optimized_writes parameter.
5. Update the parameter value to OFF.
6. Choose Save changes.
7. Reboot the database instance.

Related information

Improving write performance with RDS Optimized Writes for MySQL

DoubleWrite Buffer on the MySQL website