关于sql:文末彩蛋数据仓库服务-GaussDBDWS单点性能案例集锦

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

1.1 数据歪斜

1.1.1 问题形容

某局点 SQL 执行慢，波及大表的 SQL 执行不进去后果。

1.1.2 剖析过程

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

1. gs_ssh –c“df -h”

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

1. 通过期待视图查看作业的运行状况，发现作业总是期待局部 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;

1. 慢语句的 explain performance 显示，基表 scan 的工夫和行数各个 DN 之间不平衡。

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

数据最多的 dn 有 22831616 行，其余 dn 都是 0 行，数据有重大歪斜。

1. 通过歪斜查看接口能够发现数据歪斜。

select table_skewness(‘store_sales’);

select table_distribution(‘public’,’store_sales’);

1. 通过资源监控发现，个别节点的 CPU/IO 显著比其余节点高。

1.1.3 问题根因

GaussDB 以后反对 Hash 表和复制表两种散布形式。默认创立的表是 Hash 散布的，如果不指定散布键，则抉择表的第一列作为散布键。那么这种状况就可能存在歪斜的。

歪斜造成的负面影响十分大。

首先，SQL 的性能会十分差，因为数据只散布在局部 DN，那么 SQL 运行的时候就只有局部 DN 参加计算，没有施展分布式的劣势。

其次，会导致资源歪斜，尤其是磁盘。可能局部磁盘的空间曾经靠近极限，然而其余磁盘利用率很低。

可能呈现局部节点 CPU 过低等等问题。

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

2) 选用常常做 JOIN 字段 /group by 的列，能够缩小 STREAM 运算。

3) 不好的实际：

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

散布列用 sequence 自增生成

散布列用随机数生成 (除非任意列，或者任意两列的组合做散布键都是歪斜的，个别不选用这种办法)。

1.2 统计信息未收集

1.2.1 问题形容

略

1.2.2 剖析过程

1. 通过 explain verbose/explain performance 打印语句的执行打算
2. 执行打算中会有语句未收集统计信息的告警，并且通常 E -rows 估算十分小。

1. 上述例子中，在打印的执行打算中有 Warning 提示信息，提醒有哪些列在这个执行打算中用到了，然而这些列没有统计信息。

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

同时，E-rows 会比理论值小很多。

1.2.3 问题根因

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

统计信息是优化器生成执行打算的根底，没有收集统计信息，优化器生成的执行打算会十分差，如果统计信息未收集，会导致多种多样表现形式的性能问题。例如，等值关联走 NestLoop，大表 broadcast，集群 CPU 继续增高等等问题。

1.2.4 解决详情

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

1.3 语句不下推

1.3.1 问题形容

略

1.3.2 剖析过程

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

2. 上述执行打算中有__REMOTE 关键字，这就表明以后的语句是不下推执行的。

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

1.3.3 问题根因

目前最新版本能够反对绝大多数罕用函数的下推。

不下推函数的场景次要呈现在自定义函数属性定义谬误的场景。

不下推语句的执行形式没有利用分布式的劣势，他的执行过程相当于把大量的数据和计算过程会集到一个节点下来做，因而性能往往十分差。

1.3.4 解决详情

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

具体判断办法能够参考如下阐明：

函数相干的所有属性都在 pg_proc 这张零碎表中能够查到。其中与函数是否下推相干的两个属性是 provolatile 和 proshippable。

其中 provolatile 是继承自 PG 的字段，他的实质含意是形容函数是 IMMUTABLE/STABLE/VOLATILE 的。

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

如果一个函数的返回后果在一个 SQL 语句的调用过程中，后果是雷同的，那么他就是 STABLE 的。例如工夫相干的处理函数，他的最终显示后果可能与具体的 GUC 参数相干（例如管制工夫显示格局的参数），这类函数都是 STABLE 的。

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

1.4 not in 和 not exists

1.4.1 问题形容

客户的 SQL 语句执行慢，执行打算中有 NestLoop

1.4.2 问题定位

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

2. 执行打算中有 NestLoop

1.4.3 问题根因

NestLoop 是导致语句性能慢的次要起因。

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

导致呈现这个景象的起因是由 not in 的语义决定的 (具体能够参考外网对于 not in 和 not exists 的介绍)。

1.4.4 解决详情

大多数场景下，客户须要的后果集其实是能够通过 not exists 取得的，因而上述语句能够通过批改将 not in 批改为 not exists。

