【详述】操作insert into select时报
one instance report fail errorCode MEM_LIMIT_EXCEEDED Memory of Fragment 202ee318-8100-11ed-8600-525400ed2b7d exceed limit. read and decompress page Used: 12886247496, Limit: 12884901888. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit
表数据量只有500多M，正常scan/fliter/load，任何一个fragement都不应该触及12G。
【背景】做过哪些操作？
【业务影响】
【StarRocks版本】例如：2.3.3
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be分部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【表模型】例如：更新模型
fe.log日志
2022-12-21 15:21:58,216 WARN (thrift-server-pool-99697|143783) [Coordinator.updateFragmentExecStatus():1645] one instance report fail errorCode MEM_LIMIT_EXCEEDED Memory of Fragment 202ee318-8100-11ed-8600-525400ed2b7d exceed limit. read and decompress page Used: 12886247496, Limit: 12884901888. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit., query_id=202ee318-8100-11ed-8600-525400ed2b7b instance_id=202ee318-8100-11ed-8600-525400ed2b7d
2022-12-21 15:21:58,216 WARN (thrift-server-pool-99697|143783) [Coordinator.updateStatus():870] one instance report fail throw updateStatus(), need cancel. job id: -1, query id: 202ee318-8100-11ed-8600-525400ed2b7b, instance id: 202ee318-8100-11ed-8600-525400ed2b7d
2022-12-21 15:21:58,216 INFO (thrift-server-pool-99697|143783) [Coordinator.cancelInternal():966] unfinished instance: 202ee318-8100-11ed-8600-525400ed2b7d
2022-12-21 15:21:58,216 WARN (starrocks-mysql-nio-pool-10510|143932) [StmtExecutor.handleDMLStmt():1153] insert failed: Memory of Fragment 202ee318-8100-11ed-8600-525400ed2b7d exceed limit. read and decompress page Used: 12886247496, Limit: 12884901888. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit.
2022-12-21 15:21:58,216 WARN (starrocks-mysql-nio-pool-10510|143932) [StmtExecutor.handleDMLStmt():1229] handle insert stmt fail: insert_202ee318-8100-11ed-8600-525400ed2b7b
com.starrocks.common.DdlException: Memory of Fragment 202ee318-8100-11ed-8600-525400ed2b7d exceed limit. read and decompress page Used: 12886247496, Limit: 12884901888. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit.
at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:80) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:1154) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:430) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:322) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:440) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:676) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]
2022-12-21 15:21:58,287 INFO (starrocks-mysql-nio-pool-10510|143932) [QeProcessorImpl.unregisterQuery():79] deregister query id 202ee318-8100-11ed-8600-525400ed2b7b
fe.audit.log
2022-12-21 15:21:58,294 [slow_query] |Client=10.10.30.7:40570|User=default_cluster:u_ci|AuthorizedUser='default_cluster:u_ci'@'%'|ResourceGroup=default_wg|Catalog=default_catalog|Db=default_cluster:labelx|State=ERR|ErrorCod
e=MEM_LIMIT_EXCEEDED|Time=12459|ScanBytes=0|ScanRows=0|ReturnRows=0|CpuCostNs=0|MemCostBytes=0|StmtId=6622491|QueryId=202ee318-8100-11ed-8600-525400ed2b7b|IsQuery=false|feIp=10.10.20.5|Stmt=INSERT INTO user_coupon s
elect user_id,trans_no, amount, global_biz_id,1,verification, take_date, take_time,out_side_biz_id,dt from user_coupon where deleted = 0 and datediff(CURDATE(), take_date)>= 7|Digest=|PlanCpuCost=2.090
6963E7|PlanMemCost=0.0
sql语句：
select user_id,trans_no, amount, global_biz_id,1,verification, take_date, take_time,out_side_biz_id,dt from user_coupon where deleted = 0 and datediff(CURDATE(), take_date)>= 7;
建表语句：
CREATE TABLE `user_coupon` (
`user_id` bigint(20) NOT NULL ,
`trans_no` varchar(50) NOT NULL ,
`amount` decimal64(10, 2) NULL ,
`global_biz_id` varchar(50) NULL ,
`deleted` tinyint(4) NULL DEFAULT "0" ,
`verification` tinyint(4) NULL DEFAULT "0" ,
`take_date` date NULL ,
`take_time` datetime NULL ,
`out_side_biz_id` varchar(50) NULL ,
`dt` date NOT NULL
) ENGINE=OLAP
UNIQUE KEY(`user_id`, `trans_no`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 3
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false"
);
表数据量：
