type
status
date
slug
summary
tags
category
icon
password

前言

MySQL 在 8.0.14 引入了 innodb_parallel_read_threads 來控制掃描 cluster index 的併發線程數，可可以用於加快 CHECK TABLE 和 SELECT COUNT(*) FROM table，而在 8.0.27 版本中也再次新引入了併行相關的參數： innodb_ddl_threads用來控制 InnoDB 在 creating (sorting and building) secondary index 時的併發線程數。
For online DDL operations, storage is usually the bottleneck. To address this issue, CPU utilization and index building has been improved. Indexes can now be built simultaneously instead of serially. Memory management has also been tightened to respect memory configuration limits set by the user. See Configuring Parallel Threads for Online DDL Operations. The new innodb_ddl_threads variable defines the maximum number of parallel threads for the sort and build phases of index creation.The new innodb_ddl_buffer_size variable defines the maximum buffer size for DDL operations. The default setting is 1048576 bytes (approximately 1 MB). Defining a buffer size limit avoids potential out of memory errors for online DDL operations that create or rebuild secondary indexes. See Online DDL Memory Management. 參考： MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.27 (2021-10-19, General Availability)
For online DDL operations, storage is usually the bottleneck. To address this issue, CPU utilization and index building has been improved. Indexes can now be built simultaneously instead of serially. Memory management has also been tightened to respect memory configuration limits set by the user. See Configuring Parallel Threads for Online DDL Operations. The new innodb_ddl_threads variable defines the maximum number of parallel threads for the sort and build phases of index creation.The new innodb_ddl_buffer_size variable defines the maximum buffer size for DDL operations. The default setting is 1048576 bytes (approximately 1 MB). Defining a buffer size limit avoids potential out of memory errors for online DDL operations that create or rebuild secondary indexes. See Online DDL Memory Management. 參考： MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.27 (2021-10-19, General Availability)

正文

首先讓我們了解一下建立 secordary index 的流程：
  1. 掃描 cluster index，並將資料寫入 temporary sort files
  1. 排序資料
  1. 將 temporary sort files 中排序的結果加載到 secondary index
其中第 1 個步驟－掃描 cluster index，該步驟中最大併發線程數由 innodb_parallel_read_threads 控制，實際掃描的線程數是 innodb_parallel_read_threads 和 index subtree 數量兩者的最小者，但需要注意的是 innodb_parallel_read_threads 最多允許所有 session 使用 256 條，如果超過則會退回使用單線程。
接著第 2、3 個步驟 - 排序和載入，該步驟中的併發線程數由 innodb_ddl_threads 控制，一起引入的 innodb_ddl_buffer_size 則控制併行 DDL 操作期間所有線程的 buffer size 總量，也就是說每一個線程使用的最大 buffer size 是 innodb_ddl_buffer_size / innodb_ddl_threads，因此兩者通常需要一併調整。
在 MySQL 8.0.27 之前 Online DDL 建立 secondary index 都是單線程的，而期間的 buffer 大小則是由 innodb_sort_buffer_size 控制。 以下補充列出 innodb_sort_buffer_size 的用途： 用來紀錄 Online DDL 期間執行的 DML 語句的 temporary log file 每次會以 innodb_sort_buffer_size 來擴展，直到擴展到innodb_online_alter_log_max_size 設定的大小上限停止。此外還有 temporary log file 的 read buffer 和 write buffer 的大小。
在 MySQL 8.0.27 之前 Online DDL 建立 secondary index 都是單線程的，而期間的 buffer 大小則是由 innodb_sort_buffer_size 控制。 以下補充列出 innodb_sort_buffer_size 的用途： 用來紀錄 Online DDL 期間執行的 DML 語句的 temporary log file 每次會以 innodb_sort_buffer_size 來擴展，直到擴展到innodb_online_alter_log_max_size 設定的大小上限停止。此外還有 temporary log file 的 read buffer 和 write buffer 的大小。
備註：此功能也對於單句 ALTER 建立多個 index 能達到並行的效果，而不是像往常一樣串行建立。

限制

  1. 不支援建立包含虛擬列 (vitrual columns) 的索引
  1. 不支援空間索引 (spatial index)
  1. 不支援全文索引 (full-text index)

測試

