SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillshologres-instance-health-analyse

hologres-instance-health-analyse

Data & analytics

View repository

Updated 10-15 19:55

About this skill

Hologres Instance Health Diagnosis and Inspection

Systematically diagnose the health status of a Hologres instance, and output a structured diagnostic report covering three major modules: Warehouse resources, error analysis, and slow queries.

Time Range Parameter

The default time range in all the following queries is 7 days. Users can specify other time ranges (such as 1 day、3 days、30 days、1 hour), and simply replace the {TIME_RANGE} in the SQL.

默认值：{TIME_RANGE} = 7 days
示例：用户说“分析过去 3 天” → 替换为 interval '3 days'

Core Diagnostic Modules

Warehouse 资源 → 报错归类分析 → 慢查询分析（CPU/内存）→ 汇总诊断报告
Module Analysis Content Data Source
Warehouse Resources CPU, memory, and connection count usage pg_stat_activity、hg_warehouse_metrics
Error Analysis FAILED Query categorization, count statistics, solutions hologres.hg_query_log
Slow Queries (CPU) Aggregate by SQL fingerprint to troubleshoot CPU hotspots hologres.hg_query_log
Slow Queries (Memory) Aggregate by SQL fingerprint to troubleshoot memory consumption hologres.hg_query_log

Prerequisites

1. Install hologres-cli

pip install hologres-cli

2. Configure Connection

hologres config          # 交互式配置向导
hologres status          # 验证连接

3. Confirm Permissions

Requires Superuser or pg_read_all_stats permissions:

hologres sql run "SELECT current_user, usesuper FROM pg_user WHERE usename = current_user"

If permissions are insufficient, ask a Superuser to perform authorization:

hologres sql run --write "GRANT pg_read_all_stats TO \"云账号ID\""

4. Set SQL Tracking

Set environment variables before executing all diagnostic SQL to ensure queries are traceable:

export HOLOGRES_SKILL=hologres-instance-health-analyse

5. Serverless Computing Pool

hologres-cli The connection layer automatically sets hg_computing_resource = 'serverless'. All diagnostic queries are executed through the Serverless computing pool and will not consume business Warehouse resources. No additional configuration is required.

Step 1: Warehouse Resource Inspection

Analyze the CPU, memory, and connection count usage of each Warehouse to determine whether there are resource bottlenecks.

For detailed SQL, see references/warehouse-metrics.md。

Execution Command：

# 各 Warehouse 活跃连接分布
hologres sql run --no-limit-check "SELECT warehouse_name, state, count(1) AS conn_count, count(CASE WHEN wait_event IS NOT NULL THEN 1 END) AS waiting, count(CASE WHEN state = 'active' THEN 1 END) AS active, count(CASE WHEN state = 'idle' THEN 1 END) AS idle FROM pg_stat_activity WHERE usename != 'system' AND datname IS NOT NULL GROUP BY warehouse_name, state ORDER BY warehouse_name, count(1) DESC"

# 等待中的 Query
hologres sql run --no-limit-check "SELECT pid, usename, warehouse_name, state, wait_event_type, wait_event, now() - query_start AS wait_duration, left(query, 100) AS sql_snippet FROM pg_stat_activity WHERE wait_event IS NOT NULL AND state = 'active' AND usename != 'system' ORDER BY query_start ASC"

Key Metrics：

  • Whether the connection count is too high (alert if a single Warehouse > 200)
  • Whether there are Queries that have been waiting for locks/resources for a long time (wait_event is not empty)
  • Distribution of active Queries across Warehouses

Output Format Example：

Warehouse 资源诊断：
- Warehouse 1：活跃连接 45，等待队列 3，CPU 使用率正常
- Warehouse 2：活跃连接 120（⚠️ 偏高），有 5 个 Query 等待锁
建议：检查是否存在长事务或高频短查询导致连接堆积

Step 2: Error Categorization Analysis

Query hologres.hg_query_log for status = 'FAILED' Queries, categorize and count by error message, and provide solutions.

For detailed SQL and common errors, see references/error-analysis.md。

Execution Command：

