Parallel DDL in MySQL 8.0

This Issue's Golden Quote:

Parallel DDL in MySQL 8.0 is one of the important features of this version. It significantly optimizes the execution efficiency of certain DDL operations through multi-threaded parallel execution, greatly improving the performance of DDL operations such as index creation.

The parallel DDL feature in MySQL 8.0 is an important new feature of this version. It greatly optimizes the execution efficiency of certain DDL operations through multi-threaded parallel execution and significantly improves the performance of DDL operations such as creating index columns. This sharing will mainly introduce the principles and implementation methods of parallel DDL, as well as new features that KlustronDB will develop based on this. We hope that while gaining an understanding of this key feature, everyone will also have a deeper understanding of 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 could not allow concurrent writes to the table 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 to support parallel DDL and instant DDL: parallel DDL accelerates the execution of DDL through multi-threaded parallelism, while instant DDL achieves the instantaneous completion of DDL operations such as adding or dropping columns by only modifying the data dictionary without changing the data.

02 Detailed Explanation of MySQL Instant DDL Features

Why？

The main reasons for needing to implement Instant DDL are as follows:

• The DDL execution process for large tables is too long, especially in replication scenarios; creating an index on a table with 100 million rows takes dozens of minutes.
• DDL is executed in a single thread and cannot fully utilize resources, therefore there is a performance bottleneck.

The following is the execution process of an Online DDL: (Online DDL flowchart: image from Tencent Cloud MaYuan Technology column)

From the above figure, we can see that before MySQL 8.0, the execution of a DDL required three stages, which involved relatively complex processes such as creating a new table, importing data into the new table, and finally switching between the new and old tables. These processes were executed sequentially in a single thread, resulting in low DDL execution efficiency, and if disk, memory, and CPU resources were relatively sufficient, it would lead to a waste of these resources.

Principle of Parallel DDL:

• The basic principles of parallel DDL (creating indexes) mainly include the following points:
• There are three stages in creating a B-tree index, with two stages using multithreaded parallel processing;
• In the clustered index scan phase, multiple threads scan in parallel and generate multiple corresponding intermediate files.
• During the secondary index record sorting stage, multi-threaded parallel sorting is used to ensure that records within intermediate files are ordered, while records between files are still unordered.
• During the stage of building a secondary index B-tree, single-threaded multi-way merge sorting is performed to generate a new B-tree.

The above figure (image from Tencent Database Technology) is a schematic diagram of parallel DDL. We can see that in the first two stages of index creation, namely the scanning and sorting stages, execution is multithreaded and parallel, while the final stage is a single-threaded merge sort and generation of a new B-tree.

Process and Effects of Parallel DDL:

The process of parallel DDL is as follows:

• Set the number of threads for parallel scanning of clustered indexes with innodb_parallel_read_threads.
• Set the number of threads for sorting parallel index records innodb_ddl_threads;
• Set DDL cache innodb__buffer_size;
• Execute DDL statement

The following diagram shows the execution process of parallel DDL:

The above image shows the execution effect of parallel DDL with different configurations. We can see that in the example, the DDL execution time for creating an index decreased from more than 9 minutes to more than 2 minutes, nearly an 80% reduction, which is a very exciting improvement.

Internal Implementation of Parallel DDL:

From the source code implementation internally, the MySQL team mainly utilized the parallel scan framework already implemented in 8.0, and on this basis, implemented parallel sorting and the subsequent construction of B-trees. Specifically:

• Create a DDL namespace and include two main classes, Loader and Builder, within it;
• The Loader is mainly responsible for creating parallel threads and controlling the main process of DDL (parallel scanning of clustered indexes and calling the Builder to complete index creation);
• The main task of the Builder is to accomplish the specific work of creating indexes (sorting index records, merging, and building B-trees).
• Multiple indexes after a clustered index can be created simultaneously by creating multiple Builders.

The above diagram is a schematic of the internal implementation of parallel DDL. From the diagram, we can see that the Loader can achieve the simultaneous creation of different secondary indexes by calling different Builders.

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): Kluscomp instances composed of multiple PostgreSQL instances are responsible for accepting connection requests from verified application software clients, 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:

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 Kluscomp instances are located.
2. Load full table data: node_mgr calls the kunlun_loader tool to dump the full data from the source table into the target table.
3. 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.
4. 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 the 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)
• Enhancement and optimization of Instant DDL (implementing Instant execution for more DDLs)
• Parallelization of DDL

Wait a moment

04 ：Q&A

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

A1: This is mainly because parallel DDL is not fully parallel throughout the entire process. When creating a secondary index, the stages of scanning the clustered index and sorting records are parallel, but the final merging and B-tree building stages can only be executed in a single thread because the records in intermediate files are unordered. To achieve linear acceleration, full-process parallelism is required, which we are considering optimizing, and users will be able to see this in future versions of Klustron.

Q2: Where can I try out 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.

END