MySQL并行DDL的實(shí)現(xiàn)方法
隨著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)整。
到此這篇關(guān)于MySQL并行DDL的實(shí)現(xiàn)方法的文章就介紹到這了,更多相關(guān)MySQL并行DDL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- MySQL對(duì)數(shù)據(jù)庫和表進(jìn)行DDL命令的操作代碼
- MYSQL中DML與DDL常用語句小結(jié)
- 詳解MySQL語句中的DDL
- MySql Online DDL操作記錄詳解
- MySQL數(shù)據(jù)庫之?dāng)?shù)據(jù)表操作DDL數(shù)據(jù)定義語言
- MySQL?中定位?DDL?被阻塞的問題及解決方案
- MySQL之淺談DDL和DML
- MySQL教程數(shù)據(jù)定義語言DDL示例詳解
- Mysql學(xué)習(xí)之創(chuàng)建和操作數(shù)據(jù)庫及表DDL大全小白篇
- MySQL在線DDL工具 gh-ost的原理解析
相關(guān)文章
MySQL中 and or 查詢的優(yōu)先級(jí)分析
這個(gè)可能是容易被忽略的問題，首選我們要清楚，MySQL中，AND的執(zhí)行優(yōu)先級(jí)高于OR。也就是說，在沒有小括號(hào)()的限制下，總是優(yōu)先執(zhí)行AND語句，再執(zhí)行OR語句2021-03-03
PostgreSQL物化視圖（materialized view）過程解析
這篇文章主要介紹了PostgreSQL物化視圖（materialized view）過程解析,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-02-02
mysql事務(wù)對(duì)效率的影響分析總結(jié)
在本篇文章里小編給大家分享的是一篇關(guān)于mysql事務(wù)對(duì)效率的影響分析總結(jié)內(nèi)容，有需要的朋友們可以跟著學(xué)習(xí)下。2021-10-10
在本篇文章里小編給大家分享了關(guān)于MySQL PHP語法的相關(guān)知識(shí)點(diǎn)，需要的朋友們學(xué)習(xí)參考下。2019-02-02
MySQL 實(shí)現(xiàn)雙向復(fù)制的方法指南
這篇文章主要介紹了MySQL 實(shí)現(xiàn)雙向復(fù)制的方法指南,本文包括:主機(jī)配置，從機(jī)配置，建立主-從復(fù)制，建立雙向復(fù)制，需要的朋友可以參考下2015-03-03
MySQL中json_extract()函數(shù)的使用實(shí)例
這篇文章主要介紹了MySQL中json_extract()函數(shù)的使用實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-07-07
在使用mysql5.7時(shí)，會(huì)發(fā)現(xiàn)通過web端向數(shù)據(jù)庫中寫入中文后會(huì)出現(xiàn)亂碼，但是在數(shù)據(jù)庫中直接操作SQL語句插入數(shù)據(jù)后中文就顯示正常，這個(gè)問題怎么解決呢，下面小編給大家分享下解決方案2017-03-03
mysql數(shù)據(jù)庫備份命令分享(mysql壓縮數(shù)據(jù)庫備份)
