With #225, pixa's SQLite database has one connection, so every query a request makes waits while the eviction pass runs one of its own (found in that PR's review, #225). Some of the eviction pass's queries read whole tables:
UsageBytes (internal/imgcache/eviction.go) sums size_bytes over both content tables at the start of every pass, and every store wakes a pass;
the reconciliation pass reads every variant key and every source hash, one query each (allVariantKeys, allSourceContentHashes).
On a cache of a few hundred thousand transformed images, each of these takes long enough that requests stall behind it, and under steady misses the stalls repeat.
Definition of done
No request waits for a query that reads a whole table, however large the cache is.
Recommended: keep the cache's total size as a running total, updated by the same statements that add and remove content rows, instead of summing both tables on every pass. Run the reconciliation reads in pages of a bounded size, each its own short query.
The running total stays correct: the reconciliation pass checks it against a full sum, in pages, and corrects it.
Tests, each failing first: the eviction pass does not sum the tables on each pass; a request's query does not wait for a whole reconciliation read.
README.md no longer says requests wait while the eviction pass runs its queries, if #225 added that.
Model: opus-5-5
With https://git.eeqj.de/sneak/pixa/pulls/225, pixa's SQLite database has one connection, so every query a request makes waits while the eviction pass runs one of its own (found in that PR's review, https://git.eeqj.de/sneak/pixa/pulls/225). Some of the eviction pass's queries read whole tables:
- `UsageBytes` (`internal/imgcache/eviction.go`) sums `size_bytes` over both content tables at the start of every pass, and every store wakes a pass;
- the reconciliation pass reads every variant key and every source hash, one query each (`allVariantKeys`, `allSourceContentHashes`).
On a cache of a few hundred thousand transformed images, each of these takes long enough that requests stall behind it, and under steady misses the stalls repeat.
## Definition of done
- No request waits for a query that reads a whole table, however large the cache is.
- Recommended: keep the cache's total size as a running total, updated by the same statements that add and remove content rows, instead of summing both tables on every pass. Run the reconciliation reads in pages of a bounded size, each its own short query.
- The running total stays correct: the reconciliation pass checks it against a full sum, in pages, and corrects it.
- Tests, each failing first: the eviction pass does not sum the tables on each pass; a request's query does not wait for a whole reconciliation read.
- `README.md` no longer says requests wait while the eviction pass runs its queries, if https://git.eeqj.de/sneak/pixa/pulls/225 added that.
Model: opus-5-5
clawbot
self-assigned this 2026-10-08 03:58:17 +02:00
Built in #228: the total cache usage is kept in a cache_usage row by triggers on the two content tables; the reconciliation pass reads those tables 1000 rows per query and corrects the total from their sum; the source eviction candidate query, which also read the whole table, now uses its index. The PR body has the details.
Model: opus-5-5
Built in https://git.eeqj.de/sneak/pixa/pulls/228: the total cache usage is kept in a `cache_usage` row by triggers on the two content tables; the reconciliation pass reads those tables 1000 rows per query and corrects the total from their sum; the source eviction candidate query, which also read the whole table, now uses its index. The PR body has the details.
Model: opus-5-5
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
With #225, pixa's SQLite database has one connection, so every query a request makes waits while the eviction pass runs one of its own (found in that PR's review, #225). Some of the eviction pass's queries read whole tables:
UsageBytes(internal/imgcache/eviction.go) sumssize_bytesover both content tables at the start of every pass, and every store wakes a pass;allVariantKeys,allSourceContentHashes).On a cache of a few hundred thousand transformed images, each of these takes long enough that requests stall behind it, and under steady misses the stalls repeat.
Definition of done
README.mdno longer says requests wait while the eviction pass runs its queries, if #225 added that.Model: opus-5-5
Built in #228: the total cache usage is kept in a
cache_usagerow by triggers on the two content tables; the reconciliation pass reads those tables 1000 rows per query and corrects the total from their sum; the source eviction candidate query, which also read the whole table, now uses its index. The PR body has the details.Model: opus-5-5