Analyzing Cockroachdb

CockroachDB Analysis Skill

Analyze and optimize CockroachDB clusters with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated database/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. Cluster overview
cockroach node status --certs-dir="$CERTS_DIR" --host="$CRDB_HOST"

# 2. List databases
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW DATABASES;"

# 3. List tables in a database
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW TABLES FROM mydb;"

# 4. Get table schema
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW CREATE TABLE mydb.mytable;"

# 5. Check column names
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW COLUMNS FROM mydb.mytable;"

Phase 1 outputs:

• Cluster topology and node liveness
• List of databases and tables
• Table schemas with confirmed column names

Phase 2: Analysis (only after Phase 1)

Only reference databases, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core SQL runner — always use this
crdb_sql() {
    local query="$1"
    cockroach sql --certs-dir="${CERTS_DIR:-certs}" --host="${CRDB_HOST:-localhost}" \
        --format=tsv -e "$query" 2>/dev/null
}

# Admin UI API helper (if available)
crdb_api() {
    local endpoint="$1"
    curl -sk "https://${CRDB_HOST:-localhost}:8080/_admin/v1/$endpoint"
}

Anti-Hallucination Rules

• NEVER reference a database or table without confirming via SHOW DATABASES / SHOW TABLES
• NEVER reference column names without seeing them in SHOW COLUMNS or SHOW CREATE TABLE
• NEVER assume node IDs — always get them from node status
• NEVER guess range counts — always query crdb_internal.ranges
• NEVER assume cluster version — check with SHOW CLUSTER SETTING version

Safety Rules

• READ-ONLY ONLY: Use only SELECT, SHOW, EXPLAIN, system catalog queries
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, SET CLUSTER SETTING without explicit user request
• ALWAYS add LIMIT to queries on user tables
• Use EXPLAIN ANALYZE carefully — it executes the query
• Prefer EXPLAIN (no ANALYZE) for plan inspection without execution

Common Operations

Cluster Health Overview

#!/bin/bash
echo "=== Node Status ==="
crdb_sql "SELECT node_id, address, is_live, ranges, locality FROM crdb_internal.gossip_nodes ORDER BY node_id;"

echo ""
echo "=== Store Status ==="
crdb_sql "SELECT node_id, store_id, used, available, range_count FROM crdb_internal.kv_store_status ORDER BY node_id;"

echo ""
echo "=== Cluster Settings (non-default) ==="
crdb_sql "SHOW ALL CLUSTER SETTINGS;" | grep -v "default"

echo ""
echo "=== Version ==="
crdb_sql "SELECT version, crdb_internal.node_id() as node_id;"

Range Distribution Analysis

#!/bin/bash
echo "=== Range Distribution per Node ==="
crdb_sql "SELECT lease_holder, count(*) as range_count FROM crdb_internal.ranges GROUP BY lease_holder ORDER BY lease_holder;"

echo ""
echo "=== Hot Ranges (top 10 by QPS) ==="
crdb_sql "SELECT range_id, table_name, start_pretty, lease_holder, queries_per_second FROM crdb_internal.ranges ORDER BY queries_per_second DESC LIMIT 10;"

echo ""
echo "=== Under-replicated Ranges ==="
crdb_sql "SELECT range_id, table_name, replicas, learner_replicas FROM crdb_internal.ranges WHERE array_length(replicas, 1) < 3;"

SQL Statistics Analysis

#!/bin/bash
echo "=== Top Slow Queries (by mean latency) ==="
crdb_sql "SELECT substring(metadata->>'query', 1, 80) as query, statistics->'execution_statistics'->>'cnt' as exec_count, statistics->'execution_statistics'->>'meanRunLatency' as mean_lat FROM crdb_internal.statement_statistics ORDER BY (statistics->'execution_statistics'->>'meanRunLatency')::float DESC LIMIT 15;"

echo ""
echo "=== Schema Changes in Progress ==="
crdb_sql "SELECT job_id, description, status, running_status, fraction_completed FROM crdb_internal.jobs WHERE job_type = 'SCHEMA CHANGE' AND status IN ('running', 'paused', 'pending') ORDER BY created DESC LIMIT 10;"

Table Analysis

#!/bin/bash
DB_NAME="${1:-defaultdb}"
TABLE="${2:-mytable}"

echo "=== Table Stats ==="
crdb_sql "SHOW STATISTICS FOR TABLE $DB_NAME.$TABLE;"

echo ""
echo "=== Index Usage ==="
crdb_sql "SELECT * FROM crdb_internal.index_usage_statistics WHERE table_id = (SELECT id FROM crdb_internal.tables WHERE name = '$TABLE' AND database_name = '$DB_NAME');"

echo ""
echo "=== Table Ranges ==="
crdb_sql "SELECT count(*) as range_count, sum(range_size_mb) as total_mb FROM crdb_internal.ranges WHERE table_name = '$TABLE';"

Output Format

Present results as a structured report:

Analyzing Cockroachdb Report
════════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Common Pitfalls

• Multi-region latency: Cross-region queries have higher latency — check locality settings
• Contention on hot keys: Sequential key generation causes range hotspots — use UUID or hash-sharded indexes
• Schema change locks: Long-running schema changes can block writes — check job status
• Range splits: Tables with rapid growth may need manual pre-splitting
• Transaction retries: CockroachDB uses serializable isolation — application must handle retry errors (40001)
• Clock skew: Node clock skew > 500ms causes errors — check node status for clock offset