Summary
GET /api/v1/server/statistics/totals and /statistics/media compute their counts with synchronous better-sqlite3 queries on the main thread, and cache nothing. On a large chat.db, each COUNT(*) takes seconds; because the driver is synchronous it blocks the Node event loop for that entire time, stalling every other in-flight HTTP request — including the socket health/reachability probe. Clients interpret the missed probe as the server being unreachable and tear down their socket (then reconnect, often re-triggering the same call). On a small DB it's invisible; it scales badly with message history.
Where (refs @ 88a4921, development)
The iMessage DataSource uses the synchronous better-sqlite3 driver:
The counts are plain SELECT COUNT(*) via TypeORM .getCount(), awaited sequentially:
ServerInterface awaits these directly and caches nothing:
Mechanism
better-sqlite3 is synchronous: the query runs to completion on the calling (main) thread before the await resolves. So a multi-second COUNT(*) over a large table blocks the event loop entirely — GET /, ping, and the socket transport all wait behind it. The client's Server Management panel calls statistics/totals on open, so just opening it can trigger the stall.
Repro / scaling note
On a small DB this isn't visible. On my server (~4,400 messages) statistics/totals and /media return in 2–5 ms, and a concurrent GET / stays ~2 ms during them. The concern is the O(rows) synchronous count on large histories; reports of clients stuck "connecting" / dropping the socket are consistent with this pattern.
Proposed fix
- Cache
getDatabaseTotals / getMediaTotals in memory with a short TTL (~30–60 s) and/or refresh on a background timer, so the HTTP handler returns instantly instead of recomputing per request.
- And/or move the counts off the main thread — a
worker_thread (or a dedicated read-only better-sqlite3 connection in a worker) so even the refresh never blocks the event loop.
- And/or cheaper counts —
MAX(ROWID) as an O(1) approximation, or sqlite_stat-based estimates, to avoid full B-tree scans where an approximate/maintained count is acceptable.
Caching alone makes the request path instant; combining it with an off-thread refresh guarantees the loop never blocks even on the first/refresh computation.
Related
Sibling case of the same root pattern (synchronous main-thread work blocking the socket): #811, where the blocking work is per-contact avatar loading in GET /api/v1/contact?extraProperties=avatar. The same class of fix (cache / off-thread) applies.
Environment: server 1.9.9, macOS 15.7.7 (24G720), Messages 14.0.
Summary
GET /api/v1/server/statistics/totalsand/statistics/mediacompute their counts with synchronousbetter-sqlite3queries on the main thread, and cache nothing. On a largechat.db, eachCOUNT(*)takes seconds; because the driver is synchronous it blocks the Node event loop for that entire time, stalling every other in-flight HTTP request — including the socket health/reachability probe. Clients interpret the missed probe as the server being unreachable and tear down their socket (then reconnect, often re-triggering the same call). On a small DB it's invisible; it scales badly with message history.Where (refs @
88a4921,development)The iMessage
DataSourceuses the synchronousbetter-sqlite3driver:databases/imessage/index.ts#L37-L42—type: "better-sqlite3"The counts are plain
SELECT COUNT(*)via TypeORM.getCount(), awaited sequentially:getAttachmentCount/getChatCount/getHandleCount(index.ts#L774-L793)getMessageCount(index.ts#L471)getMediaCounts(index.ts#L636)ServerInterfaceawaits these directly and caches nothing:getDatabaseTotals(serverInterface.ts#L6-L30)getMediaTotals(serverInterface.ts#L32-L53)Mechanism
better-sqlite3is synchronous: the query runs to completion on the calling (main) thread before theawaitresolves. So a multi-secondCOUNT(*)over a large table blocks the event loop entirely —GET /,ping, and the socket transport all wait behind it. The client's Server Management panel callsstatistics/totalson open, so just opening it can trigger the stall.Repro / scaling note
On a small DB this isn't visible. On my server (~4,400 messages)
statistics/totalsand/mediareturn in 2–5 ms, and a concurrentGET /stays ~2 ms during them. The concern is the O(rows) synchronous count on large histories; reports of clients stuck "connecting" / dropping the socket are consistent with this pattern.Proposed fix
getDatabaseTotals/getMediaTotalsin memory with a short TTL (~30–60 s) and/or refresh on a background timer, so the HTTP handler returns instantly instead of recomputing per request.worker_thread(or a dedicated read-onlybetter-sqlite3connection in a worker) so even the refresh never blocks the event loop.MAX(ROWID)as an O(1) approximation, orsqlite_stat-based estimates, to avoid full B-tree scans where an approximate/maintained count is acceptable.Caching alone makes the request path instant; combining it with an off-thread refresh guarantees the loop never blocks even on the first/refresh computation.
Related
Sibling case of the same root pattern (synchronous main-thread work blocking the socket): #811, where the blocking work is per-contact avatar loading in
GET /api/v1/contact?extraProperties=avatar. The same class of fix (cache / off-thread) applies.Environment: server 1.9.9, macOS 15.7.7 (24G720), Messages 14.0.