This topic describes the faster DDL feature that provides an optimized buffer pool management mechanism. This mechanism allows you to reduce the impact of data definition language (DDL) operations and increase the number of concurrent DDL operations that are allowed.
Prerequisites
Your instance must run one of the following versions:
MySQL 8.4
MySQL 8.0 (with a minor engine version of 20200630 or later)
MySQL 5.7 (with a minor engine version of 20200630 or later)
MySQL 5.6 (with a minor engine version of 20200630 or later)
Background information
DDL operations are common for RDS instances. When you use your RDS instance, you may encounter issues related to DDL operations. For example, you may encounter the following issues:
Why does creating an index cause performance jitter on an instance and affect normal read and write operations?
Why can a DDL operation on a table smaller than 1 GB sometimes take more than 10 minutes to complete?
Why does closing a connection that uses a temporary table cause performance jitter on an instance?
The database engine team of ApsaraDB for RDS has performed in-depth analyses and intensive tests to locate these issues. Based on the analysis and test results, the team has identified defects in the cache maintenance logic that is used to manage DDL operations. To fix these issues, the team has developed the faster DDL feature. The optimized buffer pool management mechanism provided by this feature reduces competition for locks that are triggered by DDL operations. When your RDS instance processes a normal number of workloads, this allows you to ensure the performance of your RDS instance during DDL operations.
Enable faster DDL
You can enable the faster DDL feature by setting the loose_innodb_rds_faster_ddl parameter to ON in the ApsaraDB for RDS console. For more information, see Configure instance parameters.
Test with DDL operations
Test scenario
Use the in-place algorithm to perform online DDL operations by executing the following MySQL 8.0-supported statements: CREATE INDEX and OPTIMIZE TABLE. The CREATE INDEX statement creates an index on a table without the need to rebuild the table. The OPTIMIZE TABLE statement creates an index on a table with the need to rebuild the table.
Operation
Instant
In-place
Rebuild table
Allows concurrent DML
Modifies metadata only
CREATE INDEX
No
Yes
No
Yes
No
OPTIMIZE TABLE
No
Yes
Yes
Yes
No
Test instance
The RDS instance that is used for the test runs MySQL 8.0. It provides 8 CPU cores and 64 GB of memory. The size of the table on which you perform DDL operations is 600 MB.
Test procedure
Use SysBench to perform a stress test. In this test, perform online DDL operations and compare the operation results.
Test results
Operation
Average time (disabled)
Average time (enabled)
Speedup
CREATE INDEX
56 seconds
4.9 seconds
11.4x
OPTIMIZE TABLE
220 seconds
17 seconds
12.9x
Test summary
The faster DDL feature enables ApsaraDB RDS for MySQL with AliSQL to reduce the execution duration of a DDL operation by more than 90% compared with the MySQL Community Edition.
Test with temporary tables
Temporary tables are common in MySQL. For example, the system creates temporary tables that are used to query tables from the information_schema database or to expedite the execution of complex SQL statements. When a thread exits, all of the related temporary tables are deleted. This is known as a specific type of DDL operation that causes a performance jitter on your RDS instance. For more information, see Temp ibt tablespace truncation at disconnection stuck InnoDB under large BP.
Test instance
The RDS instance that is used for the test runs MySQL 8.0. It provides 8 CPU cores and 64 GB of memory.
Test procedure
Use tpcc-mysql to perform a stress test. In this test, run queries to make sure that the buffer pool reaches near full capacity. Then, initiate single-threaded requests over short-lived connections to generate temporary tables.
Test results
Metric
Baseline
With optimization
Without optimization
Transactions per second (TPS)
42,000
40,000
<10,000
The following figure shows the second-level performance data that is obtained from the stress test. The red highlighted parts indicate the TPSs that are supported by the RDS instance when the faster DDL feature is disabled.
Test summary
Every time when a thread that generates temporary tables exits, the native MySQL causes a severe performance jitter. The jitter decreases the TPS by more than 70%. After the faster DDL feature is enabled, the TPS decrease is reduced to 5%.
Optimization effect
The faster DDL feature supports MySQL 5.6, 5.7, and 8.0. However, the supported DDL operations can vary based on the selected MySQL version.
Category | DDL operation | MySQL 5.6 | MySQL 5.7 | MySQL 8.0 |
In-place DDL | For more information, see Online DDL Operations for MySQL 8.0 and Online DDL Operations for MySQL 5.7. | No | Yes | Yes |
Tablespace management | Enable or disable tablespace encryption. | No | Yes | Yes |
Truncate or drop a tablespace. | No | Yes | Yes | |
Discard a tablespace. | Yes | Yes | Yes | |
Table deletion | Truncate or drop a table. | Yes | Yes | Yes |
Undo operations | Truncate or drop an undo tablespace. | No | No | Yes |
Table flushing | Flush a table and its dirty pages. | Yes | Yes | Yes |
Defects fixed by faster DDL
The faster DDL feature fixes the following defects: