【调优实践】HANA迁移GaussDB(DWS)调优总结

一、HANA与DWS差异

HANA：单节点内存数据库；

DWS：多节点“传统”磁盘数据库。

HANA侧重于秒级分析型场景，在HANA替换时，有大量查询性能指标要求在500ms~1s之间，同时业务对性能稳定性的要求也很高，这就对GaussDB(DWS)的性能和性能稳定性提出了更高的要求。

HANA和DWS的差异主要体现在以下两点：

1.1 部署方式

单节点是HANA的主要部署方式，而DWS作为MPP数据库，往往使用多节点的部署方式。由于部署方式上的差异导致HANA和DWS在功能和性能上存在以下差异：

1. 实例：HANA单节点单实例部署，无CN/DN之分；DWS为支持分布式执行框架，包含CN/DN/GTM实例，CN用于查询解析、计划生成，并将计划分发至DN执行，DN用于实际存储和计算，GTM用于生成全局唯一事务号；
2. 数据交互：HANA不涉及多进程间的数据交互；DWS包含CN和DN以及DN间的数据交互；
3. 存储/计算倾斜：HANA不涉及存储倾斜和计算倾斜；DWS则可能出现存储倾斜和计算倾斜，分布列选择不合适可能导致存储倾斜，而JOIN/GROUP可能引发计算倾斜；
4. SQL下推：HANA不涉及SQL下推；DWS存在存储过程不支持下推的场景：存储过程在CN/DN执行结果不一致情况下，不支持下推；
5. 窗口函数：HANA只有单节点，窗口函数不存在计算瓶颈；DWS因为分布式架构，窗口函数涉及全局排序，需要在单DN计算，涉及单DN瓶颈。

1.2 内存使用方式

HANA作为内存数据库，所有数据加载在内存中，查询读写不直接涉及磁盘IO；DWS作为磁盘数据库，查询可能涉及磁盘IO。二者在内存和磁盘使用上存在以下差异：

1. 磁盘IO：HANA查询读写全部在内存中，不涉及磁盘IO；DWS数据缓存大小有限，查询可能涉及磁盘IO，可能存在IO瓶颈；
2. 中间结果集：HANA不涉及中间结果集下盘，内存不足时查询报错；DWS查询可能出现中间结果集下盘，导致查询性能劣化；
3. 内存利用率：因为全部数据加载至内存中，HANA内存利用率普遍偏高(80%~90%)；DWS单节点内存有限，且为防止查询报错设计了内存预管控机制，内存利用率普遍偏低(50%以下)。

二、查询优化总结

因为架构和功能特性上的差异，HANA迁移DWS时查询性能可能出现劣化，此时就需要进行查询优化。在HANA迁移DWS过程中常用到的查询优化如下（以下优化策略侧重于列存场景）：

2.1 计划生成

HANA支持计划管理，不需要每次查询都生成执行计划，但是DWS低版本(813)不支持计划管理，同时因为实时分析型业务对性能敏感，因此计划生成时间长对查询性能影响较大。调优时一般考虑以下几点：

1. JOIN顺序的选择

多表JOIN时，随着表数量的增加，表之间的关联顺序随之增加，而因此所需的计划生成时间也就越长。如果想要关联顺序是全局最优的，那就需要在查询表的所有关联顺序中选择一个最优的，在表数量很大时，这个时间可能需要很久。为了预防这种情况，我们可以把查询表进行分组，每组选择一个最优的关联顺序，然后各组再关联起来，这样可以大大降低计划生成时间。基于此，DWS引入了JOIN_COLLAPSE_LIMIT参数，用于控制JOIN顺序选择时每组最大表数量，JOIN_COLLAPSE_LIMIT默认值为8。

示例：JOIN_COLLAPSE_LIMIT取默认值(8)，12个表做关联。此时会将12个表分为两组，前8个表一组，后4个表一组，两组分别生成关联顺序，然后两组结果再进行关联。

