快捷導(dǎo)航

MySQL并行DDL的實(shí)現(xiàn)方法

更新時(shí)間：2024年10月28日 10:13:46 作者：吃飯端住碗

本文詳細(xì)介紹了MySQL8.0.27并行DDL的實(shí)現(xiàn)方法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

隨著MySQL版本的不斷更新，對(duì)DDL操作的支持也在不斷的完善和更新：比如從MySQL 5.6引入Online DDL，在MySQL 5.7對(duì)Online DDL進(jìn)一步完善，到現(xiàn)在的8.0版本，則對(duì)DDL的實(shí)現(xiàn)重新進(jìn)行了設(shè)計(jì)，比如DDL操作支持原子特性，在MySQL 8.0.27引入并行DDL。本篇就來探究一下MySQL 8.0.27的并行DDL對(duì)于DDL操作速度的提升。

MySQL 8.0.14引入了innodb_parallel_read_threads變量來控制掃描聚簇索引的并行線程。MySQL 8.0.27引入了innodb_ddl_threads變量來控制用于創(chuàng)建二級(jí)索引時(shí)的并行線程數(shù)量，此參數(shù)一般和一并引入的innodb_ddl_buffer_size一起使用，innodb_ddl_buffer_size用于指定進(jìn)行并行DDL操作時(shí)能夠使用的buffer大小，buffer是在所有的DDL并行線程中平均分配的，所以一般如果調(diào)大innodb_ddl_threads變量時(shí)，也需要調(diào)大innodb_ddl_buffer_size的大小。

innodb_ddl_threads、innodb_ddl_buffer_size和innodb_parallel_read_threads的默認(rèn)大小分別為：

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)

接下來測(cè)試一下調(diào)大innodb_ddl_threads、innodb_ddl_buffer_size和innodb_parallel_read_threads參數(shù)值對(duì)DDL操作的性能提升。

首先創(chuàng)建一張5000萬的表：

-- 數(shù)據(jù)庫版本為8.0.28
mysql> select @@version;
+----------+
| @@version|
+----------+
| 8.0.28   |
+----------+
1 row in set (0.00 sec)
 
-- buffer pool大小為24G
mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
|                       1073741824 |
+----------------------------------+
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)
 
-- 表空間大小為12G
# ll -h
total 12G
-rw-r-----. 1 mysql mysql 12G Jan 20 17:38 sbtest1.ibd

分別測(cè)試不同的線程數(shù)量和緩沖區(qū)大小的DDL操作時(shí)間，例如：

-- 設(shè)置并發(fā)DDL線程為1
mysql> set innodb_ddl_threads = 1;
Query OK, 0 rows affected (0.01 sec)
 
-- 設(shè)置buffer大小為512M
mysql> set innodb_ddl_buffer_size = 536870912;
Query OK, 0 rows affected (0.00 sec)
 
-- 設(shè)置并行索引掃描線程為1
mysql> set innodb_parallel_read_threads = 1;
Query OK, 0 rows affected (0.01 sec)
 
-- 執(zhí)行DDL操作
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
 
-- 查看DDL的內(nèi)存最大占用
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)

通過不斷調(diào)整相關(guān)參數(shù)得到以下結(jié)果:

innodb_ddl_threads	innodb_ddl_buffer_size	innodb_parallel_read_threads	DDL占用最大內(nèi)存	DDL時(shí)間
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

可以看到，隨著并發(fā)線程的增多和buffer的增加，DDL操作所占用的資源也越多，而DDL操作所花費(fèi)的時(shí)間則越少。不過通過對(duì)比資源的消耗和DDL速度的提升比例，最合理的并行線程數(shù)量為4-8個(gè)，而buffer大小可以根據(jù)情況進(jìn)行調(diào)整。

參考鏈接：MySQL :: MySQL 8.0 Reference Manual :: 17.12.5 Configuring Parallel Threads for Online DDL Operations

到此這篇關(guān)于MySQL并行DDL的實(shí)現(xiàn)方法的文章就介紹到這了,更多相關(guān)MySQL并行DDL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: