К основному содержанию
скилл CX Codex

huawei-cloud-dws-io-diag — скилл для Claude Code: что делает

Скилл huawei-cloud-dws-io-diag для Claude Code, Codex и Cursor. Находит первопричину перегрузки дискового ввода-вывода в кластерах хранилища данных Huawei Cloud DWS при срабатывании алертов.

MIT 180 установок ★ 59 обновлён 2026-11-12

Установить в Claude Code · Через Skills CLI (Vercel) Все способы и платформы ↓
npx skills add huaweicloud/huaweicloud-skills --skill huawei-cloud-dws-io-diag -a claude-code
Открыть на GitHub

Что делает huawei-cloud-dws-io-diag

Скилл подключается к Huawei Cloud DWS (GaussDB(DWS)) при всплесках утилизации дисков, росте задержки await или срабатывании алертов. Через трёхэтапное дерево решений скилл сопоставляет метрики и определяет сторону проблемы: клиентская нагрузка или сбой на системном уровне.

Основной сбор метрик идёт через утилиту KooCLI (hcloud), а если она недоступна или возвращает ошибку сети — управление перехватывает MCP-сервер DWS Autopilot. Инструмент выполняет исключительно чтение: он не перезагружает узлы, не завершает пользовательские SQL-запросы и не меняет параметры конфигурации кластера.

Без установленной KooCLI версии от 3.2.0 или настроенного MCP-сервера с действующими учетными данными IAM сбор метрик завершится ошибкой.

Какие задачи решает

  • выявить источник предельной утилизации дисков в кластере DWS
  • разобрать причину алерта по высокому времени ожидания ввода-вывода
  • разделить системный сбой дисковой подсистемы и тяжелые запросы клиентов
  • сформировать стандартизированный HTML-отчёт диагностики нагрузки DWS

Примеры запросов агенту

  • Диск на узле кластера DWS util под 100%, разбери причину сбоя
  • Пришёл алерт по высокой нагрузке I/O в кластере dws-prod-01, найди источник
  • Собери метрики ввода-вывода и подготовь HTML-отчёт по инциденту

Как установить huawei-cloud-dws-io-diag

1Через Skills CLI (Vercel)

npx skills add huaweicloud/huaweicloud-skills --skill huawei-cloud-dws-io-diag -a claude-code

2Вручную: положить файлы

~/.claude/skills/huawei-cloud-dws-io-diag/SKILL.md

Папка скилла целиком; для одного проекта — .claude/skills/huawei-cloud-dws-io-diag/

По шагам, с командами для каждой платформы: Как установить скилл в Claude Code: пять способов

Вопросы о huawei-cloud-dws-io-diag

Как установить huawei-cloud-dws-io-diag в Claude Code?

Выполните в терминале команду npx skills add huaweicloud/huaweicloud-skills --skill huawei-cloud-dws-io-diag -a claude-code.

Что делает huawei-cloud-dws-io-diag?

Скилл собирает метрики очередей и утилизации дисков в Huawei Cloud GaussDB(DWS), классифицирует сбой по дереву решений и выдаёт итоговый диагностический отчёт в формате HTML.

Может ли скилл сам оптимизировать SQL или перезапустить кластер?

Нет. Скилл работает строго в режиме чтения, поэтому не перезапускает узлы, не снимает зависшие запросы и не выводит инструкции по тюнингу SQL.

Исходный текст

SKILL.md · MIT
---
name: huawei-cloud-dws-io-diag
description: |
  DWS cluster I/O overload root cause diagnosis skill, based on KooCLI v3.2.0+ and DWS Autopilot MCP Server.
  Automatically collects I/O metrics, analyzes root causes (customer-side / system-side) via three-stage decision tree, and outputs a standardized diagnosis report.
  Applicable to DWS cluster I/O usage too high, I/O alarms, disk I/O load anomaly scenarios.
  Trigger words: "I/O高", "I/O告警", "I/O诊断", "I/O过载", "IO高", "IO告警", "IO诊断", "IO过载", "磁盘IO负载异常", "util打满", "await高", "high I/O", "I/O alarm", "I/O diagnosis"
tags: [huawei-cloud, dws, io, diagnostics, autopilot]

# ============================================================
# Internal extension fields
# ============================================================
trigger:
  keywords: ["DWS", "I/O高", "I/O告警", "I/O诊断", "I/O过载", "I/O使用率过高", "IO高", "IO告警", "IO诊断", "IO过载", "IO使用率过高", "磁盘I/O负载异常", "磁盘IO负载异常", "util打满", "await高", "节点数据盘I/O利用率超阈值", "high I/O", "I/O alarm", "I/O diagnosis", "I/O overload", "high I/O usage", "disk I/O anomaly", "util saturated", "high await", "I/O utilization exceeds threshold"]
  resource_types: ["DWS::instance_id"]
  hypotheses: ["io_high", "io_warning", "io_overload"]

