greptimedb-performance-diagnosis

name

greptimedb-performance-diagnosis

description

Guide for diagnosing GreptimeDB performance problems — find the bottleneck behind a slow query, lagging or stalled ingestion, or high CPU/memory, then hand off to the right fix. Use when the user reports something is slow or resource-hungry and wants to know why. Triggers on phrases like "query is slow", "查询慢", "ingestion is slow", "写入慢", "write stall", "写入卡顿/被拒绝", "high CPU", "CPU 高", "high memory", "内存高/OOM", "EXPLAIN ANALYZE", "why is GreptimeDB slow", "性能排查/诊断". This skill diagnoses; apply fixes with greptimedb-performance-tuning and greptimedb-table-design.

GreptimeDB Performance Diagnosis

Help the user find the bottleneck behind a GreptimeDB performance problem, then point them at the right fix. This skill is the triage hub: it observes and concludes, it does not apply tuning itself. Once the cause is clear, hand off to:

• greptimedb-performance-tuning — server config and table-option changes (caches, write buffer, WAL, batching).
• greptimedb-table-design — schema changes (primary key, indexes, append-only, partitioning).

Before going deep, ask for the GreptimeDB version and deployment mode (standalone or cluster). Older versions may not expose some of the metrics below, and older Grafana dashboards may lack some panels — you can supplement them from the grafana dashboards directory, where each panel's PromQL is the canonical source for the metric names used below.

Running SQL and viewing metrics/logs. To run the SQL in this guide (EXPLAIN ANALYZE, SELECT, SHOW CREATE TABLE), use any of: the greptimedb-mcp-server execute_sql tool if available; a MySQL or PostgreSQL client; or the built-in GreptimeDB Dashboard. GreptimeDB Enterprise users can use the Management Console, which also surfaces metrics and logs.

The workflow

Phase 1. Make signals observable (only as needed)

Metrics dashboards are not a prerequisite. Many diagnoses need nothing but SQL:

• A slow query can be investigated immediately with EXPLAIN ANALYZE VERBOSE (Phase 3).
• Write distribution can be checked with REGION_STATISTICS (Phase 4).

Recommend setting up observability only when the symptom needs time-series metrics (write pressure trends, cache hit ratios, CPU/memory over time):

1. Self-monitoring setup: https://docs.greptime.com/user-guide/deployments-administration/monitoring/cluster-monitoring-deployment/
2. Import the Grafana dashboards (standalone or cluster): https://github.com/GreptimeTeam/greptimedb/tree/main/grafana/dashboards

If the dashboard version is old and missing a panel, give the user the raw PromQL from the phases below so they can add it.

Phase 2. Classify the symptom

Route to the matching phase based on what the user reports:

Symptom	Go to
A specific query is slow	Phase 3
Ingestion is slow / latency high / writes stall or get rejected	Phase 4
A node has high CPU or memory (cause unclear)	Phase 5
Cache seems ineffective / lots of object-store reads	Phase 6

If the user is unsure, start from the broadest available signal (ingestion rate, request latency, CPU/memory) and narrow down.

Phase 3. Diagnose a slow query

Run the query with EXPLAIN ANALYZE VERBOSE (see access methods above):

EXPLAIN ANALYZE VERBOSE <SQL>;

EXPLAIN ANALYZE executes the query and reports per-stage runtime metrics; VERBOSE adds scan-level metrics. When comparing repeated runs, remember the second run may be served from cache — shift the time range slightly to keep the shape equivalent but avoid a fully cached repeat.

Map common findings to causes and the fix skill:

Finding in output	Likely cause	Fix
High num_file_ranges, many files < 1 row group	Many small SST files (backfill across windows, compaction pressure)	Tuning: compaction.twcs.time_window; Table-design: write/backfill by time window
High scan_cost and high rows_precise_filtered	Reads many rows then filters them out — missing a selective index	Table-design: add an index on the selective column
High fetch_metrics.cache_miss / pages_to_fetch_store / store_fetch_elapsed	Reading pages from object store instead of cache	Tuning: increase write_cache_size / page_cache_size
High build_parts_cost / build_reader_cost	Time spent building scan ranges/readers	Correlate with num_file_ranges, metadata cache misses
High metadata_cache_metrics.metadata_load_cost / cache_miss	SST metadata cache too small	Tuning: increase sst_meta_cache_size (check greptime_mito_cache_bytes{type="sst_meta"})

If the cause is still unclear, capture the full EXPLAIN ANALYZE VERBOSE output plus SHOW CREATE TABLE and escalate (see Escalation).

Phase 4. Diagnose ingestion & write pressure

First check whether the write buffer is under pressure:

• greptime_mito_write_buffer_bytes
• greptime_mito_write_stall_total
• greptime_mito_write_stalling_count
• greptime_mito_write_reject_total

