13. Tuning and operations

Most CockroachDB performance work is not tuning. It is finding out which of five things is happening, because the fixes have nothing in common:

   ① a bad PLAN             → statistics, indexes   ch 9
   ② CONTENTION on a row    → schema, FOR UPDATE    ch 5
   ③ a HOT RANGE            → key design            ch 3, 10
   ④ DISTANCE to quorum     → locality, localities  ch 11
   ⑤ the STORAGE layer      → disks, compaction     here

This chapter is the diagnostic order, and the small set of knobs actually worth touching.

Hardware, which is most of it

   DISKS      local NVMe SSD. every commit is an fsync
              on a majority of replicas, so flush latency
              is the floor under all write latency.
              Network-attached storage with variable
              latency is the most common self-inflicted
              performance problem.

   CPU        8 vCPU minimum per node for real work;
              4 is a demo. Too-small nodes cause
              cascading slowness under load.

   RAM        the SQL memory pool and Pebble's block
              cache each default to 25% of system RAM.

   NODES      fewer, bigger nodes beat many tiny ones
              for the same total capacity — less range
              overhead, fewer network hops.

cockroach start --cache=.25 --max-sql-memory=.25 …

The metrics that matter

The DB Console has hundreds; these are the ones to alert on.

   LIVENESS
     node count, liveness heartbeat latency
     → a stalled liveness write stops the cluster (ch 4)

   LATENCY
     SQL service latency p99, per statement
     KV commit / raft.process.commandcommit latency
     → separates SQL-layer from storage-layer problems

   STORAGE
     capacity used, LSM read amplification,
     compaction backlog
     → read amp climbing past ~10 means Pebble is behind

   RANGES
     under-replicated ranges (should be 0 at rest)
     unavailable ranges (should ALWAYS be 0)
     hot ranges (QPS per range)

   TXNS
     transaction restarts per second, by type
     contention events
     → the direct link to chapter 5

Danger

Unavailable ranges above zero is a page-somebody incident, not a warning. It means a range has lost a majority of its replicas, so queries touching it hang. Under-replicated ranges are normal briefly after a node restart, and alarming if they persist past the re-replication window.

Finding the expensive statement

-- the statements costing the most total time
SELECT key, count, service_lat_avg, rows_avg, retries
FROM crdb_internal.node_statement_statistics
ORDER BY service_lat_avg * count DESC LIMIT 20;

The DB Console’s Statements and Transactions pages are the same data with fingerprints, plans and contention attached, and are usually faster to work with. What you are looking for, in order:

   rows READ ≫ rows RETURNED   → index problem      ch 9, 10
   high CONTENTION time        → hot row            ch 5
   high NETWORK time           → distance/locality  ch 11
   high KV time, low SQL time  → storage or scan size
   retries > 0 and climbing    → serialization conflicts

Then, for one statement:

EXPLAIN ANALYZE (DIST, VERBOSE) SELECT …;

Finding the hot range

-- per-range traffic
SELECT * FROM crdb_internal.cluster_replicas LIMIT 20;

SHOW RANGES FROM TABLE events WITH DETAILS;

cockroach node status --ranges --certs-dir=certs

One node at 90% CPU while the rest idle is chapter 3’s problem almost every time. Load-based splitting will have already tried; if the traffic is on a single key it cannot help, and the fix is in the schema.

Finding contention

SELECT * FROM crdb_internal.cluster_contention_events
ORDER BY num_contention_events DESC LIMIT 10;

-- which keys, in human form
SELECT * FROM crdb_internal.transaction_contention_events LIMIT 10;

The classic shapes and their fixes:

   a counter row everyone updates   → shard the counter,
                                      or aggregate elsewhere
   a sequence / monotonic key       → UUID or hash shard
   long-running txns holding rows   → shorten them; move
                                      external calls OUT
                                      of the transaction
   read-modify-write races          → SELECT … FOR UPDATE

Settings worth knowing

Most cluster settings should be left alone. These are the ones that legitimately come up:

-- keep a plan regression from becoming an outage
SET disallow_full_table_scans = on;            -- session/app default

-- how long a dead node waits before re-replication
SET CLUSTER SETTING server.time_until_store_dead = '5m';

-- MVCC history: time travel, incrementals, changefeed resume
ALTER DATABASE store CONFIGURE ZONE USING gc.ttlseconds = 14400;

-- statement timeouts, so one query cannot hold resources forever
SET CLUSTER SETTING sql.defaults.statement_timeout = '30s';

-- admission control: keep foreground traffic ahead of background work
SHOW CLUSTER SETTING admission.kv.enabled;

SHOW ALL CLUSTER SETTINGS;      -- everything, with defaults marked
SELECT * FROM crdb_internal.cluster_settings WHERE value != default_value;

That last query is the first thing to run on a cluster you have inherited: it shows exactly what somebody changed and never wrote down.

Admission control

Under overload, CockroachDB deliberately queues work by priority rather than letting everything degrade together: foreground SQL ahead of background jobs, leaseholder work ahead of follower catch-up. You will see it as elevated admission queue metrics rather than as errors.

