Bug: Unbounded SQLite knowledge graph nodes table causes progressive 184% CPU freeze (was: heartbeat thread leak)

[Dvalin21]

Issue #6 - ROOT CAUSE CORRECTION

Bug: AgentRuntime heartbeat threads leak from MCP server cache eviction causing thread explosion and 90% CPU spin

Repo: RyjoxTechnologies/Octopoda-OS

---

⚠️ CRITICAL UPDATE: The original root cause was INCORRECTLY DIAGNOSED

The original issue blamed heartbeat thread leaks for the 90% CPU spin. That was a symptom, not the root cause. After extensive debugging across two sessions (including a full system freeze), the actual root cause was found.

---

ACTUAL ROOT CAUSE: Unbounded SQLite knowledge graph nodes table

The nodes table in ~/.synrix/data/synrix.db grew to 4,145,862 rows / 3.86 GB (plus a 2.2 GB WAL file = 6 GB total).

Every single Octopoda operation — remember(), heartbeat writes, daemon event tracking, metrics — creates a new row in the nodes table via SynrixSQLiteClient.add_node(). The add_node() method NEVER overwrites in place. It always marks the old version as valid_until=now and inserts a new row with an incremented version number. Old versions accumulate forever.

Growth mechanism

Every write → add_node() → INSERT new row, UPDATE old row as invalid
                            ↓
                    Old rows NEVER deleted
                            ↓
                    nodes table: 4,145,862 rows

Production database snapshot

Key prefix	Row count	Description
runtime:agents:*	2,202,903	Heartbeats + state + timestamps — highest volume
runtime:events:*	1,782,490	Crash events, agent state transitions
agents:*	343	Actual user memories
Other	160,126	Metrics, audit, shared memory, alerts

Growth rate: ~12,000 nodes/hour (~3.3 writes/second)

Why the previous fix didn't work

The thread leak fix (cache eviction + shared heartbeat) stopped thread count growth. But the database was ALREADY 4 GB. The 5+ daemon background threads all call query_prefix("runtime:agents:*", 500) which scans the nodes table. With 4M rows, each query takes seconds. Threads pile up on SQLite _write_lock. CPU spirals. System freezes.

The real chain of failure

