pingcap / pingcap/tidb

/stats/dump API: excessive memory for partitioned tables, no per-partition dump

Open
#67,828 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Problem

The `/stats/dump/{db}/{table}` HTTP API builds the entire JSON response in memory before sending any bytes. For partitioned tables this is extremely expensive:

**Measured on 8000 HASH partitions × 17 columns (30M rows):**
- Response size: **16.3 GB** (29M histogram buckets, 3.2M TopN entries, 136K FMSketches)
- TiDB server holds the full `*JSONTable` tree in memory during serialization
- `DumpStatsToJSONBySnapshot` (stats_read_writer.go:456-493) calls `TableStatsToJSON` **8001 times** synchronously (once per partition + global)
- HTTP client receives no bytes until the entire tree is built — any timeout under ~2 minutes fails with "awaiting headers"

The per-partition data is **99.998%** of the response. The global stats (the part most callers need) are **0.3 MB**.

## Current behavior

- `dumpPartitionStats=true` (the **default**): dumps all partitions + global → 16 GB for 8000 partitions
- `dumpPartitionStats=false`: dumps only global stats → 0.3 MB
- No option to dump a single specific partition

## Suggestions

### 1. Default to `dumpPartitionStats=false`, error or ignore `dumpPartitionStats=true`

The default `dumpPartitionStats=true` is a poor default — it silently causes multi-GB memory allocation on the TiDB server for partitioned tables. It should either:
- Return an error when `dumpPartitionStats=true` on a table with many partitions, or
- Ignore the flag entirely and always skip per-partition dumps (callers can query `mysql.stats_*` tables directly for per-partition data)

### 2. Add per-partition dump support

A new route or query parameter to dump stats for a specific partition:
```
GET /stats/dump/{db}/{table}?partition=p0
```
This would call `TableStatsToJSON` once for the specific partition ID instead of iterating all 8000. Response size: ~1.5 MB (one partition) vs 16 GB (all).

### 3. Is this API needed at all?

The `/stats/dump` endpoint is a JSON wrapper around the `mysql.stats_histograms`, `mysql.stats_buckets`, `mysql.stats_top_n`, `mysql.stats_fm_sketch`, and `mysql.stats_meta` system tables. All the same data is queryable directly via SQL, which:
- Doesn't require building the entire result in memory
- Supports standard SQL filtering (WHERE partition_id = ...)
- Benefits from TiDB's normal memory quota enforcement
- Can be paginated

If the only consumer of this endpoint is plan replayer / stats import-export, consider deprecating it in favor of direct SQL access to the stats tables, or at minimum documenting that it should not be used on large partitioned tables.

### Code references

- Handler: `pkg/server/handler/optimizor/statistics_handler.go:53-81`
- Routes: `pkg/server/http_status.go:223-226`
- Dump logic: `pkg/statistics/handle/storage/stats_read_writer.go:456-493`
- Default `dumpPartitionStats=true`: `statistics_handler.go:61`

### How it was found

Benchmarking `ANALYZE TABLE` on a partitioned table (8000 partitions, 30M rows). The stats dump after each ANALYZE run timed out at 60s. After increasing timeout to 10 minutes it succeeded but consumed 16 GB of TiDB memory and 16 GB on the client side.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.