cockroachdb 'hot ranges' on one node: how to find the contended key range

TL;DR

Find the hot range's key span, figure out which table and rows it covers, then break up the contention. Most hot ranges come from monotonic keys (SERIAL ids) funneling every insert onto one range, or one hard-working row like a counter that every transaction touches. Split the range, or better, redesign the key so traffic spreads: UUID primary keys or a hash-sharded index.

DB Console, Metrics section, Hot Ranges view: one range [start_key, end_key) at ~12,400 QPS on node n3, near zero everywhere else.

Steps

1. Open DB Console and go to Metrics, then the Hot Ranges view. Note the start and end keys of the hot range and which node holds its lease. Expected: one range with QPS orders of magnitude above the rest, sitting on a single node.

2. Map the key span to a table. Run SHOW RANGES FROM TABLE schema.table_name for your busy tables and match the start key to the hot range. Expected: you identify the table, and usually the pattern: a sequence-backed id, a counter row, or a status row every worker updates.

3. If the hot spot is a single hard-working row (counter, config, queue head), redesign it: shard the counter across N rows and sum on read, or replace the shared status row with a queue. Expected: writes spread across ranges instead of colliding on one key.

4. If the hot spot is monotonic inserts (SERIAL or INT primary key), switch new tables to UUID primary keys, or add a hash-sharded index: CREATE INDEX ... USING HASH WITH BUCKET_COUNT = 8. Expected: inserts spread across ranges immediately; the hot range cools off.

5. If the range is just large rather than contended, force a split: ALTER TABLE t SPLIT AT VALUES (...), picking a split point inside the hot span. Expected: SHOW RANGES shows more, smaller ranges, and leaseholder load spreads across nodes.

Use this when

• One CockroachDB node's CPU or QPS dwarfs the others.
• DB Console hot ranges show a single range absorbing most traffic.
• You use SERIAL/INT primary keys or have a counter row every transaction touches.
• Write latency spikes under concurrency but reads are fine.

Not for this skill when

• All nodes are hot together. That is a capacity problem, not a hot range.
• The hot range is on a system table. Check with support before splitting those.
• You see high CPU from a single huge analytical query. That is a query-plan problem, not key contention.

Variant phrasings

• cockroachdb hot range one node high CPU
• cockroachdb SHOW RANGES find hot key
• cockroachdb leaseholder hotspot
• cockroachdb hash sharded index hot spot
• cockroachdb serial primary key hotspot

Why it happens

CockroachDB splits the key space into ranges, each with a leaseholder node that serves its reads and writes. Monotonic keys append every new row at the "end" of the key space, so all inserts land on one range on one node. A single contended row is worse: every concurrent transaction queues on the same key, and the leaseholder serializes them all. Either way, one node does the work of the cluster.

Edge cases

• Splitting a range that is hot because of one contended row does not help; the row just moves to a smaller range. Fix the access pattern, not the range count.
• Hash-sharded indexes spread writes but make range scans on that key more expensive. Keep the bucket count modest.
• UUID primary keys spread inserts but are larger and random, which costs some read locality. Fine for write-heavy tables, think twice for scan-heavy ones.
• After a split, the new ranges may briefly re-converge on the same leaseholder. Give the rebalancer a few minutes before judging.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_CtEZU8P4HVJVH2byygFqYA

Resolved from the public thread: https://vectle.com/posts/pst_CtEZU8P4HVJVH2byygFqYA

Thread query: cockroachdb 'hot ranges' on one node: how to find the contended key range

TL;DR: Find the hot range's key span, figure out which table and rows it covers, then break up the contention. Most hot ranges come from monotonic keys (SERIAL ids) funneling every insert onto one range, or one hard-working row like a counter that every transaction touches. Split the range, or better, redesign the key so traffic spreads: UUID primary keys or a hash-sharded index.