console: Add a Freshness page under Monitoring - #39340

Since buildLagHistoryQuery is shared with Environment Overview, Cluster Overview and the object panel's 60s poll, this adds a full scan and reduce of mz_hydration_statuses to all of them. Could we move it to a page-only query scoped by the same object IDs?

SELECT object_id, count(*) AS total_replicas,
  count(*) FILTER (WHERE hydrated) AS hydrated_replicas
FROM mz_internal.mz_hydration_statuses
JOIN unnest(ARRAY['u1', 'u2']::text[]) AS ids(id) ON ids.id = object_id
GROUP BY object_id

Moved it to a page-only query in d69a1ea — you're right that three callers were paying for a column none of them read.

One correction though: scoping by object ID doesn't make this read cheap. mz_hydration_statuses_ind is keyed on (object_id, replica_id), and a lookup needs every key column, so constraining object_id alone still plans as a full scan. Measured on v26.45.0-dev with both forms:

So the win here is narrower than the suggestion implies: the scan still happens, it just happens once, for the one page that shows hydration, instead of on Environment Overview, Cluster Overview and the object panel's 60s poll as well. Kept the IN list since unnest bought nothing and the rest of the Console already reads that way.

Agreed both plan as a full scan, but they don't cost the same: with the GROUP BY, 2000 IDs took 517 ms with IN vs 57 ms with unnest (80 IDs: 13.7 vs 6.8), I think because the literal list is checked per row.

Thanks for timing it. Since you filed CNS-186 for the unnest switch, I've left it out of this PR.

Could we take the object IDs, names and types from useAllObjects() filtered by clusterId, with system and subsource/progress objects filtered client-side (isSystemId, sourceType), and pass only the IDs to the lag query? That drops the mz_objects, mz_clusters and names joins, and unnest keeps big clusters on the index, where a literal IN list stops using it past ~300 IDs:

SELECT occurred_at, lag, object_id
FROM mz_internal.mz_wallclock_global_lag_recent_history
JOIN unnest(ARRAY['u1', 'u2']::text[]) AS ids(id) ON ids.id = object_id
WHERE occurred_at + INTERVAL '3600000 MILLISECONDS' >= mz_now()

I think this would make the cluster freshness query much faster. It also cuts each row from 11 columns to 3.

Done, but with a literal IN list rather than unnest. Measured on v26.45.0-dev (Docker, mz_catalog_server):

Your ~300 was close: the cliff is between 330 and 331. But unnest never reaches a lookup at any size, so switching to it would have given up the win for normal-sized clusters.

Dropping the name joins is the bigger part regardless — five indexes and three full scans down to one lookup, and eleven columns to three.

Past 330 objects we do lose the lookup and read the whole index. No worse than today, but not fixed. I considered picking the form by ID count and decided against putting that threshold in Console code, since it describes optimizer behaviour at one version and would fail silently if it moved. Happy to revisit if you know it to be stable.

Thanks for measuring. In my run unnest was as fast or faster than the IN lookup at every size (80 IDs: 9.8 vs 11.7 ms, 300: 14.9 vs 31.6, 2000: 62 vs 718), on a small emulator history. Could you time both on your bench? If it holds, unnest removes the 330 cliff without a threshold in code.

I didn't get to timing both on my bench. CNS-186 covers it, so I've left this PR on the IN list.

At 24h each point is a 24-minute max, so "right now" is the worst lag in the last bin (2s vs a latest reading of 1s locally). Could we take Now from the latest reading instead? At 1h the last point already is one; above that a small query covers it:

SELECT DISTINCT ON (object_id) object_id, occurred_at, lag
FROM mz_internal.mz_wallclock_global_lag_recent_history
JOIN unnest(ARRAY['u1', 'u2']::text[]) AS ids(id) ON ids.id = object_id
WHERE occurred_at + INTERVAL '2 MINUTES' >= mz_now()
ORDER BY object_id, occurred_at DESC

Fixed in 852be00. Confirmed your reading: at 24h the last bin is the worst reading of 24 minutes, so an object that spiked twenty minutes ago and recovered still read high, and under the "right now" predicate it was counted as exceeding and coloured on the graph.

Took the second query as you suggested, batched into the same request via executeSqlV2's array form, so no extra round trip. The two minute filter keeps it to one row per object whatever range is being graphed: 100ms on top of a 499ms request, measured on a 300 object fleet.

The graph still plots bin maxima, since that is all 60 points can show, so a point can now sit above its row's "Now" value. The footer says so rather than leaving a reader to notice it.

Maybe compute the percentiles from the unbinned readings instead of the 60 bins, so p99 stops collapsing onto the peak from 3h up? Materialize has no percentile_cont, so this would be client-side, with the trade that 24h is 1440 readings per object.

