Improve performance of multi-row INSERTs
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Multi-row INSERTs are a way to do initial data loading to Citus clusters. Can we achieve higher throughput with that? (Note that we suggest `COPY` command over multi-row INSERTs, if possible suggest users to use `COPY`. If `COPY` is not applicable, then use multi-row INSERTs)
### Two high-level items:
#### Do not use sequential execution: See the details on the code: https://github.com/citusdata/citus/blob/22c903b151fa88aeda1532b847fe1afa8a4920c8/src/backend/distributed/executor/adaptive_executor.c#L2014-L2027
#### Reduce the CPU overhead.
The below is a perf report of a 10000 row multi-row INSERT workload. The most notable things are: `pg_analyze_and_rewrite - %20` and `pg_parse_query - %10`, which cannot be avoided.
The following could possibly be optimized:
`RegenerateTaskListForInsert - %23 `, `ExecuteMasterEvaluableFunctionsAndParameters - %12`, ` PlanFastPathDistributedStmt - 8.54% `, `ExtractRangeTableEntryList - 2.91%`, `CopyDistributedPlanWithoutCache - %3`, `copyObjectImpl %3.4`
```
- 91.67% 0.00% postgres postgres [.] exec_simple_query ▒
- exec_simple_query ▒
- 42.38% PortalRun ▒
PortalRunMulti ▒
- ProcessQuery ▒
- 37.73% ExecutorStart ▒
CitusExecutorStart ▒
standard_ExecutorStart ▒
InitPlan ▒
ExecInitNode ▒
ExecInitCustomScan ▒
CitusBeginScan ▒
- CitusBeginModifyScan ▒
+ 23.36% RegenerateTaskListForInsert ▒
+ 11.45% ExecuteMasterEvaluableFunctionsAndParameters ▒
+ 2.81% CopyDistributedPlanWithoutCache ▒
+ 4.65% ExecutorRun ▒
- 19.48% pg_analyze_and_rewrite ▒
+ 19.02% parse_analyze ▒
+ 17.13% pg_plan_queries ▒
+ 9.66% pg_parse_query ▒
1.64% pgstat_report_activity ▒
+ 1.38% finish_xact_command
```
or only citus.so
```
+ 37.73% 0.00% postgres [.] CitusExecutorStart ▒
+ 37.73% 0.00% postgres [.] CitusBeginScan ▒
+ 37.73% 0.00% postgres [.] CitusBeginModifyScan ▒
+ 23.36% 0.00% postgres [.] RegenerateTaskListForInsert ▒
+ 20.71% 0.00% postgres [.] RebuildQueryStrings ▒
+ 20.65% 0.00% postgres [.] UpdateTaskQueryString ▒
+ 20.65% 0.00% postgres [.] SetTaskQueryIfShouldLazyDeparse ◆
+ 20.65% 0.00% postgres [.] DeparseTaskQuery ▒
+ 20.65% 0.00% postgres [.] deparse_shard_query ▒
+ 20.65% 0.00% postgres [.] get_query_def_extended ▒
+ 20.55% 0.00% postgres [.] get_insert_query_def ▒
+ 20.30% 1.79% postgres [.] get_values_def ▒
+ 18.00% 2.25% postgres [.] get_rule_expr_toplevel ▒
+ 17.13% 0.00% postgres [.] distributed_planner ▒
+ 15.75% 0.26% postgres [.] get_rule_expr ▒
+ 15.44% 0.26% postgres [.] get_const_expr ▒
+ 11.50% 0.36% postgres [.] PartiallyEvaluateExpression ▒
+ 11.45% 0.00% postgres [.] ExecuteMasterEvaluableFunctionsAndParameters ▒
+ 8.69% 0.10% postgres [.] citus_evaluate_expr ▒
+ 8.54% 0.00% postgres [.] PlanFastPathDistributedStmt ▒
+ 5.42% 0.00% postgres [.] CreateDistributedPlannedStmt ▒
+ 4.65% 0.00% postgres [.] CitusExecutorRun ▒
+ 4.65% 0.00% postgres [.] CitusExecScan ▒
+ 4.65% 0.00% postgres [.] AdaptiveExecutor ▒
+ 4.60% 0.00% postgres [.] RunDistributedExecution ▒
+ 4.14% 0.00% postgres [.] ConnectionStateMachine ▒
+ 4.14% 0.00% postgres [.] TransactionStateMachine ▒
+ 4.09% 0.00% postgres [.] ProcessWaitEvents ▒
+ 4.04% 0.00% postgres [.] SendRemoteCommand ▒
+ 3.99% 0.00% postgres [.] StartPlacementExecutionOnSession ▒
+ 3.17% 0.00% postgres [.] CreateDistributedPlan ▒
+ 3.17% 0.00% postgres [.] CreateModifyPlan ▒
+ 3.17% 0.00% postgres [.] RouterInsertJob ▒
+ 3.12% 0.00% postgres [.] FastPathPlanner
```
Contributor guide
Assessment
This issue has not been assessed yet.