Used: 438105027512, Limit: 438103947386. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit目前我设置比较这个限制的值大，还是报错超出限制，

💬 StarRocks 用户问答 功能使用相关
#1

ERROR 1064 (HY000): row count of right table in hash join > 4294967295

#2

hash 表太大了，不支持，重新analyze table再试一下 谢谢！

#3

请调大并行度parallel_fragment_exec_instance_num试试

#4

Used: 438105027512, Limit: 438103947386. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit目前我设置比较这个限制的值大，还是报错超出限制，这个有办法解决吗

#5

exec_mem_limit是单个 Instance 的内存限制，可以设置大。如果设置很大后还是报错，麻烦您提供一下SQL，谢谢

#6

WITH frequent_ss_items AS
(SELECT
substr(i_item_desc, 1, 30) itemdesc,
i_item_sk item_sk,
d_date solddate,
count() cnt
FROM store_sales, date_dim, item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN (2000, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY substr(i_item_desc, 1, 30), i_item_sk, d_date
HAVING count() > 4),
max_store_sales AS
(SELECT max(csales) tpcds_cmax
FROM (SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) csales
FROM store_sales, customer, date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY c_customer_sk) x),
best_ss_customer AS
(SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) ssales
FROM store_sales, customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING sum(ss_quantity * ss_sales_price) > (50 / 100.0) *
(SELECT *
FROM max_store_sales))
SELECT sum(sales)
FROM ((SELECT cs_quantity * cs_list_price sales
FROM catalog_sales, date_dim
WHERE d_year = 1999
AND d_moy = 2
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer))
UNION ALL
(SELECT ws_quantity * ws_list_price sales
FROM web_sales, date_dim
WHERE d_year = 1999
AND d_moy = 2
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer))) y
LIMIT 100;
–Query 23B
WITH frequent_ss_items AS
(SELECT
substr(i_item_desc, 1, 30) itemdesc,
i_item_sk item_sk,
d_date solddate,
count() cnt
FROM store_sales, date_dim, item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY substr(i_item_desc, 1, 30), i_item_sk, d_date
HAVING count() > 4),
max_store_sales AS
(SELECT max(csales) tpcds_cmax
FROM (SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) csales
FROM store_sales, customer, date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY c_customer_sk) x),
best_ss_customer AS
(SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) ssales
FROM store_sales
, customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING sum(ss_quantity * ss_sales_price) > (50 / 100.0) *
(SELECT *
FROM max_store_sales))
SELECT
c_last_name,
c_first_name,
sales
FROM ((SELECT
c_last_name,
c_first_name,
sum(cs_quantity * cs_list_price) sales
FROM catalog_sales, customer, date_dim
WHERE d_year = 1999
AND d_moy = 2
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)
AND cs_bill_customer_sk = c_customer_sk
GROUP BY c_last_name, c_first_name)
UNION ALL
(SELECT
c_last_name,
c_first_name,
sum(ws_quantity * ws_list_price) sales
FROM web_sales, customer, date_dim
WHERE d_year = 1999
AND d_moy = 2
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)
AND ws_bill_customer_sk = c_customer_sk
GROUP BY c_last_name, c_first_name)) y
ORDER BY c_last_name, c_first_name, sales
LIMIT 100;

#7

现在我们做的是ds 10t 认证测试，目前资源是10台机器，每台512 cpu 64

#8

参数 pipeline_profile_level 0 1 2 数字越大越详细
麻烦提供一下详细的profile

#9

set global exec_mem_limit = 470G
set exec_mem_limit = 470G 我是这么设置但是还是报错

#10

就是查询一段时间后就出现报错，是吧？

#11

抱歉，我们换十台机器，我们导入一下数据，然后发您，您在帮忙看一下，谢谢

#12

对的，内存消耗很快

#13

/*+ SET_VAR(exec_mem_limit = 515396075520,query_timeout=10000000,batch_size=4096,parallel_fragment_exec_instance_num=32) */
查询语句增加变量，这个是针对64核512G内存的

#14

感觉跟建表也有关系，我调整建表之后，出来了，但是另一个sql 就报这个错了和上面一样的

#15

建议FE和BE要分开，这样性能和稳定性会好很多

#16

嗯嗯好的，感谢，我们按照您的方式测试一下，然后把proflie提供一下

#17

那十台机器 fe和be 您这边建议怎么分布呢

#18

exec_mem_limit 是FE的参数，是FE服务器的内存。

#19

10台服务器
3FE（3个FOLLOWER） 7BE

#20

tpcds 你们这边应该搞过10T的数据量吧

京ICP备2022026421号-1