Troubleshooting Data Loading

This guide is designed to help DBAs and operation engineers monitor the status of data load jobs through SQL interfaces—without relying on external monitoring systems. It also provides guidance on identifying performance bottlenecks and troubleshooting anomalies during load operations.

Terminology

Load Job: A continuous data load process, such as a Routine Load Job or Pipe Job.

Load Task: A one-time data load process, usually corresponding to a single load transaction. Examples include Broker Load, Stream Load, Spark Load, and INSERT INTO. Routine Load jobs and Pipe jobs continuously generate tasks to perform data ingestion.

Observe load jobs

There are two ways to observe load jobs:

• Using SQL statements SHOW ROUTINE LOAD and SHOW PIPES.
• Using system views information_schema.routine_load_jobs and information_schema.pipes.

Observe load tasks

Load tasks can also be monitored in two ways:

• Using SQL statements SHOW LOAD and SHOW ROUTINE LOAD TASK.
• Using system views information_schema.loads and statistics.loads_history.

SQL statements

The SHOW statements display both ongoing and recently completed load tasks for the current database, providing a quick overview of task status. The information retrieved is a subset of the statistics.loads_history system view.

SHOW LOAD statements return information of Broker Load, Insert Into, and Spark Load tasks, and SHOW ROUTINE LOAD TASK statements return Routine Load task information.

System views

information_schema.loads

The information_schema.loads system view stores information about recent load tasks, including active and recently completed ones. StarRocks periodically synchronizes the data to the statistics.loads_history system table for persistent storage.

information_schema.loads provides the following fields:

RUNTIME_DETAILS

• Universal metrics:

• Specific metrics for Broker Load, INSERT INTO, and Spark Load:

• Specific metrics for Routine Load:

• Specific metrics for Stream Load:

PROPERTIES

• Specific properties for Broker Load, INSERT INTO, and Spark Load:

• Specific properties for Routine Load:

statistics.loads_history

The statistics.loads_history system view stores load records for the last three months by default. DBAs can adjust the retention period by modifying the partition_ttl of the view. statistics.loads_history has the consistent schema with information_schema.loads.

Identify loading performance issues with Load Profiles

A Load Profile records execution details of all worker nodes involved in a data load. It helps you quickly pinpoint performance bottlenecks in the StarRocks cluster.

Enable Load Profiles

StarRocks provides multiple methods to enable Load Profiles, depending on the type of load:

For Broker Load and INSERT INTO

Enable Load Profiles for Broker Load and INSERT INTO at session level:

SET enable_profile = true;

By default, profiles are automatically enabled for long-running jobs (longer than 300 seconds). You can customize this threshold by:

SET big_query_profile_threshold = 60s;

:::note When big_query_profile_threshold is set to its default value 0, the default behavior is to disable Query Profiling for queries. However, for load tasks, profiles are automatically recorded for tasks with execution times exceeding 300 seconds. :::

StarRocks also supports Runtime Profiles, which periodically (every 30 seconds) report execution metrics of long-running load jobs. You can customize the report interval by:

SET runtime_profile_report_interval = 60;

:::note runtime_profile_report_interval specifies only the minimum report interval for load tasks. The actual report interval is dynamically adjusted and may exceed this value. :::

For Stream Load and Routine Load

Enable Load Profiles for Stream Load and Routine Load at table level:

ALTER TABLE <table_name> SET ("enable_load_profile" = "true");

Stream Load typically has high QPS, so StarRocks allows sampling for Load Profile collection to avoid performance degradation from extensive profiling. You can adjust the collection interval by configuring the FE parameter load_profile_collect_interval_second. This setting only applies to Load Profiles enabled via table properties. The default value is 0.

ADMIN SET FRONTEND CONFIG ("load_profile_collect_interval_second"="30");

StarRocks also allows collecting profiles only from load jobs that exceed a certain time threshold. You can adjust this threshold by configuring the FE parameter stream_load_profile_collect_threshold_second. The default value is 0.

ADMIN SET FRONTEND CONFIG ("stream_load_profile_collect_threshold_second"="10");

Analyze Load Profiles

The structure of Load Profiles is identical to that of Query Profiles. For detailed instructions, see Query Tuning Recipes.

You can analyze Load Profiles by executing ANALYZE PROFILE. For detailed instructions, see Analyze text-based Profiles.

Profiles provide detailed operator metrics. Key components include the OlapTableSink operator and the LoadChannel operator.

OlapTableSink operator

:::tip

