SQLite cleanup: missing cache_entries(locationId) index causes SQLITE_BUSY and liveness restarts

[chenrui333]

Summary

On v9.7.0, the periodic cleanup:storage-locations task becomes CPU-bound on a production-sized SQLite database because cache_entries.locationId is not indexed. The task is scheduled at the same time as cleanup:parts; under load we observed SQLITE_BUSY, cache data-plane lock failures, health timeouts, and repeated Kubernetes restarts of the only replica.

This appears to be a different SQLite-specific cause from #175 / #177. The cleanup rewrite removed the older unbounded deletion path, but the current correlated NOT EXISTS query still performs a full cache_entries scan for each storage_locations row.

Environment

• github-actions-cache-server: 9.7.0
• Helm chart: 1.1.3
• database: SQLite on a single-writer ReadWriteOnce volume
• storage: S3
• deployment: one Kubernetes replica, maxSurge: 0
• database scale: tens of thousands of storage_locations rows
• current dev checked at b7f06e0

No organization, cluster, bucket, repository, cache key, or workload identifiers are included here.

Observed behavior

At the 10-minute cleanup boundary:

1. cleanup:parts and cleanup:storage-locations start together.
2. cleanup:storage-locations spends roughly 30-35 seconds CPU-bound in the correlated query and later fails a transaction with SQLITE_BUSY.
3. Cache write and lease-update paths also report database-locked failures.
4. Both readiness and liveness time out. Even with a 30-second timeout and failure threshold 6, six consecutive probes failed over roughly eight minutes and kubelet restarted the only replica.
5. The behavior repeated after a clean restart at the next cleanup boundary. The Kubernetes node itself was not CPU constrained.

Setting DISABLE_CLEANUP_JOBS=true stopped the cleanup lock/stall behavior and kept health and data-plane requests responsive. That is only a temporary workaround because retention and orphan cleanup are then deferred.

Query-plan evidence

The current task executes the equivalent of:

select sl.folderName, sl.id
from storage_locations sl
where not exists (
  select ce.id
  from cache_entries ce
  where ce.locationId = sl.id
)
limit 10;

On the v9.7.0 schema, EXPLAIN QUERY PLAN reports:

SCAN sl
CORRELATED SCALAR SUBQUERY
SCAN ce

The migrations create indexes for cache key/version, scope, and repo ID, but not cache_entries.locationId. After adding a test-only index, the same plan changes to an indexed lookup:

SEARCH ce USING COVERING INDEX idx_cache_entries_locationId (locationId=?)

The current code is here:

• https://github.com/falcondev-oss/github-actions-cache-server/blob/b7f06e092177b27112c36f34847c7ca47e5471aa/tasks/cleanup/storage-locations.ts
• https://github.com/falcondev-oss/github-actions-cache-server/blob/b7f06e092177b27112c36f34847c7ca47e5471aa/lib/migrations.ts

• github-actions-cache-server/nitro.config.ts

  Line 28 in b7f06e0

  '*/10 * * * *': ['cleanup:parts', 'cleanup:storage-locations'], // every 10 minutes

Expected behavior

Periodic cleanup should remain bounded and should not make /health or cache data-plane routes unavailable. Concurrent cleanup tasks should not contend with each other or with cache writes for long enough to trigger pod restarts.

Suggested fix

At minimum, add a schema migration for an index on cache_entries(locationId) and a regression test that exercises the storage-location cleanup query at realistic row counts. It may also be worth serializing the two 10-minute cleanup tasks or bounding/yielding their SQLite work so cleanup cannot monopolize the single process.

Related:

• Cleanup job blocks event loop, causing liveness probe failures in Kubernetes #175 / feat: major rework #177: older cleanup event-loop blocking report and rewrite
• SQLite: select from syntax error in storage-locations cleanup task crashes server #212: SQLite select from syntax fix for the same storage-location cleanup task