http://starrocks.io logo
Join Slack
Powered by
Using the `FILES()` table function, I'm loading da...
# questions-and-troubleshooting
p
Using the 
FILES()
table function, I'm loading data from GCS, it is very slow. Analyzing the profile shows it is the FILE_SCAN that takes the bulk of the time. How do I improve this by a large margin? My files are taking 20s to load into Starrocks. Comparatively, the same operation takes ~1s in Databend.
14MB in total, over 6 files, 11,600 rows, 231 columns. This should take no time at all. What could be going wrong - any logs I can obtain to figure out why it's taking so long?
m
please provide a query profile
p
@Murphy
Copy code
Explain String                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------+
 [0mSummary [0m                                                                                                                                          |
     [0mAttention:  [1m [41;30mThe transaction of the statement will be aborted, and no data will be actually inserted!!! [0m [0m                        |
     [0mQueryId: 019acf9f-46e1-7bfd-b288-03bb8b59b464 [0m                                                                                                |
     [0mVersion: 4.0.0-rc02-b95e90b [0m                                                                                                                  |
     [0mState: Finished [0m                                                                                                                              |
     [0mTotalTime: 16s944ms [0m                                                                                                                          |
         [0mExecutionTime: 1s270ms [Scan: 861.868ms (67.86%), Network: 0ns (0.00%), ResultDeliverTime: 332.678ms (26.20%), ScheduleTime: 0ns (0.00%)] [0m|
         [0mCollectProfileTime: 0 [0m                                                                                                                    |
         [0mFrontendProfileMergeTime: 488.510us [0m                                                                                                      |
     [0mQueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 428.293 MB [0m                                                                               |
     [0mTop Most Time-consuming Nodes: [0m                                                                                                               |
         [1m [31m1. FILE_SCAN (id=0) : 863.276ms (90.20%) [0m                                                                                            |
         [0m2. OLAP_TABLE_SINK: 93.797ms (9.80%) [0m                                                                                                     |
     [0mTop Most Memory-consuming Nodes: [0m                                                                                                             |
     [0mNonDefaultVariables: [0m                                                                                                                         |
         [0mbig_query_profile_threshold: 30s -> 10s [0m                                                                                                  |
         [0menable_adaptive_sink_dop: false -> true [0m                                                                                                  |
         [0menable_load_profile: false -> true [0m                                                                                                       |
         [0menable_local_shuffle_agg: true -> false [0m                                                                                                  |
         [0menable_materialized_view_rewrite: true -> false [0m                                                                                          |
         [0menable_profile: false -> true [0m                                                                                                            |
         [0muse_compute_nodes: -1 -> 0 [0m                                                                                                               |
 [0mFragment 0 [0m                                                                                                                                       |
│    [0mBackendNum: 1 [0m                                                                                                                                |
│    [0mInstancePeakMemoryUsage: 72.174 MB, InstanceAllocatedMemoryUsage: 428.293 MB [0m                                                                 |
│    [0mPrepareTime: ? [0m                                                                                                                               |
└── [0mOLAP_TABLE_SINK [0m                                                                                                                               |
   │    [0mTotalTime: 93.797ms (9.80%) [CPUTime: 93.797ms] [0m                                                                                           |
   │    [0mOutputRows: 11.646K (11646) [0m                                                                                                               |
   │    [0mPartitionType: RANDOM [0m                                                                                                                     |
   │    [0mTable: temp_831006ac-d937-4196-b6d6-2106be7a7029 [0m                                                                                          |
   └── [1m [31mFILE_SCAN (id=0)  [0m                                                                                                                     |
           [1m [31mEstimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0] [0m                                                                     |
           [1m [31mTotalTime: 863.276ms (90.20%) [CPUTime: 1.408ms, ScanTime: 861.868ms] [0m                                                             |
           [1m [31mOutputRows: 11.646K (11646) [0m                                                                                                       |
           [1m [31mDetail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime] [0m                                                                       |
               [1m [31mIOTaskExecTime: 861.644ms [0m                                                                                                     |
                   [1m [31mFileScanner:  [0m                                                                                                             |
                       [1m [31mFileReadTime: 582.632ms [0m                                                                                               |
                   [1m [31mScannerTotalTime: 856.468ms [0m                                                                                               |
               [1m [31mIOTaskWaitTime: 223.839us [0m                                                                                                     |
 [0m                                                                                                                                                     |
Profile
Profile
Seemingly it is perhaps not the read since I can run the select alone without insert in a query window and get results in 2s.
h
Could you share your destination table definition using 
SHOW CREATE TABLE
? Maybe you're writing to too many partitions or buckets/tablets?
p
Copy code
CREATE TABLE `temp_831006ac-d937-4196-b6d6-2106be7a7029` (
  `COL1` varchar(4000) NULL COMMENT "",
  ...
  `COL233` varchar(4000) NULL COMMENT ""
) ENGINE=OLAP 
DUPLICATE KEY(`COL1`)
COMMENT "OLAP"
DISTRIBUTED BY RANDOM
PROPERTIES (
"bucket_size" = "1073741824",
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"file_bundling" = "true",
"replication_num" = "1",
"storage_volume" = "builtin_storage_volume"
);
I do not understand the 
The transaction of the statement will be aborted, and no data will be actually inserted!!!
in the explain string. Records are inserted, the query does not appear to fail.
Open in Slack
PreviousNext
Powered by