• The significant variance between the maximum and minimum values of PushChunkNum in OLAP_TABLE_SINK indicates data skew in the upstream operator, which may cause write performance bottlenecks.
• RpcClientSideTime equals the sum of RpcServerSideTime, Network transmission time, and RPC framework processing time. If the difference between RpcClientSideTime and RpcServerSideTime is significant, consider to enable data compression to reduce transmission time.
• If RpcServerSideTime accounts for a significant portion of the time spent, further analysis can be conducted using LoadChannel Profile. :::

LoadChannel operator

:::tip If WaitFlushTime takes an extended period, it may indicate insufficient resources for the flush thread. Consider adjusting the BE configuration flush_thread_num_per_store. :::

Best practices

Diagnose Broker Load performance bottleneck

1. Load data using Broker Load:

   SQLCopy

   LOAD LABEL click_bench.hits_1713874468 
   (
       DATA INFILE ("s3://test-data/benchmark_data/query_data/click_bench/hits.tbl*") 
       INTO TABLE hits COLUMNS TERMINATED BY "\t" (WatchID,JavaEnable,Title,GoodEvent,EventTime,EventDate,CounterID,ClientIP,RegionID,UserID,CounterClass,OS,UserAgent,URL,Referer,IsRefresh,RefererCategoryID,RefererRegionID,URLCategoryID,URLRegionID,ResolutionWidth,ResolutionHeight,ResolutionDepth,FlashMajor,FlashMinor,FlashMinor2,NetMajor,NetMinor,UserAgentMajor,UserAgentMinor,CookieEnable,JavascriptEnable,IsMobile,MobilePhone,MobilePhoneModel,Params,IPNetworkID,TraficSourceID,SearchEngineID,SearchPhrase,AdvEngineID,IsArtifical,WindowClientWidth,WindowClientHeight,ClientTimeZone,ClientEventTime,SilverlightVersion1,SilverlightVersion2,SilverlightVersion3,SilverlightVersion4,PageCharset,CodeVersion,IsLink,IsDownload,IsNotBounce,FUniqID,OriginalURL,HID,IsOldCounter,IsEvent,IsParameter,DontCountHits,WithHash,HitColor,LocalEventTime,Age,Sex,Income,Interests,Robotness,RemoteIP,WindowName,OpenerName,HistoryLength,BrowserLanguage,BrowserCountry,SocialNetwork,SocialAction,HTTPError,SendTiming,DNSTiming,ConnectTiming,ResponseStartTiming,ResponseEndTiming,FetchTiming,SocialSourceNetworkID,SocialSourcePage,ParamPrice,ParamOrderID,ParamCurrency,ParamCurrencyID,OpenstatServiceName,OpenstatCampaignID,OpenstatAdID,OpenstatSourceID,UTMSource,UTMMedium,UTMCampaign,UTMContent,UTMTerm,FromTag,HasGCLID,RefererHash,URLHash,CLID)
   ) 
   WITH BROKER 
   (
               "aws.s3.access_key" = "<iam_user_access_key>",
               "aws.s3.secret_key" = "<iam_user_secret_key>",
               "aws.s3.region" = "<aws_s3_region>"
   )
   

