数仓调优实践丨多次关联发散导致数据爆炸案例分析改写-低调大师

数仓调优实践丨多次关联发散导致数据爆炸案例分析改写

2023-12-12 289

本文分享自华为云社区《GaussDB(DWS)性能调优：求字段全体值中大于本行值的最小值——多次关联发散导致数据爆炸案例分析改写》，作者： Zawami 。

1、【问题描述】

语句中存在同一个表多次自关联，且均为发散关联，数据爆炸导致性能瓶颈。

2、【原始SQL】

explain verbose
WITH TMP AS
(
    SELECT WH_ID
         , (IFNULL(SUBSTR(THE_DATE,1,10),'1900-01-01') || ' ' || STOP_TIME)::TIMESTAMP AS STOP_TIME
         , (IFNULL(SUBSTR(THE_DATE,1,10),'1900-01-01') || ' ' || '23:59:59')::TIMESTAMP AS MAX_ASD
      FROM DMISC.DM_DIM_CBG_WH_HOLIDAY_D
    WHERE IS_OPEN = 'Y'
      AND STOP_TIME IS NOT NULL
)
SELECT T1.WH_ID
     , T1.THE_DATE
     , T1.IS_OPEN
     , MIN(T2.STOP_TIME) AS STOP_TIME
     , MIN(T2.MAX_ASD) AS TODAY_MAX_ASD
     , MIN(T3.MAX_ASD) AS NEXT_MAX_ASD
FROM (SELECT WH_ID
           , THE_DATE
           , IS_OPEN
           , (IFNULL(SUBSTR(THE_DATE,1,10),'1900-01-01') || ' ' || STOP_TIME)::TIMESTAMP AS STOP_TIME
        FROM DMISC.DM_DIM_CBG_WH_HOLIDAY_D
     ) T1
LEFT JOIN TMP T2
ON T1.WH_ID = T2.WH_ID
AND T1.THE_DATE < T2.STOP_TIME

LEFT JOIN TMP T3
ON T1.WH_ID = T3.WH_ID
AND ADDDATE(T1.THE_DATE,1) < T3.STOP_TIME

GROUP BY T1.WH_ID, T1.THE_DATE, T1.IS_OPEN;

从SQL中不难看出，物理表HOLIDAY_D使用WH_ID为关联键，并使用其它字段做不等值关联。

3、【性能分析】

QUERY PLAN                                                                                                                                                                                                                                                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
 id |                                    operation                                     |    E-rows     | E-distinct |   E-memory    | E-width |     E-costs                                                                                                    |
----+----------------------------------------------------------------------------------+---------------+------------+---------------+---------+-----------------                                                                                               |
  1 | ->  Row Adapter                                                                  |         51584 |            |               |      67 | 377559930171.36                                                                                                |
  2 |    ->  Vector Streaming (type: GATHER)                                           |         51584 |            |               |      67 | 377559930171.36                                                                                                |
  3 |       ->  Vector Hash Aggregate                                                  |         51584 |            | 16MB          |      67 | 377559929546.36                                                                                                |
  4 |          ->  Vector CTE Append(5, 7)                                             | 5699739636332 |            | 1MB           |      43 | 292063834485.54                                                                                                |
  5 |             ->  Vector Streaming(type: BROADCAST)                                |        757752 |            | 2MB           |      22 | 1474.87                                                                                                        |
  6 |                ->  CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d  [5, CTE tmp(1)] |        757752 |            | 1MB           |      22 | 1474.87                                                                                                        |
  7 |             ->  Vector Hash Left Join (8, 11)                                    | 5699739636332 |            | 107MB(6863MB) |      43 | 292063833010.67                                                                                                |
  8 |                ->  Vector Hash Right Join (9, 10)                                |     542231841 | 50         | 16MB          |      27 | 22365789.31                                                                                                    |
  9 |                   ->  Vector CTE Scan on tmp(1) t3                               |         31573 | 50         | 1MB           |      48 | 15155.04                                                                                                       |
 10 |                   ->  CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d               |         51584 | 50         | 1MB           |      19 | 556.58                                                                                                         |
 11 |                ->  Vector CTE Scan on tmp(1) t2                                  |         31573 | 50         | 1MB           |      48 | 15155.04                                                                                                       |

由于SQL非常慢，难以打出performance计划，我们先看verbose计划。从计划中我们看到，经过两次的关联发散，估计数据量达到了5万亿行；因为hash join根据WH_ID列进行关联，实际不会有这么多。所以调优的思路就是取消一些发散，让中间结果集行数变少。

4、【改写SQL】

