matrixone icon indicating copy to clipboard operation
matrixone copied to clipboard
verified publisher icon matrixorigin

[Bug]: insert into lineitem2 select * from lineitem (tpch 10g)oom

Open tom-csf opened this issue 1 year ago • 6 comments

Is there an existing issue for the same bug?

  • [X] I have checked the existing issues.

Branch Name

main

Commit ID

30a4bf4216f92a022e1be9bb1c0a5681e7c18fc5

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

企业微信截图_95e0450a-5317-4ba7-a73e-6051c169f805 企业微信截图_249a5889-03d1-4ffe-b3f3-1d9a335ec9ca

Expected Behavior

No response

Steps to Reproduce

1、load tpch 10g data to mo
2、create table lineitem2 like lineitem
3、insert into lineitem2 select * from lineitem

Additional information

企业微信截图_aa96fe03-483b-4d3b-bd1f-7879dddea3f2

tom-csf avatar Nov 22 '24 11:11 tom-csf

验证了一下刚解决load oom问题的commit，这个问题应该并非这两天引入的。之前也有。只是之前只关注了load，没有关注insert select。 复现方式

select mo_ctl('cn', 'task', 'disable');
drop database if exists db1;
create database db1;
use db1;

drop table if exists lineitem;
CREATE TABLE lineitem ( L_ORDERKEY    BIGINT NOT NULL,
 L_PARTKEY     INTEGER NOT NULL,
 L_SUPPKEY     INTEGER NOT NULL,
 L_LINENUMBER  INTEGER NOT NULL,
 L_QUANTITY    INTEGER /*DECIMAL(15,2)*/ NOT NULL,
 L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
 L_DISCOUNT    DECIMAL(15,2) NOT NULL,
 L_TAX         DECIMAL(15,2) NOT NULL,
 L_RETURNFLAG  VARCHAR(1) NOT NULL,
 L_LINESTATUS  VARCHAR(1) NOT NULL,
 L_SHIPDATE    DATE NOT NULL,
 L_COMMITDATE  DATE NOT NULL,
 L_RECEIPTDATE DATE NOT NULL,
 L_SHIPINSTRUCT VARCHAR(25) /*CHAR(25)*/ NOT NULL,
 L_SHIPMODE     VARCHAR(10) /*CHAR(10)*/ NOT NULL,
 L_COMMENT      VARCHAR(44) NOT NULL,
PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
);
delete from lineitem;
load data infile '/Users/ouyuanning/workspace/go/src/test/mo-tpch/data/10/lineitem.tbl' into table lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';  //这里我本地单机环境，top能稳定在5G左右

drop table if exists lineitem2;
CREATE TABLE lineitem2 ( L_ORDERKEY    BIGINT NOT NULL,
 L_PARTKEY     INTEGER NOT NULL,
 L_SUPPKEY     INTEGER NOT NULL,
 L_LINENUMBER  INTEGER NOT NULL,
 L_QUANTITY    INTEGER /*DECIMAL(15,2)*/ NOT NULL,
 L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
 L_DISCOUNT    DECIMAL(15,2) NOT NULL,
 L_TAX         DECIMAL(15,2) NOT NULL,
 L_RETURNFLAG  VARCHAR(1) NOT NULL,
 L_LINESTATUS  VARCHAR(1) NOT NULL,
 L_SHIPDATE    DATE NOT NULL,
 L_COMMITDATE  DATE NOT NULL,
 L_RECEIPTDATE DATE NOT NULL,
 L_SHIPINSTRUCT VARCHAR(25) /*CHAR(25)*/ NOT NULL,
 L_SHIPMODE     VARCHAR(10) /*CHAR(10)*/ NOT NULL,
 L_COMMENT      VARCHAR(44) NOT NULL,
PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
);

insert into lineitem2 select * from lineitem;  //这里我本地单机环境，top能上升到24G

ouyuanning avatar Nov 22 '24 12:11 ouyuanning

从pipeline看，

上面的load的pipeline：

pipeline for ap query! current cn 127.0.0.1:18000 sql:  load data infile '/Users/ouyuanning/workspace/go/src/test/mo-tpch/data/10/lineitem.tbl' into table lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true'
Scope 1 (Magic: Merge, addr:127.0.0.1:18000, mcpu: 1, Receiver: [0])
  Pipeline: merge -> multi update
  PreScopes: {
    Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 10, Receiver: [])
      DataSource: []
      Pipeline: external -> projection -> projection -> pre insert -> projection -> projection -> multi update -> connect to MergeReceiver [0]
  }

insert select 的pipeline