input_schema:
  required:
    - name: "alarm_serial_number"
      type: "string"
      description: "Alarm serial number"
    - name: "cluster_id"
      type: "string"
      description: "Cluster unique ID (instance ID), corresponding to the alarm resourceId"
    - name: "first_alarm_time"
      type: "integer"
      description: "First alarm time, Unix millisecond timestamp (UTC timezone), used directly for tool from_ts/to parameters"
    - name: "alarm_name"
      type: "string"
      description: "Alarm name"
  optional:
    - name: "project_id"
      type: "string"
      description: "Project ID, used as the project_id parameter for tool calls. If not provided, read from MCP Server config file (conf/dws_config.yaml) project_id field"
    - name: "region_id"
      type: "string"
      description: "Region identifier, used for hcloud --cli-region parameter and report region field. If not provided, read from MCP Server config file (conf/dws_config.yaml) region_id field"
    - name: "node_name"
      type: "string"
      description: "Alert node name (extracted from alarm additionalInformation host_name; empty for cluster-level alarms)"
    - name: "cluster_name"
      type: "string"
      description: "Cluster name"
    - name: "alarm_severity"
      type: "integer"
      description: "Alarm severity (1=Critical, 2=Major, 3=Minor, 4=Info)"

output_schema:
  - name: "diagnosis_summary"
    type: "string"
    description: "Diagnosis result summary"
  - name: "diagnosis_report"
    type: "string"
    description: "Diagnosis report in HTML format"

allowed-tools:
  - dws_autopilot_get_clusters
  - dws_autopilot_get_metric
  - dws_autopilot_get_hosts
---

# Huawei Cloud DWS I/O Overload Diagnosis Skill

## Overview

This skill is dedicated to DWS cluster I/O overload root cause diagnosis. When a cluster triggers an I/O usage too high alarm, it automatically collects metric data, analyzes root causes (customer-side / system-side) via a three-stage decision tree, and outputs a standardized diagnosis report.

**Architecture**: KooCLI (hcloud) → DWS Autopilot API → Cluster monitoring metrics; MCP Server (dws_autopilot) → Fallback channel for the same API

**Applicable Scenarios**:
- DWS cluster I/O usage too high alarm
- Disk I/O load anomaly investigation
- User-initiated I/O diagnosis request

**Typical Use Cases**:
- "My DWS cluster I/O usage is very high, help diagnose"
- "Received an I/O alarm, cluster ID is xxx, help analyze the cause"
- "DWS cluster disk util is maxed out, see what's causing it"

**Skill Scope & Boundaries**:
- **Service boundary**: This skill only supports Huawei Cloud DWS (GaussDB(DWS)) data warehouse service. It does NOT apply to non-DWS services such as ECS, RDS, CCE, or others.
- **Problem-type boundary**: This skill only diagnoses DWS cluster I/O overload / I/O usage too high / disk I/O load anomaly (I/O overload, I/O anomaly, high await, high util, etc.). It does NOT handle CPU overload, memory high, or other non-I/O problems (refer to the dedicated CPU/memory diagnosis skills).
- **Operation boundary**: This skill is read-only diagnosis. It does NOT execute any management or maintenance operations such as cluster restart, scale-out/in, node replacement, kill SQL, or parameter modification.

**Important Rules**: All diagnosis conclusions must come from actual tool return results. Fabricating or assuming values is prohibited. Output only contains the diagnosis report; adding remediation suggestions, outputting SQL optimization statements, and using emoji are prohibited.

**Background Knowledge**: RAID architecture, I/O key metric thresholds, three major I/O scenario indicator features, and important principles are documented in [I/O Background Knowledge](references/io-background.md). Must read before diagnosis.

## Prerequisites

### 1. CLI Requirements

- KooCLI (hcloud) >= 3.2.0
- Verify installation: `hcloud version`
- If not installed or version too low, see [CLI Installation Guide](references/cli-installation-guide.md)

### 2. MCP Server Configuration (Fallback)

- When KooCLI is unavailable, use MCP Server as an alternative
- See [MCP Installation Guide](references/dws-mcp-installation-guide.md)

### 3. Authentication Configuration

- Valid Huawei Cloud credentials (AK/SK mode or IAM Token)
- **Security Rules**:
  - Never expose AK/SK values in conversations or commands
  - Never ask users to input AK/SK directly in conversation
  - Only use `hcloud configure list` to check credential status

### 4. IAM Permission Requirements