分析SQL，可知发散是为了寻找所有STOP_TIME中大于本行THE_DATE的最小值。像这种每行都需要用到本行数据和所有数据的逻辑，或许可以使用窗口函数进行编写；但囿于笔者能力，先提供单次自关联的方法。

SQL改写如下：

explain performance
	WITH TMP AS
    (
        SELECT WH_ID
             , (IFNULL(SUBSTR(THE_DATE,1,10),'1900-01-01') || ' ' || STOP_TIME)::TIMESTAMP AS STOP_TIME
             , (IFNULL(SUBSTR(THE_DATE,1,10),'1900-01-01') || ' ' || '23:59:59')::TIMESTAMP AS MAX_ASD
          FROM DMISC.DM_DIM_CBG_WH_HOLIDAY_D
        WHERE IS_OPEN = 'Y'
    	  AND STOP_TIME IS NOT NULL
    )
    SELECT T1.WH_ID
         , T1.THE_DATE
		 , T1.IS_OPEN
         , MIN(CASE WHEN T1.THE_DATE < T2.STOP_TIME THEN STOP_TIME ELSE NULL END) AS STOP_TIME
         , MIN(CASE WHEN T1.THE_DATE < T2.STOP_TIME THEN T2.MAX_ASD ELSE NULL END) AS TODAY_MAX_ASD
         , MIN(CASE WHEN ADDDATE(T1.THE_DATE, 1) < T2.STOP_TIME THEN T2.MAX_ASD ELSE NULL END) AS NEXT_MAX_ASD
    FROM (SELECT DISTINCT WH_ID
               , THE_DATE
               , IS_OPEN
            FROM DMISC.DM_DIM_CBG_WH_HOLIDAY_D
         ) T1
    LEFT JOIN TMP T2
    ON T1.WH_ID = T2.WH_ID
	GROUP BY
		T1.WH_ID
         , T1.THE_DATE
		 , T1.IS_OPEN
    ;

经过改写，取消了一次自关联，SQL的中间结果集变小。在关联后，通过条件聚合来得到需要的值。

id |                            operation                            |        A-time        |  A-rows  | E-rows | E-distinct |  Peak Memory   | E-memory |  A-width  | E-width | E-costs  
----+-----------------------------------------------------------------+----------------------+----------+--------+------------+----------------+----------+-----------+---------+----------
  1 | ->  Row Adapter                                                 | 7490.354             |    34035 |    200 |            | 70KB           |          |           |      58 | 15149.80 
  2 |    ->  Vector Streaming (type: GATHER)                          | 7488.129             |    34035 |    200 |            | 216KB          |          |           |      58 | 15149.80 
  3 |       ->  Vector Hash Aggregate                                 | [7481.430, 7481.430] |    34035 |    200 |            | [9MB, 9MB]     | 16MB     | [112,112] |      58 | 15137.30 
  4 |          ->  Vector Hash Left Join (5, 7)                       | [909.377, 909.377]   | 31204164 | 109803 |            | [2MB, 2MB]     | 16MB     |           |      34 | 3880.50  
  5 |             ->  Vector Sonic Hash Aggregate                     | [5.876, 5.876]       |    34035 |  34036 | 6807       | [3MB, 3MB]     | 16MB     | [51,51]   |      18 | 1127.67  
  6 |                ->  CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d | [0.199, 0.199]       |    34036 |  34036 |            | [792KB, 792KB] | 1MB      |           |      18 | 532.04   
  7 |             ->  CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d    | [40.794, 40.794]     |    25122 |  21960 | 19         | [1MB, 1MB]     | 1MB      | [59,59]   |      24 | 617.13

从执行计划中可以看到，中间结果集大小已经在可接受的范围内。但是又看到聚合3千万数据使用了6s+的时间，这是过慢的，需要看执行计划中的DN信息寻找原因。

