【文末彩蛋】数据仓库服务 GaussDB(DWS) 单点性能案例集锦

摘要：介绍了 13 种 GaussDB(DWS)单点性能的案例。

一、数据倾斜

1、问题描述

某局点 SQL 执行慢，涉及大表的 SQL 执行不出来结果。

2、分析过程

数据倾斜在很多方面都会有体现：

1. gs_ssh –c “df -h”

查看各个数据磁盘的利用率，会有不均衡的现象。正常情况下，利用率最高和利用率最高的磁盘空间相差不大，如果磁盘利用率相差超过了 5%就要引起重视。

2. 通过等待视图查看作业的运行情况，发现作业总是等待部分 DN，或者个别 DN。

Select wait_status, count(*) cnt from pgxc_thread_wait_status where wait_status not like ‘%cmd%’ and wait_status not like ‘%none%’ and wait_status not like ‘%quit%’ group by 1 order by 2 desc;

3. 慢语句的 explain performance 显示，基表 scan 的时间和行数各个 DN 之间不均衡。

基表 scan 的时间最快的 dn 耗时 5ms，最慢的 dn 耗时 1173ms

数据最多的 dn 有 22831616 行，其他 dn 都是 0 行，数据有严重倾斜。

4. 通过倾斜检查接口可以发现数据倾斜。

select table_skewness('store_sales');

select table_distribution('public','store_sales');

5. 通过资源监控发现，个别节点的 CPU/IO 明显比其他节点高。

3、问题根因

GaussDB 当前支持 Hash 表和复制表两种分布方式。默认创建的表是 Hash 分布的，如果不指定分布键，则选择表的第一列作为分布键。那么这种情况就可能存在倾斜的。

倾斜造成的负面影响非常大。

首先，SQL 的性能会非常差，因为数据只分布在部分 DN，那么 SQL 运行的时候就只有部分 DN 参与计算，没有发挥分布式的优势。

其次，会导致资源倾斜，尤其是磁盘。可能部分磁盘的空间已经接近极限，但是其他磁盘利用率很低。

可能出现部分节点 CPU 过高等等问题。

4、解决详情

如何找到倾斜的表：

1.在库中表个数少于 1W 的场景，直接使用倾斜视图查询当前库内所有表的数据倾斜情况。

1 SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

2.在库中表个数非常多（至少大于 1W）的场景，因 PGXC_GET_TABLE_SKEWNESS 涉及全库查并计算非常全面的倾斜字段，所以可能会花费比较长的时间（小时级），建议参考 PGXC_GET_TABLE_SKEWNESS 视图定义，直接使用 table_distribution()函数自定义输出，减少输出列进行计算优化，例如：

表的分布键的选择方法：

1. 这个列的 distinct 值比较大，并且没有明显的数据倾斜。也可以把多列定义成分布列。

怎么看 distinct 的大小？

select count(distinct column1) from table;

怎么看数据是不是有倾斜？

select count(*) cnt, column1 from table group by column1 order by cnt limint 100;

1. 选用经常做 JOIN 字段/group by 的列，可以减少 STREAM 运算。

2. 不好的实践：

分布列用默认值（第一列）

分布列用 sequence 自增生成

分布列用随机数生成(除非任意列，或者任意两列的组合做分布键都是倾斜的，一般不选用这种方法)。

二、统计信息未收集

1、问题描述

略

2、分析过程

1. 通过 explain verbose/explain performance 打印语句的执行计划

2. 执行计划中会有语句未收集统计信息的告警，并且通常 E-rows 估算非常小。

3. 上述例子中，在打印的执行计划中有 Warning 提示信息，提示有哪些列在这个执行计划中用到了，但是这些列没有统计信息。

在 CN 的 pg_log 日志中也有会有类似的 Warning 信息。

同时，E-rows 会比实际值小很多。

3、问题根因

优化器是基于代价的优化 (Cost-Based Optimization，简称 CBO)。在这种优化器模型下，数据库根据表的元组数、字段宽度、NULL 记录比率、distinct 值、MCV 值、HB 值等表的特征值，以及一定的代价计算模型，计算出每一个执行步骤的不同执行方式的输出元组数和执行代价(cost)，进而选出整体执行代价最小/首元组返回代价最小的执行方式进行执行。

