notes-diag-changelog

Apple Notes beachball — root-cause diagnosis (rdmpw3265m)

Session: 2026-08-25 15:55 → 16:45 EDT · claude@rdmpw3265m (Opus 5, 1M context) Type: diagnosis + publication. Diagnosis was read-only; no Notes state was modified.

One-line summary

Apple Notes beachballs on rdmpw3265m are caused by two quadratic write loops inside Notes — not by hardware and not by note count alone — and the write-up is now published to the dev wiki.

What was found

Machine is not the bottleneck: 85% RAM free, zero swap, SMART Verified, 4.8 TB free, no kernel panics, no jetsam kills of Notes. 48 cores idle. The problem is algorithmic.

Library: 13,958 notes · 39,366 attachments · 769 MB NoteStore.sqlite · 5.6 GB media (6.4 GB total).

CRITICAL — unindexed external sort on the main thread. Notes_2026-08-20-175819.diag: 2,147 MB dirtied in 362 s (5.9 MB/s), 156/207 samples on the main dispatch queue. Stack: ICSelectorDelayer → NSObjectController fetchWithRequest: → NSSQLiteConnection performAndWait: → sqlite3_step → sqlite3VdbeSorterWrite → vdbeSorterListToPMA → guarded_pwrite_np (143/156 samples). EXPLAIN QUERY PLAN on the live store confirms USE TEMP B-TREE FOR ORDER BY — all 28 indexes on ZICCLOUDSYNCINGOBJECT are FK/Z_ENT indexes, none covers a sort key. The table is Core Data single-table inheritance: 69,020 rows x 214 columns, 402 MB. Measured: one pass = 0.42 s. So it is not slow once — it runs hundreds of times (387 steps).
CRITICAL — persistent history never pruned. 3,669,453 ACHANGE + 1,187,930 ATRANSACTION rows, oldest 2024-04-03 (container creation date). ~261 MB / 34% of the 769 MB DB is change log. Actual note text is 59 MB. NOT a vacuum problem: LastVacuumDate = 2026-08-22 22:31:43, freelist_count 263 of 194,729 pages. VACUUM does not prune persistent history.
HIGH — Spotlight reindex never completes. ...SpotlightIndexExtension_2026-08-24.diag: 2,151 MB over 7.3 h, 167/180 samples in Data.write(to:) rewriting HTML.index-progress (a 3.7 MB JSON blob) in full per batch — ~580 complete rewrites. OSVersionStringOfLastRun_DidSetReindexEverythingFlagPerUpgrade = "26-1-0" while OS is 26.7, ..._AttemptCounter = 10. Observed churning live with Notes.app not running.
HIGH — 33,836 com.apple.notes.table attachments (86% of all attachments, ~2.4/note).
MEDIUM — 11,592 of 13,918 notes in one folder ("Notes"), sorted on every refresh.
MEDIUM — stale legacy-account refs. DefaultAccount points into the dead 7D8CE8BA IMAP/EWS store; exchangenotesd resident for [email protected]; NotesIndexerState-HTML still pending on EWSAccount/p8. Relevant to hangs on new note creation.

Aside worth recording: ~/scripts/notes_changelog.zsh already carries a comment describing "Apple Notes blocks its main thread on cold start... EVERY AppleEvent returns -1712" on a large store, observed on this host. That wait-loop exists because of pathology #1. This diagnosis explains a workaround the fleet already had.

What changed (state modifications)

Host	Path	Change
rdmpw3265m	`~/dev/rdmpw3265m/reports/notes-hang-diagnosis-20260825.md`	created (report)
rdmpw3265m	`~/dev/rdmpw3265m/reports/notes-hang-diagnosis-20260825.html`	created (write-up)
rdmpw3265m	`~/Desktop/notes-hang-diagnosis-20260825.{html,md}`	created (copies, per request)
rdmsm4x	`~/dataroo.net/wiki/apple-notes-beachball-diagnosis.html`	created (published)

No change to Apple Notes state on any host. All DB reads used file:...?immutable=1 (read-only, WAL untouched). No remediation was applied — every fix in the report is a proposal.

Verification evidence

Wiki origin (nginx in OrbStack on rdmsm4x, port 8787): GET /apple-notes-beachball-diagnosis.html -> 200, 28,897 bytes.
Transfer integrity: sha256 e304ac03c761c654bd1a79708f898a44eb43969efbf8e797addcd44045b6e0cc identical on rdmpw3265m and rdmsm4x.
Page is fully self-contained: zero src=/href= external references.
<title>Apple Notes Beachball — Root Cause</title> parses for the generator's site-map.

Publication mechanics (worth knowing for the next agent)

~/dev/scripts/gen_site.py on rdmsm4x writes ONLY index.html, fleet.html, p/<slug>.html, directory.html, data/status.json. Its orphan sweep deletes only inside p/. Therefore a hand-published page at the wiki ROOT is never overwritten or deleted, and directory_page()'s os.walk auto-links it in the site map. Scheduled by com.eastcoastscience.devsite, StartInterval = 300 — so a dropped root page self-links within 5 minutes with no manual run. (Precedent on disk: claude-remote-control-service-research.html.)

Corrects an earlier impression in this session that the dev.dataroo.net origin was down: nginx is not a host process, it is a container under OrbStack — pgrep nginx finds nothing, but lsof -iTCP:8787 shows OrbStack listening and the origin answers 200.

How to undo

Remove rdmsm4x:~/dataroo.net/wiki/apple-notes-beachball-diagnosis.html; the next 5-minute generator run drops it from directory.html automatically.
Delete the Desktop copies and ~/dev/rdmpw3265m/reports/notes-hang-diagnosis-20260825.*.
Nothing else to reverse — the diagnosis touched no system state.

Outstanding owner actions (Rich)

Decide Tier 1 (all reversible): uncheck Notes in System Settings -> Spotlight; split the 11,592-note folder; use List view instead of Gallery.
Tier 2 is a pref experiment on private, undocumented keys — semantics inferred, not documented. Needs a recorded undo.
Tier 3 (local store rebuild) is the ONLY cure for the 4.86 M history rows and needs explicit go-ahead: 40 "On My Mac" notes are local-only and will not return from iCloud — export first; 6.4 GB re-download.
Confirm whether the Exchange ([email protected]) notes account is still used.