1. Heartbeat writes every 15s → new node version (never GC'd)
2. Daemon background threads (5+) query growing table:
   - heartbeat_monitor: every 3s → query_prefix scans 4M rows
   - anomaly_detector: every 5s → query_prefix scans 4M rows
   - metrics_aggregator: every 10s → query_prefix scans 4M rows
   - recovery_watchdog: every 5s → query_prefix scans 4M rows
   - cold_start_recovery: once → query_prefix scans 4M rows
3. Each query takes SECONDS with 4M rows
4. Threads pile up on SQLite _write_lock
5. CPU spirals to 184%+ → system freezes

NONE of this was a thread leak. Even with the shared heartbeat fix, the CPU freeze persisted because the 4 GB database was never addressed.

---

Architecture root causes

1. SynrixSQLiteClient.add_node() (sqlite_client.py:413)

• Always creates a NEW version by hashing {collection}:{name}:v{version}
• Sets old version's valid_until=now but never deletes it
• Every heartbeat write to the SAME key creates a new row
• No MAX_VERSIONS_PER_KEY limit

2. GarbageCollector.run_gc() (core/gc.py)

• Only cleans: metrics:, runtime:events:, alerts:, audit:, snapshots
• DOES NOT clean runtime:agents:* — the highest-volume prefix
• Default interval: 6 hours
• Even when it runs, it uses delete_prefix_before() which only hits the KV store, not the nodes table

3. The daemon starts 5+ background threads (core/daemon.py)

self._start_thread("heartbeat_monitor", self._heartbeat_monitor_loop)
self._start_thread("anomaly_detector", self._anomaly_detector_loop)
self._start_thread("metrics_aggregator", self._metrics_aggregator_loop)
self._start_thread("recovery_watchdog", self._recovery_watchdog_loop)
self._start_thread("garbage_collector", self._gc_loop)

• ALL call query_prefix("runtime:agents:*", 500) or similar scans
• Original intervals: 3s, 5s, 10s, 5s, 6h
• None have backpressure or rate limiting

4. FTS5 index grows unboundedly with nodes

• nodes_fts is a standalone FTS5 table (not external content)
• _sync_fts only handles individual node updates, not bulk operations
• When nodes are bulk-deleted from nodes, the FTS content table retains stale entries (4.2M entries for 133K current nodes)
• REBUILD command doesn't delete stale content rows — only the segment index

---

Fixes applied locally

All in /home/keith/.local/bin/octopoda-http.py:

Database maintenance

• Startup prune: background thread deletes runtime:agents:*, runtime:events:*, metrics:* nodes older than 1h
• Periodic maintenance: same prune every 30 minutes
• Non-blocking: runs in daemon thread, server starts immediately

Daemon loop intervals (monkey-patched at import time)

Loop	Before	After	Reduction
heartbeat_monitor	3s	15s	5x
anomaly_detector	5s	30s	6x
metrics_aggregator	10s	60s	6x
recovery_watchdog	5s	30s	6x
heartbeat timeout	10s	120s	12x

Standalone cleanup script

/home/keith/.local/bin/octopoda-clean-db.py — for manual emergency use

---

Results after cleanup

Metric	Before	After
Total nodes	4,145,862	133,269
DB file size	3.86 GB + 2.2 GB WAL	79 MB
CPU (idle)	184%+ (spiraling)	7-8% (stable)
Threads spinning	4 threads at 54-88% CPU	All sleeping on futex
RSS	~132 MB	113 MB
MCP responsiveness	Timeout/dead	Milliseconds

---

Upstream fixes required

1. add_node() must GC old versions

• Add max_versions_per_key parameter (default: 5)
• When exceeded, DELETE oldest versions instead of just marking valid_until

2. Add runtime:agents:* to GC scope

• The GC cleans metrics:, runtime:events:, alerts:, audit:, snapshots
• But NOT runtime:agents:* — the highest-volume prefix (2.2M rows)
• Heartbeat keys older than 1 hour have ZERO value

3. Daemon background threads need backpressure

• 3-5 second polling on SQLite is aggressive
• Make intervals configurable via env vars
• Add jitter to prevent thundering herd

4. FTS5 should use external content or have bulk-sync

• nodes_fts is standalone → content table grows unboundedly
• Option A: Use FTS5 content=nodes, content_rowid=id external content table
• Option B: Add rebuild_fts_after_vacuum to GC

5. WAL management

• WAL file grew to 2.2 GB alongside 3.9 GB main file
• Need PRAGMA wal_auto_checkpoint=1000 and periodic PRAGMA wal_checkpoint(TRUNCATE)

[Dvalin21]

Session 2026-05-07: Additional fixes applied

What was done

Follow-up to the original fix. Applied three additional protections to prevent re-accumulation of stale version rows:

1. SQLite Auto-Prune Triggers (permanent, survives package upgrades)

Installed two triggers directly on the nodes table:

trg_nodes_prune_old_versions (AFTER INSERT):

• When a new version is inserted, deletes old versions of the same key beyond the last 10
• Condition: WHERE version <= NEW.version - 10
• Also deletes corresponding stale FTS entries in the same trigger body

trg_nodes_prune_fts (AFTER DELETE):

• Cleans up FTS entries when any node row is deleted by the INSERT trigger

These triggers live in the database schema, not in application code. They survive pip install --upgrade and any code changes. Verified: 12 sequential inserts produce exactly 10 retained versions (configurable by editing the trigger).

2. Weekly Cron Safety Net

0 3 * * 0 python3 octopoda_prune.py

Runs the existing prune script every Sunday. With the trigger keeping bloat in check dynamically, this is a safety net with negligible cost (finds 0 stale rows, exits fast).

3. Prune Script Updated

The prune script now includes trigger definitions in its schema, so a full database rebuild preserves the triggers.

Impact

• Nodes table: 135,264 rows down to 31,838 (only latest version of each key)
• DB on disk: 86.9 MB down to 43.4 MB
• Health check latency: 76 ms to 37 ms
• Loop check latency: 143 ms to 63 ms
• 103,426 stale version rows removed (76% of all rows were garbage)

Upstream fixes still required

The trigger is a containment fix. The architectural issues from the original issue still need upstream changes:

1. add_node() should UPDATE in-place when data has not materially changed (heartbeat writes, timestamp-only updates)
2. max_versions_per_key parameter in add_node() to cap version depth
3. Add runtime:agents:* prefix to GarbageCollector scope -- currently the highest-volume prefix is ignored
4. Daemon background thread intervals need to be configurable via env vars -- 3-second polling on SQLite is too aggressive

The trigger stops unbounded growth, but every write still creates a new row + FTS update. True fix requires changes in SynrixSQLiteClient.add_node() and the daemon thread loop intervals.

[RyjoxTechnologies]

Confirmed — your root-cause analysis is correct and I want to credit it because it's exact.

Verified what's missing in the current GC

synrix_runtime/core/gc.py ships these prune targets:

Prefix	Default retention
metrics:	7 days
runtime:events:	14 days
alerts:	14 days
auditv2:	90 days
Snapshots per agent	last 10 kept

runtime:agents:* is not on the list. That's the prefix the issue identified as 2.2M of 4.1M rows in your prod DB (54%). The GC simply doesn't touch it, so heartbeat versions accumulate forever.

Proposed fix (not yet shipped — want to review before pushing):

# synrix_runtime/core/gc.py
@dataclass
class GCConfig:
    ...
    runtime_agents_days: int = 1   # heartbeats older than 1 day are useless
                                   # the daemon only needs the most recent

# In run_gc() add a step:
if self.config.runtime_agents_days > 0:
    cutoff = now - (self.config.runtime_agents_days * 86400)
    stats["runtime_agents_deleted"] = self.backend.delete_prefix_before(
        "runtime:agents:", cutoff
    )

Why retention=1d is safe for heartbeats specifically:

• runtime:agents:{id}:heartbeat is consumed only by heartbeat_monitor thread which polls the most recent row
• runtime:agents:{id}:state is consumed by recovery_watchdog which polls the most recent row
• Old versions (valid_until > 0) are pure history that nothing reads
• 1 day is a comfortable buffer for any debugging/forensic queries; can be tuned via SYNRIX_GC_RUNTIME_AGENTS_DAYS

Plus a one-time backfill migration (because the existing 4 GB DB needs to drain):

backend.delete_prefix_before("runtime:agents:", time.time() - 86400)

Run once at daemon startup if nodes row count > some threshold, then resume normal GC.

What I haven't decided yet (would value your input):

1. Should heartbeats stop versioning entirely and use UPSERT semantics? Cleaner but bigger code change in SynrixSQLiteClient.add_node(). Would also remove the option of querying historical heartbeats forensically.
2. Should we add a hard row-cap (e.g. last 100 versions per key) as a second-line defense?
3. Should the daemon log a warning when it sees runtime:agents:* row counts above N as an early signal?

Will plan these for the next patch release. Tagging this as the #1 priority for v3.1.6.

Thanks again for the deep dive — the symptom-vs-root-cause correction in your update saved a lot of time.

[Dvalin21]

@RyjoxTechnologies
No problem. I figure if I'm going to use it and find an issue I can at least share it. And to be absolutely transparent, I'm using AI on a lot of it, but make sure to take it through painfully detailed review.