Tidb Cluster Inspector

TiDB Cluster Inspector

Overview

TiDB clusters have a complex distributed architecture (TiDB + PD + TiKV) where issues in any component can cascade. This skill performs a systematic inspection across all layers:

1. Topology — Map all nodes, versions, uptime
2. Node Health — Check each TiDB/PD/TiKV node status
3. Slow Queries — Identify TOP N slow queries
4. Hot Regions — Detect read/write hotspots (hot-degree ranked)
5. TiKV Storage — Capacity and region distribution
6. PD Scheduling — Leader/region balance check
7. Leader Skew — Detect leader concentration imbalance across TiKV stores
8. TopSQL — Rank SQL by total latency, execution count, and memory

When to Use

• Periodic cluster health inspection (daily/weekly)
• After TiDB version upgrades
• Performance degradation investigation
• Capacity planning and scaling decisions
• Pre-maintenance readiness check

Required MCP Server

Required Privileges

• SELECT on information_schema.*

Diagnostic Workflow

Step 1: Cluster Topology

-- All nodes: type, address, version, status, uptime
SELECT
  TYPE AS role, INSTANCE AS address,
  VERSION AS version, STATUS AS status,
  START_TIME AS start_time
FROM information_schema.cluster_info
ORDER BY TYPE, INSTANCE;

Verify:

• All expected nodes present
• Version consistency across nodes
• No recently restarted nodes (uptime check)

Step 2: Node Health

-- Per-node server info
SELECT
  TYPE, INSTANCE, VERSION,
  TIMESTAMPDIFF(HOUR, START_TIME, NOW()) AS uptime_hours
FROM information_schema.cluster_info
WHERE STATUS != 'Up' OR STATUS IS NULL;

Alert conditions:

• Any node STATUS != 'Up' → critical
• Uptime < 24 hours → recently restarted, investigate
• Version mismatch → upgrade incomplete

Step 3: Slow Query Analysis

-- TiDB v4.0+: slow query from system table
SELECT
  Txn_start, Query_time, Parse_time, Compile_time,
  Process_time, Wait_time, Backoff_time,
  Request_count, Coprocessor_involved_count,
  LEFT(Query, 300) AS sql_fragment
FROM information_schema.slow_query
WHERE Time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY Query_time DESC
LIMIT 20;

-- TiDB v6.0+: cluster-wide slow query
SELECT * FROM information_schema.cluster_slow_query
WHERE Time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY Query_time DESC LIMIT 20;

Step 4: Hot Region Detection

-- Current hot regions: which tables/indexes are hot, read vs write, and how hot.
-- Columns verified against TiDB information_schema.TIDB_HOT_REGIONS (v4.0+).
SELECT
  DB_NAME,
  TABLE_NAME,
  INDEX_NAME,
  TYPE AS hot_type,             -- 'read' or 'write'
  MAX_HOT_DEGREE AS hot_degree, -- >0 means hot; higher = hotter
  FLOW_BYTES,                   -- bytes read + written in the region
  REGION_COUNT
FROM information_schema.TIDB_HOT_REGIONS
ORDER BY MAX_HOT_DEGREE DESC, FLOW_BYTES DESC
LIMIT 20;

Interpretation:

• MAX_HOT_DEGREE > 0 → region is a hotspot; the higher the degree the hotter
• TYPE = 'read' → read hotspot: add a covering/composite index or scatter the region to reduce point reads
• TYPE = 'write' → write hotspot: usually auto-increment or sequential inserts; consider region pre-splitting
• Pre-splitting is a write operation (run via pd-ctl or a dedicated tool such as tidba split); it is out of scope for this read-only skill — only recommend it

Step 5: TiKV Storage Status

-- Per-store storage and region distribution
SELECT
  STORE_ID,
  ADDRESS,
  STORE_STATE_NAME AS state,
  CAPACITY,
  AVAILABLE,
  USED_SIZE,
  ROUND(USED_SIZE / CAPACITY * 100, 2) AS usage_pct,
  REGION_COUNT,
  LEADER_COUNT
