Feature Introduction
This feature optimizes the time consumption of secondary index creation. Enabling it utilizes multi-threaded concurrency for external sorting of secondary index data, while optimizing locking operations on the flush list during the flush bulk loading phase. This effectively reduces the duration of CREATE INDEX operations and minimizes their impact on concurrent DML.
Supported Versions
Kernel version MySQL 8.0 20210330 and above.
Kernel version MySQL 5.7 20210331 and above.
Applicable Scenarios
Databases frequently execute DDL operations and often encounter DDL-related issues, such as:
Why does adding indexes cause instance jitter, affecting normal business read and write operations?
Why does executing DDL statements on tables under 1GB sometimes take over ten minutes?
Why do connections that use temporary tables cause instance jitter when they are exited?
To address the above common issues, the TXSQL kernel team conducted in-depth analysis and testing across multiple scenarios. They optimized locking operations on the flush list during the flush bulk loading phase, effectively reducing the duration of CREATE INDEX operations and minimizing their impact on concurrent DML, thereby mitigating the effects of DDL operations.
Performance Data
In the sysbench test, 2 billion rows of data, approximately 453 GB, were imported with the FAST DDL feature enabled.
mysql> set global innodb_fast_ddl=ON;
Query OK, 0 rows affected (0.00 sec)
Before enabling, it took 4395 seconds; after enabling, it took 2455 seconds.
Usage Instructions
In versions prior to MySQL 8.0.30, this feature is enabled or disabled via the parameter innodb_fast_ddl. The kernel versions of TencentDB for MySQL that support parameter modification in the console are as follows.
MySQL 8.0 20210330 to 20220831.
MySQL 5.7 20210331 and above.
|
innodb_fast_ddl | Yes | bool | OFF | [ON,OFF] | Enable or disable FAST DDL |
MySQL 8.0.30 and later versions support this feature by default. The console will make the parallel ddl parameter available in the future.