Skip to content

cloudthinker-ai/CloudSkills / skills/connections/analyzing-cockroachdb

analyzing-cockroachdb

Unverified

Use when working with Cockroachdb — cockroachDB cluster health, range distribution, SQL statistics, schema change monitoring, and query optimization.

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

ShortcutCounterWhy
"I'll skip discovery and check known resources"Always run Phase 1 discovery firstResource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"Follow the full discovery → analysis flowQuick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"Audit configuration explicitlyDefaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"Always check relevant metrics when availableAPI/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 errorAssumed 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
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall