By Wangde
X-Engine is a proprietary storage engine from Alibaba. As an optional engine of Alibaba Cloud ApsaraDB RDS for MySQL, X-Engine not only features high performance at a low cost, but also incorporates many new useful features. This article describes how MySQL (with X-Engine) completes quasi-instant Data Definition Language (DDL) operations, which may take several hours to complete using traditional databases.
DDL (Data Definition Language) is a type of SQL; its main function is to create and change data schema information. The most common DDL operations include adding and removing columns, changing column types, and adding and removing indexes. If you are familiar with MySQL, you definitely know that, before MySQL 8.0, many important DDL operations such as adding and removing columns may take several hours or even days to complete depending on the data size. This happens even though online DDL operations do not block other Data Manipulation Language (DML) operations, such as inserting, updating, and deleting data. Even worse, operations including changing column types require table locking, which blocks DML operations.
As Internet services are developing rapidly, frequent changes in application models have become the norm. Correspondingly, database access patterns and schemas also change. But as we discussed before, DDL operations require a long execution period and are resource-consuming. They need additional disk space to create temporary tables, which compromises system throughput. Moreover, if an instance crashes during a DDL operation, the recovery process is very slow.
Let's take a look at an example. The following steps are performed in MySQL by taking the "adding a column" operation as an example:
Evidently, this process has a long locking time. Meanwhile, the data replication operation consumes system resources and temporary space, and involves high I/O traffic. To adapt to the changing services, Fast DDL operations that do not instantly modify the data at the storage layer and can be completed fast become a necessity. MySQL 8.0 introduced the instant add column feature, which supports quick modification of only table metadata in order to add a column. However, it does not support other types of DDL operations. X-Engine stores multiple versions of table schemas. By using it, each line of record is parsed at the engine layer, and format conversion is done based on the updated schema. Therefore, X-Engine supports multiple types of Fast DDL operations.
In the current DDL implementation solutions, the number of columns is logged in record, and instant add column operations modify only the system table.
Write: Record in the new format.
Read: Populate the new column with the default values stored in the system table.
Supported types:
• Change index option
• Rename table
• Set/drop default
• Modify column when the table is empty
• Add/drop virtual columns
• Add columns
The overall implementation solution is similar to that of MySQL 8.0, where record logs the number of columns and leftmost leaf page records the default values of all columns.
Supported types:
• Add column
• Drop column
• Extend VARCHAR maximum (Only if the physical format allows; not VARCHAR(255) to VARCHAR(256))
When a DDL statement is executed, the updated system table and the new and old versions of schemas must all be recorded. Then, the modifications are broadcast. After the broadcast, the system accepts DML requests. Aurora converts all the records on the related leaf page, and then performs the DML operations.
The select request concatenates the old version of records into the new version of records.
Supported types:
• Only supports adding nullable columns, without default values
As the name implies, Fast DDL means that the database can complete the user-issued DDL commands and return results shortly. Fast DDL enables fast operations because it modifies only the metadata in the system table, without modifying the data stored at the engine layer. The key to the implementation of Fast DDL lies in the parsing of physical records in the memory and disk after the metadata is modified.
X-Engine adopts LSM-tree architecture. In this architecture, newly written data is appended to memtable in the memory. When the size of memtable reaches a threshold, it is switched to immutable memtable and stops receiving further modifications. The table is gradually flushed for persistent storage in the form of fixed-size extents. When the extents reach a certain amount, the system compacts multiple versions of extents with the same key. To ensure that each line of record can be parsed, the most intuitive and streamlined method is to append the metadata to the record. To implement independent record parsing from the system table, X-Engine stores the metadata in detail. However, appending the metadata to each line of record consumes a large amount of space. To cut storage costs, we ensure that the data schema of each memtable is consistent with that in each extent, and store the schema information in the memtable and extent.
The schema contains key information such as the number of columns, the column type, the column length, and default values. By using such information, X-Engine can parse the columns before returning the result. In addition, it returns only the result of querying the target columns. The following example shows how to return results when a table contains different schema versions of extents.
When MySQL receives a Fast DDL statement, it updates the related system table and metadata, and the new version of table schema takes effect. Then, the DDL statement has been executed successfully. The information stored in X-Engine remains unchanged to this point.
When the system receives a select request, MySQL sends the request to X-Engine, along with the information of the latest schema. This latest schema is called the target schema. Then, X-Engine locates the record in a memtable or extent, and obtains the corresponding data schema parsing record to produce the preliminary result. Next, X-Engine compares the data schema with the target schema, and adds content to, deletes content from, or modifies the preliminary result to produce and return the final result.
After a Fast DDL command is executed successfully, the new schema takes effect, but X-Engine does not perceive this. After the reception of the first DML request, such as the request for inserting, updating, or deleting data for the table, if the active memtable schema of X-Engine is not the most recent, X-Engine triggers the switch memtable operation. This operation includes freezing the active memtable, generating another active memtable, and assigning the new schema to the new active memtable. This operation does not begin until all ongoing write transactions are completed to ensure data correctness.
Each write transaction may involve n (where n >= 1) tables. When a write transaction is committed, Fast DDL determines whether the schema version for data writing in the transaction is consistent with the schema version of the active memtable before writing data to the active memtable. If not, Fast DDL returns an error and exits, and prompts the user to try again.
When the number of memtables in the memory reaches a threshold, the flush operation is triggered, and the data of the selected memtables is written to disks as extents. Meanwhile, the schema information is transmitted from the memtables to the extents. Then, the compaction operation merges the extents. If the extents in a task have different schema versions, X-Engine generates new extents based on the latest version.
Fast DDL can address the pain points in many applications. By using it, common operations, such as adding and expanding columns, can be done efficiently. X-Engine stores the detailed data of multiple versions of schemas. This feature not only saves the effort of parsing records through the system table, but also enables easy data conversion between different versions of schemas, supporting a diversity of Fast DDL statements.
Learn more about Alibaba Cloud ApsaraDB RDS databases at https://www.alibabacloud.com/product/apsaradb-for-rds-mysql
Build a Personalized Recommendation System on Alibaba Cloud in Three Steps
ApsaraDB - October 21, 2020
ApsaraDB - March 3, 2020
ApsaraDB - December 26, 2023
ApsaraDB - February 19, 2020
ApsaraDB - April 13, 2020
ApsaraDB - May 14, 2020
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for MariaDB
ApsaraDB RDS for MariaDB supports multiple storage engines, including MySQL InnoDB to meet different user requirements.
Learn More
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by ApsaraDB
