analyzing-tidb

name	analyzing-tidb
description	Use when working with Tidb — tiDB cluster topology, slow query analysis, hot region analysis, dashboard monitoring, and SQL optimization.
connection_type	tidb
preload	false

TiDB Analysis Skill

Analyze and optimize TiDB 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 topology
mysql -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" -p"$TIDB_PASSWORD" -e "SELECT * FROM information_schema.cluster_info;"

# 2. List databases
mysql -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" -p"$TIDB_PASSWORD" -e "SHOW DATABASES;"

# 3. List tables
mysql -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" -p"$TIDB_PASSWORD" -e "SHOW TABLES FROM my_database;"

# 4. Describe table (never assume column names)
mysql -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" -p"$TIDB_PASSWORD" -e "DESCRIBE my_database.my_table;"

# 5. Check TiDB version
mysql -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" -p"$TIDB_PASSWORD" -e "SELECT tidb_version();"

Phase 1 outputs:

• Cluster component topology (TiDB, TiKV, PD)
• 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 TiDB query runner — always use this
tidb_query() {
    local query="$1"
    mysql -h "${TIDB_HOST:-localhost}" -P "${TIDB_PORT:-4000}" \
        -u "${TIDB_USER:-root}" -p"${TIDB_PASSWORD}" \
        -N -B -e "$query"
}

# PD API helper
pd_api() {
    local endpoint="$1"
    curl -s "http://${PD_HOST:-localhost}:2379/pd/api/v1/$endpoint"
}

# TiDB Dashboard API
tidb_dashboard() {
    local endpoint="$1"
    curl -s "http://${TIDB_HOST:-localhost}:10080/$endpoint"
}

Anti-Hallucination Rules

• NEVER reference a database or table without confirming via SHOW commands
• NEVER reference column names without running DESCRIBE first
• NEVER assume cluster topology — always query cluster_info
• NEVER guess store IDs — always get from PD API
• NEVER assume TiDB version features — check version first

Safety Rules

• READ-ONLY ONLY: Use only SELECT, SHOW, EXPLAIN, ADMIN SHOW, information_schema queries
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, ADMIN commands that modify state without explicit user request
• ALWAYS add LIMIT to user table queries
• Use EXPLAIN ANALYZE carefully — it executes the query

Common Operations

Cluster Topology

#!/bin/bash
echo "=== Cluster Components ==="
tidb_query "SELECT TYPE, INSTANCE, STATUS_ADDRESS, VERSION, GIT_HASH FROM information_schema.cluster_info ORDER BY TYPE, INSTANCE;"

echo ""
echo "=== TiKV Stores ==="
pd_api "stores" | jq '.stores[] | {store_id: .store.id, address: .store.address, state_name: .store.state_name, capacity: .status.capacity, available: .status.available, region_count: .status.region_count}'

echo ""
echo "=== PD Members ==="
pd_api "members" | jq '.members[] | {name, peer_urls, client_urls, is_leader: .is_leader}'

Slow Query Analysis

#!/bin/bash
echo "=== Slow Queries (last 1h) ==="
tidb_query "SELECT Time, Query_time, Process_time, Mem_max, Disk_max, SUBSTR(Query, 1, 100) as query_preview FROM information_schema.slow_query WHERE Time > DATE_SUB(NOW(), INTERVAL 1 HOUR) ORDER BY Query_time DESC LIMIT 20;"

echo ""
echo "=== Slow Query Statistics ==="
tidb_query "SELECT Digest_text, COUNT(*) as count, AVG(Query_time) as avg_time, MAX(Query_time) as max_time, AVG(Process_time) as avg_process FROM information_schema.slow_query WHERE Time > DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY Digest_text ORDER BY avg_time DESC LIMIT 15;"

echo ""
echo "=== Statements Summary ==="
tidb_query "SELECT DIGEST_TEXT, SUM_LATENCY, AVG_LATENCY, EXEC_COUNT, AVG_MEM, AVG_DISK FROM information_schema.statements_summary ORDER BY SUM_LATENCY DESC LIMIT 15;"

Hot Region Analysis

#!/bin/bash
echo "=== Hot Read Regions ==="
pd_api "hotspot/regions/read" | jq '.as_peer[0:10] | .[] | {region_id: .region_id, store_id: .store_id, flow_bytes: .flow_bytes}'

echo ""
echo "=== Hot Write Regions ==="
pd_api "hotspot/regions/write" | jq '.as_peer[0:10] | .[] | {region_id: .region_id, store_id: .store_id, flow_bytes: .flow_bytes}'

echo ""
echo "=== Region Distribution ==="
pd_api "stats/region" | jq '{count, empty_count, storage_size, storage_keys}'

Table Analysis

#!/bin/bash
DB="${1:-my_database}"
TABLE="${2:-my_table}"

echo "=== Table Stats ==="
tidb_query "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA = '$DB' AND TABLE_NAME = '$TABLE';"

echo ""
echo "=== Table Regions ==="
tidb_query "SHOW TABLE $DB.$TABLE REGIONS;" | head -20

echo ""
echo "=== Index Stats ==="
tidb_query "SHOW STATS_HISTOGRAMS WHERE db_name = '$DB' AND table_name = '$TABLE';" 2>/dev/null

Output Format

Present results as a structured report:

Analyzing Tidb 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

• Auto-increment hotspot: Sequential auto-increment IDs cause write hotspots — use AUTO_RANDOM or SHARD_ROW_ID_BITS
• GC lifetime: Long transactions can block GC — check tidb_gc_life_time
• Statistics staleness: Stale stats cause bad query plans — check stats health
• Region splitting: Large tables may need manual region splitting — check region distribution
• TiFlash vs TiKV: TiFlash is columnar (analytics), TiKV is row (OLTP) — route queries appropriately
• Pessimistic vs Optimistic: Default is pessimistic locking since TiDB 4.0 — check transaction mode