Author: Li Pengbo

As a member of aikesheng DBA team, he will be transformed and is mainly responsible for MySQL fault handling and SQL audit optimization. Be dedicated to technology and responsible for customers.

Source: original contribution

*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

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

MySQL 8.0.14 introduces InnoDB_ parallel_ read_ The threads variable controls the parallel threads that scan the clustered index. MySQL 8.0.27 introduces innodb_ddl_threads variable is used to control the number of parallel threads used to create secondary indexes. This parameter is generally combined with innodb_ddl_buffer_size, innodb_ddl_buffer_size is used to specify the buffer size that can be used for parallel DDL operations. Buffer is evenly distributed among all DDL parallel threads, so it is generally adjusted to increase InnoDB_ ddl_ When the threads variable is, you also need to increase innodb_ddl_buffer_size.

innodb_ddl_threads ,innodb_ddl_buffer_size and InnoDB_ parallel_ read_ The default sizes of 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 a large innodb_ddl_threads ,innodb_ddl_buffer_size and InnoDB_ parallel_ read_ The threads parameter value improves the performance of DDL operations.

First create a 50 million table:

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

-- buffer pool Size 24 G
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)

# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/data/mysql/data/3306/mysqld.sock  --mysql-user=root --mysql-password='123' --mysql-db=action --tables=1 --table-size=50000000 --report-interval=1 --threads=8 prepare

mysql> select count(*) from action.sbtest1;
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (21.64 sec)

-- The tablespace size is 12 G
# ll -h
total 12G
-rw-r-----. 1 mysql mysql 12G Jan 20 17:38 sbtest1.ibd

Test the DDL operation time of different number of threads and buffer size, for example:

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

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

-- Set the parallel index scan thread to 1
mysql> set innodb_parallel_read_threads = 1;
Query OK, 0 rows affected (0.01 sec)

-- implement DDL operation
mysql> 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

-- see DDL Maximum memory usage
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 following results are obtained by continuously adjusting relevant parameters:

It can be seen that with the increase of concurrent threads and buffers, DDL operations occupy more resources and spend less time. However, by comparing the consumption of resources and the increase ratio of 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: https://dev.mysql.com/doc/ref...