JOIN_COLLAPSE_LIMIT取值8的情况下，大部分查询的计划生成时间都在100ms以内，处于可接受范围内。但是部分查询可能因为不同关联顺序之间代价接近，导致计划生成时间较长，此时就需要调整JOIN_COLLAPSE_LIMIT=5/3(一般情况下，取值为5即可，取值过小可能生成不太好的计划)，以此来减小计划生成对性能的影响。

2. STREAM方式的选择

两表关联时，如果关联列不是分布列，可能的计划有多种：两表按照JOIN列重分布；其中一个表广播；如果存在倾斜，可能需要倾斜优化。在关联较多时，STREAM方式的选择也可能消耗较长时间。优化方法一般有以下几种：

a) 复制表，数据量小于100W的维表可以建成复制表，一方面可以减少计划生成时间，另一方面还可以减少STREAM，缓解计算倾斜；

b) 选择JOIN列做分布列，两表分布列都是JOIN列情况下，就不需要STREAM了；

c) hint指定STREAM方式，示例：/*+ broadcast(a b) */。

此外，关闭SHARE SCAN，子查询在每个引用点都需要单独生成执行计划，并单独执行；而计划生成是单线程串行的，因此子查询引用多的情况下，计划生成也可能需要很长时间，此时开启SHARE SCAN即可解决该问题。

2.2 磁盘IO

DWS作为磁盘数据库，大量数据的磁盘IO可能成为查询的瓶颈。优化思路就是减少不必要的磁盘IO，常见的优化方向有：

1. 分区剪枝

创建分区表，使用分区剪枝，是减少磁盘IO的一个重要方式。逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。将一个大表按照分区策略切分为若干分区，在查询时只需要读取指定分区的数据，而不是全表扫描，以此提高查询性能。

2. PCK

局部聚簇（Partial Cluster Key）是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的实现基表扫描的filter过滤。如果分区剪枝不足以解决全表扫描问题，可以考虑使用PCK，提高rough check和bloom filter的CU过滤效率，以此减少磁盘IO。

3. BLOOM FILTER

在查询中，不仅显示的WHERE/ON条件可以实现对表数据的过滤，两表关联时，小表同样可以对大表数据进行过滤，这就是BLOOM FILTER。

示例：A表和B表关联：A.x=B.y，A表仅有1000条数据，B表有十亿数据，两表关联后仅有2000条数据。此时就可以考虑使用BLOOM FILTER，使用A.x的范围对B表进行过滤，过滤效果类似于给B表增加以下过滤条件：

B.y < (select max(x) from A)  AND B.y > (select min(x) from A)。

此外高效的数据类型也可以提高IO效率。

2.3 SHARE SCAN

SHARE SCAN将子查询结果集暂时保存在内存中，以此来缓解子查询多次引用带来的资源消耗。区别于pipeline的执行方式，SHARE SCAN会先将子查询执行结束，并把子查询结果保存在内存中，后续子查询使用时直接从内存中读取数据，而不需要再执行一遍。SQL中如果存在子查询多次引用的情况，但是执行计划中无CTE字样，则需要确认是否开启SHARE SCAN(enable_stream_ctescan)。

2.4 网络瓶颈

DWS作为分布式架构，在SQL执行过程可能涉及DN间数据交互(STREAM)，常见的可能产生STREAM的算子有JOIN和AGG。优化策略是减少STREAM数据量并减小倾斜，甚至消灭STREAM。

1. JOIN算子

两表JOIN时，如果需要STREAM，不同的STREAM方式网络吞吐量可能有所不同，所需的时间也不同。因为估算存在误差，优化器生成的执行计划中STREAM方式可能不是最优的，此时就需要hint指定更优的STREAM方式。示例：/*+ redistribute(t1 t2) */

2. AGG算子

AGG列不是分布列时，AGG算子可能的执行方式有三种：

a) AGG + GATHER + AGG；

b) STREAM + AGG；

c) AGG + STREAM + AGG。

不同的AGG方式，涉及的网络吞吐量有所不同。如果STREAM前AGG可以大幅缩减数据量，则选择第三种AGG方式；否则选择第二种AGG方式，某些特定场景下第一种AGG方式可能性能更好。

2.5 算子下推