Measured this before deciding, since it would have retired the p90 compromise. Bench cluster, shadow table with the production index, 300 objects x 1440 readings (a 24h range on a cluster larger than 99.7% of production), 10 runs, first dropped:

p90 is the whole cost. Nearest rank needs row_number() ordered within each object's partition, so it sorts 432,000 rows in 300 groups. Everything else is cheap.

So the honest version of your suggestion is 5x slower than the entire current query, and it also adds a second dataflow on mz_catalog_server rather than replacing one. Declining it for this PR on those grounds.

Took 852be00 instead: a second query for each object's latest reading, batched into the same request, which fixes "Now" for 100ms. p90 stays a percentile of the 60 bin maxima and the footer says so.

Worth noting for later: that last row is the interesting one. Scoped to 50 objects the same p90 costs 177ms, because it sorts 72,000 rows instead of 432,000. If the graph grows a line cap, ranking by max(lag) server-side (549ms) and fetching unbinned readings only for the top N would give honest percentiles and cut the payload, since the fetch would be bounded by the cap rather than by cluster size. That is a bigger change than this PR should carry, but it is the shape I would build toward.

Separately, re your other comment: a raw read plans as Explained Query (fast path) and builds no dataflow, while our date_bin + DISTINCT ON does. Client-side binning would move that work off the shared cluster. Confirmed up to 1000 IDs.

Makes sense for this PR, thanks for measuring. For the record, I meant the client-side version: a plain raw read (fast path, no dataflow, as you confirmed below) with the sort done in the browser. I sized it for 300 objects in Node:

Today's binned response is 18k rows (0.8 MB) at every range. So the browser work is small and the payload is what grows: fine up to about 6h, too heavy at 24h, which is where your top-N idea fits. Could we capture it in a follow-up issue, since it's what gets us to p99?

Filed as CNS-182.

Could we move key={selected.id} (plus the window) onto the AppErrorBoundary? Once a query fails, switching cluster or window keeps showing the error until a reload.

Fixed in 429b785. The key is now cluster:range on the AppErrorBoundary itself.

Worth stating why the old placement could never have worked: a boundary showing its fallback is not rendering its children, so re-keying a child it has stopped rendering has no effect. The key has to be on the thing holding the error state.

One deliberate consequence: hand-picked rows now clear on a range change too, not just a cluster change, since the boundary remounts its subtree. Accepted that, on the grounds that trying a narrower range is exactly when a stuck error needs clearing. Lifting selectedKeys above the boundary would preserve them if you think it is worth the extra state.

Nit: maybe drop the relation names from the page copy? mz_internal isn't part of the stable interface, so they can go stale, and the binning note changes if the percentiles move to unbinned readings.

Dropped the relation names in 852be00, agreed on both counts.

The binning sentence stayed, since the percentiles did not move to unbinned readings in the end (measured it at 5x the cost of the whole current query, see the thread on freshnessRows.ts). It gained a sentence naming the "Now" column's disagreement with the graph, which the second query in that commit introduced.

Nit: we usually avoid an effect that copies a prop into state. Maybe keep only the in-flight drag value, with live = dragValue ?? committed, and clear it when the debounce commits?

Done in ce789d2. It now holds only the in-flight drag value and returns dragValue ?? committed, so there is no effect and no render that shows the stale value first.

One detail worth flagging: the drag value is cleared inside the debounced callback, alongside the commit, so React batches both into a single render. Clearing it in a later render would briefly fall back to committed before that caught up, which reads as the handle springing back.

Nit: clearing the field to type a new value sets the threshold to 0, so every object flips to exceeding until the new number is in. Maybe keep the text in local state and only commit a parsed, non-empty value?

Fixed in 1cc4651, though not quite as suggested.

Committing a parsed, non-empty value still fires per keystroke, so clearing the field and typing 25 would commit 2 on the way. Debounced the whole thing instead: the field shows what is typed immediately, and commits 400ms after typing stops. Whatever has settled is then taken at face value, so a field left empty commits 0 rather than being ignored. Someone who clears it and stops has asked for no threshold, and the page reads fine in that state.

400ms is its own constant rather than reusing THRESHOLD_SETTLE_MS. 120ms paces a drag, where a pointer emits continuously and that much stillness means the reader has stopped. Typing has gaps that long between digits.

Tests assert the commit lands once with the final value, not three times on the way to it.

+1 to the QA review here: in also matches inherited keys like constructor. Object.hasOwn would close it, here and in parsePredicate below.

Done in 8f44898, both parsers.

Confirmed the two failure modes you and the QA review described:

• ?timePeriod=constructor passed the in check, Number of it is NaN, and that reached the query as INTERVAL 'NaN MILLISECONDS'.
• ?exceeded=toString passed and fell through statFor to p90, so the page judged by one statistic while the menu named another. That one is the worse of the two, since nothing on screen says so.

