Docs

RDS for MySQL

Performance

Inplace DDL performance optimization

This topic describes the inplace DDL performance optimization feature of RDS for MySQL.

Supported version

MySQL 5.7 version, and the kernel minor version is MySQL 5.7.44_20251015 and above.

MySQL 8.0 version, and the kernel minor version is MySQL 8.0.43_20251215 and above.

MySQL 8.4 version, and the kernel minor version is MySQL 8.4.7_20260415 and above.

tip

You can view the kernel minor version of an instance by Viewing instance information. If the kernel minor version of your instance is not within the supported kernel minor version range, you can Manually upgrading the instance kernel minor version.

Background

In some DDL scenarios, the performance of community MySQL 8.0 is lower than that of MySQL 5.7. Creating an index in MySQL involves the scan, sort, insert, and flush stages. The time statistics for each stage show that the sort and insert stages are the most time-consuming. For example, in community MySQL 8.0, when creating an index on 40 million rows of Sysbench data with a single thread, the sort and insert stages account for up to 72% of the total execution time.

Feature description

RDS for MySQL has optimized some hot-spot functions, such as removing unnecessary page validation in the insert stage, optimizing the two-way merge sort algorithm, and optimizing the execution path.

Instructions

Parameter description

RDS for MySQL provides a modifiable parameter for MySQL 8.0 and MySQL 8.4 instances to enable or disable the Inplace DDL optimization feature.

warning

In RDS for MySQL 5.7 instances, there is no separate modifiable parameter for setting the Inplace DDL optimization feature. If the Parallel DDL feature is enabled on a MySQL 5.7 instance, the Inplace DDL optimization feature is enabled by default.

Parameter name	Type	Default value	Does it need instance restart to take effect?	Value range	Scope	Parameter description
loose_innodb_inplace_ddl_optimize	bool	OFF	No	[ON/OFF]	GLOBAL/SESSION	Enables or disables the inplace DDL optimization feature. Values: ON: enable. OFF: disable.

Examples

To use the Inplace DDL optimization feature in RDS for MySQL 8.0 and MySQL 8.4 instances, perform the following steps.

Modify the loose_innodb_inplace_ddl_optimize parameter to ON. For more information about modifying parameters, see Modifying parameters.

Use Inplace DDL optimization to create an index.

Create the index.

Use the Inplace DDL optimization feature to execute the DDL for rebuilding the table.

The DDL statement for Inplace DDL optimization is as follows.

Performance test

Test Environment

Instance specification: RDS for MySQL 32C64G

Instance version: MySQL 8.0

Test data: 8 GiB of Sysbench data

Test steps

Create indexes for the test data by using different concurrency levels in a Community Edition MySQL 8.0 instance and an RDS for MySQL 8.0 instance. Record the total time for index creation and the sum of the time for the sorting and insertion phases.

The test results are as follows.

Index creation time

Concurrency	MySQL 8.0 Community Edition (Unit: Second)	RDS for MySQL 8.0 (Unit: Second)	Performance improvement percentage
1	110.76	81.27	36.20%
2	69.19	51.25	35%
4	51.52	43.73	17.80%
8	43.3	36.33	19.10%
16	40.59	35.26	15.10%
32	42.95	36.06	19.10%

Sum of the time for the sorting and insertion phases

Concurrency	MySQL 8.0 Community Edition (Unit: Second)	RDS for MySQL 8.0 (Unit: Second)	Performance improvement percentage
1	79.9	51	56.60%
2	50.15	32.03	56.50%
4	40.31	30.53	32.03%
8	35.93	28.53	25.90%
16	35.23	28.55	23.30%
32	37.27	30.82	20.90%

Last updated: 2026.05.28 15:17:55