Faster DDL is an AliSQL optimization that reduces the time and performance impact of data definition language (DDL) operations on ApsaraDB RDS for MySQL instances. It replaces the default buffer pool management mechanism with an optimized one that minimizes lock contention during DDL operations, cutting execution time by over 90% compared to MySQL Community Edition.
Use cases
Faster DDL addresses three common problems that affect DDL-heavy workloads:
Index creation causes performance jitter — Adding an index triggers lock contention in the buffer pool, briefly interrupting read and write operations.
DDL is slow on small tables — A DDL operation on a table smaller than 1 GB can take more than 10 minutes under the default implementation.
Temporary table cleanup stalls the instance — When a connection that created temporary tables closes, MySQL truncates the related tablespace, causing a severe TPS drop.
Prerequisites
Before you begin, make sure your RDS instance meets the following requirements:
MySQL 8.0, 5.7, or 5.6 with a minor engine version of 20200630 or later
Enable faster DDL
Set the loose_innodb_rds_faster_ddl parameter to ON in the ApsaraDB for RDS console. For instructions, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
Performance benchmarks
The following benchmarks were run on a MySQL 8.0 instance with 8 CPU cores and 64 GB of memory.
DDL operations
Using SysBench with the in-place algorithm, both CREATE INDEX and OPTIMIZE TABLE were tested on a 600 MB table:
| Operation | Without faster DDL | With faster DDL | Improvement |
|---|---|---|---|
| CREATE INDEX | 56 seconds | 4.9 seconds | 11.4x faster |
| OPTIMIZE TABLE | 220 seconds | 17 seconds | 12.9x faster |
Both operations use the in-place algorithm and permit concurrent DML operations during execution. CREATE INDEX does not rebuild the table; OPTIMIZE TABLE does.
Temporary table cleanup
Using tpcc-mysql with the buffer pool near full capacity, single-threaded short-lived connections were used to generate and clean up temporary tables:
| Scenario | TPS |
|---|---|
| No DDL operations (baseline) | 42,000 |
| With faster DDL enabled | 40,000 |
| With faster DDL disabled | < 10,000 |
Without faster DDL, each thread exit that involves temporary tables triggers a severe performance jitter, reducing TPS by over 70%. With faster DDL enabled, the TPS drop is reduced to 5%.
The following chart shows second-level TPS data from the stress test. The red highlighted sections indicate performance while faster DDL is disabled.
Supported DDL operations by version
Faster DDL support varies across MySQL versions:
| Category | Operation | MySQL 5.6 | MySQL 5.7 | MySQL 8.0 |
|---|---|---|---|---|
| In-place DDL | See MySQL 8.0 Online DDL Operations and MySQL 5.7 Online DDL Operations | No | Yes | Yes |
| Tablespace management | Enable or disable tablespace encryption | No | Yes | Yes |
| Tablespace management | Release or delete a tablespace | No | Yes | Yes |
| Tablespace management | Discard a tablespace | Yes | Yes | Yes |
| Table deletion | Release or delete a table | Yes | Yes | Yes |
| Undo operation | Release or delete an undo tablespace | No | No | Yes |
| Table refresh | Refresh a table and its dirty pages | Yes | Yes | Yes |
Bugs fixed
Faster DDL resolves the following upstream MySQL bugs: