$ guides / cockroachdb / cockroachdb-sql-latency-high ▌

Operations Guides

CockroachDB SQL latency high: sql_service_latency, per-fingerprint diagnosis, and the layers

P99 sql_service_latency is climbing. Applications are timing out or retrying. The DB Console overview chart shows the spike, but the aggregate histogram cannot tell you which query is slow or which subsystem is adding the latency.

The most common diagnostic mistake is stopping at aggregate P99. A single slow query hiding among thousands of fast ones barely moves aggregate P99 but can take down an endpoint. A system-wide storage problem lifts all fingerprints together. Without per-fingerprint breakdown, you cannot distinguish “one query regressed” from “everything got slower.”

What this means

sql_service_latency is a histogram metric (nanoseconds, per-node gateway) measuring end-to-end time from when the cluster receives a SQL statement to when it finishes executing. It includes parse, plan, and execute phases but does not include time to return results to the client. Because it is measured at the gateway node, it includes network time to DistSQL execution nodes.

sql_exec_latency is the execution-only subset. The difference between the two reveals how much time is spent in parsing and planning versus execution.

Properties that affect interpretation:

Histogram buckets are pre-defined. P99 is approximate and can be misleading for bimodal distributions where latency splits between a fast path and a slow path.
Statement statistics are aggregated per fingerprint. A change in workload mix (more expensive queries appearing) looks identical to a system slowdown from the aggregate histogram’s perspective.
Per-fingerprint statistics have a staleness window. CockroachDB collects statement statistics in-memory and flushes them periodically, by default every 10 minutes (sql.stats.flush.interval). For real-time data during an active incident, query crdb_internal tables directly.

Common causes

Cause	What it looks like	First thing to check
LSM compaction debt	All fingerprints slow, KV `exec_latency` rising, read amplification climbing	`storage_l0_sublevels` per store
Transaction contention	Specific fingerprints slow, `txn_restarts` dominated by `writetooold`	`contention_time_avg` in per-fingerprint stats
Hot range bottleneck	One node CPU-saturated while others idle, specific table latency high	Per-range QPS via `crdb_internal.ranges`
Admission control throttling	Admission queue depth and wait durations elevated	Admission metrics per store
DistSQL or network latency	RPC latency elevated between node pairs, queries touching multiple nodes	`round_trip_latency` per node pair
Disk I/O saturation	WAL fsync latency elevated, Raft log commit latency rising	`iostat` on the store device

Quick checks

# Check aggregate SQL service and execution latency
curl -s http://localhost:8080/_status/vars | grep -E 'sql_service_latency|sql_exec_latency'

# Check KV execution latency and Raft log commit latency
curl -s http://localhost:8080/_status/vars | grep -E 'exec_latency|raft.process.logcommit'

# Check LSM L0 sublevels (per store)
curl -s http://localhost:8080/_status/vars | grep storage_l0_sublevels

# Check read amplification
curl -s http://localhost:8080/_status/vars | grep read_amplification

# Check admission control queue wait times and overload
curl -s http://localhost:8080/_status/vars | grep admission

# Check transaction restart rate and cause breakdown
curl -s http://localhost:8080/_status/vars | grep txn_restarts

# Check inter-node RPC latency
curl -s http://localhost:8080/_status/vars | grep round_trip_latency

-- Top 20 statements by execution count with average latency
SELECT key, count, service_lat_avg
FROM crdb_internal.node_statement_statistics
ORDER BY count DESC LIMIT 20;

-- Statements with the highest average contention time
SELECT key, count, contention_time_avg
FROM crdb_internal.node_statement_statistics
WHERE contention_time_avg > 0
ORDER BY contention_time_avg DESC LIMIT 20;

-- Active contention events
SELECT * FROM crdb_internal.transaction_contention_events
ORDER BY contention_duration DESC LIMIT 50;

Note: crdb_internal tables are unsupported, may change between versions, and some require admin privileges. Use them for manual diagnosis during incidents, not for automated monitoring pipelines.

How to diagnose it

flowchart TD
    A["P99 sql_service_latency rising"] --> B["Drill into per-fingerprint stats"]
    B --> C{"One fingerprint or all?"}
    C -->|"One fingerprint slow"| D["Isolate the query"]
    C -->|"All fingerprints slow"| E["System-wide issue"]
    D --> F["Check contention, admission, plan"]
    E --> G["Check L0 sublevels, CPU per node, disk I/O, RPC latency"]
    F --> H["Narrow to layer: contention, admission, or storage"]
    G --> H

Confirm the latency increase is real. Check sql_service_latency P99 on each node individually, not just the cluster aggregate. One slow gateway node can skew the aggregate while most nodes are healthy. Gate your investigation on node uptime greater than 10 minutes to avoid false positives from cold cache after restart.
Break down by fingerprint. Query crdb_internal.node_statement_statistics ordered by service_lat_avg descending. This in-memory table exposes means and variances, not percentiles. If one or a few fingerprints dominate, the problem is query-specific. If all fingerprints are slow, the problem is system-wide. Remember that the stats have a staleness window.
Check the gap between sql_service_latency and sql_exec_latency. If sql_service_latency rises but sql_exec_latency is stable, the overhead is in parsing or planning, not execution. This can indicate missing statistics causing plan regressions, or an overloaded gateway node spending time on plan compilation.
Examine per-fingerprint contention time. Query contention_time_avg in crdb_internal.node_statement_statistics (admission wait time is not exposed as a per-fingerprint column; it exists at the cluster level). High average contention points to serialization conflicts. If you can access admission control metrics, sustained nonzero wait durations mean the system is at capacity and deliberately throttling.
If system-wide, check the storage layer. Pull storage_l0_sublevels, read amplification, and exec_latency per store. L0 sublevels above 10 indicate compaction is falling behind. Read amplification above 25 means each read consults too many SSTables. Rising KV exec_latency without SQL changes confirms the storage or replication layer is degrading.
Check for hot ranges. If latency is concentrated on specific tables, check the DB Console Hot Ranges page, which ranks ranges by QPS (crdb_internal.ranges does not expose a QPS column). A range with 10x the average QPS indicates a hot spot. Look for sequential primary keys, timestamp-prefixed keys, or single-row counters as the root cause.
Check transaction restart causes. Pull txn_restarts and look at the cause breakdown. writetooold means contention (schema or workload design). readwithinuncertainty means clock skew (infrastructure). txnpush means transaction conflicts (application logic). Each requires a different response.
Check for background workload interference. Query crdb_internal.jobs for running backups, schema changes, or imports. These generate I/O that competes with foreground queries. Schema change backfills and backups can measurably increase foreground latency.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`sql_service_latency` P99 per node	Client-visible query latency at the gateway	Sustained P99 above SLO or 2x rolling baseline
`sql_exec_latency` P99 per node	Isolates execution from parse/plan overhead	Diverging from `sql_service_latency` indicates planning overhead
Per-fingerprint `service_lat_avg`	Mean latency per fingerprint catches single-query regressions hidden by aggregates	Any fingerprint above 2x its own rolling baseline
Per-fingerprint `contention_time_avg`	Time spent waiting on transaction locks	Nonzero and growing for OLTP fingerprints
`storage_l0_sublevels` per store	Earliest predictor of storage-driven latency	Above 5 sustained warrants attention, above 10 is active degradation
Read amplification per store (`rocksdb_read_amplification`)	SSTable files consulted per read	Above 25 means compaction health is degraded
`exec_latency` (KV) P99	Storage-layer latency below SQL	Rising without SQL changes means storage or replication degrading
`raft.process.logcommit.latency`	WAL fsync critical path for writes	Above 50ms on SSDs signals I/O saturation
`txn_restarts` by cause	Contention and clock skew diagnostics	`readwithinuncertainty` nonzero means clock problem
`round_trip_latency` per node pair	Inter-node network health	Any pair above 5x baseline
Admission queue wait durations	Flow control throttling indicator	Sustained average wait above 10ms over 5 minutes

Fixes

LSM compaction debt

If storage_l0_sublevels is above 10 and rising:

Reduce write rate. Pause bulk operations (IMPORT, RESTORE, batch inserts). This gives compaction room to catch up.
Check if a specific store is affected. L0 is per-store. One hot store with a slow disk can degrade while others are fine.
Monitor the L0 trend. If L0 is elevated but decreasing, compaction is recovering. If it is still rising with write stalls active, the node needs write load reduction until compaction catches up.
Do not reduce compaction concurrency to lower I/O impact. This trades foreground improvement for worse L0 growth.

