Oximeter: Improve measurement lookup performance

Context: I'm investigating using oximeter to fetch current state for all metrics as part of RFD 601. One of the challenges with this approach is that it's slower than expected, and I'd like to understand why.

I made a spreadsheet of typical query latencies for fetching the latest value for a given metric for all timeseries via oximeter. These queries look like get $my_mymetric | filter @timestamp > now() - 15m | last 1, and intuitively they should be fast, since we're only asking for a single data point over a short lookback interval. But they're often slow, taking multiple seconds to complete.

In #9256, we identify some reasons for high latencies in the field lookup query, and propose some improvements in #9262. But several metrics also show high latencies for looking up measurements as well. For example, the virtual_machine:vcpu_usage metric took a median of 2s to look up just the most recent values. We should be able to do better!

I took a look at some slow measurement queries, like this one:

oxide --profile r2 api -X POST /v1/system/timeseries/query -H 'Content-Type: application/json' --field query='get virtual_machine:vcpu_usage | filter timestamp > @now() - 15m | last 1' --field include_summaries=true

This oxql query actually runs five clickhouse queries: one to look up fields and relevant timeseries, and four more to fetch the measurements. We run multiple measurement queries because they're very verbose, and we don't want to exceed the clickhouse max query size. Those measurement queries each take 500-600ms to execute, and they look like this:

WITH another_sort_bites_the_dust AS (SELECT timeseries_key, start_time, timestamp, datum FROM jmcarp.measurements_cumulativeu64_local_delta WHERE timeseries_name = 'virtual_machine:vcpu_usage' AND ((greater(timestamp, '2025-10-27 14:50:36.570596280') AND timeseries_key IN (13888273388799855450,13888340035437716348,13888540867958146162,...))) ORDER BY timeseries_key, timestamp DESC LIMIT 1 BY timeseries_key) SELECT * FROM another_sort_bites_the_dust ORDER BY timeseries_key, timestamp LIMIT 1000001

But with many more timeseries keys per query.

I did some experiments and found a few simple options that should help us make these queries faster.

• First, we can use delta compression for the timestamp columns in the measurement tables. The tables are ordered by (timeseries_name, timeseries_key, timestamp), so timestamps should be easily compressible within a (timeseries_name, timeseries_key). I tried this out, and query latency got slightly faster, with the size of the measurement table dropping by almost 50%:

oximeter_cluster_1 :) SELECT formatReadableSize(sum(bytes_on_disk)) FROM system.parts
WHERE database = 'jmcarp' AND table = 'measurements_cumulativeu64_local_delta' AND active;

SELECT formatReadableSize(sum(bytes_on_disk))
FROM system.parts
WHERE (database = 'jmcarp') AND (table = 'measurements_cumulativeu64_local_delta') AND active

Query id: a4e0d3b2-3e3c-4051-a18f-f118d7915677

Connecting to 69cce3c6-e957-4e12-b3fb-34f5aa6ecc9f.host.control-plane.oxide.internal:9000 as user default.
Connected to ClickHouse server version 23.8.7 revision 54465.

┌─formatReadableSize(sum(bytes_on_disk))─┐
│ 24.64 GiB                              │
└────────────────────────────────────────┘

1 row in set. Elapsed: 0.158 sec.

oximeter_cluster_1 :) SELECT formatReadableSize(sum(bytes_on_disk)) FROM system.parts
WHERE database = 'jmcarp' AND table = 'measurements_cumulativeu64_local' AND active;

SELECT formatReadableSize(sum(bytes_on_disk))
FROM system.parts
WHERE (database = 'jmcarp') AND (table = 'measurements_cumulativeu64_local') AND active

Query id: 61d24092-b0b2-43e9-987a-0d83d6310b74

┌─formatReadableSize(sum(bytes_on_disk))─┐
│ 41.34 GiB                              │
└────────────────────────────────────────┘

1 row in set. Elapsed: 0.011 sec.

• Second, we can partition measurement tables by timestamp. I'm guessing that many queries will be concerned with a limited time range, such as the last few hours or days. If we partition by day, those queries can ignore the large majority of the table. I tried out a combination of the first two steps, and found that latency on a slow measurement query dropped by a bit over 50%, from ~500ms to ~225ms.

• Third, we're considering a huge number of timeseries keys, even though most of them are knowably irrelevant to our query. Our fields query looks up all timeseries keys that could be relevant to a given query. But especially for metrics with high churn, we're going to look up many keys that don't exist for the given time range. For example, if a given instance was created 15d ago and deleted 14d ago, and the current query only cares about samples from the last hour, timeseries keys related to that instance aren't relevant—but we're looking for them anyway. If we limited the field query to timeseries keys that are relevant to the given time range, we could potentially run a small number of simpler queries on the measurements tables. The fields tables already include a last_updated_at column, which we could use to rule out certain timeseries keys. For example, if the last_updated_at column for a given timeseries is now() - interval 1h and the current query includes filter timestamp > @now() - 30m, we probably* don't care about that timeseries key. Although I say "probably" because last_updated_at is based on insertion time, and timestamp is based on the samples received by oximeter. We could be a bit more rigorous here and explicitly store the min and max timestamps for a given timeseries key, e.g. using a materialized view with the AggregatingMergeTree engine.