Datanode Information (identified by plan id)                                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 --Row Adapter
        (actual time=7486.498..7490.354 rows=34035 loops=1)
        (CPU: ex c/r=107, ex row=34035, ex cyc=3668104, inc cyc=22468059912)
  2 --Vector Streaming (type: GATHER)
        (actual time=7486.466..7488.129 rows=34035 loops=1)
        (Buffers: shared hit=1)
        (CPU: ex c/r=660037, ex row=34035, ex cyc=22464391808, inc cyc=22464391808)
  3 --Vector Hash Aggregate
        dn_6083_6084 (actual time=7479.644..7481.430 rows=34035 loops=1) (projection time=4488.807)
        dn_6083_6084 (Buffers: shared hit=40)
        dn_6083_6084 (CPU: ex c/r=631, ex row=31204164, ex cyc=19718763112, inc cyc=22443886288)
  4 --Vector Hash Left Join (5, 7)
        dn_6083_6084 (actual time=48.009..909.377 rows=31204164 loops=1)
        dn_6083_6084 (Buffers: shared hit=36)
        dn_6083_6084 (CPU: ex c/r=43699, ex row=59157, ex cyc=2585141400, inc cyc=2725123176)
  5 --Vector Sonic Hash Aggregate
        dn_6083_6084 (actual time=5.177..5.876 rows=34035 loops=1)
        dn_6083_6084 (Buffers: shared hit=11)
        dn_6083_6084 (CPU: ex c/r=500, ex row=34036, ex cyc=17027544, inc cyc=17619064)
  6 --CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d
        dn_6083_6084 (actual time=0.043..0.199 rows=34036 loops=1) (CU ScanInfo: smallCu: 0, totalCu: 1, avrCuRow: 34036, totalDeadRows: 0)
        dn_6083_6084 (Buffers: shared hit=11)
        dn_6083_6084 (CPU: ex c/r=17, ex row=34036, ex cyc=591520, inc cyc=591520)
  7 --CStore Scan on dmisc.dm_dim_cbg_wh_holiday_d
        dn_6083_6084 (actual time=6.464..40.794 rows=25122 loops=1) (filter time=0.872 projection time=33.671) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 1) (CU ScanInfo: smallCu: 0, totalCu: 1, avrCuRow: 34036, totalDeadRows: 0)
        dn_6083_6084 (Buffers: shared hit=25)
        dn_6083_6084 (CPU: ex c/r=3595, ex row=34036, ex cyc=122362712, inc cyc=122362712)

从中可以看出，所有算子都只在一个DN上运行了。这可以视为严重的计算倾斜，若对单点性能有更高要求需要继续优化。查看DMISC.DM_DIM_CBG_WH_HOLIDAY_D表的定义，发现它是一个复制表(distribute by replication)，在进行各层运算的时候只用其中一个DN来算。而在本SQL中，使用到这张表的时候，关联键都是WH_ID。

再查看调整分布列为WH_ID的倾斜情况：

select * from pg_catalog.table_skewness('DMISC.DM_DIM_CBG_WH_HOLIDAY_D', 'wh_id');

结果有23行，小于集群DN个数，且存在倾斜。但是本SQL需要使用该表的全量数据，故可以把这张表改为使用WH_ID作为分步键进行重分布。

由表分布方式为复制表导致的计算倾斜无法使用skew hint解决，可以改变物理表分布方式或者创建临时表来解决（复制表通常较小）。由于表在SQL中的使用情况和表的倾斜情况，不适合更改物理表分步键为WH_ID，故本例中试使用创建临时表指定重分布方式的办法解决。

DROP TABLE IF EXISTS holiday_d_tmp;
CREATE TEMP TABLE holiday_d_tmp WITH ( orientation = COLUMN, compression = low ) distribute BY hash ( wh_id ) AS ( SELECT * FROM DMISC.DM_DIM_CBG_WH_HOLIDAY_D );
EXPLAIN performance WITH TMP AS (
	SELECT
		WH_ID,
		( IFNULL ( SUBSTR( THE_DATE, 1, 10 ), '1900-01-01' ) || ' ' || STOP_TIME ) :: TIMESTAMP AS STOP_TIME,
		( IFNULL ( SUBSTR( THE_DATE, 1, 10 ), '1900-01-01' ) || ' ' || '23:59:59' ) :: TIMESTAMP AS MAX_ASD 
	FROM
		holiday_d_tmp 
	WHERE
		IS_OPEN = 'Y' 
		AND STOP_TIME IS NOT NULL 
	) SELECT
	T1.WH_ID,
	T1.THE_DATE,
	T1.IS_OPEN,
	MIN ( CASE WHEN T1.THE_DATE < T2.STOP_TIME THEN STOP_TIME ELSE NULL END ) AS STOP_TIME,
	MIN ( CASE WHEN T1.THE_DATE < T2.STOP_TIME THEN T2.MAX_ASD ELSE NULL END ) AS TODAY_MAX_ASD,
	MIN ( CASE WHEN ADDDATE ( T1.THE_DATE, 1 ) < T2.STOP_TIME THEN T2.MAX_ASD ELSE NULL END ) AS NEXT_MAX_ASD 
FROM
	( SELECT WH_ID, THE_DATE, IS_OPEN FROM holiday_d_tmp ) T1
	LEFT JOIN TMP T2 ON T1.WH_ID = T2.WH_ID 
