macOS 15.7.9/APFS: How to diagnose slow SQLite quick_check with pread-heavy samples?

Question

Created 1w

Replies 1

Boosts 0

Participants 1

I am seeking advice about supported macOS performance tracing, NOT reporting a confirmed APFS bug. This is a real application failure. AI assisted with organizing this report, and the measurements were reviewed against existing records.

Environment

Apple Silicon Mac Studio, 32 GB RAM, macOS 15.7.9 (24G830).
CPython 3.11.15, linked SQLite 3.50.4.
SQLite database: 3,224,576,000 bytes on an internal APFS volume, journal_mode=DELETE.
The application runs PRAGMA quick_check before starting worker processes. A progress handler cancels the operation after 30 seconds, producing SQLITE_INTERRUPT. This is application-triggered, not proof of corruption.

Observed results

Original database: 30.008 s wall / 5.220 s CPU; a separate run took 30.192 s wall / 5.420 s CPU. Both were interrupted.
A two-second sample showed 95/96 samples on SQLite's checkTreePage → readDbPage → unixRead → pread path. Individual pread latency was not measured.
A transaction-consistent SQLite Backup API snapshot on a different APFS volume completed quick_check in 2.412 s and integrity_check in 8.797 s, both returning "ok".
The snapshot and original were checked at different times with uncontrolled cache and system-load conditions.

Additional mmap experiment

On the same offline snapshot:

A: mmap_size=0, executed first, quick_check took 31.070 s and was interrupted.
B: reported mmap limit approximately 2 GiB, executed second, quick_check returned "ok" after 22.146 s.

Cache conditions and host load were not equivalent, so these observations do NOT establish an mmap performance improvement.

A separate deliberate mapped-I/O error test ended with SIGBUS in an isolated child process. This does not establish an ordinary APFS defect.

Questions

Which supported macOS Instruments templates or APIs can correlate pread offsets, sizes and timings with physical disk latency, filesystem cache activity, memory pressure and thread scheduling, without disabling system security protections?
What minimum set of simultaneous observations could distinguish cache misses, APFS metadata/layout overhead, physical device delays and CPU scheduling contention? How should process read-byte counts be interpreted against physical disk I/O?
How should I safely compare read-only SQLite mmap against normal pread access on disposable copies, without mistaking a warmer second run for a performance gain? What SIGBUS containment and recovery checks would be appropriate?

I am primarily looking for a reliable instrumentation plan and interpretation criteria before conducting further tests. I do not want to modify the original database, weaken integrity checks or bypass security controls.

Answer 1 · 4d

qiji_wj OP

4d

Follow-up: bounded offline kernel/filesystem evidence, not a production root-cause claim

Environment: macOS 15.7.9 (24G830), Apple Silicon, CPython 3.11.15 linked to SQLite 3.50.4. The production database is on internal APFS. Its full read-only PRAGMA quick_check has previously returned ok in 2.038s, and on other occasions our progress handler cancelled it after about 30s (SQLITE_INTERRUPT). A consistent SQLite Backup API snapshot passed quick_check and integrity_check, but that comparison was not cache-controlled.

We performed one separately authorized offline observation of an existing 3,224,576,000-byte database copy on external APFS, not the production file. The SQL, read-only connection options, and 30s cancellation logic were unchanged. fs_usage was filtered to the reader PID and spindump used -onlyTarget.

Important limitation: our capture controller's conservative 48MiB per-stream log cap sent one SIGTERM to the reader after about 10.68s. The overall 256MiB budget was not reached. This was neither completion of quick_check nor reproduction of the 30s cancellation. We will not treat it as either. The database file metadata was unchanged.

Excluding the initial pipe-gate wait, the SQLite execution phase had 534 stackshot samples: 532 included pread, 524 apfs_vnop_read, 522 cluster_read_ext, and 405 lck_mtx_sleep; 7 included runnable state. Sample counts overlap and are not exact elapsed-time categories.

The retained fs_usage prefix recorded 146,085 pread events and 53,570 RdData[A] events associated with the offline database and its external device (about 1.08GB in reported RdData byte counts). Eight fcntl GETLK/SETLK operations together accounted for about 16 microseconds. Instrumentation overhead and writing the trace output on the same external volume are confounders; there is no claim of a cold-cache control or equivalence to the internal production file.

Apple's published xnu-11417.140.69 vfs_cluster.c shows clustered I/O can sleep while issued I/O has not completed. We therefore do not infer a mutex bug merely from lck_mtx_sleep, or infer a failing disk.

Specific questions:

Does this stack pattern support ordinary clustered-read completion waiting, or can the existing stackshot distinguish it from APFS/kernel contention? Several intermediate frames are unsymbolicated. Which conclusions are justified from this existing report alone?
Is there a known supported, mechanism-specific remedy applicable to this evidence that preserves a full current-database quick_check, without changing database contents, bypassing validation, or merely extending a timeout?
If no such remedy is supported, is the technically honest conclusion that this evidence cannot provide a reliable fixed 30s completion guarantee, requiring an application time-policy decision rather than another unsubstantiated performance patch?

No production database, customer records, credentials, raw memory or private paths are attached. No additional capture, cache purge, parameter tuning or production restart is being performed based on this post.