Hi! I'm currently testing starrocks on AWS with single FE and CN to query hive table in S3. For FE I use

m6i.large

while CN use

r6id.4xlarge

with the

STARROCKS_HOME

located in local disk. After the cluster set I benchmark it with AWS Athena and noticed that it performs slower compared to Athena on aggregate heavy query. I also enabled the spill to disk otherwise the query got killed due to OOM. Is there a better setup (without increasing the cluster size) to improve datalake workload? Thanks!

The easiest way is to create a materialized view to pre-aggregate the data. If you can share the query profile, it might help pinpoint where the bottleneck is.

the issue turns out coming from hdfs scan which take more than half of the query time. Do you have any idea how to increase the scan time? the datalake datacache is enabled but seems like it doesnt utilize the cache well since there's only 4 mb of data in cache folder while the actual scanned data is around 10 gb

Can you show us a profile?

Unfortunately I already destroy my starrocks cluster but I have the result from running

EXPLAIN ANALYZE <query>

. But I cannot share you the whole plan since there might be confidentiality in the column name.

|       └──HDFS_SCAN (id=0)                                                                                                            
|              Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 160.0]                                                         
|              TotalTime: 3m8s (46.72%) [CPUTime: 1s468ms, ScanTime: 3m6s]                                                             
|              OutputRows: 221.686M (221686418)                                                                                        
|              SubordinateOperators:                                                                                                   
|                  CHUNK_ACCUMULATE                                                                                                    
|              Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                             
|                  IOTaskExecTime: 3m4s [min=2m59s, max=3m5s]                                                                          
|                      InputStream:                                                                                                    
|                          AppIOTime: 3m2s [min=2m57s, max=3m3s]                                                                       
|                          FSIOTime: 3m2s [min=2m57s, max=3m3s]                                                                        
|                      OpenFile: 2m14s [min=2m10s, max=2m17s]                                                                          
|                      Parquet:                                                                                                       
|                          ReaderInitFooterRead: 1m45s [min=1m41s, max=1m47s]                                                          
|                      ReaderInit: 2m14s [min=2m10s, max=2m17s]                                                                        
|                      SharedBuffered:                                                                                                 
|                          DirectIOTime: 1m45s [min=1m41s, max=1m47s]                                                                  
|                          SharedIOTime: 1m17s [min=1m13s, max=1m19s]                                                                  
|                  IOTaskWaitTime: 1s220ms [min=756.294ms, max=1s720ms]