Stalls mean GreptimeDB is applying backpressure (global write buffer near global_write_buffer_size, or a region temporarily not writable). A client error like Engine write buffer is full, rejecting write requests means the reject threshold (global_write_buffer_reject_size) was reached.

Don't jump to raising the write buffer. First find out why the buffer fills:

1. Flush performance — greptime_mito_flush_elapsed, plus greptime_mito_flush_requests_total, greptime_mito_flush_bytes_total, greptime_mito_flush_file_total. Datanode logs also help: search for Successfully flush memtables and divide total_rows/total_bytes by cost to estimate throughput. Slow flushes (e.g. ≥30s) or flush throughput below ingestion → the fix is partitioning/region distribution, not a bigger buffer.
2. Write distribution — greptime_mito_write_rows_total per datanode and greptime_mito_handle_request_elapsed_bucket; query REGION_STATISTICS for written_bytes_since_open and memtable_size. If one region/datanode takes most of the load → repartition (Table-design).

Conclusion → fix:

• Flush slow or unbalanced load → Table-design (partitioning / region distribution).
• Flush fast but frequent under steady load → Tuning (increase global_write_buffer_size).

Also compare frontend protocol latency (greptime_servers_http_requests_elapsed_bucket, greptime_servers_grpc_requests_elapsed_bucket, filter by path/method/code) against datanode-side greptime_mito_handle_request_elapsed_bucket and greptime_mito_write_stage_elapsed_bucket to locate whether the bottleneck is before or inside the storage engine.

Phase 5. Diagnose high CPU / memory

When a node has high CPU or memory and the cause is unclear, collect a profile from that node and provide it to developers. Profiling is node-local — use the HTTP address of the affected node (replace 127.0.0.1:4000).

CPU flamegraph (collect while CPU is high):

curl -X POST -s 'http://127.0.0.1:4000/debug/prof/cpu?seconds=10&output=flamegraph' > greptime-cpu.svg

Memory flamegraph (collect while memory is high or growing):

curl -X POST -s 'http://127.0.0.1:4000/debug/prof/mem?output=flamegraph' > greptime-mem.svg

Notes:

• Memory profiling is Linux-only (not macOS/Windows) and requires heap profiling enabled. Official Docker images enable it by default; for self-built deployments start with MALLOC_CONF=prof:true ./greptime ....
• Generating a flamegraph uses extra memory (parsing + symbolization in process) — be careful on a node near its memory limit.
• To find growing allocations, collect two output=proto profiles at different times and compare them.

See https://docs.greptime.com/reference/http-endpoints/#profiling-tools for the reference. Attach the flamegraph/profile when escalating.

Phase 6. Diagnose cache pressure

Use greptime_mito_cache_bytes{type="..."} as the primary signal: if a cache sits near its configured capacity, it is under pressure. Supporting signals are greptime_mito_cache_hit, greptime_mito_cache_miss, and greptime_mito_cache_eviction (all labeled by type). Cache types include file, index, sst_meta, page, vector, selector_result, range_result, manifest, and index-related caches.

A low hit ratio with high miss/eviction on a near-full cache → hand off to Tuning to resize that specific cache.

Escalation

When you cannot reach a confident conclusion, recommend the user collect context and open an issue or discussion at https://github.com/GreptimeTeam/greptimedb. Gather, as relevant:

• Full EXPLAIN ANALYZE VERBOSE output (for query issues).
• Table schema (SHOW CREATE TABLE).
• GreptimeDB version and deployment mode.
• Relevant metrics (PromQL results or dashboard screenshots) and logs.
• CPU/memory flamegraphs for resource issues, plus the approximate time range and workload.

If the user runs GreptimeDB self-monitoring, use the self-monitoring-export skill to export the logs/metrics for the incident window.

Reference

1. Performance tuning tips (metrics tables, EXPLAIN findings): https://docs.greptime.com/user-guide/deployments-administration/performance-tuning/performance-tuning-tips/
2. EXPLAIN reference: https://docs.greptime.com/reference/sql/explain/
3. Self-monitoring deployment: https://docs.greptime.com/user-guide/deployments-administration/monitoring/cluster-monitoring-deployment/
4. Grafana dashboards (canonical source for metric names / PromQL): https://github.com/GreptimeTeam/greptimedb/tree/main/grafana/dashboards
5. HTTP profiling endpoints: https://docs.greptime.com/reference/http-endpoints/#profiling-tools
6. REGION_STATISTICS: https://docs.greptime.com/reference/sql/information-schema/region-statistics/
7. GreptimeDB Dashboard (run SQL from a web UI): https://docs.greptime.com/getting-started/installation/greptimedb-dashboard/
8. GreptimeDB Enterprise Management Console (SQL, metrics, logs): https://docs.greptime.com/enterprise/console-ui/