GBase 8a
运维管理
文章

GBase 8a MPP Cluster一个随机分布表导致left join语句报错问题的分析处理

地平线
发表于2024-02-05 17:25:1932次浏览1个评论

概述

本文简述GBase 8a MPP集群中一个典型的随机分布表导致简单left join语句报错问题的分析及处理过程。

异常SQL用例

select 
	MD5(t.ZJHM||t.LXDH) ID,
	t.ZJHM,
	t4.CYZJDM_CN,
	t.XM,
	t.LXDH,
	t.HMGSD,
	t1.DJSJ DJSJ,
	t5.CS,
	t2.XXLYMS,
	t3.GXSJ,
	ROUND(((case when t1.DJSJ>=ADD_MONTHS(NOW(),-3) then 5
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-6) and t1.DJSJ<ADD_MONTHS(NOW(),-3) then 4
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-9) and t1.DJSJ<ADD_MONTHS(NOW(),-6) then 3
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-12) and t1.DJSJ<ADD_MONTHS(NOW(),-9) then 2
		  else 1 end)+(t.CS*1))/13*100,2)||'%' ZXD,
	t5.YLZD01,
	t6.YLZD02,
	null,
	null,
	null,
	'2020-09-27 00:00:00' LW_RKSJ,
	'2020-09-27 00:00:00' LW_GXSJ,
	date_format('2020-09-27 00:00:00','%Y-%m-%dT%H:%i:%s.%000Z') DW_INPUT_TIME
from testdb.PER_LXDH_CS_TEMP t
left join testdb.PER_LXDH_DJSJ_TEMP t1 on t.ZJHM=t1.ZJHM and t.LXDH=t1.LXDH
left join testdb.PER_LXDH_LYS_TEMP t2 on t.ZJHM=t2.ZJHM and t.LXDH=t2.LXDH
left join testdb.PER_LXDH_GXSJ_TEMP t3 on t.ZJHM=t3.ZJHM and t.LXDH=t3.LXDH
left join testdb.PER_LXDH_ZJLX_TEMP t4 on t.ZJHM=t4.ZJHM and t.LXDH=t4.LXDH
left join testdb.PER_LXDH_YLZD01_TEMP02 t5 on t.ZJHM=t5.ZJHM and t.LXDH=t5.LXDH
left join testdb.PER_LXDH_YLZD02_TEMP02 t6 on t.ZJHM=t6.ZJHM and t.LXDH=t6.LXDH
;

SQL分析

该SQL为典型的主表left join关联整合多个副表获取宽表数据的SQL。

在该用例中，主表记录数为0，副表记录数量级为亿级，理论上结果集为0，执行速度应该很快，实际情况是SQL长时间夯住，最后报错退出。

从报错信息来看，是per_lxdh_ylzd01_temp02表插入临时表’gctmpdb’, TABLE '_tmp_889468204_593488_t1428746_5_1601085645_s’出现了异常（Failed to append: status is error, last error: (gns_host: 192.168.1.146) Cannot assign requested address）

用show create table 检查源表结构，发现所有表均为随机分布表，初步怀疑方向为–>随机分布表导致的不合理执行计划。

CREATE TABLE test.per_lxdh_cs_temp (
  ZJHM varchar(18) DEFAULT NULL,
  XM varchar(50) DEFAULT NULL,
  LXDH varchar(20) DEFAULT NULL,
  HMGSD varchar(50) DEFAULT NULL,
  CS decimal(10,0) DEFAULT NULL
) ;

CREATE TABLE test.per_lxdh_djsj_temp (
  ZJHM varchar(18) DEFAULT NULL,
  LXDH varchar(20) DEFAULT NULL,
  DJSJ datetime DEFAULT NULL
) ;;

CREATE TABLE test.per_lxdh_lys_temp (
  ZJHM varchar(18) DEFAULT NULL,
  LXDH varchar(20) DEFAULT NULL,
  XXLYMS varchar(1000) DEFAULT NULL
) ;


CREATE TABLE test.per_lxdh_gxsj_temp (
  ZJHM varchar(18) DEFAULT NULL,
  LXDH varchar(20) DEFAULT NULL,
  GXSJ datetime DEFAULT NULL
) ;

CREATE TABLE test.per_lxdh_zjlx_temp (
  ZJHM varchar(18) DEFAULT NULL,
  LXDH varchar(20) DEFAULT NULL,
  CYZJDM_CN varchar(200) DEFAULT NULL
) ;

CREATE TABLE test.per_lxdh_ylzd01_temp02 (
  ZJHM varchar(72) DEFAULT NULL COMMENT '',
  LXDH varchar(300) DEFAULT NULL COMMENT '',
  cs decimal(42,0) DEFAULT NULL,
  YLZD01 longtext
) ;

CREATE TABLE test.per_lxdh_ylzd02_temp02 (
  ZJHM varchar(72) DEFAULT NULL COMMENT '',
  LXDH varchar(300) DEFAULT NULL COMMENT '',
  YLZD02 longtext
) ;