统计信息是优化器生成执行计划的基础，没有收集统计信息，优化器生成的执行计划会非常差，如果统计信息未收集，会导致多种多样表现形式的性能问题。例如，等值关联走 NestLoop，大表 broadcast，集群 CPU 持续增高等等问题。

4、解决详情

周期性地运行 ANALYZE，或者在对表的大部分内容做了更改之后马上执行 analyze。

三、语句不下推

1、问题描述

略

2、分析过程

1.通过 explain verbose 打印语句执行计划

2.上述执行计划中有__REMOTE 关键字，这就表明当前的语句是不下推执行的。

3.不下推语句在 pg_log 中会打印不下推的原因。上述语句在 CN 的日志中会找到类似以下的日志：

3、问题根因

目前最新版本可以支持绝大多数常用函数的下推。

不下推函数的场景主要出现在自定义函数属性定义错误的场景。

不下推语句的执行方式没有利用分布式的优势，他的执行过程相当于把大量的数据和计算过程汇集到一个节点上去做，因此性能往往非常差。

4、解决详情

审视用户自定义函数的 provolatile 属性是否定义正确。如果定义不正确，要修改对应的属性，使它能够下推执行。

具体判断方法可以参考如下说明：

函数相关的所有属性都在 pg_proc 这张系统表中可以查到。其中与函数能否下推相关的两个属性是 provolatile 和 proshippable。

其中 provolatile 是继承自 PG 的字段，他的本质含义是描述函数是 IMMUTABLE/STABLE/VOLATILE 的。

简单来讲，如果一个函数对于同样的输入，一定有相同的输出，那么这类函数就是 IMMUTABLE 的，例如绝大部分的字符串处理函数。

如果一个函数的返回结果在一个 SQL 语句的调用过程中，结果是相同的，那么他就是 STABLE 的。例如时间相关的处理函数，他的最终显示结果可能与具体的 GUC 参数相关（例如控制时间显示格式的参数），这类函数都是 STABLE 的。

如果一个函数的返回结果可能随着每一次的调用而返回不同的结果。例如 nextval，random 这种函数，每次调用结果都是不可预期的，那么他就是 VOLATILE 的。

四、not in 和 notexists

1、问题描述

客户的 SQL 语句执行慢，执行计划中有 NestLoop

2、问题定位

1.首先观察 SQL 语句中有 not in 语法

2.执行计划中有 NestLoop

3、问题根因

NestLoop 是导致语句性能慢的主要原因。

Hashjoin 只能做等值关联。NestLoop 的条件中有 or 条件，所以无法用 Hashjoin 求解。

导致出现这个现象的原因是由 not in 的语义决定的(具体可以参考外网关于 not in 和 not exists 的介绍)。

4、解决详情

大多数场景下，客户需要的结果集其实是可以通过 not exists 获得的，因此上述语句可以通过修改将 not in 修改为 not exists。

五、未分区剪枝

1、问题描述

三条 sql 查询慢，查询的分区表总共 185 亿条数据，查询条件中没有涉及分区键

select passtime from 表 where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37' order by passtime desc limit 10;

select max(passtime) from 表 where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37';

列存表，分区键为 createtime,哈希分布键为 motorvehicleid

2、分析过程

1.和客户确认部分业务慢，慢的业务中都涉及到了同一张表 tb_motor_vehicle

2.和客户收集几个典型的慢 sql，分别打印执行计划

从执行计划中可以看出来，两条 sql 的耗时都集中在 Partitioned CStore Scan on public.tb_motor_vehicle 列存表的分区扫描上

3.和客户确认，该表的分区键为 createtime，而涉及到的 sql 中无任何 createtime 的筛选和过滤条件，基本可以确认是由于慢 sql 的计划没有走分区剪枝，导致了全表扫描，对于 185 亿条数据量的表，全表扫描性能会很差。

4.通过在筛选条件中增加分区键过滤条件，优化后的 sql 和执行计划如下：

SELECT passtime FROM tb_motor_vehicle WHERE createtime > '2020-02-19 00:00:00' AND createtime < '2020-02-20 00:00:00' AND passtime > '2020-02-19 00:00:00' AND passtime < '2020-02-20 00:00:00' ORDER BY passtime DESC LIMIT 10000;

性能从十几分钟，优化到了 12 秒左右，性能有明显提升

3、问题根因

慢 sql 过滤条件中未涉及分区字段，导致执行计划未分区剪枝，走了全表扫描，性能严重裂化

4、解决详情

