MySQL 8.0 Parallel DDL

Key Takeaway

Parallel DDL in MySQL 8.0 is one of the significant features of this version, significantly optimizing the execution efficiency of certain DDL operations through multithreaded parallel execution, and notably enhancing the performance of DDL operations such as index creation.

The parallel DDL feature in MySQL 8.0 is a major new feature in this version. By using multithreaded parallel execution, it greatly optimizes the execution efficiency of certain DDL operations, significantly improving the performance of DDL operations like creating indexes. This presentation will mainly introduce the principles and implementation methods of parallel DDL, as well as the new features that Klustron will develop based on this, aiming to provide an understanding of this key feature and 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 the table being altered;
• Starting with MySQL 5.5, inplace DDL operations were supported. For DDL operations that supported inplace, no data copying was needed, and changes could be made directly on the existing table data, but concurrent writes were still not allowed;
• MySQL 5.6 introduced Online DDL, enabling most DDL operations to be performed with concurrent reads and writes, greatly reducing the overall impact of DDL operations on the system;
• MySQL 8.0 began supporting parallel DDL and instant DDL: parallel DDL accelerates the execution of DDL through multithreaded parallel execution, while instant DDL allows for instant completion of certain DDL operations such as adding or dropping columns by only modifying the data dictionary without altering the data.

[Image: MySQL parallel DDL diagram]

02 Detailed Explanation of MySQL Instant DDL Features

Why?

The primary reasons for implementing Instant DDL include:

• The DDL execution process for large tables takes too long, especially in replication scenarios (creating an index on a table with 100 million rows can take tens of minutes).
• DDL operations are executed in a single thread, which cannot fully utilize available resources, resulting in performance bottlenecks.

Below is the execution process of an Online DDL (flowchart image from Tencent Cloud):

[Image: MySQL parallel DDL diagram]

As shown in the above image, prior to MySQL 8.0, the execution process of a DDL required three phases, including creating a new table, importing data into the new table, and finally switching between the new and old tables. This complex process was executed in a single-threaded, serial manner, leading to low DDL execution efficiency and resource waste when disk, memory, and CPU resources were abundant.

Principles of Parallel DDL:

The basic principles of parallel DDL (creating an index) are as follows:

• Three phases of creating a B+ tree index, with two phases executed in a multithreaded parallel manner.
• In the clustered index scanning phase, multithreaded parallel scanning generates multiple intermediate files.
• In the secondary index record sorting phase, multithreaded parallel sorting ensures ordered records within each intermediate file, though records remain unordered between files.
• In the secondary index B+ tree building phase, a single thread performs multiway merge sorting and generates a new B+ tree.

[Image: MySQL parallel DDL diagram]

The above diagram (image from Tencent Database Technology) illustrates parallel DDL. We can see that the first two phases of index creation, namely scanning and sorting, are executed in a multithreaded parallel manner, while the final phase involves single-threaded merge sorting and B+ tree generation.

Flow and Effects of Parallel DDL:

The flow of parallel DDL is as follows:

• Set the number of parallel threads for scanning the clustered index using innodb_parallel_read_threads.
• Set the number of parallel threads for sorting index records using innodb_ddl_threads.
• Set the DDL cache using innodb_sort_buffer_size.
• Execute the DDL statement.

The image below shows the execution process of parallel DDL:

[Image: MySQL parallel DDL diagram]

[Image: MySQL parallel DDL diagram]

The above image shows the effects of executing parallel DDL with different configurations. In the example, the DDL execution time for creating an index dropped from over 9 minutes to just over 2 minutes, a reduction of nearly 80%, which is an exciting improvement.

Internal Implementation of Parallel DDL:

From the source code perspective, the MySQL team mainly leveraged the parallel scan framework already implemented in 8.0 and built parallel sorting and subsequent B+ tree construction on top of it. Specifically:

• A DDL namespace was created, containing two main classes: Loader and Builder.
• The Loader is responsible for creating parallel threads and controlling the main DDL process (parallel scanning of the clustered index and calling the Builder to complete index creation).
• The Builder's main task is to perform the specific work of creating the index (sorting index records, merging, and building the B+ tree).
• Multiple Builders can be created to simultaneously create multiple indexes after a clustered index.

[Image: MySQL parallel DDL diagram]

The above diagram shows a schematic of the internal implementation of parallel DDL. From the diagram, we can see that the Loader can simultaneously create different secondary indexes by calling different Builders.

03 Klustron's DDL

First, let's briefly introduce the core architecture of our distributed data product, Klustron:

Klustron's Distributed Compute-Storage Separation Architecture

• Compute Layer (Klustron-server): Multiple PostgreSQL instances form compute nodes responsible for accepting and validating connection requests from application software, receiving SQL query requests from established connections, executing requests, and returning query results.
• Storage Layer (Klustron-storage): Three or more MySQL 8.0 instances form storage nodes, which constitute a storage cluster (storage shard, or shard). Each shard stores a portion of user tables or table partitions.
• Metadata Cluster: Stores Klustron cluster metadata, including topology, node connection information, DDL logs, commit logs, and other cluster management logs.
• Cluster Manager (cluster_mgr): Maintains the correct state of the cluster and nodes, providing cluster management, logical and physical backup and recovery, and horizontal elastic scaling functions.

[Image: MySQL parallel DDL diagram]

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

Klustron's Online DDL (Repartition)

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

1. Export Full Table Data: node_mgr calls mydumper to dump the source table data and transfer the data files to the server where the compute node is located.
2. Load Full Table Data: node_mgr calls the kunlun_loader tool to load the full dumped data from the source table into the target table.
3. Binlog Catch-up: node_mgr records the starting positions of binlogs on each shard at the time of the dump and calls the binlog2sync tool. The binlog2sync tool starts dumping binlog events from that position.
4. Rename Source and Target Tables: The binlog2sync tool quickly synchronizes the remaining binlogs, then renames the target table to the source table name, and business operations resume as normal.

[Image: MySQL parallel DDL diagram]

Future of Klustron DDL:

Klustron has not only implemented the important Online DDL feature but is also completing and planning other DDL-related features, such as:

• Enhancements and optimizations for Online DDL (e.g., performance optimization through parallelization).
• Transactional DDL (implementing transactional properties for DDL, not just atomicity).
• Enhancements and optimizations for Instant DDL (implementing Instant execution for more DDL operations).
• Parallelization of DDL operations.

04: Q&A

Q1: Why can't parallel DDL achieve linear acceleration according to the number of threads?

A1: This is mainly because parallel DDL is not parallel throughout the entire process. When creating secondary indexes, the scanning of the clustered index and the sorting of records are parallel, but the final merging and B+ tree construction stages are single-threaded due to the unordered records between intermediate files. Achieving linear acceleration would require full-process parallelization, which we are considering optimizing. You can expect to see this in future versions of Klustron.

Q2: Where can I try Klustron?

A2: If you are interested in Klustron, you can download a trial version from our official website and deploy it according to the installation documentation. Additionally, we offer Klustron serverless services on Amazon Marketplace and Alibaba Cloud, which you can also try out if interested.

END