- dws:clusters:get, dws:clusters:list
- dws:metricData:get, dws:hostOverview:get
- See [IAM Policies](references/iam-policies.md)

## KooCLI Command Format Standard

### Command Format

```bash
# Query metric data
hcloud DWS ListMetricsData --cli-region=<region> --cluster_id=<id> --metric_name=<name> --project_id=<pid> --offset=0 --limit=200 --from=<from_ts> --to=<to_ts>

# Query host information
hcloud DWS ListHostOverview --cli-region=<region> --project_id=<pid> --cluster_id=<cid> --offset=0 --limit=200
```

### Tool Selection Strategy

Choose between KooCLI and MCP Server, preferring KooCLI. Step 0 checks hcloud availability:
- hcloud available (version >= 3.2.0) → Use KooCLI command line calls for subsequent steps
- hcloud unavailable (not installed or version < 3.2.0) → Use MCP Server tool calls for subsequent steps

**Fallback and Termination Strategy**:
- After selecting hcloud, if the first call returns `NETWORK_ERROR` (connection timeout, network unreachable, etc.), automatically fall back to MCP Server and use MCP mode for all subsequent steps
- If hcloud returns NETWORK_ERROR and MCP Server is also unavailable (not configured or returns authentication/connection error), terminate this skill and output: `KooCLI network unavailable and MCP Server connection failed. Please check KooCLI network configuration or DWS Autopilot MCP Server configuration and retry`
- After selecting MCP Server, if the first call returns an authentication error (e.g., 401 Unauthorized), do not fall back to hcloud. Terminate this skill directly and output: `MCP Server authentication failed. Please check DWS Autopilot MCP Server authentication configuration and retry`
- hcloud call failures that are not NETWORK_ERROR (e.g., parameter errors, insufficient permissions) do not trigger fallback; follow existing retry logic

Once a tool is selected, use it throughout without switching (except for NETWORK_ERROR fallback). If a call fails, retry once (maximum 2 attempts). If still failing, mark the metric as "unavailable" and continue to the next step. When all metric queries fail, generate the diagnosis report directly.

### Parameter Mapping

| Common Parameter | hcloud Parameter | MCP Parameter |
|------------------|------------------|---------------|
| Region | --cli-region | (built into MCP connection) |
| Project ID | --project_id | project_id |
| Cluster ID | --cluster_id | cluster_id |
| Metric Name | --metric_name | metric_name |
| Start Time | --from | from_ts |
| End Time | --to | to_ts |
| Pagination Offset | --offset | offset |
| Pagination Limit | --limit | limit |
| Sort Field | (not supported) | order_by |
| Sort Direction | (not supported) | sort_by |

### MCP Tools

| Tool Name | Purpose | Key Parameters |
|-----------|---------|----------------|
| `dws_autopilot_get_clusters` | Query cluster list | project_id, cluster_id, limit, offset |
| `dws_autopilot_get_hosts` | Query host information | project_id, cluster_id, limit, offset |
| `dws_autopilot_get_metric` | Query metric data | project_id, cluster_id, metric_name, from_ts, to_ts, limit, offset, order_by, sort_by |

**metric_data Parameter Notes**: metric_data does not support filtering by instance_name (no such parameter); query returns full cluster data and must be filtered by inst_name field for target instances; metric_data does not support period parameter (sampling period is automatically determined by the platform).

**Available metric_name**: `IOStat`, `CpuStat`, `cpu_io_diagnose_detail`, `business_concurrency`, `business_query_monitor`, `business_thread_wait`, `bussiness_conflict_lock`

**Time Protocol**: from_ts/to_ts must use Unix millisecond timestamps; all times are in UTC timezone; recommended time window: from 5 minutes before alarm time to alarm time (from_ts = first_alarm_time - 300000).

**Return Format**: Success `{"code": 0, "data": [...]}`; Failure `{"code": -1, "message": "error description"}`. On failure, retry once; if still failing, use degradation path and mark as "unavailable" in the report.

**Key Fields per Metric**:
- **IOStat**: ctime, host_id, disk_name, io_data{util, await, r_await, w_await, r_s, w_s, read_iops, write_iops, rMB_s, wMB_s, read_throughput, write_throughput, avgrq_sz, avgqu_sz} → core I/O metrics
- **CpuStat**: ctime, host_id, cpu_data{usr, sys, idle, iowait} → iowait > 30% indicates significant I/O pressure
- **cpu_io_diagnose_detail**: ctime, host_id, active_queries[{query, query_id, userName, cpu_rate, state, inst_name, duration_ms}], io_stats{read_iops, write_iops, read_throughput, write_throughput} → I/O diagnosis details + active queries
- **business_concurrency**: ctime, concurrency_value, active_connections → business concurrency
- **business_query_monitor**: ctime, query, query_id, username, duration_ms, host_id → historical real-time queries
- **business_thread_wait**: ctime, wait_event, count, username, host_id → wait events (wait wal sync, WALWriteLock, etc.)
- **bussiness_conflict_lock**: ctime, lock_type, blocker, waiter, username, host_id → lock conflicts

