20并行查询

简介: 20并行查询

一、执行计划

 

1. SQL执行计划概述

SQL执行计划是一个节点树,显示MogDB执行一条SQL语句时执行的详细步骤。每一个步骤为一个数据库运算符。

 

使用EXPLAIN命令可以查看优化器为每个查询生成的具体执行计划。EXPLAIN给每个执行节点都输出一行,显示基本的节点类型和优化器为执行这个节点预计的开销值。如图1所示。

 

SQL执行计划示例

![](https://cdn-mogdb.enmotech.com/docs-media/mogdb/performance-tuning/introduction-to-the-sql-execution-plan-1.png)

最底层节点是表扫描节点,它扫描表并返回原始数据行。不同的表访问模式有不同的扫描节点类型: 顺序扫描、索引扫描等。最底层节点的扫描对象也可能是非表行数据(不是直接从表中读取的数据),如VALUES子句和返回行集的函数,它们有自己的扫描节点类型。

如果查询需要连接、聚集、排序、或者对原始行做其它操作,那么就会在扫描节点之上添加其它节点。 并且这些操作通常都有多种方法,因此在这些位置也有可能出现不同的执行节点类型。

第一行(最上层节点)是执行计划总执行开销的预计。这个数值就是优化器试图最小化的数值。

 

2. 执行计划显示信息

除了设置不同的执行计划显示格式外,还可以通过不同的EXPLAIN用法,显示不同详细程度的执行计划信息。常见有如下几种:

 

EXPLAIN statement:只生成执行计划,不实际执行。其中statement代表SQL语句。

EXPLAIN ANALYZE statement:生成执行计划,进行执行,并显示执行的概要信息。显示中加入了实际的运行时间统计,包括在每个规划节点内部花掉的总时间(以毫秒计)和它实际返回的行数。

EXPLAIN PERFORMANCE statement:生成执行计划,进行执行,并显示执行期间的全部信息。

为了测量运行时在执行计划中每个节点的开销,EXPLAIN ANALYZEEXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行EXPLAIN ANALYZEEXPLAIN PERFORMANCE有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。

 

因此,当定位SQL运行慢问题时,如果SQL长时间运行未结束,建议通过EXPLAIN命令查看执行计划,进行初步定位。如果SQL可以运行出来,则推荐使用EXPLAIN ANALYZEEXPLAIN PERFORMANCE查看执行计划及其实际的运行信息,以便更精准地定位问题原因。

 

EXPLAIN PERFORMANCE轻量化执行方式与EXPLAIN PERFORMANCE保持一致,在原来的基础上减少了性能分析的时间,执行时间与SQL执行时间的差异显著减少


3. 详解

SQL执行计划概述节中所说,EXPLAIN会显示执行计划,但并不会实际执行SQL语句。EXPLAIN ANALYZEEXPLAIN PERFORMANCE两者都会实际执行SQL语句并返回执行信息。在这一节将详细解释执行计划及执行信息。


4. 执行计划

以如下SQL语句为例:

```sql
SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
```

执行EXPLAIN的输出为:

![](https://cdn-mogdb.enmotech.com/docs-media/mogdb/performance-tuning/introduction-to-the-sql-execution-plan-2.png)

执行计划层级解读(纵向):

 

第一层: Seq Scan on t2

 

 表扫描算子,用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从buffer或者磁盘上读上来输送给上层节点参与计算。

 

第二层: Hash

 

Hash算子,作用是把下层计算输送上来的算子计算hash值,为后续hash join操作做数据准备。

 

第三层: Seq Scan on t1

 

 表扫描算子,用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从buffer或者磁盘上读上来输送给上层节点参与hash join计算。

 

第四层: Hash Join

 

join算子,主要作用是将t1表和t2表的数据通过hash join的方式连接,并输出结果数据。

 

执行计划中的关键字说明:

 

表访问方式

Seq Scan

   全表顺序扫描。

Index Scan

   优化器决定使用两步的规划: 最底层的规划节点访问一个索引,找出匹配索引条件的行的位置,然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多,但是因为并非所有表的页面都被访问了,这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是,上层规划节点在读取索引标识出来的行位置之前,会先将它们按照物理位置排序,这样可以最小化独立抓取的开销。

如果在WHERE里面使用的好几个字段上都有索引,那么优化器可能会使用索引的ANDOR的组合。但是这么做要求访问两个索引,因此与只使用一个索引,而把另外一个条件只当作过滤器相比,这个方法未必是更优。

 

索引扫描可以分为以下几类,他们之间的差异在于索引的排序机制。

Bitmap Index Scan

  使用位图索引抓取数据页。

 

Index Scan using index_name

  使用简单索引搜索,该方式表的数据行是以索引顺序抓取的,这样就令读取它们的开销更大,但是这里的行少得可怜,因此对行位置的额外排序并不值得。最常见的就是看到这种规划类型只抓取一行,以及那些要求ORDER BY条件匹配索引顺序的查询。因为那时候没有多余的排序步骤是必要的以满足ORDER BY

 

表连接方式

Nested Loop

  嵌套循环,适用于被连接的数据子集较小的查询。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(不能大于10000),要把返回子集较小的表作为外表,而且在内表的连接字段上建议要有索引。

(Sonic) Hash Join

  哈希连接,适用于数据量大的表的连接方式。优化器使用两个表中较小的表,利用连接键在内存中建立hash表,然后扫描较大的表并探测散列,找到与散列匹配的行。Sonic和非SonicHash Join的区别在于所使用hash表结构不同,不影响执行的结果集。

 

Merge Join

  归并连接,通常情况下执行性能差于哈希连接。如果源数据已经被排序过,在执行融合连接时,并不需要再排序,此时融合连接的性能优于哈希连接。

 

运算符

sort

  对结果集进行排序。

filter

  EXPLAIN输出显示WHERE子句当作一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件,并且只输出符合条件的行。预计的输出行数降低了,因为有WHERE子句。不过,扫描仍将必须访问所有 10000 行,因此开销没有降低;实际上它还增加了一些(确切的说,通过10000 * cpu_operator_cost)以反映检查WHERE条件的额外CPU时间。

LIMIT

  LIMIT限定了执行结果的输出记录数。如果增加了LIMIT,那么不是所有的行都会被检索到。

 

执行信息

```sql
select sum(t2.c1) from t1,t2 where t1.c1=t2.c2 group by t1.c2;
```

执行EXPLAIN PERFORMANCE输出为:

![](https://cdn-mogdb.enmotech.com/docs-media/mogdb/performance-tuning/introduction-to-the-sql-execution-plan-3.png)

二、 SMP并行执行


1. 特性简介

 

MogDBSMP并行技术是一种利用计算机多核CPU架构来实现多线程并行计算,以充分利用CPU资源来提高查询性能的技术。

 

2. 特性描述

 

在复杂查询场景中,单个查询的执行较长,系统并发度低,通过SMP并行执行技术实现算子级的并行,能够有效减少查询执行时间,提升查询性能及资源利用率。SMP并行技术的整体实现思想是对于能够并行的查询算子,将数据分片,启动若干个工作线程分别计算,最后将结果汇总,返回前端。SMP并行执行增加数据交互算子Stream,实现多个工作线程之间的数据交互,确保查询的正确性,完成整体的查询。

 

SMP特性通过算子并行来提升性能,同时会占用更多的系统资源,包括CPU、内存、I/O等等。本质上SMP是一种以资源换取时间的方式,在合适的场景以及资源充足的情况下,能够起到较好的性能提升效果;但是如果在不合适的场景下,或者资源不足的情况下,反而可能引起性能的劣化。SMP特性适用于分析类查询场景,这类场景的特点是单个查询时间较长,业务并发度低。通过SMP并行技术能够降低查询时延,提高系统吞吐性能。然而在事务类大并发业务场景下,由于单个查询本身的时延很短,使用多线程并行技术反而会增加查询时延,降低系统吞吐性能。


3. 适用场景


支持并行的算子:计划中存在以下算子支持并行。

 

 Scan:支持行存普通表和行存分区表顺序扫描 、列存普通表和列存分区表顺序扫描。

 JoinHashJoinNestLoop

 AggHashAggSortAggPlainAggWindowAgg(只支持partition by,不支持order by

 StreamLocal RedistributeLocal Broadcast

 其他:ResultSubqueryscanUniqueMaterialSetopAppendVectoRow

 

SMP特有算子:为了实现并行,新增了并行线程间的数据交换Stream算子供SMP特性使用。这些新增的算子可以看做Stream算子的子类。

 Local Gather:实现实例内部并行线程的数据汇总。

 Local Redistribute:在实例内部各线程之间,按照分布键进行数据重分布。

 Local Broadcast:将数据广播到实例内部的每个线程。

 Local RoundRobin:在实例内部各线程之间实现数据轮询分发。

示例说明,以TPCH Q1的并行计划为例。

![](https://cdn-mogdb.enmotech.com/docs-media/mogdb/performance-tuning/configuring-smp-1.png)

在这个计划中,实现了Scan以及HashAgg算子的并行,并新增了Local Gather数据交换算子。其中3号算子为Local Gather算子,上面标有的“dop: 1/4”表明该算子的发送端线程的并行度为4,而接受端线程的并行度为1,即下层的4HashAggregate算子按照4并行度执行,而上层的1~2号算子按照串行执行,3号算子实现了实例内并行线程的数据汇总。

 

通过计划Stream算子上表明的dop信息即可看出各个算子的并行情况。

 

4. 非适用场景

 

索引扫描不支持并行执行。

MergeJoin不支持并行执行。

WindowAgg order by不支持并行执行。

cursor不支持并行执行。

存储过程和函数内的查询不支持并行执行。

不支持子查询subplaninitplan的并行,以及包含子查询的算子的并行。

查询语句中带有median操作的查询不支持并行执行。

带全局临时表的查询不支持并行执行。

物化视图的更新不支持并行执行。


5. 资源对SMP性能的影响

SMP架构是一种利用富余资源来换取时间的方案,计划并行之后必定会引起资源消耗的增加,包括CPU、内存、I/O等资源的消耗都会出现明显的增长,而且随着并行度的增大,资源消耗也随之增大。当上述资源成为瓶颈的情况下,SMP无法提升性能,反而可能导致集群整体性能的劣化。下面对各种资源对SMP性能的影响情况分别进行说明。

 

CPU资源

 

在一般客户场景中,系统CPU利用率不高的情况下,利用SMP并行架构能够更充分地利用系统CPU资源,提升系统性能。但当数据库服务器的CPU核数较少,CPU利用率已经比较高的情况下,如果打开SMP并行,不仅性能提升不明显,反而可能因为多线程间的资源竞争而导致性能劣化。

 

内存资源

 

查询并行后会导致内存使用量的增长,但每个算子使用内存上限仍受到work_mem等参数的限制。假设work_mem4GB,并行度为2,那么每个并行线程所分到的内存上限为2GB。在work_mem较小或者系统内存不充裕的情况下,使用SMP并行后,可能出现数据下盘,导致查询性能劣化的问题。

 

I/O资源

 

要实现并行扫描必定会增加I/O的资源消耗,因此只有在I/O资源充足的情况下,并行扫描才能够提高扫描性能。

 

6. 其他因素对SMP性能的影响

除了资源因素外,还有一些因素也会对SMP并行性能造成影响。例如分区表中分区数据不均,以及系统并发度等因素。

 

数据倾斜对SMP性能的影响

 

当数据中存在严重数据倾斜时,并行效果较差。例如某表join列上某个值的数据量远大于其他值,开启并行后,根据join列的值对该表数据做hash重分布,使得某个并行线程的数据量远多于其他线程,造成长尾问题,导致并行后效果差。

 

系统并发度对SMP性能的影响

 

SMP特性会增加资源的使用,而在高并发场景下资源剩余较少。所以,如果在高并发场景下,开启SMP并行,会导致各查询之间严重的资源竞争问题。一旦出现了资源竞争的现象,无论是CPUI/O、内存,都会导致整体性能的下降。因此在高并发场景下,开启SMP往往不能达到性能提升的效果,甚至可能引起性能劣化。

 

7. SMP 使用建议

使用限制

想要利用SMP提升查询性能需要满足以下条件:

 

系统的CPU、内存、I/O和网络带宽等资源充足。SMP架构是一种利用富余资源来换取时间的方案,计划并行之后必定会引起资源消耗的增加,当上述资源成为瓶颈的情况下,SMP无法提升性能,反而可能导致性能的劣化。在出现资源瓶颈的情况下,建议关闭SMP

 

配置步骤

 观察当前系统负载情况,如果系统资源充足(资源利用率小于50%),执行步骤2;否则退出。

 

 设置query_dop=1(默认值),利用explain打出执行计划,观察计划是否符合SMP适用场景与限制小节中的适用场景。如果符合,进入步骤3

 

 设置query_dop=value,不考虑资源情况和计划特征,强制选取dop1value

 

 在符合条件的查询语句执行前设置合适的query_dop值,在语句执行结束后关闭query_dop。举例如下。

```sql
MogDB=SET query_dop = 4;
MogDB=SELECT COUNT(*) FROM t1 GROUP BY a;
......
MogDB=SET query_dop = 1;
Copy
```

 说明:

资源许可的情况下,并行度越高,性能提升效果越好。

SMP并行度支持会话级设置,推荐客户在执行符合要求的查询前,打开smp,执行结束后,关闭smp。以免在业务峰值时,对业务造成冲击。


5. query_dop

参数说明: 用户自定义的查询并行度。

 

该参数属于USERSET类型参数,请参考表GUC参数分类中对应设置方法进行设置。

 

取值范围: 整型,1~64。打开固定SMP功能,系统会使用固定并行度。

 

 说明: 在开启并行查询后,请保证系统CPU、内存、网络等资源充足,以达到最佳效果。

 

默认值: 1

 

三、测试

 

1. 构建测试数据

```sql
[omm@node1 ~]$ gsql -d postgres -p26000 -r
gsql ((openGauss 3.1.0 build 2c0ccaf9) compiled at 2022-09-25 19:32:58 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=create table test (id int,en varchar(200));
CREATE TABLE
Time: 9.511 ms
openGauss=insert into test values(generate_series(1,10000000),md5(random()::text));
INSERT 0 10000000
Time: 25792.555 ms
openGauss=\d+
                                    List of relations
 Schema | Name | Type  | Owner |  Size |             Storage              | Description
--------+------+-------+-------+--------+----------------------------------+-------------
 public | test | table | omm   | 657 MB | {orientation=row,compression=no} |
(1 row)
```

2. CPU16c

query_dop=1
```sql
openGauss=\timing
Timing is on.
openGauss=set query_dop=1;
SET
Time: 0.322 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 1603.324 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 673.562 ms
```
query_dop=4
```sql
openGauss=set query_dop=4;
SET
Time: 0.306 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 210.510 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 217.283 ms
```
query_dop=8
```sql
openGauss=set query_dop=8;
SET
Time: 0.258 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 165.018 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 157.060 ms
```
query_dop=16
```sql
openGauss=set query_dop=16;
SET
Time: 0.274 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 173.323 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 163.022 ms
```
3. CPU为8c
query_dop=1
```sql
openGauss=set query_dop=1;
SET
Time: 0.242 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 3426.668 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 990.474 ms
```
query_dop=4
```sql
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 349.525 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 381.206 ms
```
query_dop=8
```sql
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 283.316 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 277.508 ms
```
query_dop=16
```sql
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 189.836 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 188.878 ms
```

4. CPU4c

query_dop=1
```sql
openGauss=\timing
Timing is on.
openGauss=set query_dop=1;
SET
Time: 0.314 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 3119.898 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 972.142 ms
```
query_dop=4
```sql
openGauss=set query_dop=4;
SET
Time: 0.267 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 380.970 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 337.972 ms
```
query_dop=8
```sql
openGauss=set query_dop=8;
SET
Time: 0.297 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 258.877 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 266.643 ms
```
query_dop=16
```sql
openGauss=set query_dop=16;
SET
Time: 0.306 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 224.949 ms
openGauss=select * from test where id=999;
 id  |                en
-----+----------------------------------
 999 | 4b9d69053e3de0ed30e1b9e3283f7270
(1 row)
Time: 216.097 ms
```

汇总

并行度  16c SQL执行时间 8c SQL 执行时间 4c SQL执行时间
query_dop=1 660 ms  990 ms  972 ms 
query_dop=4

215 ms 

349 ms 

380 ms 

query_dop=8

165 ms 

277 ms 

258 ms 

query_dop=16

166 ms 

188 ms 

224 ms 

query_dop=32

200 ms 

243 ms 

305 ms 

query_dop=64

325 ms 

387 ms 

440 ms 


相关实践学习
Serverless极速搭建Hexo博客
本场景介绍如何使用阿里云函数计算服务命令行工具快速搭建一个Hexo博客。
目录
相关文章
|
6月前
并发与并行的区别(详细介绍)
并发与并行的区别(详细介绍)
5711 0
并发和并行以及他们的区别
并发:         并发指的是多个任务交替执行的能力,这些任务可能不是同时执行,而是通过快速切换在不同任务之间来实现“同时执行”的效果。在多核处理器上,多个线程可以真正同时执行,而在单核处理器上,线程之间通过时间片轮转实现并发。         所以当谈论并发的时候一定要加个单位时间,也就是说单位时间内并发量是多少?离开了单位时间其实是没有意义的。 并行:         并行指的是多个任务同时执行的能力,每个任务都在独立的CPU上执行。并行通常用于同时处理独立任务,这些任务可以同时执行,而不需要相互等待或协同工作。 两者区别:         关键区别在于并发强调任务在时间上交替执行
120 0
|
3月前
|
存储 Cloud Native 关系型数据库
PolarDB 并行查询问题之EXISTS子查询在并行查询中如何解决
PolarDB 并行查询问题之EXISTS子查询在并行查询中如何解决
30 1
|
3月前
|
存储 关系型数据库 分布式数据库
PolarDB 并行查询问题之分布式查询执行过程中的数据分发如何解决
PolarDB 并行查询问题之分布式查询执行过程中的数据分发如何解决
47 1
|
3月前
|
关系型数据库 MySQL 分布式数据库
PolarDB 并行查询问题之帮助处理实时性分析查询如何解决
PolarDB 并行查询问题之帮助处理实时性分析查询如何解决
40 1
|
5月前
|
分布式计算 并行计算 调度
并行和并发的区别
并行和并发的区别
|
6月前
并行和并发有什么区别?
并行和并发有什么区别?
|
6月前
|
调度 数据库 计算机视觉
并行和并发的区别(详细)
并行和并发的区别(详细)
|
存储 并行计算 安全
并发和并行的区别
并发和并行的区别