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

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

The database is telling the truth: your transactions are colliding, so it aborts them and asks the client to retry. In a hot loop where every worker touches the same rows, almost every attempt collides and the retries snowball. Make the client retry properly with backoff, shrink each transaction so the contention window is tiny, batch the writes, and if one row is the battlefield, stop sharing it.

ERROR: restart transaction: TransactionRetryWithProtoRefreshError: too much contention, retry the transaction

Steps

1. Confirm it is contention, not something else. In DB Console go to Statements and sort by retry count, or check the transaction-retry metrics. Expected: one statement with a huge retry count, far above the rest. That is the hot loop.

2. Make the client retry correctly. Wrap the transaction in the driver's CockroachDB retry helper, or catch SQLSTATE 40001 and retry with exponential backoff and jitter. Expected: the errors turn into transparent retries. Failures seen by users drop even before you fix the contention.

3. Shrink the transaction. Move anything that is not a database write out of the txn, and do the contended write last so the lock is held for the shortest time. Expected: retry counts fall because the window where two transactions can collide gets smaller.

4. Batch the writes. Replace N single-row upserts in a loop with one multi-row INSERT ... ON CONFLICT. Expected: lock acquisitions drop roughly by N, and throughput climbs.

5. Eliminate the hot row. If every transaction updates the same counter or status row, shard it across N rows and sum on read, or replace the shared row with a queue. Expected: retries fall to near zero because workers stop queuing on one key.

Use this when

• Clients see restart transaction errors under concurrent load.
• DB Console shows one statement with an outsized retry count.
• Many workers update the same rows: counters, status flags, queue heads, inventory counts.
• Retries spike with concurrency but vanish in single-threaded tests.

Not for this skill when

• The error is a serialization failure you cannot retry (a constraint violation, for example). Those need a logic fix, not a retry loop.
• Only one client retries forever. Check for a retry loop with no backoff hammering the same doomed transaction.
• Transactions fail without contention errors. Look at the actual error code before assuming retries will help.

Variant phrasings

• cockroachdb restart transaction too much contention
• cockroachdb 40001 retry transaction hot loop
• cockroachdb TransactionRetryWithProtoRefreshError
• cockroachdb retry storm contended row
• how to handle restart transaction in cockroachdb client

Why it happens

CockroachDB uses optimistic concurrency with serializable isolation: transactions proceed without taking heavy locks, and at commit time the database detects write-write conflicts. When two concurrent transactions touch the same keys, one wins and the other is aborted with a restart-transaction error, asking the client to try again. In a hot loop every worker fights over the same rows, so most attempts lose the race and the retry rate explodes. The error is the contention made visible.

Edge cases

• Retrying without backoff turns a contention spike into a permanent retry storm. Always back off with jitter.
• Long transactions that read a lot then write a little are the worst shape for this: they hold a big read snapshot that is likely stale. Restructure to read late and write fast.
• SELECT FOR UPDATE on the hot row serializes everything through one lock and can be slower than letting the retries happen. Measure before adding it.
• Connection pool exhaustion looks similar (timeouts under load) but retries make it worse. Confirm the error code is 40001 before building a retry loop.

Provenance

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

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

Thread query: cockroachdb 'restart transaction' retry errors in a hot loop: how to fix

TL;DR: The database is telling the truth: your transactions are colliding, so it aborts them and asks the client to retry. In a hot loop where every worker touches the same rows, almost every attempt collides and the retries snowball. Make the client retry properly with backoff, shrink each transaction so the contention window is tiny, batch the writes, and if one row is the battlefield, stop sharing it.

Not specified by the original author.

TL;DR

Raise the read timeout only as a band-aid. The real fix is to find the oversized partition and remodel the data so partitions stay small, usually by adding a time bucket to the partition key. Wide partitions happen when one partition key value collects an unbounded number of rows, and a single replica has to chew through all of it before the timeout fires.

com.datastax.driver.core.exceptions.ReadTimeoutException: Cassandra timeout during read query at consistency LOCAL_ONE (1 responses were required but only 0 replica responded)

Steps

1. Run nodetool tablestats on the suspect table and look at the maximum partition size. Command: nodetool tablestats KEYSPACENAME.TABLENAME, then read "Maximum partition size". Expected: one table shows a max partition size in the hundreds of MB or larger. That is your hot table.

2. Confirm it in the logs. On the coordinator node, search system.log for ReadTimeoutException and for "compacting large partition" warnings. Expected: the same table name appears in both the timeout errors and the large-partition warnings.

3. Trace the slow query to find which replica is slow. In cqlsh, run TRACING ON, then run the timed-out query, and look at the per-replica timeline. Expected: one replica dominates the trace while the others return quickly. The wide partition lives there.

4. Check nodetool tpstats for dropped read messages. Expected: the dropped READ count is climbing, which means replicas are timing out on reads instead of answering.

5. Short-term relief: raise the readrequesttimeoutinms setting in cassandra.yaml on the coordinator nodes and restart them. Expected: timeouts stop, but p99 read latency on that table stays high. This buys time, it does not fix the partition.

6. Long-term fix: add a bucket column (for example year-month) to the partition key so rows spread across many partitions, then backfill. Expected: rerunning nodetool tablestats shows max partition size down in the KB to low-MB range, and timeouts stop for good.

Use this when

• Reads fail with ReadTimeoutException on one table while the rest of the cluster answers fine.
• nodetool tablestats shows a max partition size over roughly 100 MB.
• The log shows "compacting large partition" warnings for the same table.
• One replica is consistently slow in query traces.

Not for this skill when

• You see WriteTimeoutException instead. That is a write-path or consistency problem, not a wide partition.
• Nodes are down or unreachable. Check gossip and nodetool status first.
• The timeout comes from the client driver config rather than the server. Check the driver timeout settings before remodeling data.

Variant phrasings

• Cassandra timeout during read query at consistency LOCAL_ONE
• cassandra read timeout on wide row
• nodetool find largest partition in a table
• cassandra "compacting large partition" warning
• read query timed out, one replica slow

Why it happens

Cassandra stores each partition as one unit on its replica set, and a read of a wide partition forces a single replica to deserialize a huge chunk of data before answering. Past roughly 100 MB the work no longer fits inside the read timeout. The usual cause is a partition key with unbounded growth, like events keyed only by user id with no time bucket, so every new row lands in the same partition forever.

Edge cases

• Raising the timeout just hides the problem and lets the wide partition keep growing until compactions also choke on it.
• Heavy tombstones inside the wide partition make reads even slower. Check for tombstone warnings in the log alongside the timeouts.
• Secondary indexes on the table amplify the read, because the index lookup fans out before the wide partition is read. Drop or redesign indexes on hot tables.
• If the table uses collections (sets, lists, maps) that grow without bound, those count toward partition size too.

Provenance

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

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

Thread query: cassandra 'ReadTimeoutException' on a wide partition: how to find the hot partition

TL;DR: Raise the read timeout only as a band-aid. The real fix is to find the oversized partition and remodel the data so partitions stay small, usually by adding a time bucket to the partition key. Wide partitions happen when one partition key value collects an unbounded number of rows, and a single replica has to chew through all of it before the timeout fires.