tidb-cluster-inspector

Comprehensive TiDB cluster inspection. Checks cluster topology, node health, slow queries, hot regions, TiKV storage status, PD scheduling, leader skew, and TopSQL drain (latency / execution count / memory). Outputs a full inspection report with prioritized findings. Compatible with TiDB v4.0+.

仓库: openocta/openocta_skills
最近来源活动: 2026年6月21日 07:49
检测到的 SKILL.md 语言: 英语
星标: 162
分支: 47

安装方式

默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。

检查来源文件

决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。

返回搜索返回安装

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;

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

-- 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;

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;

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;

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;

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;

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

Capability	Why not in this skill	Where to do it
Region pre-splitting (`tidba split`)	Write operation via PD	`pd-ctl` / `SPLIT TABLE` DDL / `tidba split`
Runaway SQL throttle/kill (`tidba runaway`)	Resource-group write op	`RESOURCE GROUP` + runaway watches
Majority-DOWN region detection (`tidba region replica`)	Needs PD region-peer API	`pd-ctl` / `tidba region replica`
Native CPU-ranked TopSQL (`tidba topsql cpu`)	Needs TiDB TopSQL feature (`tidb_enable_top_sql`)	TopSQL / TiDB Dashboard

🔍 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}

🔍 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.

-- 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;

Action	Query
Cluster info	`SELECT * FROM information_schema.cluster_info`
Slow queries	`SELECT * FROM information_schema.slow_query ORDER BY Query_time DESC`
Hot regions	`SELECT * FROM information_schema.TIDB_HOT_REGIONS`
TiKV stores	`SELECT * FROM information_schema.tikv_store_status`
PD config	`SHOW CONFIG WHERE TYPE='pd'`
TopSQL drain	`SELECT DIGEST_TEXT, EXEC_COUNT, SUM_LATENCY FROM information_schema.CLUSTER_STATEMENTS_SUMMARY ORDER BY SUM_LATENCY DESC LIMIT 10`
Table stats	`SHOW STATS_META WHERE table_name='...'`