Hello Team, We are getting below error for queries with large data scan from S3. We are using shared Data Architecture with FE and CN nodes. We have StarRocks Version 4.0 and we have enable spill to disk config but it looks like not working. Is it not supported in version 4.0? SET enable_spill = *true*; SQL Error [1064] [42000]: StarRocks planner use long time 3104 ms in logical phase, This probably because 1. FE Full GC, current query allocated=2.4 GiB, optimizerContextInUse=8.0 MiB2. Hive external table fetch metadata took a long time, 3. The SQL is very complex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable —----------------------------------------------------------------------------------------------------------------------- SQL Error [1064] [42000]: Memory of default_wg exceed limit. Pipeline Backend: [Instance_IP], fragment: 019afe24-f98e-7d86-89ce-9dce1991da44 Used: 48094070392, Limit: 48090711563. Mem usage has exceed the limit of query pool: BE:10002 —--------------------------------------------------------------------------------------------------------------- SQL Error [1064] [42000]: Query reached its timeout of 300 seconds, please increase the 'query_timeout' session variable, pending time:0 —---------------------------------------------------------------- SQL Error [1064] [42000]: Memory of query_pool exceed limit. try consume:6442450944 Backend: [Instance_IP], Used: 25904780208, Limit: 48090711563. Mem usage has exceed the limit of query pool: BE:10002 SQL Error [1064] [42000]: StarRocks planner use long time 3001 ms in memo phase, This probably because 1. FE Full GC, current query allocated=2.2 GiB, optimizerContextInUse=29.3 MiB2. Hive external table fetch metadata took a long time, 3. The SQL is very complex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable SQL Error [1064] [42000]: StarRocks planner use long time 3710 ms in logical phase, This probably because 1. FE Full GC, current query allocated=2.6 GiB, optimizerContextInUse=29.3 MiB2. Hive external table fetch metadata took a long time, 3. The SQL is very complex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable SQL Error [1064] [42000]: Memory of Query019b0243-3b03-7440-892f-c45efe2f4cc4 exceed limit. get chunk from scanner Backend: [Instance_IP], fragment: 019b0243-3b03-7440-892f-c45efe2f4cc8 Used: 5488469432, Limit: 5474836480. Mem usage has exceed the limit of single query, You can change the limit by set session variable query_mem_limit.: file = s3://location//_date=2023-04-06/part-00038-f2edb0a7-4596-4a88-8896-67bd863702a4.c000.snappy.parquet: BE:10002

Spill is supported, but it only applies to some operators https://docs.starrocks.io/docs/administration/management/resource_management/spill_to_disk/#overview Also planner timeout is caused by the FE - can you check the FE memory usage? Is this issue persistent or it just happens occasionally?

@Robert Raharjo, Thanks for prompt response. This happens for big data scans only normal data scans works fine. We believe spill to disk is not happening thats the reason. Let me further test with other datasets and get back to you.