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) ```bash #!/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 ```bash #!/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 ```bash #!/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 ```bash #!/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 ```bash #!/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 ```bash #!/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 | Shortcut | Counter | Why | |----------|---------|-----| | "I'll skip discovery and check known resources" | Always run Phase 1 discovery first | Resource names change, new resources appear — assumed names cause errors | | "The user only asked for a quick check" | Follow the full discovery → analysis flow | Quick checks miss critical issues; structured analysis catches silent failures | | "Default configuration is probably fine" | Audit configuration explicitly | Defaults often leave logging, security, and optimization features disabled | | "Metrics aren't needed for this" | Always check relevant metrics when available | API/CLI responses show current state; metrics reveal trends and intermittent issues | | "I don't have access to that" | Try the command and report the actual error | Assumed permission failures prevent useful investigation; actual errors are informative | ## 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