技术分享 | MySQL 并行 DDL

随着 MySQL 版本的不断更新，对 DDL *** 作的支持也在不断的完善和更新：比如从 MySQL 5.6 引入 Online DDL ，在 MySQL 5.7 对 Online DDL 进一步完善，到现在的 8.0 版本，则对 DDL 的实现重新进行了设计，比如 DDL *** 作支持原子特性，在 MySQL 8.0.27 引入并行 DDL 。本篇就来探究一下 MySQL 8.0.27 的并行 DDL 对于 DDL *** 作速度的提升。

MySQL 8.0.14 引入了 innodb_parallel_read_threads 变量来控制扫描聚簇索引的并行线程。MySQL 8.0.27 引入了 innodb_ddl_threads 变量来控制用于创建二级索引时的并行线程数量，此参数一般和一并引入的 innodb_ddl_buffer_size 一起使用，innodb_ddl_buffer_size 用于指定进行并行 DDL *** 作时能够使用的 buffer 大小，buffer 是在所有的 DDL 并行线程中平均分配的，所以一般如果调大 innodb_ddl_threads 变量时，也需要调大 innodb_ddl_buffer_size 的大小。

innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默认大小分别为：

接下来测试一下调大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 参数值对 DDL *** 作的性能提升。

首先创建一张 5000 万的表：

分别测试不同的线程数量和缓冲区大小的 DDL *** 作时间，例如：

通过不断调整相关参数得到以下结果：

可以看到，随着并发线程的增多和 buffer 的增加，DDL *** 作所占用的资源也越多，而 DDL *** 作所花费的时间则越少。不过通过对比资源的消耗和 DDL 速度的提升比例，最合理的并行线程数量为4-8个，而 buffer 大小可以根据情况进行调整。

参考链接： https://dev.mysql.com/doc/refman/8.0/en/online-ddl-parallel-thread-configuration.html

DATABASE：默认值，基于库的并行复制方式

LOGICAL_CLOCK：基于组提交的并行复制方式

mysql>show variables like "slave_parallel_type"

mysql>show variables like "slave_parallel_workers"

master_info_repository=TABLE #开启MTS 功能后，会频繁更新master.info，设置为TABLE 减小开销

slave_master_info 记录了首次同步master 的位置relay_log_recovery=ON （slave IO 线程crash，如果relay‐log损坏，则自动放弃所有未执行的relay‐log，重新从master 上获取日志，保证relay‐log 的完整性）

slave_preserve_commit_order=ON 在slave 上应用事务的顺序是无序的，和relay log 中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log 中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。

虽然mysql5.7 添加MTS 后，虽然slave 可以并行应用relay log，但commit 部分仍然是顺序提交，其中可能会有等待的情况。

mysql5.7 并行复制 提升

分享mysql 5.7 docker 主从复制架构搭建教程，供大家参考，具体内容如下

环境版本:

MySQL : 5.7.13

Docker : 1.11.2

CentOS : 7.1

1.先在两个物理机上分别安装两个MySQL.命令如下

复制代码 代码如下:docker pull mysql:5.7.13

docker run --name anuo-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=qaz.00JK -d mysql:5.7.13

2.在主库上创建一个复制账户

复制代码 代码如下:GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.2.103' IDENTIFIED BY 'qaz.00JK'

复制账户为: rep1

指定从库的IP必须为: 192.168.2.103

复制密码为: qaz.00JK

3.修改主库的配置文件 (麻烦,应该有更方便的修改方式)

3.1先从docker拷贝配置文件到主机/root 目录:

docker cp anuo-mysql:/etc/mysql/my.cnf /root

3.2在主机打开 my.cnf , 在 [mysqld] 节点最后加上

log-bin=mysql-bin

server-id=1

3.3 再把此文件上传到docker mysql 里面覆盖

docker cp /root/my.cnf anuo-mysql:/etc/mysql/my.cnf

3.4 重启 mysql 的docker , 让配置生效

docker restart anuo-mysql

4. 修改从库的配置文件

跟第三步一样, 唯一不同是

server-id=2

5. 开始备份, 在主库执行以下命令, 让主库所有表置于只读不能写的状态, 这样达到主从库数据一致性

FLUSH TABLES WITH READ LOCK

6. 将主库的数据库备份在从库还原

用navicat for mysql 很方便 *** 作

7. 从库还原后, 释放主库的读锁, 这样主库恢复写权限

unlock tables

8.配置从库连接主库, 在从库上执行

CHANGE MASTER TO MASTER_HOST='192.168.2.108', MASTER_PORT=3306, MASTER_USER='rep1', MASTER_PASSWORD='qaz.00JK', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=898

最后两项

MASTER_LOG_FILE 和 MASTER_LOG_POS

在主库执行 : SHOW MASTER STATUS命令可以取得

对应的字段是 File 和 Position

9. 在从库启动 slave 线程开始同步

START SLAVE

---