Resident server pegs ~100% CPU and save/flush hangs indefinitely on formula-heavy .xlsx (macOS arm64)

[nightskat]

Summary

On macOS (Apple Silicon), editing an .xlsx that contains many expensive formulas (whole-column SUMIFS, expanding-range COUNTIF, SUMPRODUCT+COUNTIF) makes the auto-spawned __resident-serve__ process peg ~100% CPU and any flush-to-disk operation (save, close, or set under OFFICECLI_RESIDENT_FLUSH=each) hang indefinitely. Recovering by killing the resident loses all unflushed edits.

Environment

• officecli 1.0.132 (Homebrew bottle)
• macOS arm64 (Apple M1, 8 GB RAM)
• .NET runtime (Homebrew dotnet dependency)

Impact

• A plain set returns success but the change does not reach disk within the documented 2–10s idle-autoflush window; an external reader (openpyxl / Excel / LibreOffice) keeps seeing the pre-edit file. officecli get reads from the resident's memory so it does show the new value — which makes it easy to believe the write persisted when it hasn't.
• save / close never return (observed >2 min; SIGKILL required).
• After SIGKILL, the unflushed edit is gone → silent data-loss risk for any "edit then hand the file off" workflow.

Reproduction

Fully synthetic — no external data. Generate the workbook:

import openpyxl
wb = openpyxl.Workbook()
pln = wb.active; pln.title = "PLN"
pln.append(["Branch","PGD","CIF","Ten","Loai","MaHD","DuNo","KyHan"])
for r in range(2, 2002):
    pln.append(["HP","PGD1",f"C{r%300}","KH","INDIV",f"HD{r}",(r*13)%9_000_000_000,
                "NGAN_HAN" if r%2 else "TRUNG_DAI_HAN"])
hd = wb.create_sheet("HD"); hd.append(["CIF","Ten","MaHD","MaHT","SoTien"])
for r in range(2, 502): hd.append([f"C{r%300}","KH",f"HD{r}",f"HT{r}",(r*7)%9_000_000_000])
nt = wb.create_sheet("NOTE"); nt.append(["CIF","Ten","Flag"])
for r in range(2,302): nt.append([f"C{r}","KH","LAM_KINH_DOANH"])
rep = wb.create_sheet("REP"); rep["A1"]="HP"
for r in range(2, 502):
    rep.cell(r,11, f"=HD!$C{r}"); rep.cell(r,10, f"=HD!$A{r}")
    rep.cell(r,2,  f'=IFERROR(INDEX(PLN!$B:$B,MATCH($K{r},PLN!$F:$F,0)),"")')
    rep.cell(r,8,  f'=IF($A$1="","",SUMIFS(PLN!$G:$G,PLN!$F:$F,$K{r},PLN!$C:$C,$J{r},PLN!$A:$A,"HP")/1E6)')
    rep.cell(r,9,  f'=IF($A$1="","",SUMIFS(PLN!$G:$G,PLN!$F:$F,$K{r},PLN!$C:$C,$J{r},PLN!$H:$H,"NGAN_HAN",PLN!$A:$A,"HP")/1E6)')
    rep.cell(r,12, f'=IF($A$1="","",SUMIFS(PLN!$G:$G,PLN!$F:$F,$K{r},PLN!$C:$C,$J{r},PLN!$H:$H,"TRUNG_DAI_HAN",PLN!$A:$A,"HP")/1E6)')
    rep.cell(r,15, f'=IFERROR(VLOOKUP($J{r},NOTE!$A:$C,3,0),"CHUA")')
    rep.cell(r,16, f'=SUMIF(HD!$A:$A,$J{r},HD!$E:$E)/1E6')
qc = wb.create_sheet("QC")
qc["B2"]='=SUMPRODUCT((HD!$A$2:$A$2000<>"")*(COUNTIF(PLN!$F$2:$F$2000,HD!$C$2:$C$2000)=0))'
for r in range(2, 2002):
    qc.cell(r,4, f'=IF(AND(PLN!$F{r}<>"",PLN!$A{r}="HP",PLN!$E{r}="INDIV",PLN!$G{r}>0,COUNTIF(HD!$C:$C,PLN!$F{r})=0),PLN!$F{r},"")')
    qc.cell(r,5, f'=IF($D{r}="","",IF(COUNTIF($D$2:D{r},$D{r})=1,1,0))')
    qc.cell(r,7, f'=IF(AND(HD!$A{r}<>"",SUMIF(HD!$A:$A,HD!$A{r},HD!$E:$E)/1E6>=5,IFERROR(VLOOKUP(HD!$A{r},NOTE!$A:$C,3,0),"CHUA")="CHUA"),HD!$A{r},"")')
    qc.cell(r,8, f'=IF($G{r}="","",IF(COUNTIF($G$2:G{r},$G{r})=1,1,0))')
