Diagnosing a Python Edge Gateway that Leaks Memory and Loses Its MQTT Backlog

Three independent defects compound. First, loop_forever() in the main thread means the network loop only runs when callbacks are not blocking; Modbus reads and HTTP calls inside on_message block keepalive handling, so the broker sees missed pings and disconnects every 15–25 minutes. Second, creating a new paho client and a new SQLite connection per message leaks file descriptors and memory, and concurrent writes to one SQLite file without WAL and a single writer cause database is locked. Third, the store-and-forward path has no bounded queue or retry policy; during a two-hour outage, messages accumulate in memory or partially written rows, and on reconnect the service replays without tracking acknowledgements, so the backlog never drains. RSS growth from 180 MB to 1.2 GB is consistent with unclosed clients and connections plus unbounded buffering, not a hardware limit. The reconnect loop is both a cause of duplicate work and a consequence of blocking callbacks.

Split responsibilities. One acquisition task reads Modbus on a fixed schedule and writes normalized samples to a bounded in-process queue, for example 50,000 items, with a policy: drop oldest low-priority samples and persist high-priority alarms to disk first. A single writer task owns one SQLite connection, enables WAL, sets synchronous=NORMAL, and batches inserts in transactions of 500. An MQTT task runs the paho network loop in its own thread, or uses an asyncio client, and never performs Modbus or HTTP work inside callbacks. Use clean_session=False with a stable client ID, QoS 1, and a persistent session; on on_connect, resubscribe and start draining the spool. Implement exponential backoff with jitter. Call wait_for_publish only in the drain task, not in the acquisition path. Add an idempotency key per sample so replayed messages are safe. Bound the SQLite spool by age and size, and delete rows only after broker PUBACK. This isolates failure domains and makes backpressure explicit.

Expose queue depth, oldest item age, spool rows, publish latency p95, reconnect count, authentication failures, SQLite busy errors, RSS, CPU, SD-card free space, and messages dropped by priority. Alert when oldest item age exceeds 10 minutes, reconnects exceed 3 per hour, queue depth stays above 80% for 5 minutes, or SQLite busy errors rise above baseline. Add a synthetic canary that publishes a known value every minute and checks end-to-end arrival. Use structured logs with sample and correlation IDs, and ship logs off-device because SD cards fail. Deploy under systemd with a watchdog and a remote update path that preserves the spool. Validate with a two-hour broker outage and a four-hour network outage: after recovery, the canary and backlog must arrive without duplicates. Monitor for 72 hours after each change; memory should stabilize, not merely grow more slowly. Keep a rollback image and document the exact metrics that trigger it.