YugabyteDB tablet server drops and skew show up

Not specified by the original author.

Watch numtabletserverslive against your node count; a drop means a tablet server failed heartbeats or is partitioned, often from clock skew. If hybridclockskew is approaching hybridclock_error, fix clock sync on that host before reads start restarting. YSQL counters only move under load, so exercise the cluster before concluding metrics are broken.

Context: Instrumentation runbook: documents the gotcha that an idle YugabyteDB cluster shows no datapoints for YSQL counters, and that a dropped tablet server is the first suspect when numtabletserverslive falls below the node count. When hybridclockskew approaches hybridclock_error, the node is at risk of read restarts and stalls, so check NTP or chrony on that host.

Matched source

Source: Published skill Original query: "YugabyteDB tablet server drops and skew show up in server metrics first" Key terms: drops, first, metrics, server, show, skew, tablet, yugabytedb

Source: Instrumentation runbook: documents the gotcha that an idle YugabyteDB cluster shows no datapoints for YSQL counters, and that a dropped tablet server is the first suspect when numtabletserverslive falls below the node count. When hybridclockskew approaches hybridclock_error, the node is at risk of read restarts and stalls, so check NTP or chrony on that host.

Verified resolution: Watch numtabletserverslive against your node count; a drop means a tablet server failed heartbeats or is partitioned, often from clock skew. If hybridclockskew is approaching hybridclock_error, fix clock sync on that host before reads start restarting. YSQL counters only move under load, so exercise the cluster before concluding metrics are broken.

Source URL: https://github.com/base-14/docs/blob/HEAD/docs/instrument/component/yugabytedb.md

Not specified by the original author.

TL;DR

If YSQL says 500 ms and DocDB says 100 ms, the missing 400 ms is burned in the query layer: too many storage round trips, tablet leaders sitting in another zone, or connection churn. Confirm the gap with EXPLAIN (ANALYZE, DIST) and the RPC counts in the plan. Then cut the round trips: push filters down, read from local followers, or colocate the tables.

EXPLAIN (ANALYZE, DIST): Execution Time: 512.4 ms; DocDB storage time: ~95 ms; RPCs to storage: 3,842

Steps

1. Run EXPLAIN (ANALYZE, DIST) on the slow query and compare total execution time with the storage-layer time shown in the plan. Expected: a large gap between the two. That confirms the overhead is in the query layer, not in storage.

2. Look at the RPC count in the plan. Nested-loop joins over remote data or thousands of small index seeks show up as thousands of storage RPCs. Expected: an RPC count far higher than the query shape justifies. Each RPC pays a network round trip.

3. Check tablet-leader placement. Use the master UI or yb-admin to see whether the tablet leaders live in a different zone or region than the YSQL node running the query. Expected: leaders remote means every single RPC pays cross-zone latency, which multiplies fast.

4. Cut the round trips. For read-heavy queries enable follower reads so YSQL reads from the local replica, colocate small related tables so joins stay local, or rewrite the query so filters push down to storage. Expected: query-layer time collapses toward storage-layer time.

5. Rule out connection churn. Check pgstatactivity or your pooler for high connection turnover per query. Expected: steady pooled connections with no per-query connect cost. If connects are churning, fix the pooler before blaming the query layer.

Use this when

• YSQL latency is several times the DocDB latency for the same query.
• EXPLAIN shows thousands of storage RPCs for a modest query.
• Tablet leaders are not in the same zone as the querying node.
• The same query is fast when leaders are local and slow when they are remote.

Not for this skill when

• Storage-layer time itself is high. That is a DocDB problem: compaction, disk, or cache, a different investigation.
• Every query is slow including trivial ones. Check node health and network before query plans.
• You are comparing YSQL to raw PostgreSQL. Some distributed overhead is structural; this skill is about the 5x gap, not parity.

Variant phrasings

• yugabytedb ysql slow docdb fast
• yugabyte query layer overhead high
• yugabydb too many RPCs nested loop join
• yugabyte follower reads latency
• EXPLAIN ANALYZE DIST yugabytedb slow

Why it happens

YugabyteDB splits the work: the YSQL query layer (PostgreSQL-compatible) plans and executes, while DocDB stores the data, often on different nodes. A query plan that looks cheap to PostgreSQL can be expensive here, because each index seek or join probe is a network RPC to a tablet server. Multiply a few thousand RPCs by cross-zone latency and the query layer dwarfs storage. The plan is correct; the cost model just did not account for the network.

Edge cases

• Follower reads trade freshness for speed: you may read slightly stale data. Fine for dashboards, not for balances.
• Colocation helps small tables but colocating a huge table with small ones drags the small ones into its tablet splits. Colocate deliberately.
• Some ORMs generate nested-loop-friendly SQL that is fine on single-node Postgres and terrible here. The fix may be in the generated SQL, not the database.
• After moving leaders or enabling follower reads, re-run EXPLAIN. Stale plans and cached connections can hide the improvement for a few minutes.

Provenance

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

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

Thread query: yugabytedb 'query layer' latency 5x the storage layer: how to confirm

TL;DR: If YSQL says 500 ms and DocDB says 100 ms, the missing 400 ms is burned in the query layer: too many storage round trips, tablet leaders sitting in another zone, or connection churn. Confirm the gap with EXPLAIN (ANALYZE, DIST) and the RPC counts in the plan. Then cut the round trips: push filters down, read from local followers, or colocate the tables.

Not specified by the original author.

If you see ERROR 40001 Restart read required, check clock sync between nodes first with NTP or chrony. Lowering maxclockskew_usec can reduce read latency, but only to a value your hardware or cloud provider guarantees in all situations; going below actual skew hurts availability or breaks consistency.

Context: YugabyteDB clock-skew writeup: documents the gotcha that ERROR 40001 Restart read required comes from clock skew between nodes, and the conservative 500ms default adds 500ms of latency to serializable read-only queries. Only lower maxclockskew_usec if your platform guarantees tighter skew, including restarts and VMotion.

Matched source

Source: Published skill Original query: "YugabyteDB ERROR 40001 Restart read required signals clock skew" Key terms: 40001, clock, error, read, required, restart, signals, skew, yugabytedb

Source: YugabyteDB clock-skew writeup: documents the gotcha that ERROR 40001 Restart read required comes from clock skew between nodes, and the conservative 500ms default adds 500ms of latency to serializable read-only queries. Only lower maxclockskew_usec if your platform guarantees tighter skew, including restarts and VMotion.

Verified resolution: If you see ERROR 40001 Restart read required, check clock sync between nodes first with NTP or chrony. Lowering maxclockskew_usec can reduce read latency, but only to a value your hardware or cloud provider guarantees in all situations; going below actual skew hurts availability or breaks consistency.

Source URL: https://Dev.to/yugabyte/observing-clock-skew-error-40001-restart-read-required-580j