想从impala迁移到doris，测试环境，impala5台32g集群两个小时能跑完，doris升级了be内存，一直内存MEM_LIMIT_EXCEEDED
sql如下
SELECT ent_code,
credit_code,
ent_name,
area_code,
area_name,
industry_code AS trade_code,
industry_name AS trade_name,
CASE substr(industry_code, 1, 1)
WHEN ‘A’ THEN ‘inds_type_1’
WHEN ‘B’ THEN ‘inds_type_2’
WHEN ‘C’ THEN ‘inds_type_2’
WHEN ‘D’ THEN ‘inds_type_2’
WHEN ‘E’ THEN ‘inds_type_2’
ELSE ‘inds_type_3’
END AS major_inds_code,
CASE substr(industry_code, 1, 1)
WHEN ‘A’ THEN ‘第一产业’
WHEN ‘B’ THEN ‘第二产业’
WHEN ‘C’ THEN ‘第二产业’
WHEN ‘D’ THEN ‘第二产业’
WHEN ‘E’ THEN ‘第二产业’
ELSE ‘第三产业’
END AS major_inds_name,
ent_type_code,
ent_type_name,
legal_rep,
‘存续’ AS ent_status,
regist_date,
CASE
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 0.25 THEN ‘establishment_year_3m’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 0.5 THEN ‘establishment_year_6m’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 1.0 THEN ‘establishment_year_1y’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 3.0 THEN ‘establishment_year_3y’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 5.0 THEN ‘establishment_year_5y’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 10.0 THEN ‘establishment_year_10y’
ELSE ‘establishment_year_10y_above’
END AS establishment_year_code,
CASE
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 0.25 THEN ‘3月内’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 0.5 THEN ‘半年内’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 1.0 THEN ‘1年内’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 3.0 THEN ‘1-3年’
WHEN cast(datediff(current_timestamp(), regist_date) AS DOUBLE) / 365.0 < 5.0 THEN ‘3-5年’
WHEN cast(cast(datediff(current_timestamp(), regist_date) AS DOUBLE) AS DOUBLE) / 365.0 < 10.0 THEN ‘5-10年’
ELSE ‘10年以上’
END AS establishment_year_name,
cancel_date,
valid_date_range,
regist_capital,
regist_curr_code,
regist_curr_name,
CASE
WHEN regist_capital < 500 THEN ‘regist_capital_interval_500’
WHEN regist_capital < 1000 THEN ‘regist_capital_interval_1000’
WHEN regist_capital < 5000 THEN ‘regist_capital_interval_5000’
WHEN regist_capital < 10000 THEN ‘regist_capital_interval_10000’
ELSE ‘regist_capital_interval_10000_above’
END AS regist_capital_interval_code,
CASE
WHEN regist_capital < 500 THEN ‘0-500万’
WHEN regist_capital < 1000 THEN ‘500-1000万’
WHEN regist_capital < 5000 THEN ‘1000-5000万’
WHEN regist_capital < 10000 THEN ‘5000万-1亿’
ELSE ‘1亿以上’
END AS regist_capital_interval_name,
check_date,
org_id,
business_regist_id,
tax_id,
regist_address,
regist_org,
operate_range,
brief,
contact_person,
lon,
lat,
cast(current_timestamp() AS string) AS etl_time
FROM
(SELECT t1.eid AS ent_code,
t1.credit_no AS credit_code,
t1.ent_name AS ent_name,
t3.district_code AS area_code,
t4.area_name AS area_name,
t7.industry_code AS industry_code,
t8.industry_name AS industry_name,
t1.ent_type AS ent_type_code,
t9.type_name AS ent_type_name,
t1.legal_person AS legal_rep,
t1.status AS ent_status,
t1.establish_date AS regist_date,
t1.cancel_date AS cancel_date,
concat(t1.term_start, ‘-’, t1.term_end) AS valid_date_range,
CASE
WHEN t1.reg_capi LIKE ‘%万%美%’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 6.8452
WHEN t1.reg_capi LIKE ‘%美元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 6.8452 / 10000
WHEN t1.reg_capi LIKE ‘%万%港%’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.8716
WHEN t1.reg_capi LIKE ‘%港币’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.8716 / 10000
WHEN t1.reg_capi LIKE ‘%万欧元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 7.5710
WHEN t1.reg_capi LIKE ‘%万日元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.0516
WHEN t1.reg_capi LIKE ‘%万阿富汗尼’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.0793
WHEN t1.reg_capi LIKE ‘%万瑞士法郎’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 7.7076
WHEN t1.reg_capi LIKE ‘%万新加坡元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 5.1608
WHEN t1.reg_capi LIKE ‘%万澳大利亚元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 4.6354
WHEN t1.reg_capi LIKE ‘%万加拿大元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 5.1370
WHEN t1.reg_capi LIKE ‘%万英镑’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 8.5785
WHEN t1.reg_capi LIKE ‘%万开曼群岛元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 8.2440
WHEN t1.reg_capi LIKE ‘%万澳门元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.8459
WHEN t1.reg_capi LIKE ‘%万韩元’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) * 0.0053
WHEN t1.reg_capi LIKE ‘%万元人民币’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE)
WHEN t1.reg_capi LIKE ‘%元人民币’ THEN cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE) / 10000
ELSE cast(REGEXP_REPLACE(t1.reg_capi, ‘[^0-9.]+’, ‘’) AS DOUBLE)
END AS regist_capital,
NULL AS regist_curr_code,
CASE
WHEN t1.reg_capi LIKE ‘%美元%’ THEN ‘美元’
WHEN t1.reg_capi LIKE ‘%港币%’ THEN ‘港币’
WHEN t1.reg_capi LIKE ‘%欧元%’ THEN ‘欧元’
WHEN t1.reg_capi LIKE ‘%日元%’ THEN ‘日元’
WHEN t1.reg_capi LIKE ‘%阿富汗尼%’ THEN ‘阿富汗尼’
WHEN t1.reg_capi LIKE ‘%瑞士法郎%’ THEN ‘瑞士法郎’
WHEN t1.reg_capi LIKE ‘%新加坡元%’ THEN ‘新加坡元’
WHEN t1.reg_capi LIKE ‘%澳大利亚元%’ THEN ‘澳大利亚元’
WHEN t1.reg_capi LIKE ‘%加拿大元%’ THEN ‘加拿大元’
WHEN t1.reg_capi LIKE ‘%开曼群岛元%’ THEN ‘开曼群岛元’
WHEN t1.reg_capi LIKE ‘%澳门元%’ THEN ‘澳门元’
WHEN t1.reg_capi LIKE ‘%韩元%’ THEN ‘韩元’
WHEN t1.reg_capi LIKE ‘%英镑%’ THEN ‘英镑’
WHEN t1.reg_capi LIKE ‘%法郎%’ THEN ‘法郎’
WHEN t1.reg_capi LIKE ‘%台币%’ THEN ‘台币’
WHEN t1.reg_capi LIKE ‘%加元%’ THEN ‘加元’
ELSE ‘人民币’
END AS regist_curr_name,
t1.check_date AS check_date,
t1.org_no AS org_id,
t1.reg_no AS business_regist_id,
t1.tax_no AS tax_id,
t3.address AS regist_address,
t1.reg_org AS regist_org,
t1.scopes AS operate_range,
t2.brief AS brief,
t1.legal_person AS contact_person,
t3.longitude_2 AS lon,
t3.latitude_2 AS lat
FROM
(SELECT row_number() over (partition BY credit_no
ORDER BY update_time) AS ind,
scope AS scopes,
eid,
credit_no,
ent_name,
ent_type,
legal_person,
status,
establish_date,
cancel_date,
term_start,
term_end,
reg_capi,
check_date,
org_no,
reg_no,
tax_no,
address,
reg_org
FROM ods.ods_t_ent_base
WHERE length(credit_no) > 0
and length(ent_name) > 0
AND establish_date > ‘1847-01-01’
AND status = ‘1’) t1
LEFT JOIN [shuffle] ods.ods_t_ent_brief t2 ON t1.eid = t2.eid
LEFT JOIN [shuffle] ods.ods_t_ent_district t3 ON t1.eid = t3.eid
LEFT JOIN [shuffle] ods.ods_t_dim_district t4 ON t3.district_code = t4.area_code
LEFT JOIN [shuffle] ods.ods_t_ent_industry t7 ON t1.eid = t7.eid
LEFT JOIN [shuffle]
(SELECT row_number() over (partition BY industry_code
ORDER BY industry_level DESC) AS ind,
industry_code,
industry_name
FROM ods.ods_t_dim_industry) t8 ON t7.industry_code = t8.industry_code
AND t8.ind = 1
LEFT JOIN [broadcast] ods.ods_t_dim_ent_type t9 ON t1.ent_type = t9.type_code
WHERE t1.ind = 1) t10;
其中ods_t_ent_base，ods_t_ent_brief，ods_t_ent_district，ods_t_ent_industry都是1亿多一点，剩下的dim表最多几千条
memory summary如下
Process Memory Summary:
OS physical memory 58.80 GB. Process memory usage 48.53 GB, limit 47.04 GB, soft limit 42.34 GB. Sys available memory 3.07 GB, low water mark 1.60 GB, warning water mark 3.20 GB. Refresh interval memory growth 0 B
Memory Tracker Summary:
Type=experimental, Used=0(0 B), Peak=0(0 B)
Type=clone, Used=0(0 B), Peak=0(0 B)
Type=schema_change, Used=0(0 B), Peak=0(0 B)
Type=compaction, Used=0(0 B), Peak=1.69 GB(1816097347 B)
Type=load, Used=59.28 GB(63654914688 B), Peak=60.93 GB(65427601176 B)
Type=query, Used=11.34 KB(11616 B), Peak=11.34 KB(11616 B)
Type=global, Used=1.80 GB(1928984026 B), Peak=1.83 GB(1969055714 B)
Type=tc/jemalloc_free_memory, Used=2.55 GB(2732961312 B), Peak=-1.00 B(-1 B)
Type=process, Used=63.63 GB(68316871642 B), Peak=-1.00 B(-1 B)
MemTrackerLimiter Label=ObjLRUCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=Orphan, Type=global, Limit=-1.00 B(-1 B), Used=1.66 GB(1783670154 B), Peak=1.71 GB(1836406130 B)
MemTracker Label=PageNoCache, Parent Label=Orphan, Used=0(0 B), Peak=335.24 MB(351524908 B)
MemTracker Label=IOBufBlockMemory, Parent Label=Orphan, Used=49.80 MB(52215808 B), Peak=112.46 MB(117923840 B)
MemTracker Label=InvertedIndexSearcherCache, Parent Label=Orphan, Used=14.81 KB(15168 B), Peak=14.81 KB(15168 B)
MemTracker Label=SegmentMeta, Parent Label=Orphan, Used=27.91 MB(29262799 B), Peak=27.91 MB(29262799 B)
MemTrackerLimiter Label=DataPageCache, Type=global, Limit=-1.00 B(-1 B), Used=53.23 MB(55811120 B), Peak=64.72 MB(67864703 B)
MemTrackerLimiter Label=IndexPageCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=PKIndexPageCache, Type=global, Limit=-1.00 B(-1 B), Used=61.32 MB(64297463 B), Peak=250.54 MB(262707718 B)
MemTrackerLimiter Label=RowCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=SegmentCache, Type=global, Limit=-1.00 B(-1 B), Used=23.04 MB(24156650 B), Peak=23.04 MB(24156650 B)
MemTrackerLimiter Label=SchemaCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=LookupConnectionCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=InvertedIndexSearcherCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=InvertedIndexQueryCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=LastestSuccessChannelCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=TabletVersionCache, Type=global, Limit=-1.00 B(-1 B), Used=1.00 MB(1048639 B), Peak=1.00 MB(1048639 B)
MemTrackerLimiter Label=DeleteBitmap AggCache, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=LoadChannelMgr, Type=load, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=102.94 MB(107942476 B)
profile太长先不放了，需要的话放下面
看memtracker是load内存最大，官网教程翻了半天没找到优化的方法。。。。