wb.save("faithful.xlsx")   # ~12,000 formula cells across 5 sheets

Then:

officecli set faithful.xlsx /REP/A1 --prop value="HP"   # returns in <1s, "success"
ps aux | grep __resident-serve__                        # resident now ~93–104% CPU
officecli save faithful.xlsx                            # never returns (>2 min) → had to SIGKILL

Contrast (rules out "too many cells")

A workbook with 11,500 trivial formulas (=IF($A$1="","",A1+1)) does not reproduce it: resident sits at ~0.5% CPU and save is instant. So the trigger is formula evaluation cost (whole-column ranges + O(n²) expanding COUNTIF), not formula count. A small file with the same trivial formula flushes fine within the idle window.

Guess at the cause

It looks like the resident performs a full synchronous recalculation of the whole workbook on flush (or on a polling tick), and for these whole-column / O(n²) formulas that recalc is pathologically slow or re-entrant (CPU stays pegged even while "idle"). Options that would likely help: time-box / make the recalc incremental, or skip recalc-on-flush when the caller only set literal values (no formula edits).

Workaround for other users (until fixed)

For bulk edits to formula-heavy workbooks, write cells with a library that does a deterministic disk write (e.g. openpyxl) and recalculate with an independent engine (e.g. soffice --headless --convert-to). Reserve officecli for small, targeted edits and always save/close + verify with an external reader before handing the file off.

[goworm]

Thanks for the excellent report — the fully synthetic repro plus the trivial-formula contrast made this fast to pin down. Fixed in v1.0.134.

Root cause — your guess was right. Every persist ran a synchronous sweep that re-evaluates all formula cells (it exists to keep cached values honest for cache-trusting readers like openpyxl data_only=True). Three compounding costs in the evaluation path made that sweep effectively unbounded on your workbook: each cross-sheet cell dereference rebuilt a full sheet index, formula cells reached through references were re-evaluated on every reference (combinatorial when formulas aggregate over columns that themselves contain formulas), and the *IFS functions re-flattened their criteria ranges once per row scanned. The sweep runs on the resident's command-serving path, which is why save/close could not even be delivered.

What changed in v1.0.134:

• The evaluation session now memoizes formula-cell results and materialized ranges, and reuses per-sheet evaluators — your workbook's full sweep drops from unbounded (>8 min, killed) to ~2.5s, with all ~12k formula caches written and byte-identical to the unoptimized evaluation.
• The sweep runs under a wall-clock budget (default 5s, tunable via OFFICECLI_FORMULA_SWEEP_BUDGET_SECONDS). If a pathological workbook exhausts it, the sweep stops, sets calcPr fullCalcOnLoad (so Excel/LibreOffice recalculate on open), and the persist proceeds — flush latency is bounded no matter what, so the hang/SIGKILL/data-loss failure mode is gone structurally, not just for this shape of workbook.
• The resident's idle background flush gets a generous budget (60s) but aborts the sweep the moment another command arrives, so nothing ever queues behind it.

Verified against your exact repro script with the released officecli-mac-arm64 1.0.134 binary (macOS arm64):

$ officecli set faithful.xlsx /REP/A1 --prop value="HP"   # 0.7s
$ officecli save faithful.xlsx                            # 2.8s (previously: never returned)
$ python3 -c "import openpyxl; wb=openpyxl.load_workbook('faithful.xlsx', data_only=True); print(wb['REP']['A1'].value, wb['REP']['H2'].value)"
HP 2.6e-05                                                # external reader sees the edit + fresh formula caches

Resident CPU returns to idle after the flush, and the idle autoflush lands on disk within the documented window again.

