Use the write optimization feature - ApsaraDB RDS - Alibaba Cloud Documentation Center

ApsaraDB RDS for MySQL provides the write optimization feature. You can disable innodb_doublewrite, which refers to the InnoDB doublewrite mechanism, to improve write efficiency. This topic describes how to use the write optimization feature and how to conduct a performance test.

Background information

The InnoDB storage engine for MySQL uses the doublewrite mechanism to ensure the atomicity of data pages and prevent half-write issues. When InnoDB needs to flush dirty pages, the data is written to the doublewrite buffer. When the buffer is full, the thread that is used to flush dirty pages can be executed again only after the previous batch of data is written to the disk. Each flush operation involves two write operations. This increases the consumption of disk I/O resources.

In most cases, disk I/O may become a performance bottleneck for write-intensive applications. The InnoDB doublewrite mechanism significantly increases the consumption of bandwidth resources and affects the performance of your ApsaraDB RDS for MySQL instance.

Feature description

The write optimization feature helps ensure the atomicity of each write operation on data pages and allows you to disable the InnoDB doublewrite mechanism in a secure manner. This reduces the number of write I/O operations, simplifies flush operations, and significantly reduces IOPS and bandwidth required for data writes to disks to improve the write performance of your RDS instance. For more information about the performance test, see Performance tests.

Prerequisites

The RDS instance runs MySQL 5.7 or MySQL 8.0.
The RDS instance uses Enterprise SSDs (ESSDs) or general ESSDs.
The RDS instance is being created or is an existing YiTian RDS instance that runs RDS High-availability Edition or RDS Cluster Edition.

Billing rules

The write optimization feature is provided free of charge.

Usage notes

When you enable or disable the write optimization feature, your RDS instance restarts. Proceed with caution.
If the write optimization feature is enabled for the primary RDS instance, you cannot disable the write optimization feature for read-only RDS instances. This helps prevent replication latency caused by performance inconsistency.
When you use the write optimization feature in scenarios of read-only RDS instance creation and instance backup and restoration, the settings of the original RDS instance are inherited.

Enable the write optimization feature

When you create a standard RDS instance, you must manually enable the write optimization feature. When you create a YiTian RDS instance, the write optimization feature is automatically enabled.

Manage the write optimization feature

If the write optimization feature is enabled for your RDS instance, you can manage the feature on the Basic Information page of the RDS instance.

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
On the Basic Information page, enable or disable the write operation feature.

Performance test

Test preparations

Test resources and methods: In this test, data is written to two RDS instances that run RDS High-availability Edition and use general ESSDs. One of the instances uses a general-purpose instance type and the other instance uses a dedicated instance type. The test is used to compare instance performance before and after the write optimization feature is enabled.

RDS edition	Storage type	Instance family	Instance type	Number of CPU cores and memory capacity
RDS High-availability Edition	General ESSD	General-purpose instance family	mysql.n2.large.2c	4 cores, 8 GB
RDS High-availability Edition	General ESSD	Dedicated instance family.	mysql.x2.large.2c	4 cores, 8 GB

Test tool installation: This section describes how to install sysbench on CentOS. The following commands are used to install sysbench. For more information, see sysbench documentation.

git clone https://github.com/akopytov/sysbench.git
cd sysbench
git checkout 0.5

yum -y install make automake libtool pkgconfig libaio-devel
yum -y install mariadb-devel


./autogen.sh
./configure
make -j
make install

Test data: You must prepare 300 GB of data for the test. Specifically, you must use 30 tables, each of which contains 40 million rows.

sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepare

The following table describes the parameters that are involved in the performance test.

Parameter	Description
tables	The number of tables. In this example, 30 tables are used.
table_size	The number of rows in each table. In this example, each table contains 40 million rows.
rand-type	The distribution type of random numbers. In this example, special is used.
rand-spec-pct	The percentage of special random numbers that are considered as special values. In this example, 15% is used.
threads	The number of concurrent threads. In this example, 64 is used.
time	The duration of the test. Unit: seconds. In this example, 100 is used.

Test the write performance

Run the following command on the tested RDS instances to compare the QPS performance before and after the write optimization feature is enabled:

sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run

Test results

Test results of write optimization

The following figure shows the test results for the standard RDS instance that runs RDS High-availability Edition and uses a general-purpose instance type with 4 cores and 8 GB of memory.
After the write optimization feature is enabled, the QPS performance is improved by 48%.
The following figure shows the test results for the standard RDS instance that runs RDS High-availability Edition and uses a dedicated instance type with 4 cores and 8 GB of memory.
After the write optimization feature is enabled, the QPS performance is improved by 47%.

Test result of write optimization with I/O acceleration

Note

For more information about the I/O acceleration feature of general ESSDs, see Use the I/O acceleration feature of general ESSDs.

The following figure shows the test results for the standard RDS instance that runs RDS High-availability Edition and uses a general-purpose instance type with 4 cores and 8 GB of memory.

After the write optimization feature is enabled, the QPS performance is improved by 93%.