Atomic DDL in MySQL 8.0

This Issue's Golden Quote:

The atomic DDL of MySQL 8.0 is one of the most important features of this version. It addresses issues such as DDL crash-safety that have long troubled MySQL users by critically restructuring MySQL's data dictionary.

The atomic DDL feature in MySQL 8.0 is a very important functionality of this version. It addresses the long-standing problem in MySQL where executing DDL could cause system anomalies if a crash occurred, achieving transactional atomicity for DDL. This presentation will mainly introduce the principles and implementation methods of atomic DDL, as well as the new features that KlustronDB has developed on this basis, aiming to give everyone an understanding of this key feature while also gaining a deeper insight into Klustron.

01 Introduction to MySQL DDL

First, let's briefly review the entire development history of MySQL DDL.

Before MySQL 5.5, only Copy-based DDL operations were supported, which required copying data and did not allow concurrent writes to tables being altered.
Starting from MySQL 5.5, DDL operations in the Inplace mode are supported. For DDL operations that support Inplace, there is no need to copy data; changes can be made directly on the existing table data, but concurrent writes are still not allowed.
Starting from MySQL 5.6, Online DDL is supported, allowing most DDL operations to be performed while concurrent reads and writes continue, greatly reducing the overall impact of DDL operations on the system;
MySQL 8.0 began supporting instant DDL, allowing DDL operations to be completed instantly by only modifying the data dictionary without modifying the data.

Let's take another look at the data dictionary structure before 8.0; it mainly has the following characteristics:

Double-layer data dictionary structure: both the server layer and the engine layer have data dictionaries, and the two data dictionaries need to be synchronized
Information is stored in multiple places: in text files, MyISAM, and InnoDB
Server layer:
Text files: .FRM, .OPT files, etc.
MyISAM system tables: user, events, etc.
Storage Engine Layer:
InnoDB internal system tables: sys_tables, sys_indexes

Why Atomic DDL？

Why is atomic DDL needed? The main reasons are as follows:

Data dictionary information not synchronized: the problem of inconsistency between the server layer and the InnoDB layer data dictionary occurs frequently;
Lack of unified management of the data dictionary: stored through files, MyISAM engine, etc., without a unified API
DDL does not have atomicity, leading to crash safety and replication issues
Lacks scalability and is inconvenient to upgrade.

02 Detailed Explanation of MySQL Atomic DDL Features

MySQL 8.0 Data Dictionary:

The main design points are as follows:

Unified data dictionary information: removed .FRM and other files as well as MyISAM system tables, and unified them into a set of system tables in the InnoDB storage engine;
Access and manipulate the data dictionary through a unified interface API.
New data dictionary cache;
Implemented a new storage engine API to support atomic DDL and crash safety;
The information schema has been re-implemented as a view of the data dictionary tables.

MySQL 8.0 Data Dictionary API Framework:

Data Dictionary Client: Provides a unified read and write access interface to the data dictionary for SQL clients and the storage engine layer;
DD Shared Cache: Shared dictionary object cache;
Storage Adaptor: The encapsulation interface of the InnoDB system table;
Storage layer interface: Handler interface.

Implementation method of DDL atomicity in MySQL 8.0:

A DDL operation completed by multiple transactions is turned into a DDL Trx transaction to ensure the atomicity of dictionary table operations;
Record DDL logs to ensure the atomicity of data file operations;
During crash recovery, choose to commit or roll back based on the commit status of the DDL transaction.

The four stages of DDL in MySQL 8.0:

Prepare: Create the required objects and write the DDL log into the system table mysql.innodb_ddl_log;
Perform: Execute DDL operations, for example: perform the necessary operations to create a table;
Commit: Update the data dictionary and submit DDL Trx;
Post-DDL: Replay and delete DDL log.

Example of atomic DDL (drop table):

Drop table T1;

Prepare: Find the dictionary object T1;
Perform: Delete the relevant information of T1 in the data dictionary table;
Commit: Submit DDL transaction, evict T1 dictionary object cache;
Post-DDL: Delete data files such as t1.ibd.

The diagram below shows the invocation process of the 'drop table' DDL operation:

The following diagram shows the DDL log content of the Drop table record:

From the above content, we can see that when a DDL operation successfully completes a commit, the system will delete the data files in the post-DDL phase. However, once a rollback occurs, because the DDL log content will be rolled back, the contents in the DDL log will not be executed, and all changes in the system tables can be directly rolled back.

03 KlustronDB's DDL

First, a brief introduction to the core architecture of our Klustron's distributed data product, KlustronDB:

KlustronDB's distributed computing-storage separation architecture

Computing Layer (KlustronDB_server): Multiple PostgreSQL instances form Kluscomp instances responsible for accepting connection requests from client application software, as well as receiving SQL query requests from established connections, executing the requests, and then returning the query results;
Storage Layer (KlustronDB_storage): A storage shard (storage shard, abbreviated as shard) is composed of three or more MySQL 8.0 instances forming Klustore instances, and each shard stores a portion of user tables or table partitions;
The metashard stores the metadata of the KlustronDB cluster, including the topology, node connection information, DDL logs, commit logs, and other cluster management logs.
The cluster_mgr cluster is responsible for maintaining the correct cluster and node states, implementing functions such as cluster management, cluster logical backup and recovery, cluster physical backup and recovery, and horizontal elastic scaling.

Next, let's introduce KlustronDB's Online DDL feature.

KlustronDB Online DDL (Repartition)

Method: Export the data from the source table and write it into the target table, then import the updates made to the source table during this period into the target table. Detailed steps:

Export full table data: node_mgr calls mydumper to dump the source table data and transfer the data files to the server where the Kluscomp instances are located.
Load full table data: node_mgr calls the kunlun_loader tool to dump the full data from the source table into the target table.
binlog catch-up: node_mgr calls the binlog2sync tool according to the binlog starting positions recorded on each shard during the dump, and the binlog2sync tool starts dumping binlog events from that position.
Rename the source table and the target table: Use the binlog2sync tool to quickly synchronize the remaining binlogs, and then rename the target table to the source table name, so that the business can resume normal use.

The Future of KlustronDB DDL:

KlustronDB not only implements this important DDL feature of Online DDL, but is also completing and planning other DDL-related features, such as:

Enhancement and optimization of online DDL (performance optimization through parallel implementation, etc.)
Transactional DDL (implementing the transactionality of DDL, not just atomicity)

Wait a moment

04 ：Q&A

Q1: What is the difference between transactional DDL and atomic DDL?

A1: Atomicity only ensures the indivisibility of DDL operations, that is: a DDL either commits (executes successfully) or rolls back (execution fails), and there is no intermediate state. However, MySQL's DDL operations still cannot coexist with other transactions, meaning DDL cannot be placed in a regular user transaction to follow the user's commit or rollback. Transactional DDL supports placing DDL operations within regular user transactions, allowing the user to decide whether to commit or roll back. PostgreSQL supports transactional DDL.

Q2: Where can I try KlustronDB?

A2: Friends who are interested in KlustronDB can download a trial from our official website and deploy it according to the installation documentation. In addition, we also offer KlustronDB's serverless service on Amazon Marketplace and Alibaba Cloud, which everyone can try if interested.