GBase 8a
性能调优
文章

一个笛卡尔积导致低效Delete语句的分析及优化

地平线
发表于2024-05-21 16:06:59122次浏览1个评论

概述

本文简述GBase 8a MPP集群中一个笛卡尔积导致低效Delete语句的分析及优化过程。

异常SQL

delete from hv_temp t where mpp_date< (select max(mpp_date) from hv_temp t1 where t.systemid=t1.systemid and t1.bj='1')

异常现象

SQL执行超时（超过8小时都未执行完），同时观察到节点产生大量临时物化文件。

分析过程：

查看临时文件存储：占用约3T空间，整个存储空间为10T，临时文件达到3T，很不正常。

[gbase@node01 cache_gbase]$ du -h /opt/gnode/tmpdata --max-depth=1
3.1T	/opt/gnode/tmpdata/cache_gbase
4.0K	/opt/gnode/tmpdata/krb5cc_gbase
3.1T	/opt/gnode/tmpdata

进一步查看临时文件：有大量临时物化文件，体量异常的大

1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:37 s0002665030_GB_MAT000051595EB60xcee99840.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:39 s0002665030_GB_MAT0000517A3750x4a492e8c0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:39 s0002665030_GB_MAT0000513C8E20x4a49310c0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:39 s0002665030_GB_MAT0000520C07D0x291171c00.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:39 s0002665030_GB_MAT000052086C30x1d6660f80.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:41 s0002665030_GB_MAT0000546EDF720x4f879cc0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:41 s0002665030_GB_MAT0000543A41D40x4f879180.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:43 s0002665030_GB_MAT0000523BDC10x13f3e6080.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:43 s0002665030_GB_MAT000052595EB60xcee99840.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:45 s0002665030_GB_MAT0000527A3750x4a492e8c0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:45 s0002665030_GB_MAT0000523C8E20x4a49310c0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:45 s0002665030_GB_MAT0000530C07D0x291171c00.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:45 s0002665030_GB_MAT000053086C30x1d6660f80.express_tmp
512M -rw------- 1 gbase gbase 512M 11月 17 15:47 s0002665030_GB_MAT0000540C07D0x291171c00.express_tmp
512M -rw------- 1 gbase gbase 512M 11月 17 15:47 s0002665030_GB_MAT000054086C30x1d6660f80.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:47 s0002665030_GB_MAT0000556EDF720x4f879cc0.express_tmp
1.8G -rw------- 1 gbase gbase 1.8G 11月 17 15:47 s0002665030_GB_MAT0000553A41D40x4f879180.express_tmp
512M -rw------- 1 gbase gbase 256M 11月 17 15:47 s0002665030_GB_MAT0000566EDF720x4f879cc0.express_tmp
512M -rw------- 1 gbase gbase 256M 11月 17 15:47 s0002665030_GB_MAT0000563A41D40x4f879180.express_tmp
1.1G -rw------- 1 gbase gbase 768M 11月 17 15:47 s0002665030_GB_MAT0000537A3750x4a492e8c0.express_tmp
1.1G -rw------- 1 gbase gbase 768M 11月 17 15:48 s0002665030_GB_MAT0000533C8E20x4a49310c0.express_tmp
 91M -rw------- 1 gbase gbase  88M 11月 17 15:48 s0002665030_GB_HSJ0000004B9D50x1c8f6bf80.express_tmp
1.6G -rw------- 1 gbase gbase 1.3G 11月 17 15:48 s0002665030_GB_MAT0000533BDC10x13f3e6080.express_tmp
1.6G -rw------- 1 gbase gbase 1.3G 11月 17 15:48 s0002665030_GB_MAT000053595EB60xcee99840.express_tmp

根据临时物化文件中的相关ID查询节点gn层任务。例如“s0002665030_GB_MAT000053595EB60xcee99840.express_tmp”，通过2665030查gn层的任务：一条delete语句。

gncli ......
gbase> select * from information_schema.processlist where id = 2665030\G;
*************************** 1. row ***************************
                    ID: 2665030
                TASKID: 26636508635
             SUBTASKID: 0
              THREADID: 20266
                  USER: test
                  HOST: 192.168.4.58:11524
                    DB: testdb
               COMMAND: Query
            START_TIME: 2020-11-17 10:19:03
                  TIME: 19797
                 STATE: deleting from main table
    RESOURCE_POOL_NAME: NULL
      RESOURCE_POOL_ID: NULL