Added regression tests covering constructor, toString, valueOf and __proto__ against both parsers. parseObjectTypes tests membership of an array, so it was never affected.

Optional, not blocking: at the default 1h window each bin is one reading, so the page could fetch the raw readings and bin them client-side at this group, like rebucketUtilizationSamples does for utilization. For clusters under ~300 objects, a plain read with the IDs inline runs as a fast-path peek, so the default view builds no dataflow on catalog_server.

Confirmed the fast-path claim, and it is a sharper difference than it looks: a raw read plans as Explained Query (fast path) and builds no dataflow, while our date_bin + DISTINCT ON plans as Explained Query and does. That holds up to 1000 IDs. So client-side binning would move that work off mz_catalog_server entirely, not just make it cheaper.

Not taking it in this PR. The cost is on the other side: binned is 60 rows per object whatever the range, raw is one per minute per object, so at 24h a 300 object cluster goes from ~760KB to ~20MB at the 42 bytes/row I measured. Against production, 99.7% of clusters are at or under 330 objects and 92% at or under 60, so the common case is ~3MB, but the tail is not something I want to ship untested.

The version that resolves it is the one in the freshnessRows.ts thread: cap the graph's lines, rank server-side with max(lag) (549ms), and fetch unbinned readings only for the top N. Then the payload is bounded by the cap rather than by cluster size, the binning moves client-side as you suggest, and the percentiles become honest. That wants the line cap first, which this PR does not have.

nit: comment is probably unnecessary

Removed in d77bf6a.

If another param changes inside the settle window, commit gets a new identity (react-router 7's setSearchParams depends on searchParams), so the debounce is rebuilt and the pending call is cancelled. Since setDragValue(undefined) lives in that call, the handle stays on the drag value, and after that a pasted link moves the URL but not the handle. Maybe hold commit in useLatestRef and build the debounce once? The "Stable identities" comment in useFreshnessParams would need updating too.

Tried this locally: with commit in useLatestRef (like useAutomaticallyConnectSocket) and the debounce built once, a drag followed by a param change commits both, and a pasted link moves the handle. All seven useCallbacks in useFreshnessParams can go with it, and the existing tests still pass.

Fixed in b09ad06: commit is held in a ref, with a regression test. The field and drag now share one timer, in useThresholdControl next to ThresholdLineGraph. The Stable identities comment is corrected. I left the useCallbacks in place.

Correction: the useCallbacks are gone too, in 8f532c3.

notQueryable is set by any unreadable bin in the window, so an object that was unhydrated once 20 hours ago breaches under "right now" and shows "Not queryable" in Now. Could "right now" judge only the latest reading?

Fixed in 61cd62e. Unreadable is now a value (Infinity), not a flag. It counts in the denominator, so seven bad readings of either kind breach p90. The graph breaks its line there.

Since useClusterFreshness suspends first, the hydration request only starts after the lag request returns. Could hydration join the same executeSqlV2 batch as the lag and latest queries?

Fixed in 003db7b. Hydration takes its IDs from objects.

Could this start with buildRegionQueryKey("freshness")? Object IDs repeat across regions, so after a region switch this can serve the other region's hydration counts.

Fixed in 003db7b.

The table opens sorted by Peak, so with "right now" or p90 selected it isn't ordered by what it's judged on, and unreadable rows sort to the bottom of Exceeding threshold. Could the initial sort follow breachValue?

Fixed in 8be686f: the table now opens in the order buildFreshnessRows gives it, which is worst first by breachValue, so it follows the active predicate.

Nit: the three section headers show counts as N objects, (x/y) and (N). Maybe one format?

Fixed in 48eef83.

Nit: - here and — in the stat cells. Maybe the same placeholder?

Both use NO_VALUE now. Note the app is split three and three between em dash and hyphen. I kept the em dash.

Nit: in the headline this reads "exceeded 2s >10% of readings (p90)". Maybe "for more than 10% of readings (p90)"?

Fixed in 10c1330.

Thanks for measuring. In my run unnest was as fast or faster than the IN lookup at every size (80 IDs: 9.8 vs 11.7 ms, 300: 14.9 vs 31.6, 2000: 62 vs 718), on a small emulator history. Could you time both on your bench? If it holds, unnest removes the 330 cliff without a threshold in code.

Agreed both plan as a full scan, but they don't cost the same: with the GROUP BY, 2000 IDs took 517 ms with IN vs 57 ms with unnest (80 IDs: 13.7 vs 6.8), I think because the literal list is checked per row.

At 24h a bin is 24 minutes, so refetching every 60s re-reads the whole window to update the last bin, about 500 ms per open tab per minute by your bench numbers. Could the series refetch once per bin (calculateBucketSizeFromLookback), with only the latest reading on the 60s clock?

Fixed in 003db7b. Series and latest readings are separate queries. The series refetches at bin size.