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 用户问答 功能使用相关
#142

好的，您那个限制cpu和内存的shell脚本先不执行

#143

好的，我先把脚本停掉

#144

select /*+ SET_VAR(exec_mem_limit = 515396075520,query_timeout=10000000,batch_size=4096,parallel_fragment_exec_instance_num=60) */
supp_nation, cust_nation, l_year, sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from supplier, lineitem, orders, customer, nation n1, nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
n1.n_name = ‘FRANCE’ and n2.n_name = ‘GERMANY’
)
and l_shipdate between date ‘1995-01-01’ and date ‘1996-12-31’
) as shipping
group by supp_nation, cust_nation, l_year
order by supp_nation, cust_nation, l_year;
请您尝试一下这个SQL能跑出来吗？

#145

这个在重新导入数据，，能帮我把这个两个sql 修改成select /*+ SET_VAR(exec_mem_limit = 515396075520,query_timeout=10000000,batch_size=4096,parallel_fragment_exec_instance_num=60) */ 这个格式我尝试跑一下

#146

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;

#147

10台服务器 3台fe和10台be 就是有3台是fe和be混部？SR是MPP架构，在数据均匀情况下，以最慢节点为准，不建议混部，这样性能只会更差，而且FE和BE混部是会抢内存和CPU资源。

#148

image
image1052×152 24.5 KB

因为512G内存，可以提高配置，请在所有BE节点的be.conf设置mem_limit=95%，然后重启所有BE生效

#149

如果只有10台服务器，为了提高BE的性能，可以只设置1台FE，9台BE。适用于测试，生产还是3个FE

#150

发现混部署的没有报过超出限制，单独部署be的机器总是报错超出内存范围

#151

好的，像我这个架构部署，是不是只是下掉两台fe就可以了

#152

下掉2个FE，还有一台剩下的FE和BE混部，下掉一个BE，尽量不要混部，排查问题难度会增加很多。

#153

麻烦您看这个最下面be配置，我已经设置mem_limit 百分比了，重启be 好像不生效，是和某个参数冲突吗

#154

嗯嗯好的， ```
ALTER SYSTEM DROP follower “fe_host:edit_log_port”;

ALTER SYSTEM DECOMMISSION backend "be_host:be_heartbeat_service_port";
用这个两个语句吧
#155

看那个是FE MASTER，就保留那个，其他ALTER SYSTEM DROP follower “fe_host:edit_log_port”;
BE就使用ALTER SYSTEM DECOMMISSION backend “be_host:be_heartbeat_service_port”;

#156

看一下BE日志是否报错，使用curl http://be_host:http_port/varz 是否生效

#157

如果不行就试一下 mem_limit=0.95 可能是版本的差异

#158

MySQL [(none)]> ALTER SYSTEM DROP follower “10.0.0.80:9010”;
ERROR 1064 (HY000): Internal error processing forward 下不掉呢，我直接停掉不让他启动也可以吧

#159

嗯 除了master 把其他两个FE关掉

#160

使用starrocks-2.3.0 版本是不是性能会更好

#161

主要还是使用这两个参数，请您使用set global exec_mem_limit = 515396075520; set global parallel_fragment_exec_instance_num = 60;
永久生效在fe.conf 把这两个参数加上。

京ICP备2022026421号-1