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

一、数据倾斜

1.1 问题描述

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

1.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明显比其他节点高。

1.3 问题根因

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

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

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

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

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

1.4 解决详情

如何找到倾斜的表：

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

SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

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

SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' GROUP BY schemaname,tablename;

表的分布键的选择方法：

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

怎么看distinct的大小？

select count(distinct column1) from table;

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

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

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

3）不好的实践：

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

分布列用sequence自增生成

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

二、统计信息未收集

2.1 问题描述

略

2.2 分析过程

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

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

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

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

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

2.3 问题根因

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

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

2.4 解决详情

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

三、语句不下推

3.1 问题描述

略

3.2 分析过程

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

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

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

3.3 问题根因

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

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

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

3.4 解决详情

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

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

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

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

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

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

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

四、not in 和 not exists

4.1 问题描述

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

4.2 问题定位

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

2.执行计划中有NestLoop

4.3 问题根因

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

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

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

4.4 解决详情

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

五、未分区剪枝

5.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

5.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秒左右，性能有明显提升

5.3 问题根因

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

5.4 解决详情

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

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

6.1 问题描述

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

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

6.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秒左右跑出结果，满足客户需求。

6.3 问题根因

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

6.4 解决详情

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

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

7.1 问题描述

数据库性能时快时慢问题

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

7.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

7.3 问题根因

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

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

7.4 解决详情

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

2.设置GUC内存参数

八、“in 常量”优化

8.1 问题描述

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

8.2 分析过程

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

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

8.3 问题根因

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

8.4 解决详情

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

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

九、相关子查询1

9.1 问题描述

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

9.2 分析过程

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

9.3 问题根因

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

9.4 解决详情

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

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

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

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

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

十、相关子查询2

10.1 问题描述

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

10.2 分析过程

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

10.3 问题根因

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

10.4 解决详情

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

十一、单表点查性能差

11.1 问题描述

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

11.2 分析过程

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

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

11.3 问题根因

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

11.4 解决详情

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

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

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

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

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

十二、NestLoop+indexscan的适用场景

12.1 问题描述

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

12.2 分析过程

1. 原始的执行计划：

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

12.3 问题根因

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

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

12.4 解决详情

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

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

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

修改后的执行计划如下：

文末彩蛋：

华为云3月开年采购季正在进行中，采购季中数仓GaussDB(DWS)

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

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

SQL 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。