A repaired sensor made our dashboard take 28 seconds to load
InfraGuard Engineering Devlog, part 5 of 6. Restoring a stopped sensor on October 30 made telemetry healthier and the console slower.
Recorded cold-cache tests averaged 13.3 seconds for the first view and reached 28.5 seconds. A weak-TLS aggregation was responsible. Recent 24-hour TLS data had grown to 3.01 million of 11.67 million rows. The optimizer changed its access plan; one query took 13-36 seconds.
One covering index helped
An index covering time and TLS version let this query read the index alone. The recorded online build took 82 seconds and added 353 MB. The aggregation then completed in about 1.8 seconds.
We also precomputed the score every four minutes rather than making the first viewer pay the cold-query cost. Improving a query and removing expensive computation from a request path are separate fixes.
Another index did not
An earlier seven-day JA3 top-IP query took 3,728 ms. A covering-index experiment did not help, so we removed it. An index hint was slower at roughly 4-6 seconds.
An hourly rollup helped that workload: about 20 ms, a recorded improvement of roughly 186 times. βUse rollups for wide windowsβ sounded like a rule. On November 4, JA3 provided the counterexample.
Rollups need something to collapse
After recovery, a 24-hour TLS window held 3.06 million rows and 1.12 million distinct source IPs. Bot traffic kept grouping cardinality high. The rollup retained 2.28 million rows. Its recorded footprint was 7.6 GB, versus 6.3 GB for the raw table.
Reading the 24-hour rollup took 45-131 seconds. Grouping a million addresses spilled intermediate work to disk. The access path was not the whole problem.
We capped JA3 aggregation at six recent hours, read raw data, and shared one top-300 snapshot across screens. If the UI selects a broader range, it explicitly says the JA3 result still represents six hours.
Cache duration is not a computation budget
A 30-second cache with 30-second polling can make requests arrive just as entries expire. Recomputations taking 23-85 seconds then overlap across tabs on the same database. We moved computation behind precomputed snapshots.
Takeaway: Inspect the plan, measure the rollup-to-raw row ratio, and test the request path. Indexes, rollups and caches solve different problems. None replaces checking the actual workload.
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
Comments
No comments yet. Start the discussion.