For query differences per step, see [Metric Reference](references/metric-reference.md).

### Pagination Specification

**All tool calls (MCP and hcloud) must use paginated queries** to prevent single responses from being too large and exceeding token limits.

**Pagination Rules**:
- Use `limit=200` uniformly (do not use values larger than 200, except the declared exception below)
- **Explicit exception**: `cpu_io_diagnose_detail` uses `limit=800` pagination (due to its largest data volume, multi-node × multi-instance × multi-timepoint × multi-query); single page of 200 may not be enough
- First page `offset=0`; if returned data count = limit, then `offset+=limit` and continue querying
- Repeat until returned data count < limit, then merge all paginated data
- When merging, concatenate all page data arrays into a complete dataset

**MCP Call Example** (using IOStat):
```
Page 1: dws_autopilot_get_metric(project_id, cluster_id, metric_name="IOStat", from_ts, to_ts, limit=200, offset=0)
If returned data length = 200:
Page 2: dws_autopilot_get_metric(project_id, cluster_id, metric_name="IOStat", from_ts, to_ts, limit=200, offset=200)
If returned data length < 200: Stop pagination, merge page 1 + page 2 data
```

**hcloud Call Example** (using IOStat):
```
Page 1: hcloud DWS ListMetricsData --cli-region=<region> --cluster_id=<id> --metric_name=IOStat --project_id=<pid> --offset=0 --limit=200 --from=<from_ts> --to=<to_ts>
If returned data count = 200:
Page 2: hcloud DWS ListMetricsData --cli-region=<region> --cluster_id=<id> --metric_name=IOStat --project_id=<pid> --offset=200 --limit=200 --from=<from_ts> --to=<to_ts>
If returned data count < 200: Stop pagination, merge data
```

## Workflow

Before diagnosis, create an execution plan based on Steps 0-7, then execute sequentially. For tool selection strategy, see the "KooCLI Command Format Standard" section; subsequent steps will not repeat this. All MCP tool calls follow the "Pagination Specification" section; subsequent steps will not repeat pagination details.

### Step 0: Environment Detection & Parameter Resolution

**Required Parameter Validation**: Before proceeding, verify that at least one of `cluster_id` or `cluster_name` is provided (both may be provided, but at least one is mandatory). If both are missing, prompt the user to provide a valid `cluster_id` or `cluster_name` and terminate the diagnosis until it is provided.

**Cluster Existence Validation**: Use the cluster list query (`hcloud DWS ListClusters` in hcloud mode, or `dws_autopilot_get_clusters` in MCP mode) to confirm the target cluster exists:
- If the query returns the error codes `DWS.13026` or `DWS.13032` (cluster not found), or the returned cluster list does not contain the specified cluster_id/cluster_name → the cluster does not exist. Terminate the diagnosis and prompt: `The specified DWS cluster does not exist. Please check the cluster_id/cluster_name and retry`.
- If the query succeeds and the cluster exists → continue with the following steps.

**Parameter Resolution**: If `project_id` or `region_id` is not provided by the user, read from MCP Server config file `conf/dws_config.yaml`:
- When `project_id` is missing: Execute `python -c "import yaml; c=yaml.safe_load(open('conf/dws_config.yaml')); print(c.get('project_id',''))"` to obtain it
- When `region_id` is missing: Execute `python -c "import yaml; c=yaml.safe_load(open('conf/dws_config.yaml')); print(c.get('region_id',''))"` to obtain it
- If the corresponding field in the config file is also empty, prompt the user to provide the parameter

Execute `hcloud version`; version >= 3.2.0 → tool_mode=hcloud, otherwise tool_mode=mcp.

**hcloud Network Availability Probe**: If tool_mode=hcloud, execute a lightweight API call (e.g., `hcloud DWS ListClusters --cli-region=<region> --project_id=<project_id>`) to verify network connectivity:
- Returns normal or business error (e.g., insufficient permissions, parameter error) → Network available, keep tool_mode=hcloud
- Returns NETWORK_ERROR → Network unavailable, fall back to tool_mode=mcp
- After fallback, if MCP is also unavailable → Terminate execution, prompt user to check KooCLI network configuration or MCP Server configuration

### Step 1: Query IOStat

Call metric query with metric_name="IOStat", time window: from_ts=`first_alarm_time - 300000`, to_ts=`first_alarm_time`. In MCP mode, use limit=200 paginated query.