RESOURCE_POOl_PRIORITY: NULL
          WAITING_TIME: NULL
          RUNNING_TIME: NULL
       PARALLEL_DEGREE: NULL
             CPU_USAGE: NULL
             MEM_USAGE: 3622709196
   TEMP_DISKSPACE_SORT: 0
   TEMP_DISKSPACE_JOIN: 0
   TEMP_DISKSPACE_AGGR: 0
                  INFO: /*192.168.4.58_53606_9_2020-11-17_10:19:03*/ DELETE  FROM `t` USING `testdb`.`hv_temp_n19` `t` WHERE (`t`.`mpp_date` < (SELECT /*192.168.4.58_53606_9_2020-11-17_10:19:03*/ /*+ TID('26636508635') */ MAX(`t1`.`mpp_date`) AS `max(mpp_date)` FROM `testdb`.`hv_temp_n19` `t1` WHERE (`t1`.`bj` = '1') AND (`t`.`systemid` = `t1`.`systemid`)))
                 TRACE: show trace is closed!
1 row in set (Elapsed: 00:00:00.02)

根据gn层SQL中的gc层ID 53606，查gc层任务：确认异常SQL为一条delete语句。

[gbase@node01 ~]$ gccli -ugbase -p -h192.168.4.58

gbase> select * from information_schema.processlist where id = 53606 \G;
*************************** 1. row ***************************
                    ID: 53606
                TASKID: 26636508634
             SUBTASKID: 0
              THREADID: 19037
                  USER: test
                  HOST: 10.130.73.45:62711
                    VC: test
                    DB: testdb
               COMMAND: Query
            START_TIME: 2020-11-17 10:19:03
                  TIME: 19998
                 STATE: start deleting
    RESOURCE_POOL_NAME: NULL
      RESOURCE_POOL_ID: NULL
RESOURCE_POOl_PRIORITY: NULL
          WAITING_TIME: NULL
          RUNNING_TIME: NULL
                  LOCK: NULL
                  WAIT: NULL
                  INFO: delete from hv_temp t
where mpp_date< (select max(mpp_date) from hv_temp t1 where t.systemid=t1.systemid and t1.bj='1')
                 TRACE: NULL
1 row in set (Elapsed: 00:00:00.01)

从临时文件的生成时间来看，3T临时数据文件都是上述delete语句产生的。

查看表数据文件：不大。

[gbase@node01 sys_tablespace]$ du --max-depth=1 -h hv_temp*
2.3G	hv_temp_n1
2.2G	hv_temp_n10
2.4G	hv_temp_n11
2.3G	hv_temp_n19

分析表数据：systemid大量重复。

gbase> select count(1),count(distinct systemid) from test.testdb.hv_temp;
+------------+--------------------------+
| count(1)   | count(distinct systemid) |
+------------+--------------------------+
| 1096523328 |                   126282 |
+------------+--------------------------+
1 row in set (Elapsed: 00:00:26.79)

gbase> select count(1),count(distinct systemid) from test.testdb.hv_temp where bj = 1;
+------------+--------------------------+
| count(1)   | count(distinct systemid) |
+------------+--------------------------+
| 1096523328 |                   126282 |
+------------+--------------------------+
1 row in set (Elapsed: 00:00:16.58)

至此，基本可确定该DELETE语句由于笛卡尔积运算产生了大量临时物化文件，导致语句长时间无法执行完毕。

SQL优化：

从数据分析情况看，相同的systemid在不同月份都存在。

优化方法1，可先按天、月去重，再整表去重，整表保留数据限制时间，建议不超过3个月。经测试按月去重速度仍然很慢。

优化方法2，通过rank将去重数据插入新表，经测试速度很快，推荐用此方法。
举例如下：
drop table test.testdb.tmp_del_new;
create table test.testdb.tmp_del_new like test.testdb.hv_temp;
alter table test.testdb.tmp_del_new add sn int;
insert into test.testdb.tmp_del_new
select * from
(
select *, rank() over(partition by systemid order by mpp_date desc) as sn
from test.testdb.hv_temp
) t
where sn = 1;

评论

登录后才可以发表评论
用户头像
levvel发表于 7个月前
观点很新颖！

热门帖子