在慢 sql 的过滤条件中增加分区筛选条件，避免走全表扫描

六、行数估算过小，走了 nestloop

1、问题描述

查询语句执行慢，卡住无法返回结果

sql 特点是 2-3 张表 left join，然后通过 select 查询结果，执行计划如下：

2、分析过程

1.排查当前的 IO，内存，CPU 使用情况，没有发现资源占用高的情况

2.查看慢 sql 的线程等待状态

select * from pg_thread_wait_status where query_id=’149181737656737395’;

根据线程等待状态，并没有出现都在等待某个 DN 的情况，初步排除中间结果集偏斜到了同一个 DN 的情况。

3.到相应的实例节点上，打印等待状态为 none 的线程堆栈信息如下：

gstack 14104

通过反复打印堆栈信息，发现堆栈在变化，并没有 hang 死，所以初步判断该问题未性能慢的问题，堆栈中有 VecNestLoopRuntime，以及结合执行计划，初步判断是由于统计信息不准，优化器评估结果集较少，计划走了 nestloop 导致性能下降。

4.对表执行 analyze 后性能并没有太大改善

5.对 sql 增加 hint 关闭索引，让优化器强行走 hashjoin，发现 hint 功能没有生效，原因是 hint 无法改变子查询中的计划

6.通过 set enable_indexscan = off;执行计划被改变，走了 Hash Left Join，慢 sql 在 3 秒左右跑出结果，满足客户需求。

3、问题根因

优化器在选择执行计划时，对结果集评估较小，导致计划走了 nestloop，性能下降

4、解决详情

通过 set set enable_indexscan = off;关闭索引功能，让优化器生成的执行计划不走 nestloop，而走 Hashjoin

七、表数据膨胀，未清理脏数据

1、问题描述

数据库性能时快时慢问题

GaussDB 数据库性能时快时慢问题，原先几秒钟的 sql，目前 20 几秒出来，导致前台 IOC 页面数据加载超时，无法对用户提供图表显示

2、分析过程

1. raid 卡缓存策略未开启、CPU 开启了节能模式，查询并未开启

/opt/MegaRAID/MegaCli/MegaCli64 -LDinfo -Lall –aAll |grep 'Write Cache'（root 用户）

cat /proc/cpuinfo |grep MHz

2.和客户确认是部分业务慢，可以提供部分慢 sql，打印执行计划，耗时主要在 index scan 上，怀疑是 IO 争抢导致，通过监控 IO，发现并没有 IO 资源使用瓶颈。

3.查询当前活跃 sql，发现有大量的 create index 语句，需要和客户确认该业务是否合理

select * from pg_stat_activity where state !=’idle’ and usename !=’omm’;

4.根据执行计划，发现在部分 DN 上耗时较高，查询表的倾斜情况，并未发现有倾斜的情况

select table_skewness(‘ioc_dm.m_ss_index_event’);

5.检查内存相关参数，设置不合理，需要优化

单节点总内存大小为 256G

max_process_memory 为 12G，设置过小

shared_buffers 为 32M，设置过小

work_mem：CN：64M 、DN：64M

max_active_statements: -1（不限制并发数）

设置方式如下：

gs_guc set -Z coordinator -Z datanode -N all -I all -c "max_process_memory=25GB"

gs_guc set -Z coordinator -Z datanode -N all -I all -c "shared_buffers=8GB"

gs_guc set -Z coordinator -Z datanode -N all -I all -c "work_mem=128MB"

6.进一步分析扫描慢的原因，发现表数据膨胀严重，对其中一张 8G 大小的表，总数据量 5 万条，做完 vacuum full 后大小减小为 5.6M

3、问题根因

1.大量表频繁增删改，未及时清理，导致脏数据过多，表数据膨胀，查询慢

2.交付时，内存参数设置不合理

4、解决详情

1.对业务涉及到的常用的大表，执行 vacuum full 操作，清理脏数据；

2.设置 GUC 内存参数

八、 “in 常量”优化

1、问题描述

简单的大表过滤的 SQL 语句中有一个“in 常量”的过滤条件，常量的个数非常多(约有 2000 多个)，基表数据量比较大，SQL 语句执行不出来。

2、分析过程

1.打印语句的执行计划：

2.执行计划中，in 条件还是作为普通的过滤条件存在。这种场景下，最优的执行计划应该是将“in 常量”转化为 join 操作性能更好。

3、问题根因

