Performance Evaluation of Parallel DDL in MySQL 8.0.27

MySQL has continuously improved DDL support, from Online DDL in 5.6 to parallel DDL in 8.0.27, which adds atomic DDL operations and configurable parallelism. This article explores the performance impact of MySQL 8.0.27's parallel DDL.

MySQL 8.0.14 introduced the innodb_parallel_read_threads variable for parallel index‑scan threads. MySQL 8.0.27 added innodb_ddl_threads to control the number of parallel threads used when creating secondary indexes, typically used together with innodb_ddl_buffer_size , which specifies the buffer size shared among all DDL threads.

The default values are:

mysql> select @@global.innodb_ddl_threads;
+-----------------------------+
| @@global.innodb_ddl_threads |
+-----------------------------+
|                           4 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select @@global.innodb_ddl_buffer_size;
+---------------------------------+
| @@global.innodb_ddl_buffer_size |
+---------------------------------+
|                         1048576 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select @@global.innodb_parallel_read_threads;
+---------------------------------------+
| @@global.innodb_parallel_read_threads |
+---------------------------------------+
|                                     4 |
+---------------------------------------+
1 row in set (0.00 sec)

To evaluate the performance gain, the author created a 50‑million‑row table on MySQL 8.0.28 with a 24 GB buffer pool and measured DDL execution time while varying the three parameters.

-- Database version 8.0.28
mysql> select @@version;
+----------+
| @@version|
+----------+
| 8.0.28   |
+----------+
1 row in set (0.00 sec)

-- Buffer pool size 24G
mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
|                      25769803776 |
+----------------------------------+
1 row in set (0.001 sec)

mysql> create database action;
Query OK, 1 row affected (0.01 sec)

# Prepare a table with 50,000,000 rows using sysbench
# ... (commands omitted for brevity) ...

Different configurations were tested. Example for a single DDL thread with a 512 MB buffer and one parallel read thread:

-- Set DDL threads to 1
mysql> set innodb_ddl_threads = 1;
Query OK, 0 rows affected (0.01 sec)

-- Set buffer size to 512M
mysql> set innodb_ddl_buffer_size = 536870912;
Query OK, 0 rows affected (0.00 sec)

-- Set parallel read threads to 1
mysql> set innodb_parallel_read_threads = 1;
Query OK, 0 rows affected (0.01 sec)

-- Execute DDL
mysql> alter table action.sbtest1 add index idx_c(c);
Query OK, 0 rows affected (6 min 54.21 sec)

-- Check maximum memory used by DDL
mysql> select event_name, CURRENT_NUMBER_OF_BYTES_USED/1024/1024
       from performance_schema.memory_summary_global_by_event_name
       where event_name='memory/innodb/ddl';
+-------------------+----------------------------------------+
| event_name        | CURRENT_NUMBER_OF_BYTES_USED/1024/1024 |
+-------------------+----------------------------------------+
| memory/innodb/ddl |                           513.08750916 |
+-------------------+----------------------------------------+
1 row in set (0.00 sec)

The benchmark results are summarized in the following table:

innodb_ddl_threads

innodb_ddl_buffer_size

innodb_parallel_read_threads

DDL max memory

DDL time

1

512M

1

513M

6 min 54.21 sec

2

1G

2

1230M

4 min 12.08 sec

4

2G

4

2735M

3 min 43.01 sec

8

4G

8

5791M

3 min 19.63 sec

16

8G

16

5975M

3 min 12.33 sec

32

16G

32

6084M

3 min 11.11 sec

The data shows that increasing parallel threads and buffer size raises resource consumption but reduces DDL execution time. Considering the trade‑off between speed gain and resource usage, a practical parallel thread count of 4‑8 and an appropriately sized buffer provide the best balance.

Reference: MySQL 8.0 Online DDL Parallel Thread Configuration