# Dedicated `/metrics` High Latency Analysis for `tidbcloud_db_query_duration_seconds_*`

## Background

For a TiDB Cloud Dedicated cluster with roughly:

- 50 TiDB nodes
- 45 TiKV nodes

the Prometheus integration endpoint:

```bash
curl -sH 'Authorization: Bearer <token>' \
  https://<regional-host>/public_api/v1/clusters/<cluster-id>/metrics
```

can take about 7 seconds to return.

Code tracing shows the request path is:

1. `pipeline/cmd/prometheus/main.go`
2. `pipeline/internal/rest/handler.go`
3. `pipeline/internal/service/prometheus/manager.go`
4. `pipeline/internal/service/prometheus/registry.go`
5. `pipeline/internal/datasource/central_thanos/*`
6. `pipeline/internal/datasource/client/client.go`

The handler itself is lightweight. The dominant latency comes from downstream metric queries executed by the Dedicated metric registry.

## Original Problem Statement

The main slow path is the histogram family exported as:

- `tidbcloud_db_query_duration_seconds_sum`
- `tidbcloud_db_query_duration_seconds_count`
- `tidbcloud_db_query_duration_seconds_bucket`

These three metrics are produced by a single histogram builder:

- [`BuildDBQueryDurationSecondsQuerier`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/prometheus_builder.go#L355)

Before the optimization, that builder called:

- [`QueryDBQueryDurationSeconds`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/central_thanos.go#L365)

which used the following family-wide raw histogram query:

```promql
{__name__=~"tidb_server_handle_query_duration_seconds.+",instance=~"db-.*",kubernetes_namespace="tidb<cluster-id>"}
```

This query pulls the entire raw histogram family from downstream:

- `tidb_server_handle_query_duration_seconds_sum`
- `tidb_server_handle_query_duration_seconds_count`
- `tidb_server_handle_query_duration_seconds_bucket`

for every matching TiDB instance.

## How the Original Export Worked

The original implementation was:

1. Query the raw histogram family from downstream Thanos/Prometheus.
2. Iterate through every returned series.
3. Detect suffix by source metric name:
   - `_sum`
   - `_count`
   - `_bucket`
4. Re-encode them into a single histogram family:
   - `tidbcloud_db_query_duration_seconds`
5. Prometheus exposition encoding expands that family into:
   - `tidbcloud_db_query_duration_seconds_sum`
   - `tidbcloud_db_query_duration_seconds_count`
   - `tidbcloud_db_query_duration_seconds_bucket`

Relevant code:

- [`QueryDBQueryDurationSeconds`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/central_thanos.go#L365)
- [`BuildDBQueryDurationSecondsQuerier`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/prometheus_builder.go#L355)
- [`VecMetric.EncodePrometheusFormat`](/Users/xie/GolandProjects/metrics-integration/pipeline/central/metrics/prom/metric_model.go#L57)

## Why It Is Slow

### 1. The query returns raw instance-level histogram series

The original downstream query returned:

- one series per TiDB instance
- per `sql_type`
- per `le` bucket for `_bucket`

With about 50 TiDB nodes, the number of returned `_bucket` series becomes very large.

### 2. The query uses a family-wide regex match

The original query uses:

```promql
{__name__=~"tidb_server_handle_query_duration_seconds.+" ...}
```

This is broader than querying explicit metric names separately.

### 3. The exporter re-encodes a large result set

Even after downstream query execution finishes, the service still has to:

- deserialize the query result
- allocate metric elements
- attach labels
- encode all results back into Prometheus exposition text

For raw histogram buckets, this encoding cost is material.

## Validation Results

The following PromQL checks were run against the cluster in Grafana/Prometheus.

### Slow raw bucket query

```promql
tidb_server_handle_query_duration_seconds_bucket{
  cluster_id=~".*10123293495242577103",
  sql_type!="internal"
}
```

Observed latency: more than 30 seconds.

### Aggregated bucket-rate query

```promql
sum by (sql_type, le) (
  rate(tidb_server_handle_query_duration_seconds_bucket{
    cluster_id=~".*10123293495242577103",
    sql_type!="internal"
  }[2m])
)
```

Observed latency: about 2.61 seconds.

### Aggregated P99 query

```promql
histogram_quantile(
  0.95,
  sum by (sql_type, le) (
    rate(tidb_server_handle_query_duration_seconds_bucket{
      cluster_id=~".*10123293495242577103",
      sql_type!="internal"
    }[2m])
  )
)
```

Observed latency: about 5.35 seconds.

## Key Conclusion

The dominant bottleneck is not the histogram math itself.

The dominant bottleneck is:

- fetching the raw instance-level histogram bucket series
- returning the large result set
- re-encoding that result set into `/metrics`

This is why the raw bucket query is much slower than the pre-aggregated variants.

## What the Dedicated Dashboard Actually Needs

The current Dedicated dashboard usage in:

- [`artifacts/dashboard/grafana/dedicated-grafana.json`](/Users/xie/GolandProjects/metrics-integration/artifacts/dashboard/grafana/dedicated-grafana.json)

only uses these patterns:

```promql
sum by(cluster_name) (rate(tidbcloud_db_query_duration_seconds_sum{cluster_name="$Cluster_name"}[2m]))
```

```promql
sum by(sql_type) (rate(tidbcloud_db_query_duration_seconds_sum{cluster_name="$Cluster_name"}[2m]))
/
sum by(sql_type) (rate(tidbcloud_db_query_duration_seconds_count{cluster_name="$Cluster_name"}[2m]))
```

```promql
histogram_quantile(
  0.99,
  sum by(le, sql_type) (
    rate(tidbcloud_db_query_duration_seconds_bucket{cluster_name="$Cluster_name"}[2m])
  )
)
```

Important observation:

- the dashboard does not use `instance`
- the dashboard does not use `component`
- the dashboard only needs:
  - `cluster_name`
  - `sql_type`
  - `le` for buckets

So the original raw instance-level passthrough was significantly more expensive than what the dashboard actually consumed.

## Constraints

The following output metrics must be preserved:

- `tidbcloud_db_query_duration_seconds_sum`
- `tidbcloud_db_query_duration_seconds_count`
- `tidbcloud_db_query_duration_seconds_bucket`

The downstream dashboard still applies `rate(...)`, so the exporter cannot replace them with already-rated gauge values.

This means we should preserve histogram counter semantics while reducing cardinality.

## Implemented Optimization

The optimization has now been implemented in the current branch.

The exported metric family remains unchanged:

- `tidbcloud_db_query_duration_seconds_sum`
- `tidbcloud_db_query_duration_seconds_count`
- `tidbcloud_db_query_duration_seconds_bucket`

But the internal query path has been changed from raw instance-level histogram passthrough to aggregated histogram counters.

### Current Implementation

The current implementation uses three explicit aggregated queries:

`_bucket`

```promql
sum by (sql_type, le) (
  tidb_server_handle_query_duration_seconds_bucket{
    cluster_id=~".*<cluster-id>",
    sql_type!="internal"
  }
)
```

`_sum`

```promql
sum by (sql_type) (
  tidb_server_handle_query_duration_seconds_sum{
    cluster_id=~".*<cluster-id>",
    sql_type!="internal"
  }
)
```

`_count`

```promql
sum by (sql_type) (
  tidb_server_handle_query_duration_seconds_count{
    cluster_id=~".*<cluster-id>",
    sql_type!="internal"
  }
)
```

Then encode them back into the same histogram family:

- `tidbcloud_db_query_duration_seconds_sum`
- `tidbcloud_db_query_duration_seconds_count`
- `tidbcloud_db_query_duration_seconds_bucket`

The current builder executes these three queries in parallel and then encodes the results back into a single histogram family.

Relevant code:

- [`QueryDBQueryDurationSecondsSum`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/central_thanos.go#L353)
- [`QueryDBQueryDurationSecondsCount`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/central_thanos.go#L357)
- [`QueryDBQueryDurationSecondsBucket`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/central_thanos.go#L361)
- [`BuildDBQueryDurationSecondsQuerier`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/prometheus_builder.go#L355)

The exported labels were also reduced to only the dimensions required by the current official dashboards:

- `cluster_name`
- `cluster_id`
- `project_id`
- `sql_type`
- `le` for buckets

The following labels are no longer exported for this histogram family:

- `instance`
- `component`
- `resource_group`

### Why this is the best tradeoff

This keeps:

- the same exported metric names
- the same histogram counter semantics
- compatibility with the current dashboard

And removes:

- unnecessary `instance` cardinality
- unnecessary `component` cardinality
- unnecessary raw bucket passthrough
- a large amount of response payload and encoding overhead

## Why Not Export `rate(...)` Directly

Exporting:

```promql
rate(tidb_server_handle_query_duration_seconds_bucket[2m])
```

would be faster, but it changes metric semantics.

The dashboard currently expects raw histogram counters and computes `rate(...)` itself. If the exporter emits already-rated values, existing dashboard queries would become incorrect.

So this is not a compatible fix.

## Why Not Keep the Current Raw Histogram Export

Keeping the current design means accepting that large Dedicated clusters can hit high tail latency because:

- cardinality scales with TiDB instance count
- bucket families are large
- all series are returned and encoded on every scrape

For large clusters, this is structurally expensive.

## Optional Additional Optimizations

### 1. Filter `sql_type="internal"` at source

The dashboard does not use internal SQL traffic. Filtering it out reduces result size.

### 2. Add short TTL caching

If `/metrics` is scraped every 15 to 30 seconds, a short cache for this histogram family can reduce repeated recomputation.

This helps scrape latency, but it is a secondary optimization. It should not replace cardinality reduction.

### 3. Add recording rules downstream

If the downstream observability stack can be changed, recording rules for aggregated histogram counters would reduce on-demand query cost further.

This is operationally heavier than fixing the exporter query shape.

## Code Changes Implemented

The effective changes are:

1. Add explicit aggregated queries for:
   - `_sum`
   - `_count`
   - `_bucket`
2. Update [`BuildDBQueryDurationSecondsQuerier`](/Users/xie/GolandProjects/metrics-integration/pipeline/internal/datasource/central_thanos/prometheus_builder.go#L355)
   so it builds histogram elements from aggregated results instead of raw per-instance results.
3. Execute `_sum`, `_count`, and `_bucket` queries in parallel inside the builder.
4. Keep the output family name:
   - `tidbcloud_db_query_duration_seconds`
5. Preserve histogram output semantics so Prometheus exposition still yields:
   - `tidbcloud_db_query_duration_seconds_sum`
   - `tidbcloud_db_query_duration_seconds_count`
   - `tidbcloud_db_query_duration_seconds_bucket`

## Recommendation Summary

The implemented fix is:

- preserve the three exported `tidbcloud_db_query_duration_seconds_*` metrics
- preserve histogram counter semantics
- stop passing through raw per-instance histogram series
- export cluster-level aggregated histogram counters keyed only by:
  - `cluster_name`
  - `cluster_id`
  - `project_id`
  - `sql_type`
  - `le` for buckets

This is the best balance among:

- performance
- backward compatibility
- dashboard correctness
- implementation complexity