常见的可以下推的算子有：GROUP BY/ORDER BY LIMIT/LIMIT/DISTINCT/过滤条件等。

示例：

SELECT DISTINCT A.x FROM A JOIN B ON A.x=B.y ; 其中A.x包含大量重复数据。

此时可以考虑将DISTINCT下推至表A：

SELECT DISTINCT  A.x FROM (SELECT DISTINCT x FROM A) A JOIN B ON A.x=B.y ; 

2.6 向量化

向量化执行相对于行引擎的改变是将一次一元组的模型修改为一次一批元组，配合列存特性，可以带来巨大的性能提升。

以下场景下可能导致查询不走向量化：

1. 行存表

查询中包含行存表时，查询默认走行存引擎，此时可以设置enable_force_vector_engine=on强制走列存向量化引擎。

2. 不支持向量化的函数

SQL中包含不支持向量化的函数，比如：STRING_AGG(ORDER BY)。此时可以使用临时表，减小不可向量化范围。

2.7 NULL值过滤

NULL值不参与JOIN，但是NULL值过多时可能引发哈希链冲突，导致查询性能差，可以考虑提前NULL值过滤提升性能。

示例： SELECT * FROM A LEFT JOIN B ON A.x=B.y; B.y包含大量NULL值。因为NULL值不参与JOIN，因此可以按以下方式改写优化：

SELECT * FROM A LEFT JOIN (SELECT * FROM B WHERE y IS NOT NULL) B ON A.x=B.y; 

2.8 关联方式/顺序

1. 关联顺序不佳

关联顺序不佳时，可以hint指定关联顺序（/*+ leading((a b)) */），或set join_collapse_limit=1，并改写SQL调整表关联顺序。

2.关联方式不佳

估算异常/INDEX + NESTLOOP导致两表关联走了NESTLOOP，可以通过hint指定关联方式(/*+ hashjoin(a b) */)或删除索引。

2.9 GROUP BY

数据量较大时，SORT AGG性能相对HASH AGG性能劣化明显，导致SORT AGG的情况主要有以下几种：

代价估算异常：hint或设置enable_sort=off，提高SORT AGG代价，使AGG调整为HASH AGG；

GROUPING SETS：将GROUPING SETS改为多个GROUP BY的UNION ALL；

多COUNT(DISTINCT)：8.2.1版本后可修改为UNIQUE规避；8.2.1前版本理论上可修改为多个单COUNT(DISTINCT)的关联，但是此方法对资源消耗较大。

2.10 高效数据类型

低效的数据类型不仅影响性能，还可能消耗更多的内存。

数据类型选择优先级：

smallint > int > bigint > varchar > char

smallint > int > bigint > NUMERIC > 浮点数

2.11 ETL优化

1. MERGE全量更新

更新表和被更新表过滤，将全量数据更新修改为增量数据更新。

2. 并发更新

列存数据更新的最小单位是一个CU，并发更新同一CU数据存在事务锁冲突，实际并不会并发执行。可以考虑不同并发更新不同分区数据，避免更新同一CU数据缓解锁冲突。

2.12 LLVM编译优化

LLVM编译优化本身目的是提升查询执行性能，但是数据量较小时，性能优化幅度小于LLVM本身时间消耗，反而会导致性能劣化。LLVM导致的性能劣化主要体现在GATHER/BROADCAST时间异常或压测CPU消耗高/性能劣化明显。这种情况下可以尝试关闭LLVM编译优化解决(enable_codegen=off)。

三、总结

查询优化的基本原则是：资源利用的最大化原则，使用最小代价实现最大收益，即所谓的花小钱办大事。

1. 资源：CPU、内存、磁盘IO、网络IO

2. 高效原则（以最优的执行方式实现功能）

• nestloop/hash join
• 小表广播/大表重分布
• 全表扫描/索引扫描

3. 均衡原则

• 单点快不叫快
• 不要让CN成为瓶颈（下推）
• 存储/计算均衡（防止倾斜）

4. 内聚原则

• 内存 > 磁盘 > 网络
• 减少数据交互