**Autopilot Unavailable Determination**: Returns 50201/RDS.9999 error → Skip Steps 1-6, mark all metrics as "unavailable", proceed directly to Step 7.

**Parsing**: Group by host_id and disk_name, find the latest I/O data for each disk on each node. Extract util, await, r_await, w_await, r_s, w_s, read_iops, write_iops, rMB_s, wMB_s, read_throughput, write_throughput, avgrq_sz, avgqu_sz for each disk.

**I/O Overload Determination**: Whether any disk has util > 90% or await > 100 or throughput/IOPS reaching upper limit.

**I/O Imbalance Determination**: Whether the deviation of util or await between nodes > 20%.

**TOP Node Filtering**: Find the two nodes with the highest I/O load.

**Phenomenon Distribution Determination**: Whether a node has single disk I/O high or all disks I/O high (used for Step 4 scenario routing).

**High IO Time Points**: From the IOStat time series data, find all ctime points where any disk util > 90% on the top 2 IO nodes. Collect these ctime values as `high_io_ctimes` (list of millisecond timestamps). Also record the ctime with the highest util as `peak_io_ctime`. If no ctime has util > 90%, `high_io_ctimes` is empty and `peak_io_ctime` is the ctime with the highest util value.

**Output**: io_by_host_disk, io_by_host, max_io_hosts, problem_host_id, problem_host_util, problem_host_await, problem_host_avgrq_sz, problem_host_avgqu_sz, cluster_avg_util, is_imbalanced, io_deviation, high_io_nodes, single_disk_high_nodes, all_disk_high_nodes, high_io_ctimes, peak_io_ctime

### Step 2: Get Host Names

Call host information query (MCP mode limit=200 paginated), build host_id → {host_name, ip} mapping table. Only query host information for nodes involved in max_io_hosts from Step 1 output.

**Output**: host_id_to_info_map, problem_host_ip, node_name

### Step 3: Query CpuStat

Call metric query with metric_name="CpuStat". In MCP mode, use limit=200 paginated query.

**Parsing**: Extract iowait for each node. iowait > 30% indicates significant I/O pressure. High iowait but low CPU usage → typical I/O bottleneck.

**Output**: cpu_by_host, high_iowait_nodes

### Step 4: Three-Stage Decision Tree I/O Scenario Determination

Based on Step 1 IOStat data and Step 3 CpuStat data, determine I/O scenario via three-stage decision tree.

**Precondition**: await persistently > 100 and avgqu_sz persistently > 100?
- No → Occasional await/avgqu_sz spikes are normal, no scenario determination needed, mark as normal
- Yes → Enter three-stage determination

**Stage 1: Clear I/O Anomaly** — IOPS and throughput far below minimum specification?
- SSD: rMB/s+wMB_s << 900MB/s and r/s+w/s << 8000/s
- HDD: rMB/s+wMB_s << 250MB/s and r/s+w/s << 1000/s
- Yes → Confirmed **I/O Anomaly** (definite hardware issue)

**Stage 2: Clear I/O Overload** — IOPS or throughput exceeds maximum specification?
- SSD: rMB/s+wMB_s > 3500MB/s or r/s+w/s > 20000/s
- HDD: rMB/s+wMB_s > 800MB/s or r/s+w/s > 2000/s
- Yes → Confirmed **I/O Overload** (distinguish throughput overload/IOPS overload/dual overload)
  - Only throughput exceeds maximum → I/O throughput overload
  - Only IOPS exceeds maximum → IOPS overload
  - Both exceed maximum → Dual overload

**Stage 3: Gray Zone** — I/O close to minimum specification, or exceeds minimum but below maximum?
- Single disk poor performance (high await/high avgqu_sz), other disks normal → Determine as **I/O Anomaly**
- Single disk high I/O volume (high throughput/IOPS), other disks normal → Determine as **I/O Overload**
- All disks perform consistently → Compare with other nodes' I/O
  - Other nodes have I/O pressure but no I/O wait → Determine as this node **I/O Anomaly**
  - Other nodes have I/O pressure and wait / Other nodes have no I/O pressure and no wait → **Cannot definitively determine**, must provide both I/O overload and I/O anomaly full investigation directions
  - Other node data not available → Must suggest user provide other node iostat for comparison, while providing both full investigation directions