1.5 未分区剪枝

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

1.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 秒左右，性能有显著晋升

1.5.3 问题根因

慢 sql 过滤条件中未波及分区字段，导致执行打算未分区剪枝，走了全表扫描，性能重大裂化

1.5.4 解决详情

在慢 sql 的过滤条件中减少分区筛选条件，防止走全表扫描

1.6 行数估算过小，走了 nestloop

1.6.1 问题形容

查问语句执行慢，卡住无奈返回后果

sql 特点是 2 - 3 张表 left join，而后通过 select 查问后果，执行打算如下：

1.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 秒左右跑出后果，满足客户需要。

1.6.3 问题根因

优化器在抉择执行打算时，对后果集评估较小，导致打算走了 nestloop，性能降落

1.6.4 解决详情

通过 set set enable_indexscan = off; 敞开索引性能，让优化器生成的执行打算不走 nestloop，而走 Hashjoin

1.7 表数据收缩，未清理脏数据

1.7.1 问题形容

数据库性能时快时慢问题

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

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

1.7.3 问题根因

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

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

1.7.4 解决详情

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

2. 设置 GUC 内存参数

1.8 “in 常量”优化

1.8.1 问题形容

简略的大表过滤的 SQL 语句中有一个“in 常量”的过滤条件，常量的个数十分多 (约有 2000 多个)，基表数据量比拟大，SQL 语句执行不进去。

1.8.2 剖析过程

1. 打印语句的执行打算：

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

1.8.3 问题根因

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

1.8.4 解决详情

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

这种状况下能够通过设置 qrw_inlist2join_optmode 为 rule_base 来躲避解决。

1.9 相干子查问 1

1.9.1 问题形容

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

1.9.2 剖析过程

执行打算中有 SubPlan，这类语句的性能往往比拟差。

1.9.3 问题根因

执行打算中有 SubPlan 的语句往往性能比拟差，这是因为，援用 SubPlan 后果的算子可能须要重复的调用获取这个 SubPlan 的值，即 SubPlan 以下的后果要反复执行很屡次。

1.9.4 解决详情

这类问题通常通过改写 SQL 来躲避。往往这种场景的 SQL 语句的改写是比拟艰难，而且很容易呈现改写后的后果不统一问题。

因为咱们在比拟高的版本上曾经反对了很多场景想的 SubPlan 的主动转化为 join 操作，因而一种比拟不便的思路是打印他在高版本下的执行打算 (explain verbose)，而后依据 explain verbose 演绎进去改写后的 SQL 语句。

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

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

为了确认改写后的语句与原来的语句是等价的，能够再次打印改写后的执行打算，比照：

1.11 相干子查问 2

1.11.1 问题形容

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

1.11.2 剖析过程

上述执行打算中有 SubPlan，这类语句的性能往往比拟差。

1.11.3 问题根因

执行打算中有 SubPlan 的语句往往性能比拟差，起因与 1.9 章节案例相似。

1.11.4 解决详情

上述问题能够通过特定的改写办法来解决：

1.12 单表点查性能差

1.12.1 问题形容

单表查问的场景下，客户预期 1s 以内返回后果，理论执行耗时超过 10s

1.12.2 剖析过程

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

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

1.12.3 问题根因

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

1.12.4 解决详情

调整表定义，表批改为行存表。同时建设 btree 索引，索引建设的准则：

1. 基于充沛剖析客户 SQL 的背景上来建设索引。
2. 索引要建设的刚刚好，不要有冗余
3. 建设组合索引时候，要把过滤性比拟好的列往前放
4. 尽可能多的过滤条件都用到索引

1.13 NestLoop+indexscan 的实用场景

1.13.1 问题形容

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

1.13.2 剖析过程

1. 原始的执行打算：

1. 能够看到两个表关联走了 HashJoin，次要的耗时在基表扫描和 HashJoin 操作上。

1.13.3 问题根因

次要的耗时点是在 Hashjoin 和基表扫描上，这种状况下能够考用 NestLoop+indexScan 的打算。

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

1.13.4 解决详情

因为 NestLoop+indexScan 的打算有一些束缚：

1. Join 的时候不能有 stream（不能通过 stream 来传递 join 条件的下推）
2. 大表上要有适合的索引。

批改后的执行打算如下：

文末彩蛋：

邻近华为云 3 月开年洽购季

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

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

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

点击关注，第一工夫理解华为云陈腐技术~