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 page gives direct per-range visibility but is manual and point-in-time. The crdb_internal.ranges table provides programmatic access to per-range queries_per_second but is expensive to query and 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 page
Manual, point-in-time, direct"]
    B --> C["Layer 3: crdb_internal.ranges
SQL query, expensive
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 leases_count 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 current CockroachDB versions, the page may be named “Top Ranges” and may display additional per-range metrics including CPU time, read keys, write keys, read bytes, and write bytes.

Access requirements. The Hot Ranges page requires elevated privileges. 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 QPS via crdb_internal.ranges

For programmatic detection, query crdb_internal.ranges directly. This table exposes per-range statistics including queries_per_second.

-- Identify hot ranges by QPS (expensive: poll at 5-10 min intervals, not per-scrape)
SELECT range_id, start_pretty, table_name, lease_holder, queries_per_second
FROM crdb_internal.ranges
ORDER BY queries_per_second DESC
LIMIT 20;

This query is expensive. It performs cluster-wide RPC fan-out to gather per-range statistics from every node. Running it at a typical scrape interval (15-30 seconds) will degrade cluster performance. Poll every 5-10 minutes at most. Use this for diagnosis or low-frequency trending, not continuous monitoring.

Privilege and stability caveats. crdb_internal tables are admin-only and version-sensitive. Their schema may change between releases without notice. Some versions may require enabling allow_unsafe_internals for access. 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 crdb_internal.ranges shows 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