TJ Downes 3397d972ce perf: thread-local SQLite connections, synchronous=NORMAL, dedup indexes ...

Five targeted changes to sqlite_handler.py, all in the same file.

1. Thread-local persistent connections
   _connect() previously opened a new sqlite3.connect() on every DB call and
   ran journal_mode + busy_timeout PRAGMAs each time.  On SD-card storage each
   connection open involves file-system operations; each PRAGMA is a round-trip.
   threading.local() now caches one connection per thread (write executor thread
   + event-loop/HTTP threads), eliminating per-call setup overhead.

2. PRAGMA synchronous=NORMAL
   Default synchronous=FULL flushes WAL frames to disk after every transaction.
   NORMAL flushes only at WAL checkpoints — safe for this workload (no data loss
   beyond the current transaction on power failure) and significantly faster on
   SD cards, which have slow fsync (5-20ms per flush).

3. Migration 8: UNIQUE index on companion_messages(companion_hash, packet_hash)
   companion_push_message previously deduped via SELECT + INSERT (two statements,
   two SD-card reads per message).  The new UNIQUE index enables INSERT OR IGNORE,
   replacing the round-trip with a single atomic statement.

4. Migration 9: UNIQUE index on adverts(pubkey)
   Without this index store_advert's ON CONFLICT clause cannot fire and each
   advert inserts a new row instead of updating the existing one — unbounded
   table growth on busy meshes.  The migration deduplicates existing rows
   (keeping the most-recently-seen per pubkey) before adding the index.

5. Remove duplicate get_unsynced_count definition
   The method was defined twice with the same signature.  Python silently uses
   the last definition; the first was dead code with reversed SQL parameter
   binding order.  Removed the first; added a note to the surviving definition.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

2026-04-21 19:41:50 -07:00

106 KiB

Raw Permalink Blame History

View Raw