执行计划中，in 条件还是作为普通的过滤条件存在。这种场景下，最优的执行计划应该是将“in 常量”转化为 join 操作性能更好。

4、解决详情

qrw_inlist2join_optmode 可以控制把“in 常量”转 join 的行为。默认是 cost_base 的。如果优化器估算不准，可能会出现需要转化的场景没有做转化，导致性能较差。

这种情况下可以通过设置 qrw_inlist2join_optmode 为 rule_base 来规避解决。

九、相关子查询 1

1、问题描述

用户的 SQL 性能差，执行计划中有 SubPlan 的关键字

2、分析过程

执行计划中有 SubPlan，这类语句的性能往往比较差。

3、问题根因

执行计划中有 SubPlan 的语句往往性能比较差，这是因为，引用 SubPlan 结果的算子可能需要反复的调用获取这个 SubPlan 的值，即 SubPlan 以下的结果要重复执行很多次。

4、解决详情

这类问题通常通过改写 SQL 来规避。往往这种场景的 SQL 语句的改写是比较困难，而且很容易出现改写后的结果不一致问题。

由于我们在比较高的版本上已经支持了很多场景想的 SubPlan 的自动转化为 join 操作，因此一种比较方便的思路是打印他在高版本下的执行计划(explain verbose)，然后根据 explain verbose 演绎出来改写后的 SQL 语句。

以上述为例，他在高版本的执行计划如下：

那么根据上述信息，SQL 语句可以改写为：

为了确认改写后的语句与原来的语句是等价的，可以再次打印改写后的执行计划，对比：

十、相关子查询 2

1、问题描述

UPDATE 场景下出现了 SubPlan 导致语句执行性能差

2、分析过程

上述执行计划中有 SubPlan，这类语句的性能往往比较差。

3、问题根因

执行计划中有 SubPlan 的语句往往性能比较差，原因与 1.9 章节案例类似。

4、解决详情

上述问题可以通过特定的改写方法来解决：

十一、单表点查性能差

1、问题描述

单表查询的场景下，客户预期 1s 以内返回结果，实际执行耗时超过 10s

2、分析过程

1. 通过抓取问题 SQL 的执行信息，发现大部分的耗时都在“CStore Scan”

2．分析出问题的场景：基表是一张十亿级别的表，每晚有批量增量数据入库，同时会有少量的数据清洗的工作。白天会有高并发的查询操作，查询不涉及表关联，并且返回结果都不大。

3、问题根因

这种场景属于行列存表选择错误导致的问题。这种场景应该使用行存表+btree 索引。

4、解决详情

调整表定义，表修改为行存表。同时建立 btree 索引，索引建立的原则：

1. 基于充分分析客户 SQL 的背景下去建立索引。

2. 索引要建立的刚刚好，不要有冗余

3. 建立组合索引时候，要把过滤性比较好的列往前放

4. 尽可能多的过滤条件都用到索引

十二、NestLoop+indexscan 的适用场景

1、问题描述

某客户反馈两个表的关联要去秒级返回，其中大表有 2.7T，小表有 100GB 左右，查询结果一般都不大，过滤条件中有过滤性比价好的条件。

2、分析过程

1. 原始的执行计划：

2. 可以看到两个表关联走了 HashJoin，主要的耗时在基表扫描和 HashJoin 操作上。

3、问题根因

主要的耗时点是在 Hashjoin 和基表扫描上，这种情况下可以考用 NestLoop+indexScan 的计划。

这种计划会把 join 条件下推到基表扫描上，然后利用基表的索引，提前把数据过滤掉。

4、解决详情

由于 NestLoop+indexScan 的计划有一些约束：

1. Join 的时候不能有 stream（不能通过 stream 来传递 join 条件的下推）

2. 大表上要有合适的索引。

修改后的执行计划如下：

【文末彩蛋】

临近华为云 3 月开年采购季

据内部可靠消息，采购季中数仓GaussDB(DWS)

包月包年都将有重大优惠，对企业用户尤为友好！！！

PS：关注数仓 GaussDB(DWS)公众号，get 最新最全的产品资讯和数仓黑科技，更有超多活动，福利不停歇！欢迎访问数仓 GaussDB(DWS)开发者论坛，产品特性随时交流，求助问题还有专家在线实时答疑哦~扫描下方二维码关注我哦↓↓↓

点击关注，第一时间了解华为云新鲜技术~