This is why a cluster under heavy background load (a large backfill, a restore, a changefeed initial scan) stays usable, and also why those jobs take longer than you expected. That ordering is correct; do not disable it to make a job finish sooner.

Routine operations

# add a node: just start it with --join. nothing else.
cockroach start --join=n1,n2,n3 …

# remove a node PROPERLY — moves replicas off first
cockroach node decommission 8 --certs-dir=certs --wait=all

# rolling upgrade: one node at a time, watch ranges recover
cockroach node drain 4 --certs-dir=certs
# …restart node 4 on the new binary, wait, then the next

# finalise, once every node is upgraded and you are happy
SET CLUSTER SETTING version = crdb_version();

Caution

Never remove a node by shutting it down and deleting its data. Decommission moves its replicas elsewhere first; a hard removal leaves every range it held under-replicated until the dead-node timer fires, and removing two such nodes in quick succession can cost you a majority.

Connection management

   • point the load balancer at ALL nodes; health-check
     /health?ready=1 so draining nodes are removed
   • use a connection POOL sized to the cluster, not to
     the app instance count — each connection costs
     memory on the gateway
   • set statement_timeout and idle_in_transaction_
     session_timeout in the app's defaults
   • retry 40001 with exponential backoff (ch 5)

Capacity planning

Three ceilings, and they are hit in a predictable order:

   ① CPU on the hottest node
      usually a hot range (ch 3) long before it is
      genuine cluster-wide saturation. Check the
      spread before buying hardware.

   ② DISK
      aim to stay under 60%. above 80% the allocator
      struggles to rebalance, and a full store stops
      accepting writes entirely. Remember replication:
      1 TB of data is 3 TB of storage.

   ③ RANGE COUNT per node
      thousands of ranges per node is normal; hundreds
      of thousands is overhead — Raft, heartbeats,
      snapshots. Many tiny tables, or aggressive manual
      splits, produce this.

Adding a node is the least disruptive way to buy headroom for all three at once: start it with --join and the allocator moves replicas onto it over the following minutes, without any other action.

Upgrades

   1. read the release notes for EVERY version you are
      crossing — not only the target
   2. take a backup, and confirm it succeeded
   3. drain → restart → wait for ranges to recover →
      next node. one at a time.
   4. run on the new binary for a while. the cluster
      is in a MIXED-VERSION state and can be rolled
      back by restarting nodes on the old binary.
   5. finalise: SET CLUSTER SETTING version = crdb_version();
      after this, rollback is no longer possible.

That deliberate pause between step 3 and step 5 is the feature. Use it: run real traffic on the new version before you take away your own escape route.

Caution

Do not skip major versions. Upgrades are supported from one release to the next, and jumping two means an unsupported path — usually discovered when a node refuses to start and the cluster is half-upgraded.

Logs and the console

# structured logs, configurable by channel
cockroach start --log-config-file=logs.yaml …

   CHANNELS worth routing somewhere you will look
     OPS          node start/stop, decommission
     HEALTH       liveness, disk, clock warnings
     SQL_SCHEMA   schema-change jobs
     SQL_EXEC     slow queries, if you enable it
     STORAGE      Pebble and compaction

The DB Console (:8080 by default) is not a nice-to-have here. The Statements, Transactions, Hot Ranges, Network Latency and Jobs pages each answer a question this guide has posed, and answer it faster than any query. Learn where those five pages are before you need them at 03:00.

A weekly ten minutes

   □ unavailable ranges = 0, under-replicated = 0
   □ p99 SQL latency unchanged since last week
   □ retry rate and contention events not climbing
   □ LSM read amplification stable, disks < 60% full
   □ backup schedule succeeded, and a restore was
     tested this month                        (ch 12)
   □ changefeed lag well inside gc.ttlseconds  (ch 12)
   □ settings diff still matches what you intended

What to take from this chapter

• Diagnose in order: plan, contention, hot range, distance, storage. The fixes share nothing, so guessing wastes the most time.
• Local NVMe matters more than any setting. Every commit is an fsync on a majority; variable-latency network storage is the classic self-inflicted problem. Prefer fewer, bigger nodes.
• Alert on unavailable ranges (always zero), under-replicated ranges, liveness heartbeat latency, p99 SQL latency, LSM read amplification, and transaction restarts.
• Find the expensive statement in node_statement_statistics or the Statements page, then read rows read vs returned, contention time, network time and retries to identify which of the five problems it is.
• A single node at high CPU while others idle is nearly always a hot range from chapter 3, and the fix is in the schema, not the settings.
• Leave most settings alone; know disallow_full_table_scans, gc.ttlseconds, server.time_until_store_dead and statement timeouts. On an inherited cluster, first list the non-default settings.
• Admission control keeps foreground traffic ahead of background jobs under overload — that is why big jobs are slow, and it is correct.
• Always decommission, never hard-remove. Drain before restarting during rolling upgrades, and finalise only when every node is upgraded.

Next: failure modes — the specific ways this cluster goes wrong, what each one looks like from the outside, and what to do about it.