InfraGuard Engineering Devlog, part 6 of 6.

On November 12, five commits went into InfraGuard's rollup worker. Retention cleanup was not completing, a drain path had replayed data, and the worker blocked sensor inserts.

The common theme was a mismatch between database work and process lifetime.

A minute-long process, a seven-minute workflow

PM2 restarted the worker every minute. Database statements could outlive the client, while subsequent script steps never ran.

One drain operation aggregated a detached traffic copy in a statement taking about seven minutes. MySQL could commit that statement after the client was killed, but the subsequent rename recording progress did not happen. The next execution added the same copy again.

The engineering record described 53 nights of repeated additions and an undetached raw table accumulating about 58.8 million rows over 54 days.

We replaced the all-at-once drain with hourly transactions: aggregate one hour, delete that hour from the detached source, and commit. Output and progress now share a transaction. If it rolls back, both roll back. Remaining source data represents remaining work.

Budget each run

The worker has a 40-second execution budget and stops starting new work when it is exhausted. Retention cleanup runs in short slices rather than relying on a daily slot that might never finish.

A budget cannot make an arbitrary in-flight statement instantaneous. Work units must also be small, restartable and durably recorded.

The reader blocked writers

Under the deployed MySQL configuration's REPEATABLE READ behavior, INSERT … SELECT rollups acquired shared locks while scanning source rows. Sensors inserting late data into the recomputed interval waited behind them.

Recorded ordinary waits were 27–58 seconds every five minutes. Overlapping maintenance pushed a wait to roughly five minutes. Batches from six sensors exceeded the 240-second lock timeout. Connection pools filled the account's 50-connection limit, and failed batches were discarded rather than replayed: silent data loss.

The worker now changes its own session to READ COMMITTED when the binary-log configuration supports it. The documented configuration requires ROW; STATEMENT retains the existing isolation level and emits a warning. This is not advice to change global isolation blindly.

A scratch-table reproduction showed a late insert timing out under REPEATABLE READ and passing under READ COMMITTED. Separate per-source, heavy-rollup and maintenance locks also let unrelated sources keep progressing.

Takeaway: Make output and progress atomic, bound work to process lifetime, and reproduce locking under the actual isolation and logging configuration. A successful summary is not success if producing it prevents new observations from arriving.

About this series: Adapted from InfraGuard engineering records. Measurements describe the recorded environment, not product-wide benchmarks. AI was used to draft and translate this article.

Disclosure: Solution Market develops and sells InfraGuard. This is our engineering devlog, not an independent review. InfraGuard is a self-hosted infrastructure operations and security platform. Project · Documentation · Solution Market