GitHub user my-ship-it added a comment to the discussion: Slow Data Insertion in Cloud Berry
Hi @TanmayK2000 š The math is the clue here: ``` 8M rows / 3 days ā 31 rows/sec ā ~32 ms per row ``` Even a naive row-by-row `INSERT` with autocommit usually manages 300ā1000 rows/sec, so you're 10ā30Ć below the *worst* reasonable approach. Two things are likely stacking up. **1. NiFi probably shouldn't be in this path.** You're moving data between two tables *in the same warehouse*. Right now every row is read out of Cloudberry, pushed through NiFi's JVM and FlowFile repository, and written back over JDBC ā 17 million round trips for what is one server-side statement: ```sql INSERT INTO target SELECT * FROM source; ``` That runs fully parallel across all segments with no data landing on the coordinator. Expect minutes, not days. If NiFi must stay for orchestration, have it issue *that statement* rather than move the rows. **2. If the target is an AO/AOCO table, per-row commits degrade toward O(nĀ²).** Each write transaction into an AO table takes a serializing lock and sequentially scans `pg_aoseg` to pick a segment file ā and each transaction also UPDATEs a row in `pg_aoseg`. That table isn't reached by autovacuum; it's only cleaned when you `VACUUM` the parent table. So after 8M single-row transactions, every new insert scans past millions of dead tuples. Per-row cost grows with row count. This is a hypothesis, but there's a fast test that doubles as immediate relief: ```sql VACUUM target_table; ``` If throughput jumps, that's your bottleneck. (Related: was the first million rows noticeably faster than the last? Progressive slowdown would confirm it.) **If you're stuck with JDBC:** disable autocommit, batch 1,000ā10,000 rows per commit, add `reWriteBatchedInserts=true` to the JDBC URL, and use `PutDatabaseRecord` (many records per FlowFile) instead of `PutSQL` (one row per FlowFile). Better still, use `COPY` or a gpfdist external table. **To confirm the diagnosis, could you share:** - `\d+ target_table` ā AO/AOCO? distribution key? indexes? - Which NiFi processor and what batch size? - During the load: `SELECT wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE state <> 'idle';` - Does the flow do plain INSERTs, or UPDATEs/upserts? (AO tables serialize UPDATE/DELETE on the coordinator, which would lock throughput down completely.) Short version: this looks like an OLTP-style row-by-row write pattern against an MPP warehouse, not a Cloudberry limit. `INSERT INTO ... SELECT` should turn 3 days into minutes. GitHub link: https://github.com/apache/cloudberry/discussions/1875#discussioncomment-17849117 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