# 报错类型归类统计（默认过去 7 天，可替换 {TIME_RANGE}）
hologres sql run --no-limit-check "SELECT error_category, warehouse_name, count(1) AS error_count, min(query_start) AS first_seen, max(query_start) AS last_seen FROM (SELECT *, CASE WHEN ltrim(split_part(message, ': ', 2),' ') != 'XX000' and length(ltrim(split_part(message, ': ', 2),' ')) = 5 THEN ltrim(split_part(message, ': ', 2),' ') WHEN (query LIKE '-- query row count from analyze table%' or query LIKE '-- query from analyze table%') THEN 'AutoAnalyze-Failed' WHEN message LIKE '%canceling statement due to user request%' THEN 'User Cancelled' WHEN (message LIKE '%Total memory used by all existing queries exceeded memory limitation%' OR message LIKE '%query exceed per query memory limitation%') THEN 'OOM' WHEN message LIKE '%WriteLogRecord is not allowed in readonly mode%' THEN 'READONLY' WHEN message LIKE '%query next from foreign table executor failed%' THEN 'queryNext Foreign table Failed' WHEN message LIKE '%SERVER_INTERNAL_ERROR%query next from pg executor failed from%' THEN 'queryNext PQE Failed' WHEN (message LIKE '%Exceeds the scan limitation%' OR message LIKE '%Exceeds the partition limitation%') THEN 'Exceed Odps Scan Limit' WHEN message LIKE '%permission denied%' THEN 'permission denied' WHEN message LIKE '%duplicate key value violates%' THEN 'pk violates' WHEN message LIKE '%does not exist%' THEN 'does not exist' WHEN message LIKE '%Internal error%' THEN 'Other Internal Errors' ELSE 'OTHER' END AS error_category, warehouse_name, query_start FROM hologres.hg_query_log WHERE status = 'FAILED' AND query_start >= now() - interval '{TIME_RANGE}' AND message IS NOT NULL) t GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 50"

For the complete error classification logic (including 50+ error types), see references/error-analysis.md。

Output Format(must be output in this format):

报错分析汇总：

报错 1：ERROR: 57014: canceling statement due to statement timeout
报错次数：N 次
Warehouse：Warehouse_2
发生时段：2024-09-27 11:17:46 ~ 2024-10-09 16:36:16
解决方案：查询超时通常由慢 SQL 以高 QPS 打入导致 CPU 满载。建议：
  1. 通过慢查询分析定位高频 CPU 消耗 SQL
  2. 优化 SQL 或增加 Warehouse 资源
  3. 考虑设置合理的 statement_timeout

报错 2：...

Step 3: Slow Query Analysis

3.1 CPU-Granularity Slow Queries

Categorize by SQL fingerprint (digest) to identify the Query patterns with the highest CPU consumption.

# CPU 慢查询 Top 10（默认过去 7 天，可替换 {TIME_RANGE}）
hologres sql run --no-limit-check "SELECT digest AS sql_digest, count(1) AS exec_count, round(avg(cpu_time_ms)::numeric / 1000, 2) AS avg_cpu_sec, round(max(cpu_time_ms)::numeric / 1000, 2) AS max_cpu_sec, round(sum(cpu_time_ms)::numeric / 1000, 2) AS total_cpu_sec, round(avg(duration)::numeric / 1000, 2) AS avg_duration_sec, warehouse_name, max(query_id) AS sample_query_id, max(query)::char(100) AS sql_sample FROM hologres.hg_query_log WHERE query_start >= now() - interval '{TIME_RANGE}' AND digest IS NOT NULL AND usename != 'system' AND cpu_time_ms IS NOT NULL GROUP BY digest, warehouse_name ORDER BY sum(cpu_time_ms) DESC LIMIT 10"

3.2 Memory-Granularity Slow Queries

Categorize by SQL fingerprint to identify the Query patterns with the highest memory consumption.

