A 600k-row queue slowed our Mac to a crawl

Our Mac, a 10-core machine that runs local models and a stack of background jobs, was barely answering. The cause turned out to be a job queue that had grown to 605,034 rows in about 16 hours, and a claim query that sorted every one of them into a temp file each time a worker asked for work. This is how we found it, what we changed, and the part we still cannot prove.

What slow looked like

The machine was not frozen, just drowning. Every reading was well past its normal line:

Two lines stood out. fseventsd is the macOS daemon that turns filesystem changes into events for anything watching a folder, and it should sit near idle. kernel_task climbing past one full core usually means the kernel is busy on someone else’s behalf. Something was touching the disk very hard.

The wrong first suspect

A busy fseventsd and a hot disk point straight at Spotlight, so that went first.

Swap easing was a real improvement, which made the guess feel right for a moment. But the daemon we were worried about did not move, so whatever fed it was still running.

Counting the filesystem calls

Guessing had stopped working, so we measured. fs_usage prints every filesystem call on the machine as it happens; run as root with a filesystem filter, a short sample says who is doing what:

# 25-second sample of every filesystem call, wide output
sudo fs_usage -w -f filesys > fsu.txt   # stop it after 25 seconds

That sample held about 2.03M filesystem calls, roughly 80k every second. Grouping the write-type calls by process gave one clear answer:

etilqs_ is the prefix SQLite gives its temporary files; it is “sqlite” backwards. An unlinked temp file of that size means SQLite was spilling a big intermediate result to disk, again and again. The next question was which query.

Why a queue claim wrote 43 MB

The runner pulls work from a SQLite queue. A job-board fill had queued 605,034 jd-fetch rows, plus 3,513 jd-read rows, in about 16 hours. They were not duplicates: each was one distinct posting URL. Nothing capped how many could sit open.

Each worker claims its next job with a query that picks by tier. The tier order lived in an ORDER BY CASE expression, and SQLite cannot walk an index in the order of a CASE. The (state, tier, added_at) index was there and unused, so every claim did this:

-- the shape of the old claim, simplified
SELECT id FROM queue
WHERE state = 'open'
ORDER BY CASE tier WHEN ... THEN ... END, added_at
LIMIT 1;
-- no index matches the CASE order, so SQLite sorts every open row
-- into a temp b-tree before it can return the first one

On a small queue that sort is cheap. With about 608k rows it became a fresh temp file of about 43 MB, written about every 17 seconds, by a new process each time.

The fixes

The rewritten claim is the durable fix: the cost of finding the next job no longer depends on how long the queue is.

-- the shape of the new claim: one indexed probe per tier, in tier order
SELECT id FROM queue
WHERE state = 'open' AND tier = :tier
ORDER BY added_at
LIMIT 1;
-- run for the first tier, then the next, until one returns a row

After

Once the queue was cleared, capped and claimed on the index, and fseventsd restarted, the machine came back:

The queue fixes went in first. The reading that dropped fseventsd from 90.6% to 0.9% came right after the restart, and the restart is what cleared the load.

Verdict

A queue that only ever grows and a claim query that sorts the whole queue are each harmless when the queue is small. Together, at about 608k rows, they made a background worker write a 43 MB temp file about every 17 seconds, and the Mac spent its day keeping up. Spotlight was the obvious suspect and the wrong one; one fs_usage sample found the real writer in 25 seconds.

If your Mac is slow and fseventsd is busy, count the filesystem calls before you turn things off. If a SQLite queue shows up, check that its claim can use an index, and give it a cap. And if fseventsd stays high after the writer is gone, a restart may be what finally clears it, as it was here.

Check your own queue: read SQLite’s EXPLAIN QUERY PLAN guide to see whether your claim query uses an index, and how the query planner picks one.