用explain查看执行计划，显示该SQL执行计划一共7步，5张副表在关联前都被broadcast为复制表，基本可以确定由于上述操作导致系统资源消耗太大，在表per_lxdh_ylzd01_temp02转为复制表时报错。

explain
select 
	MD5(t.ZJHM||t.LXDH) ID,
	t.ZJHM,
	t4.CYZJDM_CN,
	t.XM,
	t.LXDH,
	t.HMGSD,
	t1.DJSJ DJSJ,
	t5.CS,
	t2.XXLYMS,
	t3.GXSJ,
	ROUND(((case when t1.DJSJ>=ADD_MONTHS(NOW(),-3) then 5
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-6) and t1.DJSJ<ADD_MONTHS(NOW(),-3) then 4
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-9) and t1.DJSJ<ADD_MONTHS(NOW(),-6) then 3
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-12) and t1.DJSJ<ADD_MONTHS(NOW(),-9) then 2
		  else 1 end)+(t.CS*1))/13*100,2)||'%' ZXD,
	t5.YLZD01,
	t6.YLZD02,
	null,
	null,
	null,
	'2020-09-27 00:00:00' LW_RKSJ,
	'2020-09-27 00:00:00' LW_GXSJ,
	date_format('2020-09-27 00:00:00','%Y-%m-%dT%H:%i:%s.%000Z') DW_INPUT_TIME
from testdb.PER_LXDH_CS_TEMP t
left join testdb.PER_LXDH_DJSJ_TEMP t1 on t.ZJHM=t1.ZJHM and t.LXDH=t1.LXDH
left join testdb.PER_LXDH_LYS_TEMP t2 on t.ZJHM=t2.ZJHM and t.LXDH=t2.LXDH
left join testdb.PER_LXDH_GXSJ_TEMP t3 on t.ZJHM=t3.ZJHM and t.LXDH=t3.LXDH
left join testdb.PER_LXDH_ZJLX_TEMP t4 on t.ZJHM=t4.ZJHM and t.LXDH=t4.LXDH
left join testdb.PER_LXDH_YLZD01_TEMP02 t5 on t.ZJHM=t5.ZJHM and t.LXDH=t5.LXDH
left join testdb.PER_LXDH_YLZD02_TEMP02 t6 on t.ZJHM=t6.ZJHM and t.LXDH=t6.LXDH
;

+----+----------------+----------------+---------+---------------------------------+
| ID | MOTION         | OPERATION      | TABLE   | CONDITION                       |
+----+----------------+----------------+---------+---------------------------------+
| 07 | [RESULT]       |  LEFT JOIN     |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |   Step         | <05>    |                                 |
|    |                |   Step         | <06>    |                                 |
| 06 | [REDIST(lxdh)] |  Table         | t6[DIS] |                                 |
| 05 | [REDIST(lxdh)] |  LEFT JOIN     |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |   LEFT JOIN    |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |    LEFT JOIN   |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |     LEFT JOIN  |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |      LEFT JOIN |         | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |                |       Table    | t[DIS]  |                                 |
|    |                |       Step     | <00>    |                                 |
|    |                |      Step      | <01>    |                                 |
|    |                |     Step       | <02>    |                                 |
|    |                |    Step        | <03>    |                                 |
|    |                |   Step         | <04>    |                                 |
| 04 | [BROADCAST]    |  Table         | t5[DIS] |                                 |
| 03 | [BROADCAST]    |  Table         | t4[DIS] |                                 |
| 02 | [BROADCAST]    |  Table         | t3[DIS] |                                 |
| 01 | [BROADCAST]    |  Table         | t2[DIS] |                                 |
| 00 | [BROADCAST]    |  Table         | t1[DIS] |                                 |
+----+----------------+----------------+---------+---------------------------------+

SQL优化

将所有源表重建为zjhm分布的hash分布表，再次查看执行计划，显示执行计划精简为1步，同时没有BROADCAST和REDIST的动作。

explain
select 
	MD5(t.ZJHM||t.LXDH) ID,
	t.ZJHM,
	t4.CYZJDM_CN,
	t.XM,
	t.LXDH,
	t.HMGSD,
	t1.DJSJ DJSJ,
	t5.CS,
	t2.XXLYMS,
	t3.GXSJ,
	ROUND(((case when t1.DJSJ>=ADD_MONTHS(NOW(),-3) then 5
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-6) and t1.DJSJ<ADD_MONTHS(NOW(),-3) then 4
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-9) and t1.DJSJ<ADD_MONTHS(NOW(),-6) then 3
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-12) and t1.DJSJ<ADD_MONTHS(NOW(),-9) then 2
		  else 1 end)+(t.CS*1))/13*100,2)||'%' ZXD,
	t5.YLZD01,
	t6.YLZD02,
	null,
	null,
	null,
	'2020-09-27 00:00:00' LW_RKSJ,
	'2020-09-27 00:00:00' LW_GXSJ,
	date_format('2020-09-27 00:00:00','%Y-%m-%dT%H:%i:%s.%000Z') DW_INPUT_TIME
