Repository navigation
perf(migrator): potential 2-3x speedup for migrate-from-hana.js (low priority — current timing acceptable) #474
Description
Summary
Low-priority optimization track. Current re-migration wall-clock (~5 hours for 10.8M TaskRecords from a local laptop) is acceptable for July prod cutover, but several straightforward wins are available if we get bored after #467/#469/#472/#473 land.
Observed timing on 2026-06-20 DEV re-migration
- Reference data (Tags, Events, Groups, Missions, Tutorials, Steps, Users): ~10 min combined for ~820K rows
- Tutorials phase took 90s longer than necessary — once any row in a 5000-row batch fails the unique-constraint check, the batchInsert fallback drops back to row-by-row INSERT for the whole batch (2862 single round-trips instead of 1). 5 slug conflicts cost ~88s here. (Issue fix(migrator): source-side duplicate Tutorials slugs crash insert with @assert.unique violations #473 fixes the cause; this is a separate degradation pattern.)
- TaskRecords: ~30k-50k rows/min sustained, ~5 hours for 10.8M rows. Zero errors so this is the steady-state best case for the current code shape.
Hypothesis: cross-region latency dominates
Migrator runs on a developer laptop. Source HANA is in us30, target is in eu10-005, the laptop is somewhere else. Every paginated page does:
SELECT ... WHERE ID > lo AND ID <= hiagainst us30 → ~150-300ms RTT for the read alone- Map rows in Node locally
INSERT INTO ... VALUES (...)batched against eu10-005 → another ~150-300ms RTT
For 216 pages of TaskRecords, that's ~1-2 minutes of pure cross-region latency overhead — each page sequentially blocked on the laptop-as-relay path. The original cutover may have been faster simply by running from a CF task closer to one of the HANA endpoints.
Three optimizations, in expected-impact order
1. Run the migrator as a CF task in the target region (high ROI, medium complexity)
Push a small migrator CF app to eu10-005 / tutorial-system / dev. cf run-task migrator -c "node scripts/migrate-from-hana.js". The cross-region read from us30 still happens, but the write side is intra-region (sub-ms RTT to the bound HANA). Estimated 30-50% speedup.
Caveats:
- Source creds need to be passed via task env (
IMS_HANA_CREDENTIALSalready supports this) - CF task default disk is 512MB; bump to 2GB for the in-memory page maps
- Memory
feedback_cf_target_before_pushapplies — the task would target eu10-005, source-only reads from us30
2. Bump BATCH_SIZE 5000 → 20000 for non-error-prone entities (high ROI, trivial)
Currently scripts/migrate-from-hana.js:145 hard-codes BATCH_SIZE = 5000. For TaskRecords/Users/Steps where batches are reliably clean (no unique-constraint violations expected), 20000-row batches cut round-trip count 4×. HANA's prepared-statement parameter cap is ~32K, so 20000 rows × ~14 columns is well under at 280K params per call — wait, that's over. Need to chunk by columns × rows ≤ 32K. So 20000 × 14 = 280K — over cap. Conservative: BATCH_SIZE = 2200 for 14-column tables, BATCH_SIZE = 4500 for 7-column tables. Or compute per-entity from cols.length.
Actually re-reading the migrator's batchInsert, execBatch(stmt, paramRows) passes ALL paramRows in a single call — hdb chunks internally to fit the protocol. The bottleneck isn't HANA's per-statement cap; it's network round-trips per batch. So BATCH_SIZE = 20000 is fine if hdb's internal chunking handles it. Worth measuring.
3. Parallelize independent entities (medium ROI, higher complexity)
Today entities run serially via for...await in main(). Independent ones could run in parallel:
- Tags, Events, Tutorials, Missions, Groups, Steps, Users — all FK-independent of each other (only depend on Tags + parent links computed earlier)
- Users could run in parallel with Tutorials
- TaskRecords MUST come last (FKs to Users + Steps/Tutorials)
A Promise.all([migrateTags, migrateEvents, migrateUsers]) after the lookup-map phase would shave the Users phase's ~10 min entirely off critical path. Risk: 2-3 simultaneous source connections + the lookup-map preload on the source side; need to verify hdb client thread-safety.
Acceptance (when this gets picked up)
- Add a
PERF_BATCH_SIZEenv override and document it in docs/developers/operations/migration-from-ims.md - Add a
--cf-taskmode (or document the cf push pattern) for the cross-region speedup - Update docs/developers/operations/re-migration-runbook.md with the new wall-clock estimate
- Capture before/after timing in a tracked file under
.migration-data/perf-history/so we have apples-to-apples for the July cutover
Severity
Low priority — current timing is acceptable. July cutover allows for an overnight migration window; 5 hours fits inside that. File this for after #472, #473, #467, #469 ship.
Related: #468 (the corruption fixes that motivated the re-migration), #472 (parameter binding hotfix), #473 (slug-dup tolerance), #467 (schema guardrails).
Activity
perf(migrator): per-entity timing recorder for migrate-from-hana.js (…
jung-thomas commentedon Jun 22, 2026
Prerequisite landed: PR #561 ships the per-entity timing recorder so tomorrow's full DEV re-migration becomes the baseline against which the three optimizations in this issue will be measured.
The four acceptance bullets:
PERF_BATCH_SIZEenv override — *partially_ deferred — the recorder lands first; the env override pairs with Option 2 (BATCH_SIZE bump) when we pick that--cf-taskmode (Option 1).migration-data/perf-history/— done in PR perf(migrator): per-entity timing recorder for migrate-from-hana.js (+ docs-build hotfix) (#474) #561Three optimizations still open; pick after tomorrow's baseline data lands:
BATCH_SIZEbump 5000 → 20000 — pick if per-page durations stay <2s (already network-bound; bigger batches are free), skip if any per-page hits a row-by-row fallbackLeaving open until at least one of the three lands.
jung-thomas commentedon Jun 23, 2026
2026-06-23 update — today's PROD→DEV migration ran ~4× faster than the baseline this issue was written against
Ran a full IMS PROD → DEV migration today as the dress-rehearsal for the July cutover. Captured complete perf data; today's run invalidates the 5-hour baseline this issue assumed.
Headline numbers
Zero errors across both phases. Full per-entity + per-page JSON in
.migration-data/perf-history/:2026-06-23T13-52-48-611Z-unknown.json— reference data run (12.75M rows incl. lookup-map preload, 11.4 min, 18.7k rows/sec overall)2026-06-23T16-41-45-019Z-unknown.json— TaskRecords + downstream run (11.93M rows, 66 min, 3014 rows/sec overall)TaskRecords paginated detail (the bottleneck cited in this issue)
268 pages, 50k-row windows. Page duration stats:
At ~13.7 sec/page × 268 pages = 61 min for TaskRecords alone — matches the totalDurationSec. No tail degradation: last 5 pages averaged 16 sec each (slightly slower from larger sourceRowCount per window, not from MERGE INTO cost growth — total throughput stayed flat at ~2900-2970 rows/sec).
What's different from 2026-06-20?
Migrator code is the same (same
batchInsert, same pagination, same 5000-row batches). The only environmental differences I can identify:teein the output pipeline — earlier sessions today proved Git Bash on Windows drops the migrator's stdout when a pipe goes idle >30s. The clean direct-to-file redirect today removed that overhead.None of these are durable speedups — a cold July cutover would lose (3). But (1) and (2) are operational fixes already in place.
Acceptance criteria status
Add a— not implemented today; would still be nice-to-havePERF_BATCH_SIZEenv overrideAdd a— not implemented; the laptop-as-relay overhead is now ~17 min of the 77 min total, not the 1-2 min/page perf(migrator): potential 2-3x speedup for migrate-from-hana.js (low priority — current timing acceptable) #474 estimated. The CF-task speedup would shave roughly half of that = ~8-10 min, not the 30-50% gain originally projected.--cf-taskmode.migration-data/perf-history/— done; today's two JSONs are the new baseline.Recommendation
Drop the priority further — current timing is now well inside any conceivable July cutover window, and the proposed optimizations would shave maybe 30 min off a 77-min run. The remaining work:
PERF_BATCH_SIZEenv override + CF-task mode + parallel-entity dispatch — still worthwhile if we get bored, but not blocking July.Keep this issue open as enhancement / low-priority. Today's perf data is the new baseline against which future regressions should be measured. If we ever see TaskRecords drop back below ~100k rows/min, that's a signal to reach for the optimizations in this issue.
Reproducibility caveat
The 4× delta over the 2026-06-20 baseline is bigger than I can fully account for. A second clean run (cold cache, single process, no tee) would be needed to confirm 178k rows/min is the durable steady-state vs a today-only anomaly. Not a priority to do now, but worth pinning if we hit a slowdown in the July cutover and want apples-to-apples.