MySQL parallel DDL implementation method

With the continuous update of MySQL version, support for DDL operations is also constantly being improved and updated: For example, the introduction of Online DDL from MySQL 5.6, and the further improvement of Online DDL in MySQL 5.7. To the current version 8.0, the implementation of DDL has been redesigned. For example, DDL operations support atomic characteristics, and parallel DDL is introduced in MySQL 8.0.27. This article will explore the improvement of parallel DDL operation speed of MySQL 8.0.27.

MySQL 8.0.14 introduces the innodb_parallel_read_threads variable to control the parallel threads that scan clustered indexes. MySQL 8.0.27 introduces the innodb_ddl_threads variable to control the number of parallel threads used to create secondary indexes. This parameter is generally used with the innodb_ddl_buffer_size introduced together. Innodb_ddl_buffer_size is used to specify the buffer size that can be used when performing parallel DDL operations. Buffers are distributed evenly among all DDL parallel threads. Therefore, if the innodb_ddl_threads variable is generally increased, the size of innodb_ddl_buffer_size is also required.

The default sizes of innodb_ddl_threads, innodb_ddl_buffer_size and innodb_parallel_read_threads 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)

Next, test the performance improvement of the DDL operation by increasing the innodb_ddl_threads, innodb_ddl_buffer_size and innodb_parallel_read_threads parameters.

First create a 50 million table:

-- The database version is8.0.28
mysql&gt; select @@version;
+----------+
| @@version|
+----------+
| 8.0.28   |
+----------+
1 row in set (0.00 sec)
 
-- buffer poolSize is24G
mysql&gt; select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
|                       1073741824 |
+----------------------------------+
1 row in set (0.001 sec)
 
mysql&gt; create database action;
Query OK, 1 row affected (0.01 sec)
 
# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/data/mysql/data/3306/  --mysql-user=root --mysql-password='123' --mysql-db=action --tables=1 --table-size=50000000 --report-interval=1 --threads=8 prepare
 
mysql&gt; select count(*) from action.sbtest1;
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (21.64 sec)
 
-- 表空间Size is12G
# ll -h
total 12G
-rw-r-----. 1 mysql mysql 12G Jan 20 17:38

Test the DDL operation time for different thread counts and buffer sizes, for example:

-- Set up concurrencyDDLThe thread is1
mysql&gt; set innodb_ddl_threads = 1;
Query OK, 0 rows affected (0.01 sec)
 
-- set upbufferSize is512M
mysql&gt; set innodb_ddl_buffer_size = 536870912;
Query OK, 0 rows affected (0.00 sec)
 
-- set up并行索引扫描The thread is1
mysql&gt; set innodb_parallel_read_threads = 1;
Query OK, 0 rows affected (0.01 sec)
 
-- implementDDLoperate
mysql&gt; alter table action.sbtest1 add index idx_c(c);
Query OK, 0 rows affected (6 min 54.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
-- CheckDDLThe maximum memory usage
mysql&gt; 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)

By continuously adjusting relevant parameters, the following results are obtained:

innodb_ddl_threads	innodb_ddl_buffer_size	innodb_parallel_read_threads	DDL takes up the maximum 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

It can be seen that with the increase of concurrent threads and the increase of buffers, the more resources the DDL operation consumes, and the less time it takes for DDL operation. However, by comparing the resource consumption and the increase in DDL speed, the most reasonable number of parallel threads is 4-8, and the buffer size can be adjusted according to the situation.

Reference link:MySQL :: MySQL 8.0 Reference Manual :: 17.12.5 Configuring Parallel Threads for Online DDL Operations

This is the end of this article about the implementation method of MySQL parallel DDL. For more related content on MySQL parallel DDL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!