乐趣区

关于mysql:GreatSQL重磅特性InnoDB并行并行查询优化测试

jiezi

欢送来到 GreatSQL 社区分享的 MySQL 技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
  • 1、测试环境
  • 2、测试数据

GreatSQL 马上正式开源了，这次又新增了两个重磅个性：InnoDB 事务锁优化 以及 InnoDB 引擎的并行查问优化，这两个个性是由华为鲲鹏计算团队奉献的 Patch 合并而来。

InnoDB 并行查问优化怎么实现的？

依据 B + 树的特点，能够将 B + 树划分为若干子树，此时多个线程能够并行扫描同一张 InnoDB 表的不同局部。对执行打算进行多线程革新，每个子线程执行打算与 MySQL 原始执行打算统一，但每个子线程只需扫描表的局部数据，子线程扫描实现后再进行后果汇总。通过多线程革新，能够充分利用多核资源，晋升查问性能。

优化后，在 TPC- H 测试中体现优异，最高可晋升 30 倍，均匀晋升 15 倍。

该个性实用于周期性数据汇总报表之类的 SAP、财务统计等业务，例如月初、月底跑批业务等。

应用限度：

  • 暂不反对子查问，可想方法革新成 JOIN。
  • 临时只反对 ARM 架构平台，X86 架构平台优化也会尽快实现。

对于该 Patch 详情见：https://support.huaweicloud.c…

本文针对 InnoDB 引擎的并行查问优化 个性进行比照测试。

1、测试环境

服务器：神州鲲泰 R222，华为 Hi1616 * 2（主频 2400 MHz 共 64 个逻辑 CPU），256G 内存。

操作系统：Docker 20.10.2，Docker 容器下的 CentOS Linux release 7.9.2009，Linux 4.15.0-29-generic。

本次测试采纳 TPC-H，dbgen 结构测试数据参数 dbgen -vf -s 50，导入后数据库物理大小约 70G。GreatSQL 要害配置：

# 运行 Q10 测试时，须要较大长期表
temptable_max_ram = 6G

#使得本测试基于纯内存场景
innodb_buffer_pool_size=96G

#InnoDB 并行查问优化
#global 级别，设置并行查问的开关，bool 值，on/off。默认 off，敞开并行查问个性。可在线动静批改。force_parallel_execute = ON

#global 级别，设置零碎中总的并行查问线程数。有效值的范畴是（0, ULONG_MAX），默认值是 64。parallel_max_threads = 64

#global 级别，并行执行时 leader 线程和 worker 线程应用的总内存大小下限。有效值的范畴是（0, ULONG_MAX），默认值是 1G
parallel_memory_limit = 32G

2、测试数据

测试过程中，留神要确保每次查问都是基于纯内存的场景，也就是确保 innodb_buffer_pool_size 大于数据库物理大小，并确认查问过程中没有额定的物理 I / O 产生。

个别 SQL 例如 Q10 在运行过程中会产生长期表（Using temporary），这时候须要加大 temptable_max_ram 选项值。该选项默认值 1G，在上述测试数据量前提下，大略须要加大到 4G 能力 hold 住。如果该选项值不够的话，可能运行过程中会提醒诸如 The table ‘/tmp/#sql57_a1_0’ is full 这样的谬误提醒，而后退出查问，这是 MySQL 的 BUG#99100。

InnoDB 并行查问个性通过 HINT 语法能够很不便地应用，首先确认启用了该个性（可在线动静关上）：

$ mysqladmin var|grep force_parallel_execute
| force_parallel_execute                                   | ON

那么默认所有的 SQL 只有符合条件，即可主动采纳并行查问，通过查看执行打算确认：

mysql> EXPLAIN SELECT ... FROM ... WHERE ...
...
Parallel execute (4 workers)
...

能够看到执行打算输入中蕴含 Parallel execute (4 workers) 关键字，这就示意最高可并行 4 个线程查问。

也能够查看树状执行打算：

mysql> EXPLAIN FORMAT=TREE SELECT ... FROM ... WHERE ...
...
| -> Limit: 1 row(s)
    -> Sort: lineitem.l_returnflag, lineitem.l_linestatus, limit input to 1 row(s) per chunk
        -> Table scan on <temporary>
            -> Aggregate using temporary table
                -> Parallel scan on <temporary>
                    -> Sort: lineitem.l_returnflag, lineitem.l_linestatus
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (lineitem.l_shipdate <= <cache>((DATE'1998-12-01' - interval '88' day)))  (cost=6342898.28 rows=19669815)
                                    -> PQblock scan on lineitem  (cost=6342898.28 rows=59015354)
...

能够看到执行打算中蕴含 PQblock scan on … 关键字，并且留神到同一行里提醒 cost=6342898.28，这是启用并行查问的条件之一，也就是 cost 超过了 parallel_cost_threshold = 1000 设置的阈值开关。

一条 SQL 若不想启用并行查问，加上相应的 HINT 即可：

mysql> SELECT /*+ NO_PQ */ ... FROM ... WHERE ...

也能够动静调整并行线程数为最高 64 线程：

mysql> SELECT /*+ PQ(64) */ ... FROM ... WHERE ...

好了，间接查看后果比照数据：

从这个测试后果简略概括几条：

  • 1、均匀晋升约 14 倍，最高晋升约 32 倍。
  • 2、如果并发量更高，则优化成果更好。
  • 3、Q5 原始 SQL 性能晋升不多，调整 JOIN 程序后性能晋升显著（从只晋升 28% 跃升到 11 倍）。

GreatSQL 将于近期正式开源，欢送关注。

Enjoy GreatSQL :)

文章举荐：

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12，MGR 整个集群挂掉后，如何能力主动选主，不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』：《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15，都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

对于 GreatSQL

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

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili：
https://space.bilibili.com/13…

微信 &QQ 群：
可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

退出移动版