from test.PER_LXDH_CS_TEMP t
left join test.PER_LXDH_DJSJ_TEMP t1 on t.ZJHM=t1.ZJHM and t.LXDH=t1.LXDH
left join test.PER_LXDH_LYS_TEMP t2 on t.ZJHM=t2.ZJHM and t.LXDH=t2.LXDH
left join test.PER_LXDH_GXSJ_TEMP t3 on t.ZJHM=t3.ZJHM and t.LXDH=t3.LXDH
left join test.PER_LXDH_ZJLX_TEMP t4 on t.ZJHM=t4.ZJHM and t.LXDH=t4.LXDH
left join test.PER_LXDH_YLZD01_TEMP02 t5 on t.ZJHM=t5.ZJHM and t.LXDH=t5.LXDH
left join test.PER_LXDH_YLZD02_TEMP02 t6 on t.ZJHM=t6.ZJHM and t.LXDH=t6.LXDH
;

+----+----------+-----------------+----------+---------------------------------+
| ID | MOTION   | OPERATION       | TABLE    | CONDITION                       |
+----+----------+-----------------+----------+---------------------------------+
| 00 | [RESULT] |  LEFT JOIN      |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |   LEFT JOIN     |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |    LEFT JOIN    |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |     LEFT JOIN   |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |      LEFT JOIN  |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |       LEFT JOIN |          | (zjhm = zjhm) AND (lxdh = lxdh) |
|    |          |        Table    | t[zjhm]  |                                 |
|    |          |        Table    | t1[zjhm] |                                 |
|    |          |       Table     | t2[zjhm] |                                 |
|    |          |      Table      | t3[zjhm] |                                 |
|    |          |     Table       | t4[zjhm] |                                 |
|    |          |    Table        | t5[zjhm] |                                 |
|    |          |   Table         | t6[zjhm] |                                 |
+----+----------+-----------------+----------+---------------------------------+

再次执行SQL成功，耗时1秒。

select 
	MD5(t.ZJHM||t.LXDH) ID,
	t.ZJHM,
	t4.CYZJDM_CN,
	t.XM,
	t.LXDH,
	t.HMGSD,
	t1.DJSJ DJSJ,
	t5.CS,
	t2.XXLYMS,
	t3.GXSJ,
	ROUND(((case when t1.DJSJ>=ADD_MONTHS(NOW(),-3) then 5
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-6) and t1.DJSJ<ADD_MONTHS(NOW(),-3) then 4
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-9) and t1.DJSJ<ADD_MONTHS(NOW(),-6) then 3
		  when t1.DJSJ>=ADD_MONTHS(NOW(),-12) and t1.DJSJ<ADD_MONTHS(NOW(),-9) then 2
		  else 1 end)+(t.CS*1))/13*100,2)||'%' ZXD,
	t5.YLZD01,
	t6.YLZD02,
	null,
	null,
	null,
	'2020-09-27 00:00:00' LW_RKSJ,
	'2020-09-27 00:00:00' LW_GXSJ,
	date_format('2020-09-27 00:00:00','%Y-%m-%dT%H:%i:%s.%000Z') DW_INPUT_TIME
from test.PER_LXDH_CS_TEMP t
left join test.PER_LXDH_DJSJ_TEMP t1 on t.ZJHM=t1.ZJHM and t.LXDH=t1.LXDH
left join test.PER_LXDH_LYS_TEMP t2 on t.ZJHM=t2.ZJHM and t.LXDH=t2.LXDH
left join test.PER_LXDH_GXSJ_TEMP t3 on t.ZJHM=t3.ZJHM and t.LXDH=t3.LXDH
left join test.PER_LXDH_ZJLX_TEMP t4 on t.ZJHM=t4.ZJHM and t.LXDH=t4.LXDH
left join test.PER_LXDH_YLZD01_TEMP02 t5 on t.ZJHM=t5.ZJHM and t.LXDH=t5.LXDH
left join test.PER_LXDH_YLZD02_TEMP02 t6 on t.ZJHM=t6.ZJHM and t.LXDH=t6.LXDH
;
Query OK, 0 rows affected (Elapsed: 00:00:01.89)
Records: 0  Duplicates: 0  Warnings: 0

案例总结

这是一个典型的随机分布表导致left join语句报错问题，具体原因是随机分布表结构导致left join的副表在关联前都被broadcast为复制表，资源消耗太大，部分表在转为复制表过程中内存报错导致SQL执行失败。通过将随机分布表转为关联字段zjhm分布的hash分布表解决。

 

评论

登录后才可以发表评论
GBase用户51820发表于 2个月前
厉害了

热门帖子