Summary
On a LAPI running v1.7.8 with the SQLite backend (WAL enabled), a single GET /v1/alerts request can get stuck inside sqlite3_step() indefinitely (3h+ and counting in my case), pinning one CPU core at 100%. Because the work happens in the cgo SQLite call, it is invisible to the Go CPU profiler (/debug/pprof/profile returns an essentially empty profile) and does not show as a running goroutine in a goroutine dump — it appears as a goroutine parked in [syscall]. The stuck query holds the connection and the DB stops being written to, so the LAPI effectively wedges while looking "healthy" (heartbeats and /health keep responding on other connections).
This is distinct from #4464 (which is a steady ~2x baseline CPU increase from the 1.7.8 decisions-stream change, scaling with bouncer count and visible in pprof). This report is about a single query that never returns and burns a whole core in native SQLite code.
Environment
- CrowdSec v1.7.8-63227459 (docker), Go 1.26.3, libre2 (
cscli version confirms)
- Deployed via the official Helm chart on k3s (LAPI as a separate pod from agents)
- DB backend:
type: sqlite, db_path: /var/lib/crowdsec/data/crowdsec.db, use_wal: true, flush max_items: 5000, max_age: 7d
- DB file size: 11.5 MB (small)
- A handful of bouncers (traefik plugin) + 3 agents + a web UI/dashboard client polling
/v1/alerts
- CAPI enabled —
cscli alerts list shows community-blocklist alerts each carrying very large decision sets (e.g. +2397/-0 IPs, +900/-0 IPs)
Symptom
- One core pinned at ~100% (cgroup CPU jumped from ~0.004 cores to a flat 1.00 core) starting at a precise instant and never recovering.
- The hot OS thread sits in
R state and has accumulated ~96 min of CPU time and rising.
- DB file mtime froze at the exact moment the spin began;
cscli decisions list returns nothing while the query holds the engine.
- Event throughput is unchanged/idle (~20 log lines/min) — the spin is not correlated with parsing/acquisition load.
- A 15-second
curl /debug/pprof/profile returns only ~540 bytes (negligible samples) despite the core being saturated — consistent with time being spent in the cgo SQLite call, which SIGPROF cannot interrupt.
Evidence — goroutine dump (/debug/pprof/goroutine?debug=2)
The handler goroutine, blocked in rows.Next() for 182 minutes, with the full ent → SQLite path showing the source is FindAlerts / QueryAlertWithFilter:
goroutine 8974 [select, 182 minutes]:
github.com/mattn/go-sqlite3.(*SQLiteRows).Next(...)
github.com/mattn/go-sqlite3@v1.14.44/sqlite3.go:2535
database/sql.(*Rows).Next(...)
entgo.io/ent/dialect/sql/sqlgraph.(*query).nodes(...)
entgo.io/ent@v0.14.6/dialect/sql/sqlgraph/graph.go:1012
github.com/crowdsecurity/crowdsec/pkg/database/ent.(*AlertQuery).sqlAll(...)
github.com/crowdsecurity/crowdsec/pkg/database/ent/alert_query.go:509
github.com/crowdsecurity/crowdsec/pkg/database/ent.(*AlertQuery).All(...)
github.com/crowdsecurity/crowdsec/pkg/database/ent/alert_query.go:266
github.com/crowdsecurity/crowdsec/pkg/database.(*Client).QueryAlertWithFilter(...)
github.com/crowdsecurity/crowdsec/pkg/database/alerts.go:855
github.com/crowdsecurity/crowdsec/pkg/apiserver/controllers/v1.(*Controller).FindAlerts(...)
github.com/crowdsecurity/crowdsec/pkg/apiserver/controllers/v1/alerts.go:299
... gin middleware chain ...
The actual cgo call, parked in [syscall] for the same 182 minutes (this is the thread burning the core):
goroutine 69682 [syscall, 182 minutes]:
github.com/mattn/go-sqlite3._Cfunc__sqlite3_step_internal(...)
_cgo_gotypes.go:443
github.com/mattn/go-sqlite3.(*SQLiteRows).nextSyncLocked(...)
github.com/mattn/go-sqlite3@v1.14.44/sqlite3.go:2552
created by github.com/mattn/go-sqlite3.(*SQLiteRows).Next in goroutine 8974
github.com/mattn/go-sqlite3@v1.14.44/sqlite3.go:2531
All parser/bucket/output routines were idle in [select] the whole time, confirming the LAPI is otherwise doing nothing.
Likely trigger
The request is a recurring GET /v1/alerts?limit=0&since=24h poll (from a dashboard/web-UI client). My strong suspicion is that QueryAlertWithFilter eager-loads the decisions edge for matched alerts, and with CAPI community-blocklist alerts that each contain thousands of decisions, the ent-generated SQLite query degenerates into a pathological plan. A full scan of an 11.5 MB DB should take milliseconds; 3 hours of CPU implies effectively quadratic/exponential behavior (e.g. a large IN (...) edge load), not slow I/O (the thread is CPU-bound R, never in D).
Why this is a bug (not just "a slow query")
- 3h+ of CPU on an 11.5 MB database is not a tuning problem — the plan is degenerate.
- There is no statement/query timeout, so a single API request can wedge the LAPI DB connection indefinitely and silently (the request never logs because it never completes).
- The condition is reachable by a normal, repeating read endpoint that ships with the product and is hit by every dashboard/web-UI.
Workaround
Restart the LAPI pod — CPU immediately drops back to baseline. (Reproduces again once the offending /v1/alerts poll hits the degenerate plan.)
Asks
- Bound
QueryAlertWithFilter / FindAlerts (avoid unbounded eager-load of the decisions edge; paginate or cap edge loading).
- Add a server-side statement timeout for LAPI DB queries so one request can't pin a core forever.
Happy to provide the full goroutine dump or test a patched build.
Summary
On a LAPI running v1.7.8 with the SQLite backend (WAL enabled), a single
GET /v1/alertsrequest can get stuck insidesqlite3_step()indefinitely (3h+ and counting in my case), pinning one CPU core at 100%. Because the work happens in the cgo SQLite call, it is invisible to the Go CPU profiler (/debug/pprof/profilereturns an essentially empty profile) and does not show as arunninggoroutine in a goroutine dump — it appears as a goroutine parked in[syscall]. The stuck query holds the connection and the DB stops being written to, so the LAPI effectively wedges while looking "healthy" (heartbeats and/healthkeep responding on other connections).This is distinct from #4464 (which is a steady ~2x baseline CPU increase from the 1.7.8 decisions-stream change, scaling with bouncer count and visible in pprof). This report is about a single query that never returns and burns a whole core in native SQLite code.
Environment
cscli versionconfirms)type: sqlite,db_path: /var/lib/crowdsec/data/crowdsec.db,use_wal: true, flushmax_items: 5000,max_age: 7d/v1/alertscscli alerts listshows community-blocklist alerts each carrying very large decision sets (e.g.+2397/-0 IPs,+900/-0 IPs)Symptom
Rstate and has accumulated ~96 min of CPU time and rising.cscli decisions listreturns nothing while the query holds the engine.curl /debug/pprof/profilereturns only ~540 bytes (negligible samples) despite the core being saturated — consistent with time being spent in the cgo SQLite call, which SIGPROF cannot interrupt.Evidence — goroutine dump (
/debug/pprof/goroutine?debug=2)The handler goroutine, blocked in
rows.Next()for 182 minutes, with the full ent → SQLite path showing the source isFindAlerts/QueryAlertWithFilter:The actual cgo call, parked in
[syscall]for the same 182 minutes (this is the thread burning the core):All parser/bucket/output routines were idle in
[select]the whole time, confirming the LAPI is otherwise doing nothing.Likely trigger
The request is a recurring
GET /v1/alerts?limit=0&since=24hpoll (from a dashboard/web-UI client). My strong suspicion is thatQueryAlertWithFiltereager-loads thedecisionsedge for matched alerts, and with CAPI community-blocklist alerts that each contain thousands of decisions, the ent-generated SQLite query degenerates into a pathological plan. A full scan of an 11.5 MB DB should take milliseconds; 3 hours of CPU implies effectively quadratic/exponential behavior (e.g. a largeIN (...)edge load), not slow I/O (the thread is CPU-boundR, never inD).Why this is a bug (not just "a slow query")
Workaround
Restart the LAPI pod — CPU immediately drops back to baseline. (Reproduces again once the offending
/v1/alertspoll hits the degenerate plan.)Asks
QueryAlertWithFilter/FindAlerts(avoid unbounded eager-load of the decisions edge; paginate or cap edge loading).Happy to provide the full goroutine dump or test a patched build.