GROUP BY
	T1.WH_ID,
	T1.THE_DATE,
	T1.IS_OPEN;

下面是对应的执行计划：

id |                                      operation                                       |      A-time      |  A-rows  |  E-rows  | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs  
----+--------------------------------------------------------------------------------------+------------------+----------+----------+------------+----------------+----------+---------+---------+----------
  1 | ->  Row Adapter                                                                      | 673.495          |    34035 |    34032 |            | 70KB           |          |         |      58 | 68112.60 
  2 |    ->  Vector Streaming (type: GATHER)                                               | 671.103          |    34035 |    34032 |            | 216KB          |          |         |      58 | 68112.60 
  3 |       ->  Vector Hash Aggregate                                                      | [0.079, 672.724] |    34035 |    34032 |            | [1MB, 1MB]     | 16MB     | [0,114] |      58 | 67794.10 
  4 |          ->  Vector Hash Left Join (5, 6)                                            | [0.047, 76.395]  | 31205167 | 27587201 |            | [324KB, 485KB] | 16MB     |         |      34 | 8876.88  
  5 |             ->  CStore Scan on pg_temp_cn_5003_6_22022_139764371019520.holiday_d_tmp | [0.004, 0.098]   |    34036 |    34036 | 1          | [760KB, 792KB] | 1MB      |         |      18 | 1553.65  
  6 |             ->  CStore Scan on pg_temp_cn_5003_6_22022_139764371019520.holiday_d_tmp | [0.008, 3.253]   |    25122 |    22018 | 1          | [880KB, 1MB]   | 1MB      | [0,61]  |      24 | 1557.76

从计划中我们可以看到，耗时比单个DN运算快了不少，当然这里没有算上创建临时表的时间约0.2s。

5、【调优总结】

在本案例中，因为实际执行SQL时间太长先看了verbose计划而非performance计划，发现中间结果集发散问题后，进行等价逻辑改写，把两个(等值-不等值)关联改为一个等值关联和条件聚合。之后，我们发现SQL因复制表存在计算倾斜问题，考虑SQL消费表数据的方式和表的统计数据，采用了使用临时表重新指定分布方式的方法，解决了计算倾斜问题，SQL从单点25min+优化到单点800ms。

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

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/10320830

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Go语言实现GoF设计模式：适配器模式

本文分享自华为云社区《【Go实现】实践GoF的23种设计模式：适配器模式》，作者：元闰子。简介适配器模式（Adapter）是最常用的结构型模式之一，在现实生活中，适配器模式也是处处可见，比如电源插头转换器，它可以让英式的插头工作在中式的插座上。 GoF 对它的定义如下： Convert the interface of a class into another interface clients expect. Adapter lets classes work together that couldn’t otherwise because of incompatible interfaces. 简单来说，就是适配器模式让原本因为接口不匹配而无法一起工作的两个类/结构体能够一起工作。适配器模式所做的就是将一个接口Adaptee，通过适配器Adapter转换成 Client 所期望的另一个接口Target来使用，实现原理也很简单，就是Adapter通过实现Target接口，并在对应的方法中调用Adaptee的接口实现。 UML 结构场景上下文在简单的分布式应用系统（示例代码工...

2023-12-12

366

本文分享自华为云社区《一文掌握Ascend C孪生调试》，作者：昇腾CANN。 1 What，什么是孪生调试 Ascend C提供孪生调试方法，即CPU域模拟NPU域的行为，相同的算子代码可以在CPU域调试精度，NPU域调试性能。孪生调试的整体方案如下：开发者通过调用Ascend C类库编写Ascend C算子kernel侧源码，kernel侧源码通过通用的GCC编译器进行编译，编译生成通用的CPU域的二进制，可以通过gdb通用调试工具等调试手段进行调试；kernel侧源码通过毕昇编译器进行编译，编译生成NPU域的二进制文件，可以通过msprof工具进行性能数据采集等方式进行调试。针对NPU域的调试来讲，根据依赖和调用动态库的不同，分为NPU域仿真调试和NPU域上板调试。NPU域仿真调试，依赖和调用的是model仿真器指定的库文件，运行model仿真不需要使用真实的NPU环境；上板调试，依赖和调用的是真实NPU环境上的库文件，进行上板调试需要真实的NPU环境。 CPU域调试用于定位逻辑错误、内存错误等功能问题； NPU域调试不仅可以通过数据打印的方式定位功能问题，也可以用于定位性能问...

2023-12-12

373

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。