FROM information_schema.tikv_store_status
ORDER BY STORE_ID;

Alert conditions:

• Usage > 80% → plan expansion
• Leader count heavily imbalanced → PD scheduling issue
• Any store state != 'Up' → critical

Step 6: PD Scheduling

-- Check PD scheduling configuration
SHOW CONFIG WHERE TYPE = 'pd' AND NAME LIKE '%schedule%';
SHOW CONFIG WHERE TYPE = 'pd' AND NAME LIKE '%replicate%';

Key configs:

• schedule.leader-schedule-limit — Leader balance speed
• schedule.region-schedule-limit — Region balance speed
• schedule.enable-cross-table-merge — Region merge optimization

Step 7: Region Leader Skew

-- Leader concentration per TiKV store (cluster-level, lightweight — no full
-- region scan). leader_share_pct should be roughly even across TiKV nodes.
SELECT
  STORE_ID,
  ADDRESS,
  REGION_COUNT,
  LEADER_COUNT,
  ROUND(LEADER_COUNT / NULLIF(REGION_COUNT, 0) * 100, 2) AS leader_pct_of_store,
  ROUND(
    LEADER_COUNT /
      (SELECT SUM(LEADER_COUNT) FROM information_schema.TIKV_STORE_STATUS) * 100,
    2
  ) AS leader_share_pct
FROM information_schema.TIKV_STORE_STATUS
ORDER BY leader_share_pct DESC;

Alert conditions:

• One store's leader_share_pct far exceeds the others → PD leader scheduling skew
• Check schedule.leader-schedule-limit and each store's weight / labels config
• Per-table leader drill-down is possible via TIKV_REGION_STATUS (one row per region — expensive on large clusters; always scope with WHERE DB_NAME = '...' AND TABLE_NAME = '...')

Step 8: TopSQL Diagnosis

Rank SQL by drain across the cluster using the statement summary. All latency fields are in nanoseconds (SUM_LATENCY / 1e9 → seconds, / 1e6 → ms).

8a — Top 10 by total latency (which SQL drains the most cluster time):

SELECT
  DIGEST_TEXT,
  SCHEMA_NAME,
  STMT_TYPE,
  EXEC_COUNT,
  ROUND(SUM_LATENCY / 1e9, 3) AS total_latency_s,
  ROUND(AVG_LATENCY / 1e6, 2) AS avg_latency_ms,
  ROUND(MAX_LATENCY / 1e6, 2) AS max_latency_ms
FROM information_schema.CLUSTER_STATEMENTS_SUMMARY
WHERE EXEC_COUNT > 0
ORDER BY SUM_LATENCY DESC
LIMIT 10;

8b — Top 10 by execution count (high-frequency queries):

SELECT
  DIGEST_TEXT,
  SCHEMA_NAME,
  EXEC_COUNT,
  ROUND(AVG_LATENCY / 1e6, 2) AS avg_latency_ms
FROM information_schema.CLUSTER_STATEMENTS_SUMMARY
WHERE EXEC_COUNT > 0
ORDER BY EXEC_COUNT DESC
LIMIT 10;

8c — Top 10 by memory (OOM risk):

SELECT
  DIGEST_TEXT,
  SCHEMA_NAME,
  EXEC_COUNT,
  SUM_MEMORY,
  ROUND(AVG_MEM / 1024 / 1024, 2) AS avg_mem_mb
FROM information_schema.CLUSTER_STATEMENTS_SUMMARY
WHERE EXEC_COUNT > 0
ORDER BY SUM_MEMORY DESC
LIMIT 10;

8d — Diagnostic TOP 5 (the single biggest combined drain):