See CockroachDB storage_l0_sublevels climbing for detailed treatment.

Transaction contention

If contention_time_avg is high and txn_restarts show writetooold:

Identify the contended keys via crdb_internal.transaction_contention_events.
Check for hot keys. Multiple transactions writing the same key, or sequential key patterns on the same range, create serialization conflicts.
Short-term: reduce the scope or frequency of the contended transactions.
Long-term: redesign the schema to reduce write conflicts. Hash-prefixed keys, explicit locking hints, or workload partitioning can help.

Hot range bottleneck

If one node is CPU-saturated while others idle:

Identify the hot range via the DB Console Hot Ranges page (per-range QPS is not exposed in crdb_internal.ranges).
Short-term: ALTER TABLE ... SPLIT AT VALUES (...) to manually split the hot range. This is a schema change: it persists new range boundaries and triggers background split work (snapshots, rebalancing), so use it deliberately and only for genuinely hot keys.
Long-term: redesign primary keys to avoid sequential patterns. UUID or hash-prefixed keys distribute writes across ranges.

See CockroachDB hot range bottleneck for detailed treatment.

Admission control throttling

If admission wait durations are elevated:

The system is at or beyond capacity. Admission control is protecting itself by adding latency to maintain stability.
Check which queue is active. The store queue (kv-stores) throttles because L0 is elevated. The kv or sql-kv-response queues throttle because of CPU or memory pressure. The elastic-cpu queue throttling is often acceptable (background work being deprioritized).
Reduce load or add capacity. Admission control is not the problem. It is the signal that you have no burst headroom.

Disk I/O saturation

If raft.process.logcommit.latency is above 50ms on SSDs or WAL fsync latency is elevated:

Check iostat for device queueing. Average read latency above 1ms or write latency above 2ms on SSD indicates queueing.
Cloud volumes may be throttling. EBS gp3 baseline (3000 IOPS, 125 MiB/s) can be exceeded by moderate CockroachDB load. Burst credits exhaust, then sustained throttling follows.
Separate WAL onto a dedicated device to isolate the most latency-sensitive I/O from compaction traffic.

Clock skew

If txn_restarts show readwithinuncertainty as a cause:

Check clock_offset_meannanos on all nodes. Any sustained offset above 50% of --max-offset (default 500ms) needs urgent attention.
Check NTP on all nodes: chronyc tracking or ntpstat.
A node self-terminates at 80% of max-offset. Fix the clock before restarting a self-terminated node or it will crash-loop.

Prevention

Establish per-fingerprint baselines. Aggregate P99 hides query-specific regressions. Track P99 per fingerprint against a rolling 1-hour mean. Alert on deviations above 2x.
Instrument L0 sublevel count. This is the single most predictive storage signal. It gives 10 to 30 minutes of warning before write stalls.
Break down transaction restarts by cause. A total retry rate alarm wastes diagnosis time. writetooold, readwithinuncertainty, and txnpush each require a different response.
Use per-node metrics, not cluster averages. One hot leaseholder or overloaded node hides under healthy global metrics.
Monitor admission control as a capacity signal. Regular queuing means zero burst headroom.
Track backup and schema change job duration trends. Backups that grow from 20 minutes to 3 hours eventually overlap with the next backup window, compounding I/O contention.

How Netdata helps

Per-second granularity on sql_service_latency and sql_exec_latency histograms reveals latency shifts that 15 to 30 second scrape intervals miss, especially brief spikes during lease transfers or range splits.
Correlating SQL latency with L0 sublevels, read amplification, and KV exec_latency in a single view collapses the time between seeing the symptom and identifying the storage layer as the cause.
Admission control queue depth and wait durations alongside SQL latency immediately distinguishes throttling-driven latency from storage-driven latency.
Transaction restart cause breakdown correlated with latency tells you whether the tail is contention, clock skew, or application conflict without a separate investigation.
RPC latency per node pair displayed alongside SQL metrics catches DistSQL and network-driven latency that inflates sql_service_latency at the gateway.

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

The Netdata solution

CockroachDB monitoring with Netdata

Netdata monitors CockroachDB with per-second metrics and automatic dashboards. Watch LSM compaction, Raft liveness, clock skew, hot ranges, and intent buildup so the distributed-systems failure modes in these runbooks surface early.

See CockroachDB monitoring → Start monitoring free