yugabyte/yugabytedb-skills/yb-perf

YugabyteDB performance: must-checks

You already know how to diagnose a slow Postgres-style system and read a monitoring stack. This skill adds only what is different on YugabyteDB, plus the rules that stop the common wrong conclusions. It covers two layers: SQL / session (S rows) and metrics (M rows).

Process (any slowness question)

1. Scope first: one path slow, or many unrelated paths slow at once? Use the app's

per-endpoint / activity latency if available, else the spread across pg_stat_statements. Many at once → node, quorum or resource. One path → a query: find the statements that path runs. The top total_exec_time query is often a healthy batch job. This gate decides between layers: a node problem slows every path that touches that node. If the complaint is one path and other paths run at normal latency, the cause is on that path (its statements, its connections, its client), and any node finding is secondary.

1. Run the one-call checks in the order the scope sets, from this skill's directory,

before any hand-written query. They are cheap and read-only, and each layer is blind to causes the other sees (metrics miss scan amplification, connection churn / catalog warm-up, unindexed FK cascades; SQL misses a CPU-starved node and leader skew).

• One path slow → scripts/must-check.sql first, and explain that path from its

statements, connections and client. Run scripts/metrics-check.sh only if the path's statements don't explain it, and treat a node finding as secondary unless other paths are slow too.

• Many paths slow, or metrics are all you have → scripts/metrics-check.sh first, then

scripts/must-check.sql (if reachable) to rule out a query driving the node.

• Scope unknown → ask, or find it (app per-activity latency, the pgss spread) before

choosing. If a layer is unreachable, say so in the report. With pasted evidence, apply the checks to what you have.

• SQL: run it once, as one call, with ysqlsh -f <path> or the session wrapper you were given.
• Metrics: bash <path>/metrics-check.sh http://<prometheus>:9090. On YBA set the scope first:

YB_SEL='node_prefix="<prefix>",export_type="tserver_export"' and YB_MASTER_SEL='node_prefix="<prefix>",export_type="master_export"' (header of the script has the rest). It prints per-node tables, flags any node at 2× or more its peers' median, and ends with a per-node verdict.

1. Drill into the layer the evidence points at; confirm with the other (conflicts in

metrics → retries / ASH in SQL; a uniform SQL latency rise → the per-node M rows).

1. Rank causes by the latency the user feels, not by database time spent. Waits (row

locks, conflict retries, queueing) are excluded from pg_stat_statements timings, so a 1 ms statement can be the 500 ms problem.

1. Break every latency, CPU or queue metric down per node before reading an aggregate. On

RF3, one bad node is diluted about 3× in a cluster average. Compare each node with its peers; the outlier is the finding.

1. **When the complaint is broad (many paths slow) and one node is a large, new latency

outlier, explain that node first. A node leading the hottest tablets routinely runs 2-3× its peers in a healthy cluster, so a modest ratio is a lead, not a cause, and never outranks a SQL finding that explains the path the user named. Name the outlier's ceiling: CPU limit, disk, or a noisy neighbour. Low CPU on the slow node does not** rule out CPU: a capped node cannot use more, so compare its usage with its limit (M3c, or the container / VM size). A busy table on that node is the cause only if its load is new or abnormal for that table -- table load sits where the table's leaders are, and the same load on a healthy node would be fast.

1. **Before writing "no database-side cause" or "infrastructure is healthy", list which

must-checks you ran and what each showed.** Any check you couldn't run is an open gap, not a clean result. A node that is up is not a node that is healthy.

Must-checks: SQL / session

Must-checks: metrics (per node)

Safety

• Read-only by default. Anything that changes the system (DDL, cancelling transactions,

statistics resets, GUC / GFlag changes) is proposed for approval, not run.

• EXPLAIN ANALYZE executes the statement -- never run it on INSERT/UPDATE/DELETE (or any

write); use plain EXPLAIN, or an equivalent SELECT.

Scripts and references

• scripts/must-check.sql -- run first for SQL: S1-S5, S8, S9 plus context (version, pgss window), p99/p50, unindexed foreign keys and abnormal terminations.
• scripts/metrics-check.sh -- run first for metrics: M1-M10 in one Prometheus pass (read-only HTTP GETs; Read and Write mean and p99), YBA or self-managed selectors. M11 (app metrics) is a reminder only: check it yourself.
• scripts/ash.sql -- when time goes to waits: ASH wait breakdown, top query_id, hot tablets, zone split (-v window='30 minutes').
• scripts/all-nodes.sh -- pgss, pg_stat_activity, pg_locks and ASH are per node: run either SQL file on every node (--help for host mapping, --exec, --kube).
• references/yb-facts.md -- read before relying on a column, GUC, default, version gate or wait-event name: pgss, stats, locks, EXPLAIN, hints, query diagnostics, ASH.
• references/access.md -- read when there is no direct ysqlsh: YBA Kubernetes, Aeon, paste mode, CSV exports.
• references/metric-names.md -- read before writing PromQL beyond the script: exact metric and label names, YBA vs self-managed labels.
• references/yb-metric-facts.md -- read when interpreting a metric value: leader counts, percentiles, tablet limits and splitting, metric sources (YBA, node endpoint, Aeon).
• YBA's Prometheus listens on port 9090 of the YBA host; node_prefix is the universe's universeDetails.nodePrefix (YBA API GET /api/v1/customers/{cUUID}/universes/{uUUID}). For more, if the yba-api skill is installed, see its Prometheus reference.

Report

• One primary cause tied to the complaint, with its evidence (per-node evidence, naming the

node or component, when it is an infrastructure cause).

• Then secondary findings, explicitly subordinated.
• Then the must-checks you ran (S and M), what each showed, and the gaps: checks or whole

layers you couldn't reach.