cockroachdb / cockroachdb/cockroach

sql/importer: adaptively determine number of merge iterations based on input size

Open
#163,396 1 comment 0 reactions 0 assignees View on GitHub
A-import C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

IMPORT currently uses a hardcoded single merge iteration in the distributed merge pipeline, while index backfill uses two iterations (local merge followed by final merge). Neither implementation adaptively determines the optimal number of merge stages based on input characteristics.

### Current State

**Index Backfill** (`pkg/sql/index_backfiller.go:236`):
```go
// Uses a two-level merge: iteration 1 merges local SSTs on each node,
// then iteration 2 performs a final cross-node merge.
const maxIterations = 2
```

**IMPORT** (`pkg/sql/importer/import_processor_planning.go:367`):
```go
// Hardcoded to single iteration
MaxIterations: 1,
```

### Problem

When importing very large datasets or many files, a single merge iteration can cause significant memory pressure during the final merge phase. Issue #161865 documents that merging >3000 SSTs can lead to OOM, as memory consumption is proportional to the number of SSTs being merged.

The current hardcoded approach doesn't account for:
- Total input data size
- Number of SST files being merged
- Available cluster resources
- Key distribution and skew

### Proposed Solution

IMPORT should inspect input file sizes and SST counts during planning and intelligently determine the optimal number of merge iterations needed to:
- Keep memory consumption bounded during each merge phase
- Distribute work efficiently across the cluster
- Avoid OOMs when importing large datasets

The logic should consider:
1. **Total SST count** - Use two-stage merge (local + final) when count exceeds a threshold
2. **Total data size** - Factor in overall data volume
3. **SST size distribution** - Account for uneven file sizes and key skew

This would use the multi-level merge infrastructure already implemented in #158354, adopting the same two-stage approach that index backfill uses.

### Example Heuristic

```
if estimatedSSTCount < threshold:
maxIterations = 1 // Single-stage merge (direct to final)
else:
maxIterations = 2 // Two-stage merge (local + final, like index backfill)
```

The exact threshold would need tuning based on memory profiles and performance testing. A reasonable starting point might be around 1000-3000 SSTs based on the OOM issues observed in #161865.

### Related Issues

- Informs: #158354 (multi-level merge infrastructure)
- Addresses: #161865 (memory consumption during final merge)
- Related: #161887 (adaptive behavior based on data characteristics)

### Benefits

1. **Prevents OOMs** - Addresses the root cause of memory pressure in #161865
2. **Improves performance** - Better resource utilization for large imports
3. **Matches backfill behavior** - Brings IMPORT to parity with index backfill's multi-stage approach
4. **Scales better** - Handles large imports efficiently with intermediate local merge stage

### Epic
CRDB-48845

Jira issue: CRDB-60196

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.