2. Use SHOW PROFILELIST to retrieve the list of runtime profiles.

   SQLCopy

   MySQL [click_bench]> SHOW PROFILELIST;
   +--------------------------------------+---------------------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------+
   | QueryId                              | StartTime           | Time     | State   | Statement                                                                                                                        |
   +--------------------------------------+---------------------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------+
   | 3df61627-f82b-4776-b16a-6810279a79a3 | 2024-04-23 20:28:26 | 11s850ms | Running | LOAD LABEL click_bench.hits_1713875306 (DATA INFILE ("s3://test-data/benchmark_data/query_data/click_bench/hits.tbl*" ... |
   +--------------------------------------+---------------------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
   

3. Use ANALYZE PROFILE to view the Runtime Profile.

   SQLCopy

   MySQL [click_bench]> ANALYZE PROFILE FROM '3df61627-f82b-4776-b16a-6810279a79a3';
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Explain String                                                                                                                                              |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Summary                                                                                                                                             |
   |     Attention: The transaction of the statement will be aborted, and no data will be actually inserted!!!                           |
   |     Attention: Profile is not identical!!!                                                                                          |
   |     QueryId: 3df61627-f82b-4776-b16a-6810279a79a3                                                                                                   |
   |     Version: default_profile-70fe819                                                                                                                |
   |     State: Running                                                                                                                                  |
   |     Legend: ⏳ for blocked; 🚀 for running; ✅ for finished                                                                                             |
   |     TotalTime: 31s832ms                                                                                                                             |
   |         ExecutionTime: 30s1ms [Scan: 28s885ms (96.28%), Network: 0ns (0.00%), ResultDeliverTime: 7s613ms (25.38%), ScheduleTime: 145.701ms (0.49%)] |
   |         FrontendProfileMergeTime: 3.838ms                                                                                                           |
   |     QueryPeakMemoryUsage: 141.367 MB, QueryAllocatedMemoryUsage: 82.422 GB                                                                          |
   |     Top Most Time-consuming Nodes:                                                                                                                  |
   |         1. FILE_SCAN (id=0)  🚀 : 28s902ms (85.43%)                                                                                              |
   |         2. OLAP_TABLE_SINK 🚀 : 4s930ms (14.57%)                                                                                                      |
   |     Top Most Memory-consuming Nodes:                                                                                                                |
   |     Progress (finished operator/all operator): 0.00%                                                                                                |
   |     NonDefaultVariables:                                                                                                                            |
   |         big_query_profile_threshold: 0s -> 60s                                                                                                      |
   |         enable_adaptive_sink_dop: false -> true                                                                                                     |
   |         enable_profile: false -> true                                                                                                               |
   |         sql_mode_v2: 32 -> 34                                                                                                                       |
   |         use_compute_nodes: -1 -> 0                                                                                                                  |
   | Fragment 0                                                                                                                                          |
   | │   BackendNum: 3                                                                                                                                   |
   | │   InstancePeakMemoryUsage: 128.541 MB, InstanceAllocatedMemoryUsage: 82.422 GB                                                                    |
   | │   PrepareTime: 2.304ms                                                                                                                            |
   | └──OLAP_TABLE_SINK                                                                                                                                  |
   |    │   TotalTime: 4s930ms (14.57%) [CPUTime: 4s930ms]                                                                                               |
   |    │   OutputRows: 14.823M (14823424)                                                                                                               |
   |    │   PartitionType: RANDOM                                                                                                                        |
   |    │   Table: hits                                                                                                                                  |
   |    └──FILE_SCAN (id=0)  🚀                                                                                                                       |
   |           Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                          |
   |           TotalTime: 28s902ms (85.43%) [CPUTime: 17.038ms, ScanTime: 28s885ms]                                                                 |
   |           OutputRows: 14.823M (14823424)                                                                                                       |
   |           Progress (processed rows/total rows): ?                                                                                              |
   |           Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                          |
   |               IOTaskExecTime: 25s612ms [min=19s376ms, max=28s804ms]                                                                            |
   |               IOTaskWaitTime: 63.192ms [min=20.946ms, max=91.668ms]                                                                            |
   |                                                                                                                                                         |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   40 rows in set (0.04 sec)
   

The profile shows that the FILE_SCAN section took nearly 29 seconds, accounting for approximately 90% of the total 32-second duration. This indicates that reading data from object storage is currently the bottleneck in the loading process.

Diagnose Stream Load Performance

1. Enable Load Profile for the target table.

   SQLCopy

   mysql> ALTER TABLE duplicate_200_column_sCH SET('enable_load_profile'='true');
   Query OK, 0 rows affected (0.00 sec)
   

2. Use SHOW PROFILELIST to retrieve the list of profiles.

   SQLCopy

   mysql> SHOW PROFILELIST;
   +--------------------------------------+---------------------+----------+----------+-----------+
   | QueryId                              | StartTime           | Time     | State    | Statement |
   +--------------------------------------+---------------------+----------+----------+-----------+
   | 90481df8-afaf-c0fd-8e91-a7889c1746b6 | 2024-09-19 10:43:38 | 9s571ms  | Finished |           |
   | 9c41a13f-4d7b-2c18-4eaf-cdeea3facba5 | 2024-09-19 10:43:37 | 10s664ms | Finished |           |
   | 5641cf37-0af4-f116-46c6-ca7cce149886 | 2024-09-19 10:43:20 | 13s88ms  | Finished |           |
   | 4446c8b3-4dc5-9faa-dccb-e1a71ab3519e | 2024-09-19 10:43:20 | 13s64ms  | Finished |           |
   | 48469b66-3866-1cd9-9f3b-17d786bb4fa7 | 2024-09-19 10:43:20 | 13s85ms  | Finished |           |
   | bc441907-e779-bc5a-be8e-992757e4d992 | 2024-09-19 10:43:19 | 845ms    | Finished |           |
   +--------------------------------------+---------------------+----------+----------+-----------+
   

3. Use ANALYZE PROFILE to view the Profile.

   SQLCopy

   mysql> ANALYZE PROFILE FROM '90481df8-afaf-c0fd-8e91-a7889c1746b6';
   +-----------------------------------------------------------+
   | Explain String                                            |
   +-----------------------------------------------------------+
   | Load:                                                     |
   |   Summary:                                                |
   |      - Query ID: 90481df8-afaf-c0fd-8e91-a7889c1746b6     |
   |      - Start Time: 2024-09-19 10:43:38                    |
   |      - End Time: 2024-09-19 10:43:48                      |
   |      - Query Type: Load                                   |
   |      - Load Type: STREAM_LOAD                             |
   |      - Query State: Finished                              |
   |      - StarRocks Version: main-d49cb08                    |
   |      - Sql Statement                                      |
   |      - Default Db: ingestion_db                           |
   |      - NumLoadBytesTotal: 799008                          |
   |      - NumRowsAbnormal: 0                                 |
   |      - NumRowsNormal: 280                                 |
   |      - Total: 9s571ms                                     |
   |      - numRowsUnselected: 0                               |
   |   Execution:                                              |
   |     Fragment 0:                                           |
   |        - Address: 172.26.93.218:59498                     |
   |        - InstanceId: 90481df8-afaf-c0fd-8e91-a7889c1746b7 |
   |        - TxnID: 1367                                      |
   |        - ReplicatedStorage: true                          |
   |        - AutomaticPartition: false                        |
   |        - InstanceAllocatedMemoryUsage: 12.478 MB          |
   |        - InstanceDeallocatedMemoryUsage: 10.745 MB        |
   |        - InstancePeakMemoryUsage: 9.422 MB                |
   |        - MemoryLimit: -1.000 B                            |
   |        - RowsProduced: 280                                |
   |          - AllocAutoIncrementTime: 348ns                  |
   |          - AutomaticBucketSize: 0                         |
   |          - BytesRead: 0.000 B                             |
   |          - CloseWaitTime: 9s504ms                         |
   |          - IOTaskExecTime: 0ns                            |
   |          - IOTaskWaitTime: 0ns                            |
   |          - IndexNum: 1                                    |
   |          - NumDiskAccess: 0                               |
   |          - OpenTime: 15.639ms                             |
   |          - PeakMemoryUsage: 0.000 B                       |
   |          - PrepareDataTime: 583.480us                     |
   |            - ConvertChunkTime: 44.670us                   |
   |            - ValidateDataTime: 109.333us                  |
   |          - RowsFiltered: 0                                |
   |          - RowsRead: 0                                    |
   |          - RowsReturned: 280                              |
   |          - RowsReturnedRate: 12.049K (12049) /sec         |
   |          - RpcClientSideTime: 28s396ms                    |
   |          - RpcServerSideTime: 28s385ms                    |
   |          - RpcServerWaitFlushTime: 0ns                    |
   |          - ScanTime: 9.841ms                              |
   |          - ScannerQueueCounter: 1                         |
   |          - ScannerQueueTime: 3.272us                      |
   |          - ScannerThreadsInvoluntaryContextSwitches: 0    |
   |          - ScannerThreadsTotalWallClockTime: 0ns          |
   |            - MaterializeTupleTime(*): 0ns                 |
   |            - ScannerThreadsSysTime: 0ns                   |
   |            - ScannerThreadsUserTime: 0ns                  |
   |          - ScannerThreadsVoluntaryContextSwitches: 0      |
   |          - SendDataTime: 2.452ms                          |
   |            - PackChunkTime: 1.475ms                       |
   |            - SendRpcTime: 1.617ms                         |
   |              - CompressTime: 0ns                          |
   |              - SerializeChunkTime: 880.424us              |
   |            - WaitResponseTime: 0ns                        |
   |          - TotalRawReadTime(*): 0ns                       |
   |          - TotalReadThroughput: 0.000 B/sec               |
   |       DataSource:                                         |
   |          - DataSourceType: FileDataSource                 |
   |          - FileScanner:                                   |
   |            - CastChunkTime: 0ns                           |
   |            - CreateChunkTime: 227.100us                   |
   |            - FileReadCount: 3                             |
   |            - FileReadTime: 253.765us                      |
   |            - FillTime: 6.892ms                            |
   |            - MaterializeTime: 133.637us                   |
   |            - ReadTime: 0ns                                |
   |          - ScannerTotalTime: 9.292ms                      |
   +-----------------------------------------------------------+
   76 rows in set (0.00 sec)
   

Appendix

Useful SQL for Operations

:::note This section only applies to shared-nothing clusters. :::

query the throughput per minute

-- overall
select date_trunc('minute', load_finish_time) as t,count(*) as tpm,sum(SCAN_BYTES) as scan_bytes,sum(sink_rows) as sink_rows from _statistics_.loads_history group by t order by t desc limit 10;

-- table
select date_trunc('minute', load_finish_time) as t,count(*) as tpm,sum(SCAN_BYTES) as scan_bytes,sum(sink_rows) as sink_rows from _statistics_.loads_history where table_name = 't' group by t order by t desc limit 10;

Query RowsetNum and SegmentNum of a table

-- overall
select * from information_schema.be_tablets t, information_schema.tables_config c where t.table_id = c.table_id order by num_segment desc limit 5;
select * from information_schema.be_tablets t, information_schema.tables_config c where t.table_id = c.table_id order by num_rowset desc limit 5;

-- table
select * from information_schema.be_tablets t, information_schema.tables_config c where t.table_id = c.table_id and table_name = 't' order by num_segment desc limit 5;
select * from information_schema.be_tablets t, information_schema.tables_config c where t.table_id = c.table_id and table_name = 't' order by num_rowset desc limit 5;

• High RowsetNum (>100) indicates too frequent loads. You may consider to reduce frequency or increase Compaction threads.
• High SegmentNum (>100) indicates excessive segments per load. You may consider increase Compaction threads or adopt the random distribution strategy for the table.

Check data skew

Data skew across nodes

-- overall
SELECT tbt.be_id, sum(tbt.DATA_SIZE) FROM information_schema.tables_config tb JOIN information_schema.be_tablets tbt ON tb.TABLE_ID = tbt.TABLE_ID group by be_id;

-- table
SELECT tbt.be_id, sum(tbt.DATA_SIZE) FROM information_schema.tables_config tb JOIN information_schema.be_tablets tbt ON tb.TABLE_ID = tbt.TABLE_ID WHERE tb.table_name = 't' group by be_id;

If you detected node-level skew, you may consider to use a higher-cardinality column as the distribution key or adopt the random distribution strategy for the table.

Data skew across tablets

select tablet_id,t.data_size,num_row,visible_version,num_version,num_rowset,num_segment,PARTITION_NAME from information_schema.partitions_meta m, information_schema.be_tablets t where t.partition_id = m.partition_id and m.partition_name = 'att' and m.table_name='att' order by t.data_size desc;

Common monitoring metrics for loading

BE Load

These metrics are available under the BE Load category in Grafana. If you cannot find this category, verify that you are using the latest Grafana dashboard template.

ThreadPool

These metrics help analyze the status of thread pools — for example, whether tasks are being backlogged, or how long they spend pending. Currently, there are four monitored thread pools:

• async_delta_writer
• memtable_flush
• segment_replicate_sync
• segment_flush

Each thread pool includes the following metrics:

:::note

• A reliable indicator for backlog is whether pending duration keeps increasing. workers util and queue count are necessary but not sufficient indicators.
• If a backlog occurs, use rate and execute duration to determine whether it is due to increased load or slower processing.
• workers util helps assess how busy the pool is, which can guide tuning efforts. :::

LoadChannel::add_chunks

These metrics help analyze the behavior of LoadChannel::add_chunks after receiving a BRPC tablet_writer_add_chunks request.

:::note

• The latency metric equals the sum of wait_memtable, wait_writer, and wait_replica.
• A high waiting ratio indicates downstream bottlenecks, which should be further analyzed. :::

Async Delta Writer

These metrics help analyze the behavior of the async delta writer.

:::note

• The total time per task (from the upstream perspective) equals pending plus execute.
• execute further includes wait_memtable plus wait_replica.
• A high pending time may indicate that execute is slow or the thread pool is undersized.
• If wait occupies a large portion of execute, downstream stages are the bottleneck; otherwise, the bottleneck is likely within the writer’s logic itself. :::

MemTable Flush

These metrics analyze MemTable flush performance.

:::note

• By comparing rate and size, you can determine whether the workload is changing or if massive imports are occurring — for example, a small rate but large size indicates a massive import.
• The compression ratio can be estimated using memory-size / disk-size.
• You can also assess if I/O is the bottleneck by checking the proportion of io time in execute. :::

Segment Replicate Sync

Segment Flush

These metrics analyze segment flush performance.

:::note

• By comparing rate and size, you can determine whether the workload is changing or if large imports are occurring — for example, a small rate but large size indicates a massive import.
• You can also assess if I/O is the bottleneck by checking the proportion of io time in execute. :::