GreatSQL重磅特性，InnoDB并行并行查询优化测试

不念不忘少年蓝@ 2022-09-06 04:55 203阅读 0赞

本文首发于 GreatSQL社区 微信公众号。

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

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

根据B+树的特点，可以将B+树划分为若干子树，此时多个线程可以并行扫描同一张InnoDB表的不同部分。对执行计划进行多线程改造，每个子线程执行计划与MySQL原始执行计划一致，但每个子线程只需扫描表的部分数据，子线程扫描完成后再进行结果汇总。通过多线程改造，可以充分利用多核资源，提升查询性能。

优化后，在TPC-H测试中表现优异，最高可提升30倍，平均提升15倍。

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

使用限制：

*  暂不支持子查询，可想办法改造成JOIN。
 *  暂时只支持ARM架构平台，X86架构平台优化也会尽快完成。

关于该Patch详情见：https://support.huaweicloud.com/fg-kunpengdbs/kunpengdbs\_20\_0005.html

本文针对 **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 ...

好了，直接查看结果对比数据：

<table> 
 <thead> 
  <tr> 
   <th>TPCH</th> 
   <th>并行扫描（默认参数）<br>耗时（秒）</th> 
   <th>并行扫描（参数优化后）<br>耗时（秒）</th> 
   <th>未优化前<br>耗时（秒）</th> 
   <th>并行扫描 vs 未优化前的提升</th> 
   <th>提高查询并行读优化后提升</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Q1</td> 
   <td>616.407015</td> 
   <td>43.688772</td> 
   <td>1396.791060</td> 
   <td>31.971</td> 
   <td>14.109</td> 
  </tr> 
  <tr> 
   <td>Q3</td> 
   <td>139.579648</td> 
   <td>24.343778</td> 
   <td>330.946837</td> 
   <td>13.595</td> 
   <td>5.734</td> 
  </tr> 
  <tr> 
   <td>Q5</td> 
   <td>343.604734</td> 
   <td>30.501792</td> 
   <td>338.576433</td> 
   <td>11.100</td> 
   <td>11.265</td> 
  </tr> 
  <tr> 
   <td>Q6</td> 
   <td>248.830780</td> 
   <td>20.128220</td> 
   <td>233.490352</td> 
   <td>11.600</td> 
   <td>12.362</td> 
  </tr> 
  <tr> 
   <td>Q10</td> 
   <td>155.077042</td> 
   <td>41.948881</td> 
   <td>263.921069</td> 
   <td>6.291</td> 
   <td>3.697</td> 
  </tr> 
  <tr> 
   <td>Q12</td> 
   <td>325.281718</td> 
   <td>24.850585</td> 
   <td>582.405888</td> 
   <td>23.436</td> 
   <td>13.089</td> 
  </tr> 
  <tr> 
   <td>Q19</td> 
   <td>17.475904</td> 
   <td>5.296522</td> 
   <td>42.447522</td> 
   <td>8.014</td> 
   <td>3.300</td> 
  </tr> 
 </tbody> 
</table>

从这个测试结果简单概括几条：

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

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

全文完。

Enjoy GreatSQL :)

--------------------

**文章推荐：**

*  [MGR最佳实践(MGR Best Practice)][MGR_MGR Best Practice]
 *  [万里数据库MGR Bug修复之路][MGR Bug]
 *  [在Docker中部署GreatSQL并构建MGR集群][Docker_GreatSQL_MGR]
 *  [将GreatSQL添加到系统systemd服务][GreatSQL_systemd]
 *  [金融应用场景下跨数据中心的MGR架构方案][MGR]  
    
 *  [InnoDB Cluster+GreatSQL快速部署MGR集群，并实现读写分离和故障自动转移][InnoDB Cluster_GreatSQL_MGR]
 *  [利用GreatSQL部署MGR集群，并完成添加新节点 、滚动升级、切主][GreatSQL_MGR_]
 *  [《叶问》33期，MGR最佳配置参考，PFS里的监测指标要全开吗，mysqld进程占用内存过高怎么排查][33_MGR_PFS_mysqld]

--------------------

扫码加入GreatSQL/MGR交流**QQ群**

![29971a8b70a89b2b393f415cafdd678e.png][]

点击文末“**阅读原文**”直达老叶专栏

[MGR_MGR Best Practice]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg%3D%3D&chksm=c26f6633f518ef25f19309e4b01ce5d4527d945a2a030b66a9d9bbaa598cb668eca30cb91a0f&idx=1&mid=2247484174&scene=21&sn=4f795e889e3778876b09143c4ffdc770#wechat_redirect
[MGR Bug]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg%3D%3D&chksm=c26f64abf518edbdb278f1c21e98e39dc73b78397d01c2d583450a1da6d72a4ff32a0ec6db9c&idx=1&mid=2247483798&scene=21&sn=21fc1b1797074e002e1c684263b731e8#wechat_redirect
[Docker_GreatSQL_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b40488a4cc95e2f4a7750a3cad05fd70d3b9b5f366ded103e66e16f2cdda6f67fa231803c&idx=1&mid=2653936162&scene=21&sn=fa76aa58c0262d6a4e43c675f305db6d#wechat_redirect
[GreatSQL_systemd]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b43ea8a4ccafc0edffe28125815e5a490370463d7f7c00436f5d58b47df58affd0afe263d&idx=1&mid=2653936000&scene=21&sn=d21f6f3a89e03c08663df5b34f6b09f8#wechat_redirect
[MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b43108a4cca06841d2adf3a63dfe6af99df09b09164e8676d99cb08fc5e569bfc543a0ccf&idx=1&mid=2653935994&scene=21&sn=91c596c81944aabd396d33f4924aac26#wechat_redirect
[InnoDB Cluster_GreatSQL_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b43308a4cca26c2db9912a9ce5e2d73df1a10e7415f83538742b92e5475a0b11b88e39f4c&idx=1&mid=2653935962&scene=21&sn=38f2acfb147f16ae8daaa6a48a3102ae#wechat_redirect
[GreatSQL_MGR_]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b43258a4cca336b0260c6b9cfc363f10596c775bdd0408d89dca855f3c7949911aa50ec8f&idx=1&mid=2653935951&scene=21&sn=ec8794371cd6f7677a5355b7e7ac39ea#wechat_redirect
[33_MGR_PFS_mysqld]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b436b8a4cca7ddf138db1d2342aa19f0452bff97beb2f5015cf0eb377a7d2a6899d1c3c05&idx=1&mid=2653935873&scene=21&sn=7511853d63145b7d72ae533586b6ad81#wechat_redirect
[29971a8b70a89b2b393f415cafdd678e.png]: /images/20220829/53ec7923e6b549a7af8faf7eee30abb4.png