網路上的測試結果

  • 愛可生 blog
      • notion image
      💡
      可以看到，随着并发线程的增多和 buffer 的增加，DDL 操作所占用的资源也越多，而 DDL 操作所花费的时间则越少。不过通过对比资源的消耗和 DDL 速度的提升比例，最合理的并行线程数量为4-8个，而 buffer 大小可以根据情况进行调整。 來自：技术分享 | MySQL 并行 DDL (actionsky.com)

個人測試

CPU：2 cores
Memory：8G
數據： sysbench sbtest 5000萬數據

變量 innodb_parallel_read_threads

innodb_parallel_read_theads
innodb_ddl_buffer_size
innodb_ddl_threads
執行時間
1
1048576 (default)
4 (default)
3 min 02.62 sec
2
1048576 (default)
4 (default)
2 min 56.28 sec
4
1048576 (default)
4 (default)
2 min 39.48 sec
8
1048576 (default)
4 (default)
2 min 43.75 sec
16
1048576 (default)
4 (default)
2 min 51.87 sec
可以看到隨著 innodb_parallel_read_theads 的增加，執行時間也有跟著縮短，但因為機器本身的 CPU 只有 2 core，後續過大的設定並沒有達到改善的效果，因此需要根據機器的硬體設置合適的設定。

變量 innodb_ddl_threads

innodb_ddl_threads
innodb_ddl_buffer_size
innodb_parallel_read_theads
執行時間
1
1048576 (default)
4 (default)
2 min 46.41 sec
2
1048576 (default)
4 (default)
2 min 40.58 sec
4
1048576 (default)
4 (default)
2 min 39.48 sec
8
1048576 (default)
4 (default)
2 min 45.82 sec
16
1048576 (default)
4 (default)
2 min 45.40 sec
可以看到一開始隨著 ddl_threads 的增加，執行時間有跟著縮短，但是當 threads 達到 4 之後會因為 ddl_buffer_size 沒有同時增加，導致每個 threads 均分的 buffer 變少，對於執行時間沒有減少甚至小幅增加了。

變量 innodb_ddl_buffer_size

innodb_ddl_buffer_size
innodb_ddl_threads
innodb_parallel_read_theads
執行時間
1048576 (default)
4 (default)
4 (default)
2 min 39.48 sec
5242880
4 (default)
4 (default)
2 min 21.76 sec
10485760
4 (default)
4 (default)
2 min 16.50 sec
104857600
4 (default)
4 (default)
2 min 12.29 sec
可以看到隨著 buffer 的增加，執行時間有明顯的縮短，但不斷增加 buffer_size 也不一定會提高性能，雖然這部分因為機器性能不足無法接下去測試，但可以看到在最後 10M 跟 100M 的設定縮小的幅度卻不如 1M 和 5M 的差距，另外在測試途中有因為 memory 不足導致 mysqld 服務 OOM，因此需要謹慎設定。

變量 innodb_ddl_threads + innodb_ddl_buffer_size

innodb_ddl_threads
innodb_ddl_buffer_size
innodb_parallel_read_theads
執行時間
4 (default)
1048576 (default)
4 (default)
2 min 39.48 sec
4 (default)
2097152
4 (default)
2 min 35.09 sec
4 (default)
4194304
4 (default)
2 min 24.44 sec
8
2097152
4 (default)
2 min 29.86 sec
8
4194304
4 (default)
2 min 27.90 sec
8
8388608
4 (default)
2 min 22.95 sec
16
4194304
4 (default)
2 min 25.78 sec
16
8388608
4 (default)
2 min 19.09 sec
整體來說可以看到 ddl_threads 和 ddl_buffer_size 兩個參數一同調整的效果是最好，因次需要根據自身的機器裝況及數據來妥善這些參數，而不是單獨調整單一設定。

參考

MySQL :: MySQL 8.0 Reference Manual :: 15.12.5 Configuring Parallel Threads for Online DDL Operations
MySQL :: MySQL 8.0 Reference Manual :: 15.12.4 Online DDL Memory Management
MySQL 8.0 – InnoDB Parallel Threads for Online DDL Operations – lefred blog: tribulations of a MySQL Evangelist
技术分享 | MySQL 并行 DDL (actionsky.com)