When troubleshooting a write-heavy YugabyteDB workload, one of the first questions is often:
- Are my rows evenly distributed across the tablets?
That is an important question, but it only tells part of the story.
A hash-sharded table can have an almost perfectly even row distribution while one tablet, tablet leader, or node accounts for a disproportionate amount of database activity.
💡 Balanced data does not always mean balanced work.
Row counts tell you where the data lives. Active Session History (ASH) helps tell you where the database is spending its time.
To make that comparison easier, I put together a utility called yb_dml_hotspots.sh.
The script analyzes INSERT, UPDATE, and DELETE workloads and correlates information from pg_stat_statements, YugabyteDB Active Session History, tablet metadata, tablet leadership, and, optionally, the actual distribution of stored rows.
Why Is This Useful?
Imagine a table with six tablets.
A row-distribution check shows that every tablet owns almost exactly 16.67% of the rows.
Perfect, right?
Maybe.
What if one of those tablets accounts for 29% of the ASH activity?
That tablet has only one-sixth of the data, but YugabyteDB is spending almost twice as much active or waiting time there as its share of the rows would suggest.
Looking only at row counts, tablet sizes, or the primary key would not make that obvious.
🔎 The important distinction
A data hotspot and an activity hotspot are not necessarily the same thing.
The goal of this script is to help identify both… and, when possible, show what the hot tablet is spending its time doing.
How the Script Works
yb_dml_hotspots.sh pulls several pieces of YugabyteDB observability data together into one analysis.
It can:
- ● Find matching
INSERT,UPDATE,DELETE, or all three DML statement types inpg_stat_statements. - ● Collect all matching query IDs, even if only the top few statements are displayed.
- ● Query ASH across the YSQL nodes.
- ● Find the hottest tablets for the selected DML workload.
- ● Break activity down by wait class and wait event.
- ● Map tablet IDs back to physical tables and secondary indexes.
- ● Show the leader for each tablet.
- ● Automatically determine the HASH column or columns from the table’s primary key.
- ● Optionally calculate the current stored-row distribution across the tablets.
- ● Compare each tablet’s percentage of rows with its percentage of ASH activity.
- ● Perform the same comparison at the tablet-leader level.
YugabyteDB ASH exposes query_id, tablet-related wait information, wait classes/events, and sample_weight, while query_id can be correlated with pg_stat_statements.queryid. That makes it possible to connect a SQL workload to the distributed tablet activity underneath it.
The script also uses YugabyteDB tablet metadata to associate hash ranges with tablet IDs and leaders.
The ASH-to-Row Ratio
When
--row-counts is enabled, the script calculates:
ASH % / Row %= ASH-to-row ratio
⚡ Example
If a tablet owns 16.67% of the rows but accounts for 30% of the base-table ASH, its ASH-to-row ratio is approximately 1.80.
That is much more interesting than simply saying, “The rows are evenly distributed.”
Where Should You Run It?
A development, performance-test, or pre-production environment is the ideal place to use the full capabilities of the script.
That gives you the freedom to replay a workload, add temporary instrumentation, run row-distribution scans, isolate a particular test window, and compare multiple runs without affecting production traffic.
That does not mean the script is useless in production.
Most of the default analysis reads observability and metadata sources such as ASH, pg_stat_statements, and tablet metadata. The expensive part is optional.
⚠️ Production Caution
The script intentionally does not count rows by default.
The
--row-counts option can scan the entire target table. The --timestamp option can also require a large scan because it derives the workload start with MIN(column).
For a large production table, consider starting with
--since and leaving --row-counts disabled.
Important Options
The script has several options, but a few are especially useful during hotspot analysis.
Using a Temporary Timestamp Column
One challenge with ASH analysis is deciding exactly where the workload of interest begins.
A simple technique during a controlled test is to add a temporary timestamp column before starting the workload.
For example, our demo table will be called event_feature:
ALTER TABLE event_feature
ADD COLUMN inserted_at timestamptz DEFAULT statement_timestamp();
Because statement_timestamp() is evaluated for the SQL statement, rows inserted by the workload get a timestamp associated with the statement that inserted them.
The application does not have to explicitly populate the column.
After the load begins, the script can use:
--timestamp inserted_at
to calculate the start of the test using:
MIN(inserted_at)
The script then restricts ASH analysis to samples from that workload window.
🕒 Why add the timestamp?
ASH is historical. If the database was already active before your test began, simply analyzing the last 20 minutes can include unrelated activity.
A temporary timestamp column gives the script a convenient way to anchor the ASH analysis to the actual test workload.
There is an important tradeoff, however.
On a very large table, calculating MIN(inserted_at) may itself be expensive.
If you already know the load start time, this is better:
--since '2026-09-20 19:53:29-04'
That avoids scanning the target table just to determine when the test began.
⚠️ Don’t Forget to Clean Up
If
inserted_at was added only for this troubleshooting exercise, remove it when the analysis is complete.ALTER TABLE event_feature DROP COLUMN inserted_at;Temporary instrumentation is useful, but it should not quietly become part of the permanent application schema unless you actually want it there.
Demo: Finding an INSERT Hotspot
For this demo, assume we have an application performing a high-volume INSERT workload against this table:
CREATE TABLE event_feature (
feature_hash varchar(40) NOT NULL,
feature_type smallint NOT NULL,
is_anonymous char(1) NOT NULL,
feature_id bigint NOT NULL,
description varchar(150),
payload text NOT NULL,
version smallint NOT NULL,
PRIMARY KEY (
feature_hash HASH,
feature_type ASC,
is_anonymous ASC
)
);
There is also a unique secondary index:
CREATE UNIQUE INDEX event_feature_uk
ON event_feature (feature_id HASH)
INCLUDE (
description,
feature_type,
is_anonymous,
feature_hash,
version,
payload
);
The application performs variable-size multi-row INSERTs.
That is useful for this demo because PostgreSQL/YSQL normalizes different statement shapes into multiple queryid values when the number of VALUES entries differs.
Step 1: Add the Temporary Timestamp
Before starting the load:
ALTER TABLE event_feature
ADD COLUMN inserted_at timestamptz DEFAULT statement_timestamp();
Step 2: Start the Workload
Run the test workload long enough to build a meaningful amount of activity.
For a repeatable pre-production test, I like starting with an empty or known dataset so that the relationship between the workload and the resulting row distribution is easy to understand.
Step 3: Run yb_dml_hotspots.sh
For example:
./yb_dml_hotspots.sh event_feature insert \
--seed 10.0.0.11 \
--row-counts \
--timestamp inserted_at
The script connects to the seed node and discovers the primary YSQL servers using yb_servers(). YugabyteDB exposes host, port, node type, location, and UUID information through this function.
yb_dml_hotspots.sh Script
#!/usr/bin/env bash
set -euo pipefail
# ==============================================================================
# yb_dml_hotspots.sh
#
# Analyze YugabyteDB YSQL DML activity (INSERT / UPDATE / DELETE) using:
# * pg_stat_statements
# * yb_active_session_history
# * yb_tablet_metadata
# * yb_servers()
#
# Safe defaults:
# * Does NOT scan/count the target table unless --row-counts is supplied.
# * Uses a bounded ASH window (default: 20 minutes).
# * Uses primary-cluster YSQL nodes discovered from yb_servers() when --seed
# is used.
#
# Authentication:
# Use normal psql authentication, for example PGPASSWORD or ~/.pgpass.
#
# Privacy:
# All identifiers and network addresses shown in examples are fictitious.
#
# Examples:
# ./yb_dml_hotspots.sh example_table insert --seed 10.0.0.10
# ./yb_dml_hotspots.sh example_table insert --seed 10.0.0.10 \
# --timestamp inserted_at --row-counts
# ./yb_dml_hotspots.sh example_table insert --seed 10.0.0.10
# ./yb_dml_hotspots.sh example_table all \
# --nodes 10.0.0.10,10.0.0.11,10.0.0.12
# ./yb_dml_hotspots.sh some_table update --seed node1 \
# --hash-key "tenant_id,record_id" --row-counts
# ==============================================================================
DEFAULT_PORT=5433
DEFAULT_DB="yugabyte"
DEFAULT_USER="yugabyte"
DEFAULT_WINDOW="20 minutes"
DEFAULT_TOP=10
DEFAULT_QUERY_TOP=10
DEFAULT_ASH_TOP=10
PORT="${DEFAULT_PORT}"
DB="${DEFAULT_DB}"
USER_NAME="${DEFAULT_USER}"
WINDOW="${DEFAULT_WINDOW}"
TOP_N="${DEFAULT_TOP}"
QUERY_TOP_N="${DEFAULT_QUERY_TOP}"
ASH_TOP_N="${DEFAULT_ASH_TOP}"
SEED=""
NODES_ARG=""
TIMESTAMP_COL=""
SINCE_TS=""
HASH_KEY_OVERRIDE=""
ROW_COUNTS=0
SHOW_QUERY_IDS=0
usage()
{
cat <<'USAGE'
Usage:
yb_dml_hotspots.sh TABLE OPERATION [OPTIONS]
Operations:
insert Analyze INSERT statements
update Analyze UPDATE statements
delete Analyze DELETE statements
all Analyze INSERT + UPDATE + DELETE together
Connection options:
--seed HOST[:PORT] Connect to one YSQL node and discover primary
cluster nodes using yb_servers().
--nodes LIST Explicit comma-separated YSQL endpoint list.
Examples:
--nodes host1,host2,host3
--nodes host1:5433,host2:5433,host3:5433
Bypasses yb_servers() discovery.
--port PORT Default YSQL port for endpoints without a port.
Default: 5433
--database DB Database name. Default: yugabyte
--user USER YSQL user. Default: yugabyte
ASH time options:
--window INTERVAL Maximum ASH lookback. Default: "20 minutes"
--since TIMESTAMP Explicit workload start timestamp. ASH starts at
the later of this timestamp and now - --window.
This does not scan the target table.
--timestamp COLUMN Derive workload start using MIN(COLUMN). Useful
after TRUNCATE/reload instrumentation, but note that
MIN() can require reading a large table unless an
appropriate index can satisfy it.
--since and --timestamp are mutually exclusive.
Row-distribution options:
--row-counts Opt in to CURRENT STORED ROW distribution analysis.
This can scan the entire target table and should be
used carefully on very large tables. When base-table
ASH is available, also compares ASH share vs row share
by tablet and tablet leader.
--hash-key COLUMNS Override automatically detected PK HASH key.
Use a comma-separated list for a composite HASH key:
--hash-key "tenant_id,record_id"
Normally this is not needed; the script parses the
primary-key LSM index definition automatically.
Output options:
--top N Number of top entries in summary sections.
Default: 10
--query-top N Number of pg_stat_statements rows to display
per node. Default: 10
Use 0 to show all matching query rows.
This affects display only; all matching query IDs
are always included in the ASH analysis.
--show-query-ids Display the full list of matching query IDs.
By default, only the number of discovered query IDs
is shown. All IDs are always used for ASH analysis.
--ash-top N Number of per-node ASH wait-detail rows to show
for each node. Default: 10
Use 0 to show all per-node ASH wait rows.
-h, --help Show this help
Authentication:
The script uses psql with -w (no interactive password prompt).
Configure ~/.pgpass or export PGPASSWORD before running.
Examples:
./yb_dml_hotspots.sh example_table insert --seed 10.0.0.10
./yb_dml_hotspots.sh example_table insert \
--seed 10.0.0.10 \
--timestamp inserted_at \
--row-counts
./yb_dml_hotspots.sh example_table insert --seed 10.0.0.10
./yb_dml_hotspots.sh example_table all \
--nodes 10.0.0.10,10.0.0.11,10.0.0.12
USAGE
}
fatal()
{
echo >&2
echo "ERROR: $*" >&2
echo >&2
exit 1
}
trim()
{
local value="$1"
value="${value#"${value%%[![:space:]]*}"}"
value="${value%"${value##*[![:space:]]}"}"
printf '%s' "${value}"
}
validate_identifier()
{
local value="$1"
[[ "${value}" =~ ^[A-Za-z_][A-Za-z0-9_]*$ ]]
}
parse_endpoint()
{
local endpoint
local host
local port
endpoint=$(trim "$1")
host=""
port="${PORT}"
[[ -n "${endpoint}" ]] || return 1
# [IPv6]:port
if [[ "${endpoint}" =~ ^\[([^]]+)\]:([0-9]+)$ ]]
then
host="${BASH_REMATCH[1]}"
port="${BASH_REMATCH[2]}"
# hostname:port or IPv4:port
elif [[ "${endpoint}" =~ ^([^:]+):([0-9]+)$ ]]
then
host="${BASH_REMATCH[1]}"
port="${BASH_REMATCH[2]}"
else
host="${endpoint}"
fi
[[ "${port}" =~ ^[0-9]+$ ]] || return 1
(( port >= 1 && port <= 65535 )) || return 1
printf '%s|%s\n' "${host}" "${port}"
}
# Extract HASH-key columns from a YugabyteDB LSM index definition.
# Handles both:
# ... USING lsm (a HASH, b ASC)
# and:
# ... USING lsm ((a, b) HASH, c DESC)
extract_hash_cols_from_indexdef()
{
awk '
function btrim(v) {
sub(/^[[:space:]]+/, "", v)
sub(/[[:space:]]+$/, "", v)
return v
}
function emit_token(tok, cleaned, upper) {
tok = btrim(tok)
if (tok == "")
return
upper = toupper(tok)
if (upper ~ /[[:space:]]HASH([[:space:]]+(ASC|DESC))?[[:space:]]*$/) {
cleaned = tok
sub(/[[:space:]]+[Hh][Aa][Ss][Hh]([[:space:]]+([Aa][Ss][Cc]|[Dd][Ee][Ss][Cc]))?[[:space:]]*$/, "", cleaned)
cleaned = btrim(cleaned)
if (substr(cleaned, 1, 1) == "(" && substr(cleaned, length(cleaned), 1) == ")" && index(cleaned, ",") > 0)
cleaned = substr(cleaned, 2, length(cleaned) - 2)
cleaned = btrim(cleaned)
if (out != "")
out = out ", "
out = out cleaned
}
}
{
original = $0
lower = tolower(original)
using_pos = index(lower, "using lsm")
if (using_pos == 0)
next
rest = substr(original, using_pos)
rest_lower = tolower(rest)
open_pos = index(rest_lower, "(")
if (open_pos == 0)
next
body = ""
depth = 1
for (i = open_pos + 1; i <= length(rest); i++) {
ch = substr(rest, i, 1)
if (ch == "(") {
depth++
body = body ch
}
else if (ch == ")") {
depth--
if (depth == 0)
break
body = body ch
}
else {
body = body ch
}
}
token = ""
depth = 0
for (i = 1; i <= length(body); i++) {
ch = substr(body, i, 1)
if (ch == "(") {
depth++
token = token ch
}
else if (ch == ")") {
depth--
token = token ch
}
else if (ch == "," && depth == 0) {
emit_token(token)
token = ""
}
else {
token = token ch
}
}
emit_token(token)
}
END {
print out
}
'
}
# Build a comma-separated SQL argument list such as:
# d.shard_key
# or:
# d.tenant_id, d.record_id
#
# Only simple unquoted identifiers are accepted for row-count analysis.
build_hash_args()
{
local cols="$1"
local col
local result=""
local -a parts
IFS=',' read -r -a parts <<< "${cols}"
for col in "${parts[@]}"
do
col=$(trim "${col}")
if ! validate_identifier "${col}"
then
return 1
fi
if [[ -n "${result}" ]]
then
result+=", "
fi
result+="d.${col}"
done
[[ -n "${result}" ]] || return 1
printf '%s' "${result}"
}
# ------------------------------------------------------------------------------
# Parse command line
# ------------------------------------------------------------------------------
if [[ $# -eq 0 ]]
then
usage
exit 1
fi
if [[ "$1" == "-h" || "$1" == "--help" ]]
then
usage
exit 0
fi
[[ $# -ge 2 ]] || fatal "TABLE and OPERATION are required. Use --help for usage."
TABLE_INPUT="$1"
OPERATION=$(printf '%s' "$2" | tr '[:upper:]' '[:lower:]')
shift 2
case "${OPERATION}" in
insert|update|delete|all)
;;
*)
fatal "Invalid operation '${OPERATION}'. Use insert, update, delete, or all."
;;
esac
while [[ $# -gt 0 ]]
do
case "$1" in
--seed)
[[ $# -ge 2 ]] || fatal "--seed requires HOST[:PORT]"
SEED="$2"
shift 2
;;
--nodes)
[[ $# -ge 2 ]] || fatal "--nodes requires a comma-separated endpoint list"
NODES_ARG="$2"
shift 2
;;
--port)
[[ $# -ge 2 ]] || fatal "--port requires a port number"
PORT="$2"
shift 2
;;
--database)
[[ $# -ge 2 ]] || fatal "--database requires a database name"
DB="$2"
shift 2
;;
--user)
[[ $# -ge 2 ]] || fatal "--user requires a user name"
USER_NAME="$2"
shift 2
;;
--window)
[[ $# -ge 2 ]] || fatal "--window requires a PostgreSQL interval"
WINDOW="$2"
shift 2
;;
--since)
[[ $# -ge 2 ]] || fatal "--since requires a timestamp"
SINCE_TS="$2"
shift 2
;;
--timestamp)
[[ $# -ge 2 ]] || fatal "--timestamp requires a column name"
TIMESTAMP_COL="$2"
shift 2
;;
--hash-key)
[[ $# -ge 2 ]] || fatal "--hash-key requires one or more comma-separated columns"
HASH_KEY_OVERRIDE="$2"
shift 2
;;
--row-counts)
ROW_COUNTS=1
shift
;;
--top)
[[ $# -ge 2 ]] || fatal "--top requires an integer"
TOP_N="$2"
shift 2
;;
--query-top)
[[ $# -ge 2 ]] || fatal "--query-top requires a non-negative integer"
QUERY_TOP_N="$2"
shift 2
;;
--ash-top)
[[ $# -ge 2 ]] || fatal "--ash-top requires a non-negative integer"
ASH_TOP_N="$2"
shift 2
;;
--show-query-ids)
SHOW_QUERY_IDS=1
shift
;;
-h|--help)
usage
exit 0
;;
*)
fatal "Unknown option '$1'. Use --help for usage."
;;
esac
done
[[ "${PORT}" =~ ^[0-9]+$ ]] || fatal "Invalid port '${PORT}'"
(( PORT >= 1 && PORT <= 65535 )) || fatal "Port must be between 1 and 65535"
[[ "${TOP_N}" =~ ^[0-9]+$ ]] || fatal "--top must be a positive integer"
(( TOP_N > 0 )) || fatal "--top must be greater than zero"
[[ "${QUERY_TOP_N}" =~ ^[0-9]+$ ]] || fatal "--query-top must be a non-negative integer"
[[ "${ASH_TOP_N}" =~ ^[0-9]+$ ]] || fatal "--ash-top must be a non-negative integer"
if [[ -n "${SEED}" && -n "${NODES_ARG}" ]]
then
fatal "Use either --seed or --nodes, not both."
fi
if [[ -n "${SINCE_TS}" && -n "${TIMESTAMP_COL}" ]]
then
fatal "Use either --since or --timestamp, not both."
fi
if [[ -n "${TIMESTAMP_COL}" ]] && ! validate_identifier "${TIMESTAMP_COL}"
then
fatal "Invalid timestamp column '${TIMESTAMP_COL}'. Only simple unquoted identifiers are supported."
fi
# Accept schema.table or table, using normal unquoted PostgreSQL identifiers.
TABLE_INPUT=$(printf '%s' "${TABLE_INPUT}" | tr '[:upper:]' '[:lower:]')
if [[ "${TABLE_INPUT}" == *.* ]]
then
INPUT_SCHEMA="${TABLE_INPUT%%.*}"
INPUT_TABLE="${TABLE_INPUT#*.}"
validate_identifier "${INPUT_SCHEMA}" || fatal "Invalid schema name '${INPUT_SCHEMA}'"
validate_identifier "${INPUT_TABLE}" || fatal "Invalid table name '${INPUT_TABLE}'"
else
INPUT_SCHEMA=""
INPUT_TABLE="${TABLE_INPUT}"
validate_identifier "${INPUT_TABLE}" || fatal "Invalid table name '${INPUT_TABLE}'"
fi
# ------------------------------------------------------------------------------
# Temporary files
# ------------------------------------------------------------------------------
QUERY_INFO_FILE=$(mktemp)
QUERY_ID_FILE=$(mktemp)
QUERY_OP_FILE=$(mktemp)
RAW_FILE=$(mktemp)
TABLET_NODE_FILE=$(mktemp)
TABLET_CLUSTER_FILE=$(mktemp)
WAIT_CLUSTER_FILE=$(mktemp)
OP_ASH_FILE=$(mktemp)
TABLET_META_FILE=$(mktemp)
RELATION_ASH_FILE=$(mktemp)
BASE_META_FILE=$(mktemp)
BASE_TABLET_FILE=$(mktemp)
BASE_LEADER_FILE=$(mktemp)
ROW_DIST_FILE=$(mktemp)
ROW_LEADER_FILE=$(mktemp)
ASH_ROW_TABLET_FILE=$(mktemp)
ASH_ROW_LEADER_FILE=$(mktemp)
cleanup()
{
rm -f \
"${QUERY_INFO_FILE}" \
"${QUERY_ID_FILE}" \
"${QUERY_OP_FILE}" \
"${RAW_FILE}" \
"${TABLET_NODE_FILE}" \
"${TABLET_CLUSTER_FILE}" \
"${WAIT_CLUSTER_FILE}" \
"${OP_ASH_FILE}" \
"${TABLET_META_FILE}" \
"${RELATION_ASH_FILE}" \
"${BASE_META_FILE}" \
"${BASE_TABLET_FILE}" \
"${BASE_LEADER_FILE}" \
"${ROW_DIST_FILE}" \
"${ROW_LEADER_FILE}" \
"${ASH_ROW_TABLET_FILE}" \
"${ASH_ROW_LEADER_FILE}" \
"${BASE_TABLET_FILE}.new"
}
trap cleanup EXIT
# ------------------------------------------------------------------------------
# Discover / build node list
# ------------------------------------------------------------------------------
declare -a NODE_HOSTS=()
declare -a NODE_PORTS=()
declare -A NODE_SEEN=()
add_node()
{
local host="$1"
local port="$2"
local key="${host}:${port}"
if [[ -z "${NODE_SEEN[${key}]+x}" ]]
then
NODE_HOSTS+=("${host}")
NODE_PORTS+=("${port}")
NODE_SEEN["${key}"]=1
fi
}
if [[ -n "${NODES_ARG}" ]]
then
IFS=',' read -r -a NODE_ITEMS <<< "${NODES_ARG}"
for ITEM in "${NODE_ITEMS[@]}"
do
PARSED=$(parse_endpoint "${ITEM}") || fatal "Invalid endpoint '${ITEM}'"
IFS='|' read -r HOST PORT_VALUE <<< "${PARSED}"
add_node "${HOST}" "${PORT_VALUE}"
done
CONNECTION_MODE="explicit node list"
SEED_DISPLAY="not used"
else
if [[ -z "${SEED}" ]]
then
SEED="localhost"
fi
PARSED=$(parse_endpoint "${SEED}") || fatal "Invalid seed endpoint '${SEED}'"
IFS='|' read -r SEED_HOST SEED_PORT <<< "${PARSED}"
echo
echo "=============================================================="
echo " DISCOVERING PRIMARY YSQL NODES"
echo "=============================================================="
echo
echo "Seed: ${SEED_HOST}:${SEED_PORT}"
echo
DISCOVERED=$(
psql \
-X \
-q \
-w \
-h "${SEED_HOST}" \
-p "${SEED_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' \
-c "
SELECT host, port
FROM yb_servers()
WHERE node_type = 'primary'
ORDER BY host, port;
"
) || fatal "Could not discover nodes from seed ${SEED_HOST}:${SEED_PORT}"
[[ -n "${DISCOVERED}" ]] || fatal "yb_servers() returned no primary-cluster YSQL nodes"
while IFS='|' read -r HOST PORT_VALUE
do
[[ -n "${HOST}" ]] || continue
add_node "${HOST}" "${PORT_VALUE}"
done <<< "${DISCOVERED}"
CONNECTION_MODE="seed discovery"
SEED_DISPLAY="${SEED_HOST}:${SEED_PORT}"
fi
(( ${#NODE_HOSTS[@]} > 0 )) || fatal "No YSQL nodes are available for analysis"
# Verify that every endpoint is reachable. Missing a node would make cluster-wide
# pg_stat_statements / ASH analysis incomplete, so fail rather than silently skip.
for i in "${!NODE_HOSTS[@]}"
do
HOST="${NODE_HOSTS[$i]}"
PORT_VALUE="${NODE_PORTS[$i]}"
if ! psql \
-X \
-q \
-w \
-h "${HOST}" \
-p "${PORT_VALUE}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-At \
-c "SELECT 1;" \
>/dev/null 2>&1
then
fatal "Cannot connect to discovered node ${HOST}:${PORT_VALUE}. Use --nodes with reachable YSQL endpoints if yb_servers() advertises addresses that are not reachable from this host."
fi
done
CONTROL_HOST="${NODE_HOSTS[0]}"
CONTROL_PORT="${NODE_PORTS[0]}"
# ------------------------------------------------------------------------------
# Resolve target relation
# ------------------------------------------------------------------------------
TABLE_INFO=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' \
-c "
SELECT
n.nspname,
c.relname,
c.oid,
c.relkind
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE c.oid = to_regclass('${TABLE_INPUT}')::oid;
"
) || fatal "Could not resolve table '${TABLE_INPUT}'"
[[ -n "${TABLE_INFO}" ]] || fatal "Table '${TABLE_INPUT}' was not found in database '${DB}'"
IFS='|' read -r SCHEMA_NAME TABLE_NAME TABLE_OID TABLE_RELKIND <<< "${TABLE_INFO}"
QUALIFIED_TABLE="${SCHEMA_NAME}.${TABLE_NAME}"
TABLE_LABEL=$(printf '%s' "${QUALIFIED_TABLE}" | tr '[:lower:]' '[:upper:]')
if [[ "${TABLE_RELKIND}" != "r" && "${TABLE_RELKIND}" != "p" ]]
then
fatal "${QUALIFIED_TABLE} is not a table (relkind=${TABLE_RELKIND})"
fi
# ------------------------------------------------------------------------------
# Detect PK / HASH key
# ------------------------------------------------------------------------------
PK_INDEX_DEF=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-At \
-c "
SELECT pg_get_indexdef(i.indexrelid, 0, true)
FROM pg_index i
WHERE i.indrelid = ${TABLE_OID}
AND i.indisprimary
LIMIT 1;
"
) || fatal "Could not inspect primary key for ${QUALIFIED_TABLE}"
AUTO_HASH_KEY=""
HASH_KEY=""
HASH_KEY_SOURCE="not detected"
HASH_ARGS=""
HASH_KEY_USABLE=0
if [[ -n "${PK_INDEX_DEF}" ]]
then
AUTO_HASH_KEY=$(printf '%s\n' "${PK_INDEX_DEF}" | extract_hash_cols_from_indexdef)
fi
if [[ -n "${HASH_KEY_OVERRIDE}" ]]
then
HASH_KEY="${HASH_KEY_OVERRIDE}"
HASH_KEY_SOURCE="override"
elif [[ -n "${AUTO_HASH_KEY}" ]]
then
HASH_KEY="${AUTO_HASH_KEY}"
HASH_KEY_SOURCE="automatic"
fi
if [[ -n "${HASH_KEY}" ]]
then
if HASH_ARGS=$(build_hash_args "${HASH_KEY}")
then
HASH_KEY_USABLE=1
else
HASH_KEY_USABLE=0
fi
fi
# Verify timestamp column only when requested.
if [[ -n "${TIMESTAMP_COL}" ]]
then
TS_EXISTS=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-At \
-c "
SELECT EXISTS (
SELECT 1
FROM pg_attribute
WHERE attrelid = ${TABLE_OID}
AND attname = '${TIMESTAMP_COL}'
AND attnum > 0
AND NOT attisdropped
);
"
)
[[ "${TS_EXISTS}" == "t" ]] || fatal "Column '${TIMESTAMP_COL}' does not exist on ${QUALIFIED_TABLE}"
fi
# ------------------------------------------------------------------------------
# Determine ASH lower bound
# ------------------------------------------------------------------------------
LOAD_START=""
LOAD_START_SOURCE="window only"
if [[ -n "${SINCE_TS}" ]]
then
# Validate via PostgreSQL rather than attempting timestamp parsing in Bash.
LOAD_START=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-v since_ts="${SINCE_TS}" \
-At \
-c "SELECT :'since_ts'::timestamptz;"
) || fatal "Invalid --since timestamp '${SINCE_TS}'"
LOAD_START_SOURCE="--since"
elif [[ -n "${TIMESTAMP_COL}" ]]
then
echo
echo "NOTE: --timestamp derives the load start with MIN(${TIMESTAMP_COL})."
echo " On a very large table this lookup itself may be expensive unless"
echo " an appropriate index can satisfy it. Use --since to avoid that scan."
echo
LOAD_START=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-At \
-c "
SELECT MIN(${TIMESTAMP_COL})
FROM ${QUALIFIED_TABLE}
WHERE ${TIMESTAMP_COL} IS NOT NULL;
"
) || fatal "Could not derive workload start from ${QUALIFIED_TABLE}.${TIMESTAMP_COL}"
[[ -n "${LOAD_START}" ]] || fatal "Could not derive workload start from ${QUALIFIED_TABLE}.${TIMESTAMP_COL}; the table may be empty"
LOAD_START_SOURCE="MIN(${TIMESTAMP_COL})"
fi
# ------------------------------------------------------------------------------
# Startup summary
# ------------------------------------------------------------------------------
echo
echo "=============================================================="
echo " YB DML HOTSPOT ANALYSIS - CONFIGURATION"
echo "=============================================================="
echo
echo "Target table : ${QUALIFIED_TABLE}"
echo "Table OID : ${TABLE_OID}"
echo "Operation : ${OPERATION}"
echo "Database : ${DB}"
echo "User : ${USER_NAME}"
echo "Connection mode : ${CONNECTION_MODE}"
echo "Seed : ${SEED_DISPLAY}"
echo "ASH window : ${WINDOW}"
echo "Summary top N : ${TOP_N}"
if (( QUERY_TOP_N == 0 ))
then
echo "Query rows : all matching rows"
else
echo "Query rows : top ${QUERY_TOP_N} per node"
fi
if (( ASH_TOP_N == 0 ))
then
echo "Per-node ASH rows : all"
else
echo "Per-node ASH rows : top ${ASH_TOP_N} per node"
fi
if [[ -n "${LOAD_START}" ]]
then
echo "ASH workload start : ${LOAD_START} (${LOAD_START_SOURCE})"
else
echo "ASH workload start : not supplied; using window only"
fi
if [[ -n "${PK_INDEX_DEF}" ]]
then
echo "Primary key : ${PK_INDEX_DEF}"
else
echo "Primary key : not visible/detected"
fi
if [[ -n "${HASH_KEY}" ]]
then
echo "HASH key : ${HASH_KEY} (${HASH_KEY_SOURCE})"
else
echo "HASH key : not detected (possibly range-sharded/internal ybrowid)"
fi
if (( ROW_COUNTS == 1 ))
then
echo "Row counts : ENABLED - may scan the entire table"
else
echo "Row counts : disabled (safe default)"
fi
echo
echo "YSQL nodes:"
for i in "${!NODE_HOSTS[@]}"
do
printf ' %s:%s\n' "${NODE_HOSTS[$i]}" "${NODE_PORTS[$i]}"
done
# ------------------------------------------------------------------------------
# Build DML statement matching regexes
# ------------------------------------------------------------------------------
# pg_stat_statements stores normalized SQL text. Match either unqualified table
# names or schema-qualified names. This utility intentionally targets ordinary
# unquoted/simple identifiers.
TARGET_RE='([[:alnum:]_"]+[.])?"?'"${TABLE_NAME}"'"?'
INSERT_RE='^[[:space:]]*INSERT[[:space:]]+INTO[[:space:]]+(ONLY[[:space:]]+)?'"${TARGET_RE}"'([[:space:]]|[(])'
UPDATE_RE='^[[:space:]]*UPDATE[[:space:]]+(ONLY[[:space:]]+)?'"${TARGET_RE}"'[[:space:]]'
DELETE_RE='^[[:space:]]*DELETE[[:space:]]+FROM[[:space:]]+(ONLY[[:space:]]+)?'"${TARGET_RE}"'([[:space:]]|$)'
case "${OPERATION}" in
insert)
MATCH_PREDICATE="query ~* '${INSERT_RE}'"
;;
update)
MATCH_PREDICATE="query ~* '${UPDATE_RE}'"
;;
delete)
MATCH_PREDICATE="query ~* '${DELETE_RE}'"
;;
all)
MATCH_PREDICATE="(query ~* '${INSERT_RE}' OR query ~* '${UPDATE_RE}' OR query ~* '${DELETE_RE}')"
;;
esac
# ------------------------------------------------------------------------------
# Discover query IDs cluster-wide from node-local pg_stat_statements
# ------------------------------------------------------------------------------
echo
echo "=============================================================="
echo " DISCOVERING ${TABLE_LABEL} DML QUERY IDs"
echo "=============================================================="
echo
for i in "${!NODE_HOSTS[@]}"
do
HOST="${NODE_HOSTS[$i]}"
PORT_VALUE="${NODE_PORTS[$i]}"
echo "Checking pg_stat_statements on ${HOST}:${PORT_VALUE}..." >&2
psql \
-X \
-q \
-w \
-h "${HOST}" \
-p "${PORT_VALUE}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' <<SQL >> "${QUERY_INFO_FILE}"
SELECT
host(inet_server_addr()) || ':' || inet_server_port() AS source_node,
CASE
WHEN query ~* '${INSERT_RE}' THEN 'insert'
WHEN query ~* '${UPDATE_RE}' THEN 'update'
WHEN query ~* '${DELETE_RE}' THEN 'delete'
ELSE 'other'
END AS operation,
queryid,
calls,
rows,
CASE
WHEN calls > 0
THEN ROUND(rows::numeric / calls, 2)
ELSE 0
END AS rows_per_call,
ROUND(total_exec_time::numeric, 2) AS total_exec_ms,
ROUND(mean_exec_time::numeric, 2) AS mean_exec_ms
FROM pg_stat_statements
WHERE ${MATCH_PREDICATE}
ORDER BY calls DESC;
SQL
done
if [[ ! -s "${QUERY_INFO_FILE}" ]]
then
fatal "No matching ${OPERATION} DML statements for ${QUALIFIED_TABLE} were found in pg_stat_statements on any node"
fi
awk -F'|' '{print $3}' "${QUERY_INFO_FILE}" | sort -u > "${QUERY_ID_FILE}"
awk -F'|' '!seen[$3]++ {print $3 "|" $2}' "${QUERY_INFO_FILE}" > "${QUERY_OP_FILE}"
QUERY_IDS=$(paste -sd, "${QUERY_ID_FILE}")
if [[ -z "${QUERY_IDS}" ]]
then
fatal "No query IDs were discovered for ${QUALIFIED_TABLE}"
fi
echo
echo "Matching ${TABLE_LABEL} DML statements:"
echo
if (( QUERY_TOP_N == 0 ))
then
{
echo "node|operation|query_id|calls|rows|rows_per_call|total_exec_ms|mean_exec_ms"
sort -t'|' -k1,1 -k4,4nr "${QUERY_INFO_FILE}"
} | column -t -s '|'
else
echo "Displaying top ${QUERY_TOP_N} matching statement(s) per node by calls."
echo "All matching query IDs are still included in the ASH analysis."
echo
{
echo "node|operation|query_id|calls|rows|rows_per_call|total_exec_ms|mean_exec_ms"
sort -t'|' -k1,1 -k4,4nr "${QUERY_INFO_FILE}" |
awk -F'|' -v n="${QUERY_TOP_N}" '
$1 != previous_node {
previous_node = $1
shown = 0
}
shown < n {
print
shown++
}
'
} | column -t -s '|'
fi
QUERY_ID_COUNT=$(awk 'END {print NR + 0}' "${QUERY_ID_FILE}")
echo
echo "Matching query IDs discovered: ${QUERY_ID_COUNT}"
echo "All ${QUERY_ID_COUNT} query IDs will be included in the ASH analysis."
if (( SHOW_QUERY_IDS == 1 ))
then
echo
echo "Matching query IDs:"
echo
sed 's/^/ /' "${QUERY_ID_FILE}"
fi
# ------------------------------------------------------------------------------
# Collect ASH from every node
# ------------------------------------------------------------------------------
echo
echo "=============================================================="
echo " COLLECTING ${TABLE_LABEL} DML ASH"
echo "=============================================================="
echo
echo "Nodes : ${#NODE_HOSTS[@]}"
echo "Max window : ${WINDOW}"
if [[ -n "${LOAD_START}" ]]
then
echo "Load start : ${LOAD_START}"
fi
echo
for i in "${!NODE_HOSTS[@]}"
do
HOST="${NODE_HOSTS[$i]}"
PORT_VALUE="${NODE_PORTS[$i]}"
echo "Querying ASH on ${HOST}:${PORT_VALUE}..." >&2
if [[ -n "${LOAD_START}" ]]
then
psql \
-X \
-q \
-w \
-h "${HOST}" \
-p "${PORT_VALUE}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-v ash_window="${WINDOW}" \
-v load_start="${LOAD_START}" \
-AtF '|' <<SQL >> "${RAW_FILE}"
SELECT
host(inet_server_addr()) || ':' || inet_server_port() AS source_node,
ash.query_id,
ash.wait_event_aux AS tablet_id_prefix,
ash.wait_event_class,
ash.wait_event,
SUM(COALESCE(ash.sample_weight, 1))::double precision AS weighted_samples
FROM yb_active_session_history ash
WHERE ash.query_id IN (${QUERY_IDS})
AND ash.sample_time >= GREATEST(
:'load_start'::timestamptz,
clock_timestamp() - :'ash_window'::interval
)
AND ash.wait_event_component = 'TServer'
AND ash.wait_event_aux IS NOT NULL
GROUP BY
host(inet_server_addr()) || ':' || inet_server_port(),
ash.query_id,
ash.wait_event_aux,
ash.wait_event_class,
ash.wait_event;
SQL
else
psql \
-X \
-q \
-w \
-h "${HOST}" \
-p "${PORT_VALUE}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-v ash_window="${WINDOW}" \
-AtF '|' <<SQL >> "${RAW_FILE}"
SELECT
host(inet_server_addr()) || ':' || inet_server_port() AS source_node,
ash.query_id,
ash.wait_event_aux AS tablet_id_prefix,
ash.wait_event_class,
ash.wait_event,
SUM(COALESCE(ash.sample_weight, 1))::double precision AS weighted_samples
FROM yb_active_session_history ash
WHERE ash.query_id IN (${QUERY_IDS})
AND ash.sample_time >= clock_timestamp() - :'ash_window'::interval
AND ash.wait_event_component = 'TServer'
AND ash.wait_event_aux IS NOT NULL
GROUP BY
host(inet_server_addr()) || ':' || inet_server_port(),
ash.query_id,
ash.wait_event_aux,
ash.wait_event_class,
ash.wait_event;
SQL
fi
done
if [[ ! -s "${RAW_FILE}" ]]
then
echo
echo "No tablet-associated ASH samples were found for ${TABLE_LABEL}"
echo "${OPERATION} activity in the selected time window."
echo
exit 0
fi
# ------------------------------------------------------------------------------
# ASH by DML operation
#
# This section is useful only when INSERT / UPDATE / DELETE are being analyzed
# together. For a single-operation run it would simply report 100% for the
# operation already selected on the command line, so suppress it.
# ------------------------------------------------------------------------------
if [[ "${OPERATION}" == "all" ]]
then
awk -F'|' '
NR == FNR {
op[$1] = $2
next
}
{
operation = op[$2]
if (operation == "")
operation = "unknown"
samples[operation] += $6
total += $6
}
END {
if (total == 0)
exit
for (operation in samples) {
pct = samples[operation] * 100.0 / total
printf "%s|%.2f|%.2f\n", operation, samples[operation], pct
}
}
' "${QUERY_OP_FILE}" "${RAW_FILE}" > "${OP_ASH_FILE}"
echo
echo "=============================================================="
echo " ASH ACTIVITY BY DML OPERATION"
echo "=============================================================="
echo
{
echo "operation|weighted_samples|pct_of_dml_ash"
sort -t'|' -k2,2nr "${OP_ASH_FILE}"
} | column -t -s '|'
fi
# ------------------------------------------------------------------------------
# Hot tablets per node
# ------------------------------------------------------------------------------
awk -F'|' '
{
node = $1
tablet = $3
value = $6
key = node FS tablet
tablet_samples[key] += value
node_total[node] += value
}
END {
for (key in tablet_samples) {
split(key, a, FS)
node = a[1]
tablet = a[2]
pct = tablet_samples[key] * 100.0 / node_total[node]
printf "%s|%s|%.2f|%.2f\n", node, tablet, tablet_samples[key], pct
}
}
' "${RAW_FILE}" > "${TABLET_NODE_FILE}"
echo
echo "=============================================================="
echo " HOT TABLETS PER NODE - ${TABLE_LABEL} ${OPERATION^^}"
echo "=============================================================="
for i in "${!NODE_HOSTS[@]}"
do
HOST="${NODE_HOSTS[$i]}"
PORT_VALUE="${NODE_PORTS[$i]}"
NODE_LABEL="${HOST}:${PORT_VALUE}"
echo
echo "NODE: ${NODE_LABEL}"
echo "--------------------------------------------------------------"
{
echo "tablet_id_prefix|weighted_samples|pct_of_node"
awk -F'|' -v node="${NODE_LABEL}" '$1 == node {print $2 "|" $3 "|" $4}' "${TABLET_NODE_FILE}" |
sort -t'|' -k2,2nr |
awk -v n="${TOP_N}" 'NR <= n'
} | column -t -s '|'
done
# ------------------------------------------------------------------------------
# Cluster-wide hot tablets
# ------------------------------------------------------------------------------
awk -F'|' '
{
tablet = $3
value = $6
tablet_samples[tablet] += value
total += value
}
END {
if (total == 0)
exit
for (tablet in tablet_samples) {
pct = tablet_samples[tablet] * 100.0 / total
printf "%s|%.2f|%.2f\n", tablet, tablet_samples[tablet], pct
}
}
' "${RAW_FILE}" > "${TABLET_CLUSTER_FILE}"
echo
echo "=============================================================="
echo " CLUSTER-WIDE HOT TABLETS - ${TABLE_LABEL} ${OPERATION^^}"
echo "=============================================================="
echo
{
echo "tablet_id_prefix|weighted_samples|pct_of_cluster"
sort -t'|' -k2,2nr "${TABLET_CLUSTER_FILE}" |
awk -v n="${TOP_N}" 'NR <= n'
} | column -t -s '|'
# ------------------------------------------------------------------------------
# Cluster-wide wait breakdown
# ------------------------------------------------------------------------------
awk -F'|' '
{
tablet = $3
wait_class = $4
wait_event = $5
value = $6
key = tablet FS wait_class FS wait_event
samples[key] += value
total += value
}
END {
if (total == 0)
exit
for (key in samples) {
split(key, a, FS)
tablet = a[1]
wait_class = a[2]
wait_event = a[3]
pct = samples[key] * 100.0 / total
printf "%s|%s|%s|%.2f|%.2f\n", tablet, wait_class, wait_event, samples[key], pct
}
}
' "${RAW_FILE}" > "${WAIT_CLUSTER_FILE}"
echo
echo "=============================================================="
echo " CLUSTER-WIDE WAIT BREAKDOWN - ${TABLE_LABEL} ${OPERATION^^}"
echo "=============================================================="
echo
{
echo "tablet_id_prefix|wait_class|wait_event|weighted_samples|pct_cluster"
sort -t'|' -k4,4nr "${WAIT_CLUSTER_FILE}" |
awk -v n="${TOP_N}" 'NR <= n'
} | column -t -s '|'
# ------------------------------------------------------------------------------
# Per-node wait detail
#
# --ash-top limits this intentionally verbose section independently of --top.
# A value of 0 shows every ASH wait-detail row.
# ------------------------------------------------------------------------------
echo
echo "=============================================================="
echo " PER-NODE WAIT BREAKDOWN - ${TABLE_LABEL} ${OPERATION^^}"
echo "=============================================================="
for i in "${!NODE_HOSTS[@]}"
do
HOST="${NODE_HOSTS[$i]}"
PORT_VALUE="${NODE_PORTS[$i]}"
NODE_LABEL="${HOST}:${PORT_VALUE}"
echo
echo "NODE: ${NODE_LABEL}"
echo "--------------------------------------------------------------"
if (( ASH_TOP_N == 0 ))
then
{
echo "node|query_id|tablet_id_prefix|wait_class|wait_event|weighted_samples"
awk -F'|' -v node="${NODE_LABEL}" '$1 == node' "${RAW_FILE}" |
sort -t'|' -k6,6nr |
awk -F'|' '{
printf "%s|%s|%s|%s|%s|%.2f\n", $1, $2, $3, $4, $5, $6
}'
} | column -t -s '|'
else
{
echo "node|query_id|tablet_id_prefix|wait_class|wait_event|weighted_samples"
awk -F'|' -v node="${NODE_LABEL}" '$1 == node' "${RAW_FILE}" |
sort -t'|' -k6,6nr |
awk -v n="${ASH_TOP_N}" 'NR <= n' |
awk -F'|' '{
printf "%s|%s|%s|%s|%s|%.2f\n", $1, $2, $3, $4, $5, $6
}'
} | column -t -s '|'
fi
done
# ------------------------------------------------------------------------------
# Tablet -> relation mapping
# ------------------------------------------------------------------------------
TABLET_PREFIXES=$(
awk -F'|' '{print $3}' "${RAW_FILE}" |
sort -u |
sed "s/^/'/;s/$/'/" |
paste -sd,
)
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' <<SQL > "${TABLET_META_FILE}"
SELECT
substring(m.tablet_id, 1, 15) AS tablet_prefix,
m.oid,
m.relname AS relation,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'p' THEN 'partitioned_table'
WHEN 'I' THEN 'partitioned_index'
ELSE COALESCE(c.relkind::text, '?')
END AS relation_type,
COALESCE(parent.relname, '') AS parent_table,
m.leader,
COALESCE(m.start_hash_code::text, ''),
COALESCE(m.end_hash_code::text, ''),
COALESCE(m.tablet_state, ''),
m.tablet_id
FROM yb_tablet_metadata m
LEFT JOIN pg_class c
ON c.oid = m.oid
LEFT JOIN pg_index i
ON i.indexrelid = c.oid
LEFT JOIN pg_class parent
ON parent.oid = i.indrelid
WHERE m.db_name = current_database()
AND substring(m.tablet_id, 1, 15) IN (${TABLET_PREFIXES})
ORDER BY
m.relname,
m.start_hash_code,
m.tablet_id;
SQL
echo
echo "=============================================================="
echo " TABLET -> TABLE / INDEX MAPPING"
echo "=============================================================="
echo
{
echo "tablet_prefix|oid|relation|type|parent_table|leader|start_hash|end_hash|state|tablet_id"
cat "${TABLET_META_FILE}"
} | column -t -s '|'
# ------------------------------------------------------------------------------
# ASH activity by physical relation (base table / secondary indexes / other)
# ------------------------------------------------------------------------------
awk -F'|' '
FILENAME == ARGV[1] {
relation[$1] = $3
type[$1] = $4
parent[$1] = $5
next
}
FILENAME == ARGV[2] {
tablet = $3
value = $6
rel = relation[tablet]
typ = type[tablet]
par = parent[tablet]
if (rel == "") {
rel = "<unmapped>"
typ = "?"
par = ""
}
key = rel FS typ FS par
samples[key] += value
total += value
}
END {
if (total == 0)
exit
for (key in samples) {
split(key, a, FS)
pct = samples[key] * 100.0 / total
printf "%s|%s|%s|%.2f|%.2f\n", a[1], a[2], a[3], samples[key], pct
}
}
' "${TABLET_META_FILE}" "${RAW_FILE}" > "${RELATION_ASH_FILE}"
echo
echo "=============================================================="
echo " ASH ACTIVITY BY PHYSICAL RELATION"
echo "=============================================================="
echo
{
echo "relation|type|parent_table|weighted_samples|pct_of_dml_ash"
sort -t'|' -k4,4nr "${RELATION_ASH_FILE}"
} | column -t -s '|'
# ------------------------------------------------------------------------------
# Current base-table tablet list, keyed by target table OID.
# ------------------------------------------------------------------------------
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' <<SQL > "${BASE_META_FILE}"
SELECT
substring(tablet_id, 1, 15),
leader,
COALESCE(start_hash_code::text, ''),
COALESCE(end_hash_code::text, ''),
tablet_id
FROM yb_tablet_metadata
WHERE db_name = current_database()
AND oid = ${TABLE_OID}
AND tablet_state = 'RUNNING'
ORDER BY
start_hash_code,
start_range,
tablet_id;
SQL
if [[ ! -s "${BASE_META_FILE}" ]]
then
echo
echo "WARNING: No RUNNING base-table tablets were found for OID ${TABLE_OID}."
echo " Partitioned parent tables may require analyzing a child partition"
echo " directly for base-table tablet summaries."
echo
else
# --------------------------------------------------------------------------
# Base-table ASH distribution
# --------------------------------------------------------------------------
> "${BASE_TABLET_FILE}"
while IFS='|' read -r TABLET LEADER START_HASH END_HASH FULL_TABLET_ID
do
SAMPLES=$(
awk -F'|' -v tablet="${TABLET}" '
$3 == tablet {
total += $6
}
END {
printf "%.2f", total + 0
}
' "${RAW_FILE}"
)
echo "${TABLET}|${LEADER}|${SAMPLES}|${START_HASH}|${END_HASH}|${FULL_TABLET_ID}" >> "${BASE_TABLET_FILE}"
done < "${BASE_META_FILE}"
BASE_TOTAL=$(
awk -F'|' '{total += $3} END {printf "%.2f", total + 0}' "${BASE_TABLET_FILE}"
)
awk -F'|' -v total="${BASE_TOTAL}" '
{
pct = 0
if (total > 0)
pct = $3 * 100.0 / total
printf "%s|%s|%.2f|%.2f|%s|%s|%s\n", $1, $2, $3, pct, $4, $5, $6
}
' "${BASE_TABLET_FILE}" > "${BASE_TABLET_FILE}.new"
mv "${BASE_TABLET_FILE}.new" "${BASE_TABLET_FILE}"
echo
echo "=============================================================="
echo " ${TABLE_LABEL} BASE-TABLE ASH DISTRIBUTION"
echo "=============================================================="
echo
{
echo "tablet_id_prefix|leader|weighted_samples|pct_of_base_table_ash|start_hash|end_hash|tablet_id"
sort -t'|' -k3,3nr "${BASE_TABLET_FILE}"
} | column -t -s '|'
# --------------------------------------------------------------------------
# Base-table ASH by tablet leader
# --------------------------------------------------------------------------
awk -F'|' '
{
samples[$2] += $3
total += $3
}
END {
if (total == 0)
exit
for (leader in samples) {
pct = samples[leader] * 100.0 / total
printf "%s|%.2f|%.2f\n", leader, samples[leader], pct
}
}
' "${BASE_TABLET_FILE}" > "${BASE_LEADER_FILE}"
echo
echo "=============================================================="
echo " ${TABLE_LABEL} BASE-TABLE ASH BY TABLET LEADER"
echo "=============================================================="
echo
{
echo "leader|weighted_samples|pct_of_base_table_ash"
sort -t'|' -k2,2nr "${BASE_LEADER_FILE}"
} | column -t -s '|'
fi
# ------------------------------------------------------------------------------
# Optional row-distribution analysis. ONE table scan only.
# ------------------------------------------------------------------------------
if (( ROW_COUNTS == 1 ))
then
echo
echo "=============================================================="
echo " CURRENT STORED ROW DISTRIBUTION"
echo "=============================================================="
echo
if [[ "${TABLE_RELKIND}" == "p" ]]
then
echo "Skipped: ${QUALIFIED_TABLE} is a partitioned parent table."
echo "Analyze an individual child table/partition for physical tablet routing."
elif (( HASH_KEY_USABLE == 0 ))
then
echo "Skipped: a usable simple-column HASH key could not be determined."
if [[ -n "${HASH_KEY}" ]]
then
echo "Detected/supplied HASH key text: ${HASH_KEY}"
fi
echo "Use --hash-key col1[,col2...] to override when appropriate."
elif grep -qiE '(^|,[[:space:]]*)ybrowid([[:space:]]*,|$)' <<< "${HASH_KEY}"
then
echo "Skipped: the table appears to use internal ybrowid HASH sharding."
echo "ybrowid is not available as a normal user column for yb_hash_code()."
else
HASH_BOUNDARY_COUNT=$(
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-At \
-c "
SELECT count(*)
FROM yb_tablet_metadata
WHERE db_name = current_database()
AND oid = ${TABLE_OID}
AND tablet_state = 'RUNNING'
AND start_hash_code IS NOT NULL
AND end_hash_code IS NOT NULL;
"
)
if [[ "${HASH_BOUNDARY_COUNT}" == "0" ]]
then
echo "Skipped: current base-table tablets do not expose hash boundaries."
echo "The table may be range-sharded."
else
echo "WARNING: --row-counts is enabled. The next query can scan the entire"
echo " ${QUALIFIED_TABLE} table."
echo
echo "HASH key : ${HASH_KEY} (${HASH_KEY_SOURCE})"
echo
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-AtF '|' <<SQL > "${ROW_DIST_FILE}"
SELECT
substring(t.tablet_id, 1, 15) AS tablet_id_prefix,
t.leader,
t.start_hash_code,
t.end_hash_code,
COUNT(*) AS row_count
FROM ${QUALIFIED_TABLE} d
JOIN yb_tablet_metadata t
ON yb_hash_code(${HASH_ARGS}) >= t.start_hash_code
AND yb_hash_code(${HASH_ARGS}) < t.end_hash_code
WHERE t.db_name = current_database()
AND t.oid = ${TABLE_OID}
AND t.tablet_state = 'RUNNING'
GROUP BY
t.tablet_id,
t.leader,
t.start_hash_code,
t.end_hash_code
ORDER BY t.start_hash_code;
SQL
ROW_TOTAL=$(
awk -F'|' '{total += $5} END {printf "%.0f", total + 0}' "${ROW_DIST_FILE}"
)
echo "Current stored rows by tablet:"
echo
{
echo "tablet_id_prefix|leader|start_hash|end_hash|row_count|pct_of_rows"
awk -F'|' -v total="${ROW_TOTAL}" '
{
pct = 0
if (total > 0)
pct = $5 * 100.0 / total
printf "%s|%s|%s|%s|%s|%.2f\n", $1, $2, $3, $4, $5, pct
}
' "${ROW_DIST_FILE}"
} | column -t -s '|'
awk -F'|' '
{
rows[$2] += $5
tablets[$2]++
total += $5
}
END {
if (total == 0)
exit
for (leader in rows) {
pct = rows[leader] * 100.0 / total
printf "%s|%d|%.0f|%.2f\n", leader, tablets[leader], rows[leader], pct
}
}
' "${ROW_DIST_FILE}" > "${ROW_LEADER_FILE}"
echo
echo "Current stored rows by tablet leader:"
echo
{
echo "leader|tablet_count|row_count|pct_of_rows"
sort -t'|' -k3,3nr "${ROW_LEADER_FILE}"
} | column -t -s '|'
# ------------------------------------------------------------------
# Compare base-table ASH share with current stored-row share.
#
# Ratio interpretation:
# 1.00 = ASH share matches row share
# >1.00 = more ASH than expected from row share
# <1.00 = less ASH than expected from row share
# ------------------------------------------------------------------
if [[ -s "${BASE_TABLET_FILE}" ]]
then
> "${ASH_ROW_TABLET_FILE}"
while IFS='|' read -r TABLET LEADER START_HASH END_HASH ROW_COUNT
do
ROW_PCT=$(
awk -v rows="${ROW_COUNT}" -v total="${ROW_TOTAL}" 'BEGIN {
if (total > 0)
printf "%.2f", rows * 100.0 / total
else
printf "0.00"
}'
)
ASH_PCT=$(
awk -F'|' -v tablet="${TABLET}" '
$1 == tablet {
printf "%.2f", $4
found = 1
exit
}
END {
if (!found)
printf "0.00"
}
' "${BASE_TABLET_FILE}"
)
RATIO=$(
awk -v ash="${ASH_PCT}" -v rows="${ROW_PCT}" 'BEGIN {
if (rows > 0)
printf "%.2f", ash / rows
else
printf "0.00"
}'
)
echo "${TABLET}|${LEADER}|${ROW_PCT}|${ASH_PCT}|${RATIO}" \
>> "${ASH_ROW_TABLET_FILE}"
done < "${ROW_DIST_FILE}"
echo
echo "=============================================================="
echo " ASH VS CURRENT ROW DISTRIBUTION BY TABLET"
echo "=============================================================="
echo
{
echo "tablet_id_prefix|leader|row_pct|ash_pct|ash_row_ratio"
sort -t'|' -k5,5nr "${ASH_ROW_TABLET_FILE}"
} | column -t -s '|'
fi
if [[ -s "${BASE_LEADER_FILE}" ]]
then
> "${ASH_ROW_LEADER_FILE}"
while IFS='|' read -r LEADER TABLET_COUNT ROW_COUNT ROW_PCT
do
ASH_PCT=$(
awk -F'|' -v leader="${LEADER}" '
$1 == leader {
printf "%.2f", $3
found = 1
exit
}
END {
if (!found)
printf "0.00"
}
' "${BASE_LEADER_FILE}"
)
RATIO=$(
awk -v ash="${ASH_PCT}" -v rows="${ROW_PCT}" 'BEGIN {
if (rows > 0)
printf "%.2f", ash / rows
else
printf "0.00"
}'
)
echo "${LEADER}|${ROW_PCT}|${ASH_PCT}|${RATIO}" \
>> "${ASH_ROW_LEADER_FILE}"
done < "${ROW_LEADER_FILE}"
echo
echo "=============================================================="
echo " ASH VS CURRENT ROW DISTRIBUTION BY TABLET LEADER"
echo "=============================================================="
echo
{
echo "leader|row_pct|ash_pct|ash_row_ratio"
sort -t'|' -k4,4nr "${ASH_ROW_LEADER_FILE}"
} | column -t -s '|'
echo
echo "Ratio: 1.00 = ASH share matches row share; >1.00 = more ASH per row share."
fi
if [[ "${OPERATION}" == "update" || "${OPERATION}" == "delete" || "${OPERATION}" == "all" ]]
then
echo
echo "NOTE: Current stored-row distribution is context only for UPDATE/DELETE."
echo " It does not measure which rows those statements targeted."
fi
fi
fi
else
echo
echo "=============================================================="
echo " CURRENT STORED ROW DISTRIBUTION"
echo "=============================================================="
echo
echo "Skipped (safe default). Use --row-counts to enable the potentially"
echo "expensive full-table row-distribution scan."
fi
# ------------------------------------------------------------------------------
# Index definitions
# ------------------------------------------------------------------------------
echo
echo "=============================================================="
echo " ${TABLE_LABEL} INDEX DEFINITIONS"
echo "=============================================================="
echo
psql \
-X \
-q \
-w \
-h "${CONTROL_HOST}" \
-p "${CONTROL_PORT}" \
-U "${USER_NAME}" \
-d "${DB}" \
-v ON_ERROR_STOP=1 \
-P pager=off <<SQL
SELECT
CASE WHEN i.indisprimary THEN 'PRIMARY KEY' ELSE 'INDEX' END AS kind,
idx.relname AS index_name,
pg_get_indexdef(i.indexrelid, 0, true) AS index_definition
FROM pg_index i
JOIN pg_class idx
ON idx.oid = i.indexrelid
WHERE i.indrelid = ${TABLE_OID}
ORDER BY
i.indisprimary DESC,
idx.relname;
SQL
# ------------------------------------------------------------------------------
# Hottest current base-table tablet summary
# ------------------------------------------------------------------------------
if [[ -s "${BASE_TABLET_FILE}" ]]
then
HOT_LINE=$(
sort -t'|' -k3,3nr "${BASE_TABLET_FILE}" |
awk '$3 + 0 > 0 {print; exit}'
)
if [[ -n "${HOT_LINE}" ]]
then
IFS='|' read -r HOT_TABLET HOT_LEADER HOT_SAMPLES HOT_PCT HOT_START HOT_END HOT_FULL_ID <<< "${HOT_LINE}"
echo
echo "=============================================================="
echo " HOTTEST CURRENT ${TABLE_LABEL} BASE-TABLE TABLET"
echo "=============================================================="
echo
echo "Tablet prefix : ${HOT_TABLET}"
echo "Tablet ID : ${HOT_FULL_ID}"
echo "Leader : ${HOT_LEADER}"
echo "Hash range : ${HOT_START}-${HOT_END}"
echo "Weighted samples : ${HOT_SAMPLES}"
echo "Base-table ASH % : ${HOT_PCT}%"
fi
fi
echo
echo "=============================================================="
echo " ANALYSIS COMPLETE - ${TABLE_LABEL} ${OPERATION^^}"
echo "=============================================================="
echo
Step 4: Look at the Configuration
One of the first sections confirms what the script detected:
Target table : public.event_feature
Operation : insert
Connection mode : seed discovery
Seed : 10.0.0.11:5433
ASH window : 20 minutes
Summary top N : 10
Query rows : top 10 per node
Per-node ASH rows : top 10 per node
ASH workload start : 2026-09-20 23:53:29.490512+00
HASH key : feature_hash (automatic)
Row counts : ENABLED - may scan the entire table
Notice that the script automatically found:
HASH key : feature_hash
No hash column had to be provided on the command line.
Step 5: Find the Matching INSERTs
The script searches pg_stat_statements on all three nodes for matching INSERTs against the target table.
For a batched workload, there may be several normalized forms:
node operation calls rows_per_call mean_exec_ms
10.0.0.11 insert 345543 14.00 89.10
10.0.0.11 insert 39677 7.00 69.60
10.0.0.11 insert 39122 5.00 57.32
...
--query-top keeps this section manageable.
The important part is that limiting the displayed rows does not limit the analysis.
For example:
Matching query IDs discovered: 14
All 14 query IDs will be included in the ASH analysis.
Step 6: Find the Hot Tablets
Next, the script aggregates the ASH activity for those query IDs.
An anonymized example might look like:
tablet_id_prefix weighted_samples pct_of_cluster
aaaaaaaaaaaaaaa 18477.00 24.88
bbbbbbbbbbbbbbb 12897.75 17.37
ccccccccccccccc 10547.00 14.20
ddddddddddddddd 7625.00 10.27
eeeeeeeeeeeeeee 7292.00 9.82
fffffffffffffff 6095.00 8.21
Tablet aaaaaaaaaaaaaaa clearly has the most ASH activity.
But is it simply receiving more rows?
We don’t know yet.
Step 7: Look at the Wait Events
The wait breakdown helps show what those hot tablets are doing:
tablet_id_prefix wait_class wait_event
aaaaaaaaaaaaaaa Consensus Raft_WaitingForReplication
bbbbbbbbbbbbbbb Consensus Raft_WaitingForReplication
aaaaaaaaaaaaaaa TabletWait MVCC_WaitForSafeTime
aaaaaaaaaaaaaaa Common OnCpu_Passive
This is one of the advantages of using ASH rather than looking only at storage distribution.
We are not just finding that a tablet is busy.
We can see what the activity is associated with.
Step 8: Separate Base-Table Activity from Index Activity
The script maps tablet IDs back to their physical relation.
For example:
That distinction can be important.
A workload may have a beautifully distributed base table but a problematic secondary index—or vice versa.
Step 9: Compare ASH with the Actual Row Distribution
Because we supplied --row-counts, the script scans the table and routes the HASH key through yb_hash_code() to determine how many rows belong to each current tablet.
In this demo, the result looks almost perfect:
There is effectively no row skew.
Now compare that with ASH:
Now the hotspot jumps off the page.
Tablet aaaaaaaaaaaaaaa owns only 16.66% of the rows but accounts for 29.36% of the base-table ASH.
Its ASH-to-row ratio is 1.76.
The data is balanced.
The activity is not.
Step 10: Look at the Tablet Leaders
The leader-level comparison makes another pattern visible:
Each node leads two tablets and owns approximately one-third of the rows.
Yet 10.0.0.13 is responsible for 46.12% of the base-table ASH.
That is another clue that this is not simply a HASH-key distribution problem.
💡 This is where the script earns its keep.
Without the ASH comparison, we might look at the nearly perfect 33/33/33 row distribution and conclude that everything is balanced.
The workload tells a different story.
Complete Demo Output
# Anonymized example output for publication.
# Object names, hosts, OIDs, query IDs, tablet IDs, and timestamp are fictitious.
[ec2-user@ip-10-0-0-50 demo]$ ./yb_dml_hotspots.sh event_feature insert --seed 10.0.0.11 --row-counts --timestamp inserted_at
==============================================================
DISCOVERING PRIMARY YSQL NODES
==============================================================
Seed: 10.0.0.11:5433
NOTE: --timestamp derives the load start with MIN(inserted_at).
On a very large table this lookup itself may be expensive unless
an appropriate index can satisfy it. Use --since to avoid that scan.
==============================================================
YB DML HOTSPOT ANALYSIS - CONFIGURATION
==============================================================
Target table : public.event_feature
Table OID : 18000
Operation : insert
Database : yugabyte
User : yugabyte
Connection mode : seed discovery
Seed : 10.0.0.11:5433
ASH window : 20 minutes
Summary top N : 10
Query rows : top 10 per node
Per-node ASH rows : top 10 per node
ASH workload start : 2026-09-20 20:00:00+00 (MIN(inserted_at))
Primary key : CREATE UNIQUE INDEX event_feature_pkey ON event_feature USING lsm (feature_hash HASH, feature_type ASC, is_anonymous ASC)
HASH key : feature_hash (automatic)
Row counts : ENABLED - may scan the entire table
YSQL nodes:
10.0.0.11:5433
10.0.0.12:5433
10.0.0.13:5433
==============================================================
DISCOVERING PUBLIC.EVENT_FEATURE DML QUERY IDs
==============================================================
Checking pg_stat_statements on 10.0.0.11:5433...
Checking pg_stat_statements on 10.0.0.12:5433...
Checking pg_stat_statements on 10.0.0.13:5433...
Matching PUBLIC.EVENT_FEATURE DML statements:
Displaying top 10 matching statement(s) per node by calls.
All matching query IDs are still included in the ASH analysis.
node operation query_id calls rows rows_per_call total_exec_ms mean_exec_ms
10.0.0.11:5433 insert 7123456789012345001 345543 4837602 14.00 30787691.39 89.10
10.0.0.11:5433 insert -7123456789012345002 39677 277739 7.00 2761594.79 69.60
10.0.0.11:5433 insert -7123456789012345003 39122 195610 5.00 2242286.94 57.32
10.0.0.11:5433 insert -7123456789012345004 33466 200796 6.00 2091232.27 62.49
10.0.0.11:5433 insert 7123456789012345005 32306 129224 4.00 1646031.03 50.95
10.0.0.11:5433 insert 7123456789012345006 28389 227112 8.00 2090622.41 73.64
10.0.0.11:5433 insert 7123456789012345007 21448 193032 9.00 1671392.61 77.93
10.0.0.11:5433 insert 7123456789012345008 21207 63621 3.00 884523.66 41.71
10.0.0.11:5433 insert 7123456789012345009 20013 260169 13.00 1756327.81 87.76
10.0.0.11:5433 insert 7123456789012345010 17363 17363 1.00 231702.18 13.34
10.0.0.12:5433 insert 7123456789012345001 355602 4978428 14.00 30773029.29 86.54
10.0.0.12:5433 insert -7123456789012345002 40931 286517 7.00 2762664.08 67.50
10.0.0.12:5433 insert -7123456789012345003 40120 200600 5.00 2257568.05 56.27
10.0.0.12:5433 insert -7123456789012345004 34473 206838 6.00 2079993.62 60.34
10.0.0.12:5433 insert 7123456789012345005 33544 134176 4.00 1600716.05 47.72
10.0.0.12:5433 insert 7123456789012345006 28893 231144 8.00 2030469.74 70.28
10.0.0.12:5433 insert 7123456789012345007 21938 197442 9.00 1635150.99 74.54
10.0.0.12:5433 insert 7123456789012345008 21783 65349 3.00 906554.78 41.62
10.0.0.12:5433 insert 7123456789012345009 20622 268086 13.00 1758375.96 85.27
10.0.0.12:5433 insert 7123456789012345010 17660 17660 1.00 223423.02 12.65
10.0.0.13:5433 insert 7123456789012345001 368519 5159266 14.00 31570392.66 85.67
10.0.0.13:5433 insert -7123456789012345002 42128 294896 7.00 2812057.59 66.75
10.0.0.13:5433 insert -7123456789012345003 42086 210430 5.00 2330965.57 55.39
10.0.0.13:5433 insert -7123456789012345004 35777 214662 6.00 2136990.72 59.73
10.0.0.13:5433 insert 7123456789012345005 34467 137868 4.00 1663613.70 48.27
10.0.0.13:5433 insert 7123456789012345006 30084 240672 8.00 2152452.39 71.55
10.0.0.13:5433 insert 7123456789012345007 23014 207126 9.00 1724424.48 74.93
10.0.0.13:5433 insert 7123456789012345008 22592 67776 3.00 901250.41 39.89
10.0.0.13:5433 insert 7123456789012345009 21484 279292 13.00 1811444.61 84.32
10.0.0.13:5433 insert 7123456789012345010 18489 18489 1.00 236642.49 12.80
Matching query IDs discovered: 14
All 14 query IDs will be included in the ASH analysis.
==============================================================
COLLECTING PUBLIC.EVENT_FEATURE DML ASH
==============================================================
Nodes : 3
Max window : 20 minutes
Load start : 2026-09-20 20:00:00+00
Querying ASH on 10.0.0.11:5433...
Querying ASH on 10.0.0.12:5433...
Querying ASH on 10.0.0.13:5433...
==============================================================
HOT TABLETS PER NODE - PUBLIC.EVENT_FEATURE INSERT
==============================================================
NODE: 10.0.0.11:5433
--------------------------------------------------------------
tablet_id_prefix weighted_samples pct_of_node
c142671eda00e15 7292.00 40.65
ec7b4849fe015df 6095.00 33.98
fb1d06c1abe8fcc 2088.00 11.64
ac0bdd0e971b0f5 1950.00 10.87
81fbedee7e45e67 76.00 0.42
ff2e3c9aeebc975 75.00 0.42
6544c1df6f7cbc9 65.00 0.36
d105d1361b66098 64.00 0.36
5936e8d8b7a32a3 62.00 0.35
06c49c8330b7550 61.00 0.34
NODE: 10.0.0.12:5433
--------------------------------------------------------------
tablet_id_prefix weighted_samples pct_of_node
d54d4d652bcc5c4 12897.75 54.38
85c21ad28c5bb98 7625.00 32.15
f71cea2d79e7557 1496.25 6.31
6ed5af42351239a 1383.50 5.83
1791f8279e52bc3 46.00 0.19
2930916feec932a 45.00 0.19
3492420717b8e0d 45.00 0.19
f243de84caee50b 44.00 0.19
6459bda9ce3a004 40.00 0.17
1cc3453762b3b91 37.00 0.16
NODE: 10.0.0.13:5433
--------------------------------------------------------------
tablet_id_prefix weighted_samples pct_of_node
8dd35bd6ace0fc8 18477.00 56.68
55c30e42631493d 10547.00 32.36
b27792460bd1d29 1635.00 5.02
6567b5d20841e83 1579.00 4.84
f360699c86dca4f 49.00 0.15
85f73cbd2e8d5b1 49.00 0.15
eeb918d62543641 47.00 0.14
a1497109871adcc 45.00 0.14
86f375f132a05ad 45.00 0.14
caa2af2ab7b054b 43.00 0.13
==============================================================
CLUSTER-WIDE HOT TABLETS - PUBLIC.EVENT_FEATURE INSERT
==============================================================
tablet_id_prefix weighted_samples pct_of_cluster
8dd35bd6ace0fc8 18477.00 24.88
d54d4d652bcc5c4 12897.75 17.37
55c30e42631493d 10547.00 14.20
85c21ad28c5bb98 7625.00 10.27
c142671eda00e15 7292.00 9.82
ec7b4849fe015df 6095.00 8.21
fb1d06c1abe8fcc 2088.00 2.81
ac0bdd0e971b0f5 1950.00 2.63
b27792460bd1d29 1635.00 2.20
6567b5d20841e83 1579.00 2.13
==============================================================
CLUSTER-WIDE WAIT BREAKDOWN - PUBLIC.EVENT_FEATURE INSERT
==============================================================
tablet_id_prefix wait_class wait_event weighted_samples pct_cluster
8dd35bd6ace0fc8 Consensus Raft_WaitingForReplication 9262.00 12.47
d54d4d652bcc5c4 Consensus Raft_WaitingForReplication 6882.25 9.27
55c30e42631493d Consensus Raft_WaitingForReplication 5578.00 7.51
8dd35bd6ace0fc8 TabletWait MVCC_WaitForSafeTime 4983.00 6.71
c142671eda00e15 Consensus Raft_WaitingForReplication 4430.00 5.97
8dd35bd6ace0fc8 Common OnCpu_Passive 4087.00 5.50
85c21ad28c5bb98 Consensus Raft_WaitingForReplication 3996.25 5.38
ec7b4849fe015df Consensus Raft_WaitingForReplication 3798.00 5.11
d54d4d652bcc5c4 TabletWait MVCC_WaitForSafeTime 3265.75 4.40
d54d4d652bcc5c4 Common OnCpu_Passive 2565.00 3.45
==============================================================
PER-NODE WAIT BREAKDOWN - PUBLIC.EVENT_FEATURE INSERT
==============================================================
NODE: 10.0.0.11:5433
--------------------------------------------------------------
node query_id tablet_id_prefix wait_class wait_event weighted_samples
10.0.0.11:5433 7123456789012345001 c142671eda00e15 Consensus Raft_WaitingForReplication 2839.00
10.0.0.11:5433 7123456789012345001 ec7b4849fe015df Consensus Raft_WaitingForReplication 2477.00
10.0.0.11:5433 7123456789012345001 fb1d06c1abe8fcc Consensus Raft_WaitingForReplication 1190.00
10.0.0.11:5433 7123456789012345001 ac0bdd0e971b0f5 Consensus Raft_WaitingForReplication 1179.00
10.0.0.11:5433 7123456789012345001 c142671eda00e15 TabletWait MVCC_WaitForSafeTime 969.00
10.0.0.11:5433 7123456789012345001 ec7b4849fe015df TabletWait MVCC_WaitForSafeTime 752.00
10.0.0.11:5433 7123456789012345001 c142671eda00e15 Common OnCpu_Passive 686.00
10.0.0.11:5433 7123456789012345001 ec7b4849fe015df Common OnCpu_Passive 589.00
10.0.0.11:5433 -7123456789012345002 c142671eda00e15 Consensus Raft_WaitingForReplication 203.00
10.0.0.11:5433 -7123456789012345003 c142671eda00e15 Consensus Raft_WaitingForReplication 197.00
NODE: 10.0.0.12:5433
--------------------------------------------------------------
node query_id tablet_id_prefix wait_class wait_event weighted_samples
10.0.0.12:5433 7123456789012345001 d54d4d652bcc5c4 Consensus Raft_WaitingForReplication 4311.50
10.0.0.12:5433 7123456789012345001 85c21ad28c5bb98 Consensus Raft_WaitingForReplication 2498.50
10.0.0.12:5433 7123456789012345001 d54d4d652bcc5c4 TabletWait MVCC_WaitForSafeTime 1987.50
10.0.0.12:5433 7123456789012345001 d54d4d652bcc5c4 Common OnCpu_Passive 1563.75
10.0.0.12:5433 7123456789012345001 85c21ad28c5bb98 TabletWait MVCC_WaitForSafeTime 1078.00
10.0.0.12:5433 7123456789012345001 85c21ad28c5bb98 Common OnCpu_Passive 1001.75
10.0.0.12:5433 7123456789012345001 f71cea2d79e7557 Consensus Raft_WaitingForReplication 805.00
10.0.0.12:5433 7123456789012345001 6ed5af42351239a Consensus Raft_WaitingForReplication 760.00
10.0.0.12:5433 -7123456789012345002 d54d4d652bcc5c4 Consensus Raft_WaitingForReplication 341.50
10.0.0.12:5433 -7123456789012345003 d54d4d652bcc5c4 Consensus Raft_WaitingForReplication 315.75
NODE: 10.0.0.13:5433
--------------------------------------------------------------
node query_id tablet_id_prefix wait_class wait_event weighted_samples
10.0.0.13:5433 7123456789012345001 8dd35bd6ace0fc8 Consensus Raft_WaitingForReplication 5827.00
10.0.0.13:5433 7123456789012345001 55c30e42631493d Consensus Raft_WaitingForReplication 3566.00
10.0.0.13:5433 7123456789012345001 8dd35bd6ace0fc8 TabletWait MVCC_WaitForSafeTime 3108.00
10.0.0.13:5433 7123456789012345001 8dd35bd6ace0fc8 Common OnCpu_Passive 2478.00
10.0.0.13:5433 7123456789012345001 55c30e42631493d TabletWait MVCC_WaitForSafeTime 1543.00
10.0.0.13:5433 7123456789012345001 55c30e42631493d Common OnCpu_Passive 1419.00
10.0.0.13:5433 7123456789012345001 6567b5d20841e83 Consensus Raft_WaitingForReplication 813.00
10.0.0.13:5433 7123456789012345001 b27792460bd1d29 Consensus Raft_WaitingForReplication 804.00
10.0.0.13:5433 -7123456789012345002 8dd35bd6ace0fc8 Consensus Raft_WaitingForReplication 480.00
10.0.0.13:5433 -7123456789012345003 8dd35bd6ace0fc8 Consensus Raft_WaitingForReplication 457.00
==============================================================
TABLET -> TABLE / INDEX MAPPING
==============================================================
tablet_prefix oid relation type parent_table leader start_hash end_hash state tablet_id
d54d4d652bcc5c4 18000 event_feature table 10.0.0.12:5433 0 10922 RUNNING d54d4d652bcc5c47a1b358b5fedbbf4e
8dd35bd6ace0fc8 18000 event_feature table 10.0.0.13:5433 10922 21845 RUNNING 8dd35bd6ace0fc82ed6b382dc8dc4cb2
ec7b4849fe015df 18000 event_feature table 10.0.0.11:5433 21845 32768 RUNNING ec7b4849fe015df5c67dd3070868b34d
85c21ad28c5bb98 18000 event_feature table 10.0.0.12:5433 32768 43690 RUNNING 85c21ad28c5bb98ad1f9fd374128557b
55c30e42631493d 18000 event_feature table 10.0.0.13:5433 43690 54613 RUNNING 55c30e42631493dd661d6889bc9a44b5
c142671eda00e15 18000 event_feature table 10.0.0.11:5433 54613 65536 RUNNING c142671eda00e15a6845f98d2ccb8e5c
6567b5d20841e83 18001 event_feature_uk index event_feature 10.0.0.13:5433 0 10922 RUNNING 6567b5d20841e838a13fb714ce2d64cc
fb1d06c1abe8fcc 18001 event_feature_uk index event_feature 10.0.0.11:5433 10922 21845 RUNNING fb1d06c1abe8fccf452e417e1eb91088
b27792460bd1d29 18001 event_feature_uk index event_feature 10.0.0.13:5433 21845 32768 RUNNING b27792460bd1d294102ca6b8c667b179
f71cea2d79e7557 18001 event_feature_uk index event_feature 10.0.0.12:5433 32768 43690 RUNNING f71cea2d79e7557d85c83cd483843f55
6ed5af42351239a 18001 event_feature_uk index event_feature 10.0.0.12:5433 43690 54613 RUNNING 6ed5af42351239aecc6db0e0ccfd894d
ac0bdd0e971b0f5 18001 event_feature_uk index event_feature 10.0.0.11:5433 54613 65536 RUNNING ac0bdd0e971b0f5e07d5ba55c45cccdc
d37bd32a81b0c7e 18002 event_feature_map table 10.0.0.13:5433 43690 54613 RUNNING d37bd32a81b0c7ee4d3e2d7d211fd180
==============================================================
ASH ACTIVITY BY PHYSICAL RELATION
==============================================================
relation type parent_table weighted_samples pct_of_dml_ash
event_feature table 62933.75 84.76
event_feature_uk index event_feature 10131.75 13.64
<unmapped> ? 1187.00 1.60
event_feature_map table 1.00 0.00
==============================================================
PUBLIC.EVENT_FEATURE BASE-TABLE ASH DISTRIBUTION
==============================================================
tablet_id_prefix leader weighted_samples pct_of_base_table_ash start_hash end_hash tablet_id
8dd35bd6ace0fc8 10.0.0.13:5433 18477.00 29.36 10922 21845 8dd35bd6ace0fc82ed6b382dc8dc4cb2
d54d4d652bcc5c4 10.0.0.12:5433 12897.75 20.49 0 10922 d54d4d652bcc5c47a1b358b5fedbbf4e
55c30e42631493d 10.0.0.13:5433 10547.00 16.76 43690 54613 55c30e42631493dd661d6889bc9a44b5
85c21ad28c5bb98 10.0.0.12:5433 7625.00 12.12 32768 43690 85c21ad28c5bb98ad1f9fd374128557b
c142671eda00e15 10.0.0.11:5433 7292.00 11.59 54613 65536 c142671eda00e15a6845f98d2ccb8e5c
ec7b4849fe015df 10.0.0.11:5433 6095.00 9.68 21845 32768 ec7b4849fe015df5c67dd3070868b34d
==============================================================
PUBLIC.EVENT_FEATURE BASE-TABLE ASH BY TABLET LEADER
==============================================================
leader weighted_samples pct_of_base_table_ash
10.0.0.13:5433 29024.00 46.12
10.0.0.12:5433 20522.75 32.61
10.0.0.11:5433 13387.00 21.27
==============================================================
CURRENT STORED ROW DISTRIBUTION
==============================================================
WARNING: --row-counts is enabled. The next query can scan the entire
public.event_feature table.
HASH key : feature_hash (automatic)
Current stored rows by tablet:
tablet_id_prefix leader start_hash end_hash row_count pct_of_rows
d54d4d652bcc5c4 10.0.0.12:5433 0 10922 3493707 16.67
8dd35bd6ace0fc8 10.0.0.13:5433 10922 21845 3490952 16.66
ec7b4849fe015df 10.0.0.11:5433 21845 32768 3493795 16.67
85c21ad28c5bb98 10.0.0.12:5433 32768 43690 3494991 16.68
55c30e42631493d 10.0.0.13:5433 43690 54613 3494616 16.67
c142671eda00e15 10.0.0.11:5433 54613 65536 3491167 16.66
Current stored rows by tablet leader:
leader tablet_count row_count pct_of_rows
10.0.0.12:5433 2 6988698 33.34
10.0.0.13:5433 2 6985568 33.33
10.0.0.11:5433 2 6984962 33.33
==============================================================
ASH VS CURRENT ROW DISTRIBUTION BY TABLET
==============================================================
tablet_id_prefix leader row_pct ash_pct ash_row_ratio
8dd35bd6ace0fc8 10.0.0.13:5433 16.66 29.36 1.76
d54d4d652bcc5c4 10.0.0.12:5433 16.67 20.49 1.23
55c30e42631493d 10.0.0.13:5433 16.67 16.76 1.01
85c21ad28c5bb98 10.0.0.12:5433 16.68 12.12 0.73
c142671eda00e15 10.0.0.11:5433 16.66 11.59 0.70
ec7b4849fe015df 10.0.0.11:5433 16.67 9.68 0.58
==============================================================
ASH VS CURRENT ROW DISTRIBUTION BY TABLET LEADER
==============================================================
leader row_pct ash_pct ash_row_ratio
10.0.0.13:5433 33.33 46.12 1.38
10.0.0.12:5433 33.34 32.61 0.98
10.0.0.11:5433 33.33 21.27 0.64
Ratio: 1.00 = ASH share matches row share; >1.00 = more ASH per row share.
==============================================================
PUBLIC.EVENT_FEATURE INDEX DEFINITIONS
==============================================================
kind | index_name | index_definition
-------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY | event_feature_pkey | CREATE UNIQUE INDEX event_feature_pkey ON event_feature USING lsm (feature_hash HASH, feature_type ASC, is_anonymous ASC)
INDEX | event_feature_uk | CREATE UNIQUE INDEX event_feature_uk ON event_feature USING lsm (feature_id HASH) INCLUDE (description, feature_type, is_anonymous, feature_hash, version, payload)
(2 rows)
==============================================================
ANALYSIS COMPLETE - PUBLIC.EVENT_FEATURE INSERT
==============================================================
What Should You Do When You Find a Hot Tablet?
The script identifies where the disproportionate activity is occurring. It does not automatically tell you the root cause.
The next investigation depends on the wait profile and the workload.
For example, you may want to examine:
- ● Tablet leader placement.
- ● CPU utilization on the involved TServer.
- ● Raft replication latency.
- ● Storage latency.
- ● Replica placement.
- ● Network latency between replicas.
- ● MVCC-related waits.
- ● Secondary-index activity.
- ● The DML statement shape and batch size.
- ● Application concurrency.
- ● Whether the hotspot follows a particular tablet or follows a particular node after leadership changes.
That last test can be especially informative.
If the hotspot moves with the tablet, investigate something tablet- or key-range-specific.
If multiple tablets led by the same TServer consistently show elevated activity, investigate the node and its replicas.
A Note About UPDATE and DELETE
The script also supports:
yb_dml_hotspots.sh event_feature update ...
yb_dml_hotspots.sh event_feature delete ...
and
yb_dml_hotspots.sh event_feature all ...
When all is used, the output also includes an ASH breakdown by DML operation.
There is one important distinction when using --row-counts with UPDATE or DELETE workloads.
The current row distribution describes where the rows exist now.
It does not necessarily describe the distribution of the rows that were targeted by a historical UPDATE or DELETE workload.
So the row-to-ASH comparison is most intuitive for INSERT testing, especially when starting from an empty or known dataset.
For
UPDATE and DELETE, ASH itself remains extremely useful, but interpret current row counts appropriately. Cleanup
If you added the timestamp column only for this analysis, remove it after the test:
ALTER TABLE event_feature
DROP COLUMN inserted_at;
Final Takeaway
Even distribution of rows does not guarantee even distribution of work.
That distinction is easy to miss in a distributed database. A table can look perfectly balanced when you inspect its HASH key or count rows by tablet, while one tablet leader still spends significantly more time processing or waiting on the workload.
yb_dml_hotspots.sh helps connect those two views. By correlating pg_stat_statements, Active Session History, tablet metadata, wait events, leader placement, and optional row counts, it can show whether a problem is caused by data skew, activity skew, or something deeper in the write path.
For controlled performance testing, the optional inserted_at timestamp column can make the analysis even cleaner by anchoring ASH to the exact workload window. Just remember that it is temporary instrumentation… if you added it only for troubleshooting, remove it when the analysis is complete.
The important lesson is simple:
- Don’t stop at “the rows are balanced.” Ask whether the work is balanced too.
That extra comparison can be the difference between chasing the wrong key-distribution problem and identifying the tablet, leader, wait event, or node that actually deserves your attention.
Have Fun!
My wife bought me an ice-cream cake for my birthday and insisted on photographic evidence that I actually ate it. 😂🎂
And yes… those are our lovely dining room curtains behind me. Try not to let them steal the show. 😆