**Phenomenon Distribution Determination** (based on Step 1 IOStat data, analyzed by host_id + disk_name dimension):
- **Single node + single disk I/O high**: Only one disk on one node has I/O anomaly, other disks on that node are normal or low → Data skew / disk failure
- **Single node + all disks I/O high (overload type)**: All disks on one node have throughput/IOPS exceeding limits → Prioritize suspicion of node-level anomaly (RAID card read-write strategy degradation / RAID card CC verification / RAID card failure), because DWS data distribution is disk-based and will not cause all disks to be overloaded simultaneously; also provide I/O overload investigation direction as possibility B
- **Single node + all disks I/O high (anomaly type)**: All disks on one node have throughput/IOPS below limits but await is extremely high → Route to I/O anomaly (single node + all disks)
- **Multiple nodes / all nodes + all DN data disks I/O high** → Business-layer overload (full table scan, batch import, etc.)
- **System disk I/O high** → pg_log logs / pg_audit audit logs / system-level tasks

**I/O Type Determination** (for overload scenario routing):
- wMB/s >> rMB/s → Write I/O dominant
- rMB/s >> wMB/s → Read I/O dominant

**Output**: io_scenario, io_type, phenomenon_distribution, is_gray_zone, gray_zone_result

### Step 5: Query Business Concurrency and I/O Diagnosis Details

**5a: Query Business Concurrency**

Call metric query with metric_name="business_concurrency". In MCP mode, use limit=200 paginated query.

**Output**: max_concurrency, avg_concurrency, is_high_concurrency

**5b: Query I/O Diagnosis Details for Top 2 Nodes**

Call `dws_autopilot_get_metric`, parameters same as Step 3, `metric_name` changed to "cpu_io_diagnose_detail". **Note: metric_data does not support filtering by host_id; query returns full cluster data, then filter by the host_id of the top 2 IO nodes.**

**Paginated Query**: This metric has the largest data volume (multi-node × multi-instance × multi-timepoint × multi-query), a single page of 800 may not be enough. First page offset=0, limit=800; if returned data count = 800, then offset+=800 and continue querying, until returned data < 800, then merge all paginated data.

**High IO Time Period Filtering**: From the returned data, only keep sampling points where `ctime ∈ {{steps.iostat_metrics.output.high_io_ctimes}}`, excluding irrelevant queries from normal time periods. If high_io_ctimes is empty (no high IO time points), take the data from the time point with the highest util, annotate as "IO did not persistently exceed threshold, the following is query reference at the highest IO moment".

Parse returned data, extract for each node:
- Active query statement list (SQL)
- Execution user (userName)
- Query duration (duration_ms)
- IO consumption per SQL (io_read, io_write, collected by pidstat -t -d)
- IO statistics (io_stats: read_iops, write_iops, read_throughput, write_throughput)
- Lock wait status

**Spill to Disk Determination Rule**: Infer based on active statement characteristics, mark `spill_detected=true` if any of the following conditions is met:
- SQL statement contains multi-table JOIN + sort (ORDER BY) / aggregation (GROUP BY/DISTINCT) operations, and write IO is significantly higher than read IO (when io_data_available=true, io_write >> io_read, or io_stats.write_throughput abnormally high)
- business_thread_wait contains `wait file write` wait event
- Record matched SQL in `full_scan_queries` (reuse this field to record spill SQL), spill_info records details of the first spill SQL

**Full Table Scan Determination Rule**: Infer based on active statement characteristics, mark `full_scan_detected=true` if the following condition is met:
- SQL statement is a large table SELECT */COUNT(*)/MAX/MIN etc. aggregation query, and WHERE condition filtering is poor (no WHERE or only non-equivalence conditions)
- Record matched SQL in `full_scan_queries`

**Data Skew Inference Rule**: Infer based on IOStat inter-node IO deviation, non-confirmatory judgment:
- If IOStat shows a node's util is significantly higher than other nodes (deviation > 20%), and active queries on that node are concentrated on a few tables, then `is_data_skewed=true`
- skew_info records the two nodes with the largest deviation and deviation percentage, description annotated as "Inferred from inter-node IO deviation, possible data skew, recommend checking table distribution columns"

**IO Contribution TopN Statistics Rule** (time window aggregation, aligned with DMS monitoring item logic):
- **Data Availability Determination**: Check io_read/io_write fields of all active_queries, if all are 0 or fields do not exist, then `io_data_available=false`. **Note: io_data_available=true is a rare scenario** (only when cluster nodes have deployed pidstat collection plugin and collection is normal, io_read/io_write will have non-zero values, in most cases this field is 0), therefore io_data_available=false is the norm
- **Filter Threshold** (aligned with DMS plugin filter logic, io_read/io_write unit is kB/s): Only when `cpu_rate < 20%` AND `io_read < 1024` AND `io_write < 1024`, filter out that row (AND relationship, all three below threshold to filter, any one exceeding threshold is retained), retained rows participate in TopN statistics
- **Ruby User Filtering**: Rows with userName "Ruby" do not participate in TopN sorting (aligned with DMS monitoring item logic), Ruby's IO consumption is reflected in system-side root cause description
- **Cross-Sampling Point Aggregation**: Group by query_id, aggregate across multiple sampling points in high IO time period (io_read/io_write are rate values kB/s):
  - **Total IO**: Σ(io_read + io_write), reflects cumulative IO contribution within time window
  - **Total CPU**: Σ(cpu_rate), reflects cumulative CPU contribution within time window
  - **Appearance Frequency**: Number of sampling points where this SQL appears / total sampling points in high IO time period, e.g. 3/3, reflects persistence