pipeline for ap query! current cn 127.0.0.1:18000 sql:  insert into lineitem2 select * from lineitem
Scope 1 (Magic: Merge, addr:127.0.0.1:18000, mcpu: 1, Receiver: [80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119])
  Pipeline: merge -> multi update
  PreScopes: {
    Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [1])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [80]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [0])
          Pipeline: merge -> shuffle build
          PreScopes: {
            Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 10, Receiver: [])
              DataSource: db1.lineitem[L_ORDERKEY L_PARTKEY L_SUPPKEY L_LINENUMBER L_QUANTITY L_EXTENDEDPRICE L_DISCOUNT L_TAX L_RETURNFLAG L_LINESTATUS L_SHIPDATE L_COMMITDATE L_RECEIPTDATE L_SHIPINSTRUCT L_SHIPMODE L_COMMENT]
              Pipeline: tablescan -> projection -> projection -> pre insert -> projection -> projection -> shuffle -> dispatch shuffle to all of MergeReceiver [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, 50, 52, 54, 56, 58, 60, 62, 64, 66, 68, 70, 72, 74, 76, 78]
          }
        Scope 2 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [])
          DataSource: db1.lineitem2[__mo_cpkey_col]
          Pipeline: tablescan -> projection -> shuffle -> dispatch shuffle to all of MergeReceiver [1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 35, 37, 39, 41, 43, 45, 47, 49, 51, 53, 55, 57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79]
      }
    Scope 2 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [3])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [81]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [2])
          Pipeline: merge -> shuffle build
      }
    Scope 3 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [5])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [82]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [4])
          Pipeline: merge -> shuffle build
      }
    Scope 4 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [7])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [83]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [6])
          Pipeline: merge -> shuffle build
      }
    Scope 5 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [9])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [84]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [8])
          Pipeline: merge -> shuffle build
      }
    Scope 6 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [11])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [85]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [10])
          Pipeline: merge -> shuffle build
      }
    Scope 7 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [13])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [86]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [12])
          Pipeline: merge -> shuffle build
      }
    Scope 8 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [15])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [87]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [14])
          Pipeline: merge -> shuffle build
      }
    Scope 9 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [17])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [88]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [16])
          Pipeline: merge -> shuffle build
      }
    Scope 10 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [19])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [89]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [18])
          Pipeline: merge -> shuffle build
      }
    Scope 11 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [21])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [90]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [20])
          Pipeline: merge -> shuffle build
      }
    Scope 12 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [23])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [91]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [22])
          Pipeline: merge -> shuffle build
      }
    Scope 13 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [25])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [92]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [24])
          Pipeline: merge -> shuffle build
      }
    Scope 14 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [27])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [93]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [26])
          Pipeline: merge -> shuffle build
      }
    Scope 15 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [29])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [94]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [28])
          Pipeline: merge -> shuffle build
      }
    Scope 16 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [31])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [95]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [30])
          Pipeline: merge -> shuffle build
      }
    Scope 17 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [33])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [96]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [32])
          Pipeline: merge -> shuffle build
      }
    Scope 18 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [35])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [97]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [34])
          Pipeline: merge -> shuffle build
      }
    Scope 19 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [37])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [98]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [36])
          Pipeline: merge -> shuffle build
      }
    Scope 20 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [39])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [99]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [38])
          Pipeline: merge -> shuffle build
      }
    Scope 21 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [41])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [100]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [40])
          Pipeline: merge -> shuffle build
      }
    Scope 22 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [43])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [101]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [42])
          Pipeline: merge -> shuffle build
      }
    Scope 23 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [45])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [102]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [44])
          Pipeline: merge -> shuffle build
      }
    Scope 24 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [47])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [103]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [46])
          Pipeline: merge -> shuffle build
      }
    Scope 25 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [49])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [104]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [48])
          Pipeline: merge -> shuffle build
      }
    Scope 26 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [51])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [105]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [50])
          Pipeline: merge -> shuffle build
      }
    Scope 27 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [53])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [106]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [52])
          Pipeline: merge -> shuffle build
      }
    Scope 28 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [55])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [107]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [54])
          Pipeline: merge -> shuffle build
      }
    Scope 29 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [57])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [108]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [56])
          Pipeline: merge -> shuffle build
      }
    Scope 30 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [59])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [109]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [58])
          Pipeline: merge -> shuffle build
      }
    Scope 31 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [61])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [110]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [60])
          Pipeline: merge -> shuffle build
      }
    Scope 32 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [63])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [111]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [62])
          Pipeline: merge -> shuffle build
      }
    Scope 33 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [65])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [112]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [64])
          Pipeline: merge -> shuffle build
      }
    Scope 34 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [67])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [113]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [66])
          Pipeline: merge -> shuffle build
      }
    Scope 35 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [69])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [114]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [68])
          Pipeline: merge -> shuffle build
      }
    Scope 36 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [71])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [115]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [70])
          Pipeline: merge -> shuffle build
      }
    Scope 37 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [73])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [116]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [72])
          Pipeline: merge -> shuffle build
      }
    Scope 38 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [75])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [117]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [74])
          Pipeline: merge -> shuffle build
      }
    Scope 39 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [77])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [118]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [76])
          Pipeline: merge -> shuffle build
      }
    Scope 40 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [79])
      Pipeline: merge -> dedup join -> multi update -> connect to MergeReceiver [119]
      PreScopes: {
        Scope 1 (Magic: Remote, addr:127.0.0.1:18000, mcpu: 1, Receiver: [78])
          Pipeline: merge -> shuffle build
      }
  }

ouyuanning avatar Nov 22 '24 12:11 ouyuanning

从胜锋在OOM时抓的malloc看，主要是shuffle的占用较大。

堆内： CN_61373461-3435-6162-3834-386564623961_heap_019353a7-222f-744d-ab40-93851197af0b.zip image

堆外： CN_61373461-3435-6162-3834-386564623961_malloc_019353a6-b19c-7ace-a70e-06d48b5f4935.zip image

ouyuanning avatar Nov 22 '24 12:11 ouyuanning

可能可以确认下shuffle的内存占用情况。 如果shuffle内存占用没什么问题，那么可能对于multi_update用到的shuffle，感觉不要 * 4 来扩大。不然太危险了

ouyuanning avatar Nov 22 '24 12:11 ouyuanning

需要修改执行计划 @aunjgr

badboynt1 avatar Nov 25 '24 02:11 badboynt1

开会结论是：2.0.1无法解决，需要等 @badboynt1 的join优化完成之后

aunjgr avatar Nov 28 '24 03:11 aunjgr