# 内存慢查询 Top 10（默认过去 7 天，可替换 {TIME_RANGE}）
hologres sql run --no-limit-check "SELECT digest AS sql_digest, count(1) AS exec_count, round(avg(memory_bytes)::numeric / 1048576, 2) AS avg_memory_mb, round(max(memory_bytes)::numeric / 1048576, 2) AS peak_memory_mb, round(avg(duration)::numeric / 1000, 2) AS avg_duration_sec, warehouse_name, max(query_id) AS sample_query_id, max(query)::char(100) AS sql_sample FROM hologres.hg_query_log WHERE query_start >= now() - interval '{TIME_RANGE}' AND digest IS NOT NULL AND memory_bytes IS NOT NULL GROUP BY digest, warehouse_name ORDER BY avg(memory_bytes) DESC LIMIT 10"

For detailed analysis SQL, see references/slow-query-analysis.md。

Output Format(must be output in this format):

慢 SQL 1：
QueryID：xxxxx（示例）
SQL 指纹：md5xxxxxx
执行次数：N 次 / 天
平均 CPU：X 秒  |  累计 CPU：Y 秒
原因：该 SQL 扫描量大，缺乏分区过滤，导致全表扫描
优化建议：
  1. 添加分区裁剪条件
  2. 检查是否缺少 bitmap/clustering key 索引
  3. 使用 EXPLAIN ANALYZE 查看执行计划

慢 SQL 2：
...

Step 4: Summarize and Output the Diagnostic Report

After completing the first three steps of analysis, comprehensively output the following structured report:

========== Hologres 实例健康诊断报告 ==========
诊断时间：{当前时间}
诊断范围：过去 {TIME_RANGE}（默认 7 days）

【1. Warehouse 资源】
状态：正常 / 异常
...

【2. 报错分析】
FAILED Query 总数：N 条
主要报错类型：
  - 超时（statement timeout）：X 次 → [解决方案]
  - OOM（memory exceeded）：Y 次 → [解决方案]
  - ...

【3. 慢查询分析】
CPU 热点 SQL Top 3：
  1. [指纹] 累计 CPU X 秒，N 次/天 → [优化建议]
内存热点 SQL Top 3：
  1. [指纹] 峰值内存 X MB，N 次/天 → [优化建议]

【综合建议】
优先级高：...
优先级中：...
=============================================

Execution Guide

Environment Preparation：

export HOLOGRES_SKILL=hologres-instance-health-analyse

Execute step by step, report the result after each step：

  1. hologres sql run --no-limit-check: Warehouse resource query (pg_stat_activity)
  2. hologres sql run --no-limit-check: Error classification statistics (hg_query_log WHERE status='FAILED')
  3. hologres sql run --no-limit-check: CPU slow query analysis (aggregated by digest)
  4. hologres sql run --no-limit-check: Memory slow query analysis (aggregated by digest)
  5. Based on the above results, output a structured diagnostic report

Error handling：

When the CLI returns a structured error, determine whether to retry based on the retryable field:

{"ok": false, "error": {"code": "QUERY_TIMEOUT", "message": "...", "retryable": true, "hint": "Query exceeded time limit. Simplify query or add filters."}}
  • retryable: true → wait 3 seconds and retry once
  • retryable: false → based on hint adjust parameters and retry

Common retryable errors:CONNECTION_ERROR、CONNECTION_TIMEOUT、QUERY_TIMEOUT、QUERY_ERROR

Reference documentation

Document Content
warehouse-metrics.md Warehouse resource monitoring SQL
error-analysis.md Error classification analysis + common error solutions
slow-query-analysis.md Complete set of slow query diagnostic SQL

Notes

  1. hologres.hg_query_log By default, data is retained for one month, and a single query returns at most 10000 rows
  2. When queryingyou must include query_start time range conditions, to avoid a full table scan
  3. query_start Do not nest expressions in conditions (avoid the to_char(query_start, ...) approach)
  4. Superuser or pg_read_all_stats permission is required to view logs of the entire instance
  5. digest The field is supported starting from V2.2; for lower-version instances this field is empty
  6. All commands return output in JSON format,data.rows is the result row array
  7. Use --no-limit-check to skip the LIMIT check (diagnostic aggregate queries do not need LIMIT)