- **TopN SQL IO Contribution**:
  - When io_data_available=true: From aggregated per-SQL rows (lwtid non-empty and userName not "Ruby"), sort by Total IO DESC take Top3, secondary sort Appearance Frequency DESC. Extract query_preview, query_id, userName, total_io_read, total_io_write, frequency, cpu_rate, io_characteristic
  - When io_data_available=false: From aggregated per-SQL rows (lwtid non-empty and userName not "Ruby"), sort by Total CPU DESC take Top3, secondary sort Appearance Frequency DESC. Extract query_preview, query_id, userName, total_cpu_rate, frequency, cpu_rate, io_characteristic. Annotate as "IO消耗数据不可用,按CPU占用排序作为参考"
- **TopN User IO Contribution**:
  - When io_data_available=true: From aggregated per-user rows (lwtid empty and userName non-empty and userName not "Ruby"), sort by Total IO DESC take Top3, secondary sort Appearance Frequency DESC. Extract userName, total_io_read, total_io_write, frequency
  - When io_data_available=false: From aggregated per-user rows (lwtid empty and userName non-empty and userName not "Ruby"), sort by Total CPU DESC take Top3, secondary sort Appearance Frequency DESC. Extract userName, total_cpu_rate, frequency. Annotate as "IO消耗数据不可用,按CPU占用排序作为参考"

**Time Annotation Rule**: `ctime` returned by `cpu_io_diagnose_detail` is the Autopilot collection snapshot time, not the actual SQL start time. If active_queries contains `duration_ms` field, then SQL start time = ctime - duration_ms, annotated as "start time"; if `duration_ms` is unavailable, use ctime directly, annotated as "collection time" (do not annotate collection time as "start time").

**Active User Statistics Rule**: Only count users and connections with state=active; idle state not counted. Group by userName and count active query numbers, identify top users.

**Output (steps.top_node_io_diagnose.output)**:
```
io_query_details:           # Node IO query details [{host_id, queries, io_stats}]
spill_detected:             # Whether spill to disk is detected (boolean)
spill_info:                 # Spill information {query, query_id, username, host_id, io_write_rate}
full_scan_detected:         # Whether full table scan is detected (boolean)
full_scan_queries:          # Full table scan query list [{query, query_id, username, host_id, duration_ms}]
is_data_skewed:             # Whether data skew is possible (inferred from IOStat inter-node deviation, non-confirmatory) (boolean)
skew_info:                  # Skew inference information {max_deviation_node, min_deviation_node, deviation_pct, description} (inferred from IOStat inter-node IO deviation, marked when deviation > 20%, cannot confirm specific table names)
topn_sql_io:                # Top3 SQL IO contribution (time window aggregation) [{query_preview, query_id, user_name, total_io_read, total_io_write, frequency, cpu_rate, io_characteristic}]
topn_user_io:               # Top3 User IO contribution (time window aggregation) [{user_name, total_io_read, total_io_write, frequency}]
io_data_available:          # Whether io_read/io_write data is available (boolean)
peak_io_ctime:              # Highest util time point ctime in high IO time period
```

**5c: Query Wait Events and Lock Conflicts**

Call metric query with metric_name="business_thread_wait" and metric_name="bussiness_conflict_lock". In MCP mode, use limit=200 paginated query.

**Parsing**: Identify I/O-related wait events (e.g., wait wal sync, WALWriteLock, I/O wait). Identify lock conflicts causing I/O anomalies.

**Output**: wait_events, io_wait_detected, lock_conflicts, lock_conflict_detected

### Step 6: Analyze Diagnosis Results

Based on data collected in Steps 1-5, combined with Step 4 three-stage decision tree result and phenomenon distribution, route to corresponding scenario and execute full investigation direction analysis.

**Time Formatting**: All Unix millisecond timestamps (first_alarm_time, ctime, etc.) are in UTC timezone. In the report, they must be converted to Beijing time (UTC+8) string `YYYY-MM-DD HH:MM:SS`. Can use `python -c "from datetime import datetime,timezone,timedelta; print(datetime.fromtimestamp({ms}/1000,tz=timezone(timedelta(hours=8))).strftime('%Y-%m-%d %H:%M:%S'))"`. Do not mentally calculate timestamp values.