Your workaround is no longer needed on 1.0.134 — brew upgrade officecli should pick it up. If you still see a slow flush on a bigger real-world workbook, OFFICECLI_FORMULA_SWEEP_BUDGET_SECONDS is the dial, and we'd be interested in that workbook's shape.

[nightskat]

Confirmed fixed on 1.0.134 — thanks for the fast turnaround, and for explaining the root cause.

Verified against my original repro (macOS arm64, Homebrew):

officecli set faithful.xlsx /REP/A1 --prop value="HP"   # 0.66s
officecli save faithful.xlsx                            # 0.24s  (previously: never returned)

An external reader sees the edit plus fresh caches for SUMIFS / INDEX+MATCH / SUMIF. The hang, the SIGKILL, and the data-loss path are all gone. 🎉

---

While verifying I hit one residual issue, in the sweep-abort path you described ("aborts the sweep the moment another command arrives"):

When an incoming command aborts the idle sweep, the workbook is persisted with partial formula caches and no calcPr fullCalcOnLoad.

The budget-exhaustion path sets fullCalcOnLoad so cache-trusting readers know to recalculate. The abort path doesn't — so a partially-swept file is indistinguishable from a fully-swept one, and openpyxl data_only=True silently reads None for the cells the sweep never reached.

Repro

Same faithful.xlsx from the issue body. QC!B2 is a SUMPRODUCT that evaluates to the number 0, so it's a clean signal.

officecli set faithful.xlsx /REP/A1 --prop value="HP"

A — leave it alone ~35s (idle sweep runs to completion):

>>> openpyxl.load_workbook("faithful.xlsx", data_only=True)["QC"]["B2"].value
0          # cached ✅

B — wait 3s, then issue any command (officecli save faithful.xlsx):

"message": "No pending changes for faithful.xlsx"

>>> openpyxl.load_workbook("faithful.xlsx", data_only=True)["QC"]["B2"].value
None       # no cached value ❌

and xl/workbook.xml contains no <calcPr> element in either branch. Leaving the resident alive another 40s does not repair it — the partial cache is permanent on disk.

Suggested fix

Have the abort path set calcPr fullCalcOnLoad before persisting, exactly as the budget-exhaustion path does. Optionally, save could report that the sweep was truncated instead of "No pending changes", which currently reads as a clean success.

Severity

Much lower than the original hang — Excel and LibreOffice recalculate on open either way, so a human never sees a wrong number. It only bites automated readers that trust the cached values.

_{One note for anyone reading this later: QC!D2 and QC!E2 also come back as None, but those formulas genuinely evaluate to the empty string, and openpyxl surfaces "" results as None. That's expected and not part of this report — QC!B2 is numeric, which is why it's the reliable signal.}

[goworm]

Good catch — confirmed, and fixed for the next release.

What reproduced:

• ✅ A command arriving mid-sweep aborts it and the flush persists partially-refreshed caches.
• ✅ It never healed: the flush cleared the resident's dirty flag, so later idle windows never re-swept, and an explicit save clean-skipped with "No pending changes" — the partial state was permanent, exactly as you described.

The fix (self-healing rather than just marking): a yield-interrupted sweep now leaves the resident's dirty flag set. The next idle window saves again and re-runs the sweep — so the file converges to fully swept as soon as one idle window survives uninterrupted (measured: interrupted flush leaves QC!B2 uncached, ~6s later the healing re-sweep fills it). The retained dirty flag also means an explicit save after an interrupted flush performs a real save (with its own sweep) instead of reporting "No pending changes".

One deliberate asymmetry: only yield-interrupted sweeps retry. Budget-exhausted sweeps don't — retrying a sweep the budget can't fit would burn a full budget every idle cycle for nothing, and fullCalcOnLoad is the terminal answer for that case.

One part we could not reproduce: in our runs the interrupted flush does write <calcPr fullCalcOnLoad="1"/> into xl/workbook.xml (the yield abort exits through the same path as budget exhaustion, which sets it — and on your openpyxl-generated file, the fill-missing-cache branch sets it as well, from the very first formula cell swept). If you still see a workbook.xml with no <calcPr> at all on 1.0.134, could you share the exact sequence (and whether the file was regenerated fresh between branches A and B)? There may be a path we're not seeing.

The fix will ship in the next release; I'll note here when it's out.