@Rocky we have been trying to copy a month from a table into an empty table, the data is large (60m rows per day) but it takes forever, are we missing something?

Moving 1.8 billion rows (60M rows/day * 30 days) in a single

INSERT INTO ... SELECT

statement is a massive task that can often hit resource limits or timing issues. Because this runs as a single transaction, any failure at the 99% mark would cause the entire month of data to roll back. Here are the recommended strategies to speed this up and make it more reliable: 1. Process Day-by-Day (Recommended) Instead of copying the whole month at once, perform the copy partition by partition (or day by day). This reduces the pressure on memory and transaction logs and allows you to track progress.

sql
INSERT INTO target_table 
SELECT * FROM source_table 
WHERE dt = '2023-10-01';

Repeat for each day. If your table is partitioned, you can also use

INSERT OVERWRITE target_table PARTITION (p20231001) SELECT ...

to target specific partitions directly. 2. Tune Parallelism and Memory For such a large data volume, the default session settings might be too conservative. Increase these variables before running your query:

sql
-- Increase the timeout (default is 14400s / 4 hours)
SET insert_timeout = 28800; 

-- Increase memory limit for the query (e.g., 64GB or more depending on your BE RAM)
SET query_mem_limit = 68719476736; 

-- Increase parallelism (set to roughly half your BE CPU cores)
SET pipeline_dop = 16;

3. Use

SET_VAR

Hints If you prefer to keep the settings tied to the specific statement, use hints:

sql
INSERT /*+ SET_VAR(query_mem_limit = 68719476736, pipeline_dop = 16) */ 
INTO target_table SELECT * FROM source_table WHERE ...;

4. Check Table Bucket Configuration If the "empty table" was created with too few buckets, the write performance will be throttled because only a few BEs/disks will be active. • Ensure the

DISTRIBUTED BY HASH(...) BUCKETS

value is appropriate for 1.8 billion rows. • A rule of thumb is roughly 100MB–1GB of data per tablet (bucket). For 1.8B rows, you likely need hundreds of buckets across your cluster. • Since StarRocks v2.5.7+, you can use Automatic Bucketing by not specifying the number of buckets, letting StarRocks optimize it based on data volume. 5. Monitor the "Bottleneck" To see why it is "taking forever," you can check the profile of the running query: 1. Run

SHOW PROC '/current_queries';

to get the

QueryID

. 2. Get the execution profile using

Get_Query_Profile(query_id)

. 3. Look for the

DataBuffer

or

ChunkBuffer

metrics. If the "Wait" time is high on the source side, the bottleneck is reading; if it's high on the "OlapTableSink" side, the bottleneck is the write/compaction speed. 6. Transactional Considerations If you are copying data to an empty table and the schema is identical, you might consider using

INSERT OVERWRITE

. While the performance is similar to

INSERT INTO

, it is more efficient for replacing data and ensures that if the job fails, the table remains clean. Summary: Break the month into daily chunks. It is much safer, easier to debug, and usually faster because it avoids massive memory overhead and long-running transaction locks. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • faq/Sql_faq.md • administration/management/resource_management/Memory_management.md • administration/management/resource_management/Query_management.md

Try using broker load

@Robert Raharjo what would change doing that?