CockroachDB detecting hot ranges: per-range QPS, CPU asymmetry, and the Hot Ranges page

Hot ranges are the most common performance bottleneck in CockroachDB that does not surface in aggregate metrics. The leaseholder model routes all reads and writes for a range through a single node. When one range receives disproportionate traffic, that node saturates while the rest of the cluster idles. The cluster-wide CPU average looks healthy. The per-node breakdown tells a different story.

Detection uses three complementary layers, each with different cost and fidelity tradeoffs. Per-node CPU and QPS asymmetry (standard Prometheus metrics) is cheap and continuous but indirect: it tells you a hot range likely exists, not which one. The DB Console Hot Ranges/Top Ranges page provides direct per-range QPS visibility but is manual and point-in-time. crdb_internal.ranges provides programmatic access to range layout (leaseholder, range size, replicas) but exposes no per-range QPS and is expensive to query, so it must not be scraped at regular intervals.

The three detection layers

flowchart TD
    A["Layer 1: per-node CPU/QPS
Prometheus metrics
Continuous, safe, indirect"] -->|"One node 2x+ others
with similar range counts"| B["Layer 2: DB Console
Hot Ranges/Top Ranges page
Manual, point-in-time, direct"]
    B --> C["Layer 3: hot ranges logs +
crdb_internal.ranges layout
Poll every 5-10 min max"]
    A --> C

Layer 1 answers “is there likely a hot range?” Layer 2 answers “which range is hot right now?” Layer 3 answers “which range is hot, and can I alert on it programmatically?”

Layer 1: per-node CPU and QPS asymmetry

This is the only layer safe for continuous monitoring. CockroachDB exposes per-node CPU and SQL throughput via Prometheus metrics at no extra cost. The signal is asymmetry: one node running significantly hotter than the others.

Key metrics for asymmetry detection:

The threshold: one node at more than 2x CPU of others with similar range counts. The leaseholder of a hot range bears all SQL execution, KV processing, and Raft coordination for that range’s traffic. A single hot range can drive one node to 80% CPU while peers sit at 20%. The cluster average (~35%) looks unremarkable.

CPU asymmetry cannot identify the specific range. Multiple hot ranges on the same node, or a legitimate workload imbalance from lease distribution, produce the same pattern. It is a trigger for investigation, not a diagnosis.

# Check per-node CPU asymmetry from Prometheus endpoint
curl -s http://localhost:8080/_status/vars | grep -E 'sys_cpu_(user|sys)_ns'

Compare the rate of change across nodes. Raw counter values are cumulative nanoseconds.

Cross-referencing with QPS. Per-node SQL statement counters confirm whether CPU imbalance correlates with traffic imbalance. If one node handles 3x the sql_insert_count of peers, you have a write-heavy hot range. If CPU is imbalanced but QPS is uniform, the bottleneck may be compaction, GC pressure, or another non-hot-range issue. Check storage_l0_sublevels per store to rule out storage-driven CPU imbalance.

Gating out false positives. Before concluding CPU asymmetry means a hot range, check whether the affected node simply holds more ranges or leases. Query replicas_leaseholders and compare range counts per node. If one node has 2x the leases, the CPU imbalance may be a distribution problem. If range and lease counts are balanced but CPU is skewed, a hot range is the likely explanation.

Layer 2: the DB Console Hot Ranges page

The DB Console provides a dedicated Hot Ranges page that ranks ranges by queries per second. Use it when Layer 1 signals asymmetry.

In CockroachDB v26.1 and later, this page is renamed “Top Ranges” and lists the highest-ranked ranges by QPS, CPU time, read keys, write keys, read bytes, and write bytes. In earlier versions (v25.3 and below), the page is called “Hot Ranges” and shows the same per-range metrics (QPS, CPU, read/write keys, read/write bytes).

Access requirements. The Top Ranges page requires the admin role or a SQL user with the VIEWCLUSTERMETADATA system privilege; VIEWACTIVITY and VIEWACTIVITYREDACTED do not grant access to it (the older Hot Ranges page accepted the legacy VIEWACTIVITY/VIEWACTIVITYREDACTED role options). Confirm your role assignment with your cluster administrator if access is denied.

How to use it:

1. Navigate to the Hot Ranges (or Top Ranges) page in the DB Console.
2. Sort by QPS descending.
3. Compare the top range’s QPS to the average. A range at more than 10x the average QPS is hot.
4. Note the table name and range start key for the top ranges.

If the page shows per-range CPU, a range with high CPU but moderate QPS may indicate expensive scans rather than throughput-driven heat. Disproportionately high write keys indicate a write-heavy hotspot, typically caused by sequential primary keys or a single counter row.

The page is point-in-time and manual. It cannot trigger alerts, and the data refreshes on the console’s internal cadence. For automated detection or historical trending, use Layer 3.

Layer 3: per-range visibility via crdb_internal.ranges and hot ranges logs

crdb_internal.ranges exposes range layout programmatically (range_id, start_pretty, end_pretty, replicas, lease_holder, range_size), but it does not expose per-range QPS. Per-range QPS is available in the DB Console Hot Ranges/Top Ranges page and in the hot_ranges_stats log events; the advanced hot-ranges.sh debug script also partitions hot ranges by queries_per_second, writes_per_second, read_bytes_per_second, and write_bytes_per_second.

-- Range layout and leaseholder per range (expensive: poll at 5-10 min intervals, not per-scrape)
SELECT range_id, start_pretty, lease_holder, range_size
FROM crdb_internal.ranges
ORDER BY lease_holder
LIMIT 20;

Querying crdb_internal.ranges is a cluster-wide KV join and is expensive. Running it at a typical scrape interval (15-30 seconds) will degrade cluster performance. Poll every 5-10 minutes at most, and prefer SHOW RANGES FROM TABLE <table> WITH DETAILS when you need leaseholder and key-range information for a single table. Use these for diagnosis or low-frequency trending, not continuous monitoring; for per-range QPS, use the Hot Ranges/Top Ranges page or the hot ranges logs.

Privilege and stability caveats. crdb_internal tables are read-only and version-sensitive; their schema may change between releases without notice. Reading crdb_internal.ranges requires the admin role or the VIEWACTIVITY/VIEWACTIVITYREDACTED/ZONECONFIG privileges, and most crdb_internal objects are treated as unsafe internals gated behind the allow_unsafe_internals session setting (default off). Do not build automated monitoring pipelines that depend on specific column names without version-pinning and testing per release.

Interpreting the results:

The load-based splitting threshold. CockroachDB attempts to split ranges that exceed a QPS threshold automatically. The default kv.range_split.load_qps_threshold is 2500 QPS. If a range consistently exceeds this threshold but does not split, it likely contains a “popular key” - a single row or narrow key range receiving most of the traffic. A single hot row cannot be split further by the load-based splitter. This is the signature of a counter or sequence table.

What causes hot ranges

Hot ranges are almost always caused by key access patterns that concentrate traffic on a narrow portion of the keyspace.

Sequential primary keys. SERIAL and auto-incrementing primary keys generate monotonically increasing values. All inserts land at the end of the key range, and the range containing the current end of the sequence receives all write traffic. This is the most common cause.

Timestamp-prefixed keys. Primary keys that start with a timestamp (for example, (created_at, id)) concentrate inserts in the range covering the current time window. Reads for recent data also pile onto the same range.

Single-row counters and sequence tables. An application-maintained counter stored in a single row, or a sequence table used for ID generation, sends all updates to one key. The range containing that key cannot split because traffic is concentrated on a single row.

Unbalanced partition keys. Hash partitioning with a skewed key distribution can still concentrate traffic if the hash function does not spread the workload evenly.

If the Hot Ranges/Top Ranges page or the hot ranges logs show a hot range, check the table’s primary key definition. If the primary key is SERIAL, auto-incrementing, or timestamp-prefixed, you have found the cause.

Signals to watch

Short-term and long-term responses

Once you identify a hot range, the response depends on whether you need immediate relief or a permanent fix.

Short-term: manual range split. ALTER TABLE ... SPLIT AT forces a range boundary at a specific key. This can distribute traffic across two leaseholders if the hot range spans multiple key values. It does not help if heat is concentrated on a single row. This is a schema-affecting operation that changes range layout cluster-wide; test in a non-production environment first and apply during a maintenance window.

Long-term: redesign the primary key. Replace sequential keys with UUID or hash-prefixed keys that distribute writes uniformly across the keyspace. This is the only permanent fix for sequential-key hotspots.

No fix for single-row counters. A single-row counter cannot be split. Options include application-level sharding (maintaining N counter rows and summing on read) or accepting the hotspot and ensuring the leaseholder node has sufficient CPU headroom.

For the full diagnostic procedure and failure pattern details, see CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles.

How Netdata helps

Netdata’s per-second metrics collection makes CPU and QPS asymmetry visible without expensive crdb_internal queries:

• Per-node CPU breakdown. sys_cpu_user_ns and sys_cpu_sys_ns per node at per-second resolution. Hot range asymmetry appears as one node consistently higher than peers, visible side-by-side across all nodes.
• QPS correlation. Per-node sql_select_count, sql_insert_count, sql_update_count, and sql_delete_count rates confirm whether CPU imbalance correlates with traffic concentration. Per-second granularity catches transient asymmetry that 15-30 second scrape intervals miss.
• Leaseholder distribution. The leases_count metric per store shows whether one node holds disproportionate leases, which mimics hot range symptoms and must be ruled out.
• Contention and latency correlation. When CPU asymmetry appears, correlate it with txn_restarts, SQL latency P99, and admission control queue depth to narrow the diagnosis: is the hot range causing contention, throttling, or pure CPU saturation?
• No expensive queries for continuous monitoring. Per-node Prometheus metrics give you the hot range early warning signal continuously, without the cluster-wide RPC cost of crdb_internal.ranges. Reserve the expensive query for targeted investigation.

Netdata’s CockroachDB monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload
• CockroachDB monitoring checklist: the signals every production cluster needs
• CockroachDB monitoring maturity model: from survival to expert
• CockroachDB node liveness failure: heartbeats, lease redistribution, and flapping
• CockroachDB clock offset high: catching clock drift before self-termination
• CockroachDB clock skew cascade: how shared NTP drift causes quorum loss
• CockroachDB clock synchronization error: this node is more than 500ms away from at least half of the known nodes
• CockroachDB compaction backlog growing: when Pebble can’t keep pace with writes
• CockroachDB storage_l0_sublevels climbing: the earliest warning of write stalls
• CockroachDB LSM compaction death spiral: L0 sublevels, read amplification, and write stalls
• CockroachDB Pebble write stalls: when the storage engine refuses writes