SELECT
  DIGEST_TEXT,
  SCHEMA_NAME,
  STMT_TYPE,
  EXEC_COUNT,
  ROUND(SUM_LATENCY / 1e9, 3) AS total_latency_s,
  ROUND(AVG_LATENCY / 1e6, 2) AS avg_latency_ms,
  ROUND(SUM_MEMORY / 1024 / 1024, 2) AS total_mem_mb
FROM information_schema.CLUSTER_STATEMENTS_SUMMARY
WHERE EXEC_COUNT > 0
ORDER BY SUM_LATENCY DESC
LIMIT 5;

Empty result guard — if the queries above return no rows, statement summary is disabled, so recommend enabling it:

SELECT @@tidb_enable_stmt_summary AS enabled;   -- expected ON (default since v4.0)
-- SET GLOBAL tidb_enable_stmt_summary = ON;     -- requires SUPER privilege; recommend to DBA

Scope & Limitations

This skill is a read-only SQL inspection. Some TiDB operational capabilities are not reachable via SQL — they require the PD HTTP API, write access, or a dedicated feature. For those, point the DBA to the right tool instead of attempting them here:

The four enhanced steps above (hot region, leader skew, TopSQL latency/exec/memory) cover the read-only diagnosis surface. This skill approximates "CPU drain" via total latency, which is the standard proxy when the TopSQL feature is not enabled.

Output Template

🔍 TiDB Cluster Inspection Report
══════════════════════════════════
📋 Cluster Overview
  Topology: {tidb_count} TiDB + {pd_count} PD + {tikv_count} TiKV
  TiDB Version: {version}
  Inspection Time: {timestamp}

✅ Node Health
  | Role | Address | Version | Status | Uptime |
  |------|---------|---------|--------|--------|
  | TiDB | ...     | 4.0.11  | Up     | 187d   |
  | PD   | ...     | 4.0.11  | Up     | 327d   |
  | TiKV | ...     | 4.0.11  | Up     | 4d     |

⚠️ Slow Queries TOP 5
  | Duration | Scan Rows | SQL |
  |----------|-----------|-----|

⚡ TopSQL TOP 5 (by total latency)
  | SQL (digest) | Schema | Execs | Total Latency | Avg Latency |
  |--------------|--------|-------|---------------|-------------|

🔥 Hot Regions
  | DB | Table | Type | Flow Bytes |
  |----|-------|------|------------|

⚖️ Leader Skew (per TiKV store)
  | Store | Leader Share% | Note |
  |-------|---------------|------|

💾 TiKV Storage
  | Store | Capacity | Used | Usage% | Regions | Leaders |
  |-------|----------|------|--------|---------|---------|

⚙️ PD Config
  Leader Schedule: {enabled}
  Region Schedule: {enabled}

💡 Findings & Recommendations
  {Prioritized action items}

Example Usage

User: "Inspect my TiDB cluster"

AI Response:

🔍 TiDB Cluster Inspection

Topology: 3 TiDB + 3 PD + 3 TiKV = 9 nodes
Version: v4.0.11 ✅ (consistent)

✅ All 9 nodes Up

🔥 Hot Regions: 5 found
  order_service.order_detail (read, 9.5MB flow)
  → Check index on order_detail

💾 TiKV Storage: 3 stores
  All < 50% usage ✅
  Leaders balanced ✅

💡 Recommendation: Investigate read hotspot on order_service
   order_detail table — consider adding composite index.

Best Practices

Inspection Schedule

TiDB-Specific Tuning

-- Enable slow query log
SET GLOBAL tidb_enable_slow_log = ON;
SET GLOBAL tidb_slow_log_threshold = 200; -- 200ms

-- Hot region scatter (execute via pd-ctl)
-- pd-ctl scheduler add scatter-range {table_name}

-- Region merge for small tables
SET GLOBAL tidb_merge_partial_ddl = ON;

Quick Reference

Use this skill for systematic TiDB cluster health inspection across all components.