**User Identity Judgment** (based on database user, i.e., cpu_io_diagnose_detail username):
- Database user is "omm" or "Ruby" → **System cause**
- Database user is not "omm" nor "Ruby" → **Customer-side cause**

#### Scenario Routing and Full Investigation Directions

Based on Step 4's `io_scenario` and `phenomenon_distribution`, route to the corresponding scenario...

© huaweicloud · MIT · huaweicloud/huaweicloud-skills

Похожие скиллы

Все скиллы по теме «Облака: AWS, Azure, GCP» →
dotnet-diag Microsoft Плагин dotnet-diag для GitHub Copilot добавляет скиллы для анализа производительности .NET, отладки и разбора инцидентов. Скиллы помогают искать причины сбоев и медленной работы. ★ 6 320звёзд репо alibabacloud-polardb-ai-assistant aliyun Скилл alibabacloud-polardb-ai-assistant для анализа и управления кластерами Alibaba Cloud PolarDB. Помогает диагностировать производительность, проверять параметры, анализировать логи и планировать обновления без лишних обращений в консоль. CX Codex +1 216установок cloud-databases-onboarding официальный google Скилл cloud-databases-onboarding для Claude Code помогает выбрать базу Google Cloud по требованиям проекта и подготовить файлы для её создания. Запускается при выборе сервиса или запросе на новую базу. CX Codex +1 3 600установок alibabacloud-das-agent aliyun Скилл alibabacloud-das-agent для Claude Code, Codex, Cursor и других агентов, который подключает Alibaba Cloud DAS и позволяет диагностировать базы данных на естественном языке. CX Codex +1 395установок clickhouse-cloud-management terrylica Скилл clickhouse-cloud-management помогает управлять пользователями и правами доступа в ClickHouse Cloud через SQL-запросы по HTTPS. CX Codex +1 126установок database-cloud-optimization-cost-optimize sickn33 Скилл database-cloud-optimization-cost-optimize для Claude Code анализирует расходы AWS, Azure и GCP, находит перерасход и предлагает изменения с оценкой риска. Помогает контролировать затраты на базы данных и облачную инфраструктуру. CX Codex +1 ★ 50 109звёзд репо

Из того же набора80

Весь набор →
huawei-cloud-sac-yolo Скилл huawei-cloud-sac-yolo разворачивает на Huawei Cloud GPU-инстанс и контейнер для обучения YOLO через Terraform, а в конце отдаёт адрес веб-интерфейса. CX Codex +1 187установок huawei-cloud-dws-cpu-diag Скилл huawei-cloud-dws-cpu-diag разбирает причину высокой загрузки CPU на кластере Huawei Cloud DWS: собирает метрики, делит причины на клиентские и системные и отдаёт готовый HTML-отчёт. CX Codex +1 184установок huawei-cloud-business-tf-support Скилл huawei-cloud-business-tf-support запрашивает биллинг, балансы и расценки Huawei Cloud для сценариев Terraform. Работает только на чтение через локальные скрипты Python. CX Codex +1 183установок huawei-cloud-dws-sql-check Скилл huawei-cloud-dws-sql-check для Claude Code статически проверяет SQL для Huawei DWS: синтаксис по грамматике и оформление по гайду разработки. Для проверки кластер не нужен. CX Codex +1 183установок huawei-cloud-cce-metric-analyzer Скилл huawei-cloud-cce-metric-analyzer собирает метрики кластеров Huawei Cloud CCE, сопоставляет их с ресурсами ECS, ELB, NAT и находит аномалии нагрузки через AOM Prometheus и утилиту hcloud. CX Codex +1 171установок huawei-cloud-cce-alarm-correlation-engine Скилл huawei-cloud-cce-alarm-correlation-engine для работы с алертами Huawei Cloud AOM в кластерах CCE: выгружает активные и исторические события, ищет штормы и дубликаты, ведёт правила алертов и уведомлений. CX Codex +1 168установок huawei-cloud-ucs-cluster-onboarding-manager Скилл huawei-cloud-ucs-cluster-onboarding-manager для Claude Code подключает Kubernetes-кластеры к Huawei Cloud UCS и управляет их группами и доступом. Выполняет операции через hcloud CLI. CX Codex +1 168установок huawei-cloud-msmodelslim-model-adapt Скилл huawei-cloud-msmodelslim-model-adapt для Claude Code и Codex для создания адаптеров моделей Transformers под msModelSlim. Проводит от разбора config.json до проверки квантованных весов. CX Codex +1 165установок

Спонсоры проекта

Компании, которые помогают держать аналитику открытой