乐趣区

关于mysql:MySQL-8031并行构建索引特性管窥

  • GreatSQL 社区原创内容未经受权不得随便应用,转载请分割小编并注明起源。
  • GreatSQL 是 MySQL 的国产分支版本,应用上与 MySQL 统一。
  • 本文起源:原创投稿;作者:YeJinrong/ 叶金荣

测试效率晋升 36% ~ 100%,相当可观

本文目录

  • 并行构建索引测试
  • 进一步提高索引构建效率
  • 并行构建索引的限度

MySQL 8.0.31 于 2022.10.11 公布了,比我预计的日期早了一周,先赞一个。

看了下 release notes,新增的货色不算多,感觉 MySQL 官网对 8.0 版本曾经进入维稳的后半段了,英文不好的同学能够戳此查看 徐轶韬老师针对 8.0.31 做的疾速解读。另外,依据 徐老师的最新推文,也理解到 MySQL 针对 8.0 版本缩短了规范反对(Premier Support)时长,从原来的 2023.4 缩短到了 2025.4,不过延长反对(Extended Support)的期限没有扭转,依然是 2026.4。

本次公布的 8.0.31 新个性中,我留神到有一句不太起眼的阐明:

InnoDB: InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded. Previously, this action was performed by a single thread.

只有这么简略的一句,没更多扩大解释阐明。简言之,就是反对并行构建索引,晋升索引构建性能。

并行构建索引测试

还是间接做个测试看看吧。

利用 sysbench 构建一个有 400 万行记录的测试表,只有一个主键索引时,表空间物理文件大小为 1044381696 Bytes,增加完测试索引后,表空间物理文件大小涨到 1434451968 Bytes,减少了 37.35%。


mysql> CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  4000000 |
+----------+
1 row in set (0.35 sec)

接下来,我别离针对 GreatSQL 8.0.25-16、MySQL 8.0.31 做重建索引的测试,每个数据库跑 10 次,计算其每次耗时,去掉最大最小偏差值,取剩下的 8 次求平均值。都先采纳默认设置,最初失去的后果如下表:

GreatSQL 8.0.25-16 MySQL 8.0.31
均匀耗时(秒) 42.529 31.202

能够看到,应用 MySQL 8.0.31 重建索引的效率晋升了约 36%,还是相当可观的,如果是线上环境有大表可能晋升成果更可观。

进一步提高索引构建效率

从 MySQL 8.0.27 开始,新增选项 innodb_ddl_buffer_size,其作用是作为 Online DDL 期间的 buffer(代替 innodb_sort_buffer_size 的作用),用于晋升辅助索引构建的效率。这是 Online DDL 期间总共能够应用的 buffer,如果有多个 DDL 并发线程,则每个线程最大可用的 buffer 是 innodb_ddl_buffer_size / innodb_ddl_threads。其默认值是 1MB,显著太低了,我在本案中尝试批改成 64M、128MB、256MB、512MB、1GB,再比照测试其成果。

选项 innodb_ddl_threads 也是从 8.0.27 开始新增的,用于定义 Online DDL 的并发线程数,默认值是 4,可依据理论状况调整。

模式 均匀耗时(秒) 晋升比例
GreatSQL 8.0.25-16 默认值 42.529
MySQL 8.0.31 默认值 31.202 36.30%
64M 23.448 81.38%
64M + 8thds 21.202 100.59%
128M 22.856 86.07%
128M + 8thds 21.456 98.21%
256M 22.047 92.90%
256M + 8thds 21.266 99.99%
512M 22.885 85.84%
512M + 8thds 23.227 83.10%
1G 25.239 68.51%
1G + 8thds 24.486 73.69%

下面表格中,64M 示意 innodb_ddl_buffer_size=64M,8thds 示意innodb_ddl_threads=8,其余以此类推。

能够看到,当 innodb_ddl_buffer_sizeinnodb_ddl_threads 值适当加大后,重建索引的耗时明显降低,最好的状况下,索引重建效率可晋升 1 倍;不过其效率也并不随着值减少而线性进步。看起来,线上生产环境须要依据实测状况进行调整。

循环自动测试的 shell 脚本也顺便分享进去:


#!/bin/bash
db=MySQL
# 设置不同的 ddl buffer size
for dbp in 64 128 256 512 1024
do
exec 3>&1 4>&2 1>> parallel-index-build-${dbp}m.log 2>&1

# 循环 10 次
for i in $(seq 1 10)
do
 echo "$db cycle $i"
 mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_buffer_size=$dbp*1024*1024" test > /dev/null 2>&1
 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_buffer_size" test
 mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 drop index i1, drop index i2;" test > /dev/null 2>&1
 sleep 5
 time mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 add index i1(k), add index i2(pad);" test
 echo ""echo""
 sleep 5
done

# 调整 ddl threads = 8
exec 3>&1 4>&2 1>> parallel-index-build-${dbp}m-8th.log 2>&1
for i in $(seq 1 10)
do
 echo "$db cycle $i"
 mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_threads=8" test > /dev/null 2>&1
 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_threads" test
 mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 drop index i1, drop index i2;" test > /dev/null 2>&1
 sleep 5
 time mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 add index i1(k), add index i2(pad);" test
 echo ""echo""
 sleep 5
done

# 复原 ddl threads=4
 mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_threads=4" test > /dev/null 2>&1
 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_threads" test
done

并行构建索引的限度

最初,再来看下并行构建索引新个性都有哪些限度:

不反对虚构列(virtual columns)。
不反对全文索引(full-text index)。
不反对空间索引(spatial index)。
期待 MySQL 8.0 将来新版本中持续推出更多实用的新个性吧。

延长浏览

[Changes in MySQL 8.0.31] (https://dev.mysql.com/doc/rel…
Configuring Parallel Threads for Online DDL Operations, https://dev.mysql.com/doc/ref…
Online DDL Memory Management, https://dev.mysql.com/doc/ref…

Changes in MySQL 8.0.31
Configuring Parallel Threads for Online DDL Operations
[Online DDL Memory Management]() https://dev.mysql.com/doc/ref…)


Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支,专一于晋升 MGR 可靠性及性能,反对 InnoDB 并行查问个性,是实用于金融级利用的 MySQL 分支版本。

相干链接:GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区:

欢送来 GreatSQL 社区发帖发问
https://greatsql.cn/

技术交换群:

微信:扫码增加 GreatSQL 社区助手 微信好友,发送验证信息 加群

退出移动版