Hologres Slow Query Analysis — Ho记录res Slow 查询 Analysis

Prerequisites

This 技能 requires ho记录res-命令行工具 to be 安装ed first:

pip 安装 ho记录res-命令行工具 导出 HO记录RES_技能=ho记录res-slow-查询-analysis

All SQL execution and GUC parameter operations depend on ho记录res-命令行工具 commands (ho记录res sql 运行, ho记录res guc 设置).

Ho记录res Slow 查询 Analysis

诊断 and analyze slow/fAIled queries in Alibaba Cloud Ho记录res using the ho记录res.hg_查询_记录 系统 table.

Version Requirements Ho记录res Version Feature V0.10+ Basic slow 查询 记录 V2.2+ SQL fingerprint (digest) V2.2.7+ Default threshold 100ms V3.0.2+ 聚合d records for <100ms queries Quick 启动

• 检查 权限s

-- Superuser: view all DB 记录s ALTER USER "cloud_account_id" SUPERUSER;

-- Or join pg_read_all_stats group GRANT pg_read_all_stats TO "cloud_account_id";

-- For current DB only (SPM 模型) CALL spm_grant('_admin', 'cloud_account_id');

• Basic 查询 Count

SELECT count(*) FROM ho记录res.hg_查询_记录;

• Recent Slow Queries (10 min)

SELECT 状态 AS "状态", duration AS "Duration(ms)", 查询_启动 AS "启动 Time", (read_bytes/1048576)::text || ' MB' AS "Read", (memory_bytes/1048576)::text || ' MB' AS "Memory", (cpu_time_ms/1000)::text || ' s' AS "CPU", 查询_id AS "查询ID", 查询::char(50) AS "查询" FROM ho记录res.hg_查询_记录 WHERE 查询_启动 >= now() - interval '10 min' ORDER BY duration DESC LIMIT 100;

Core Diagnostic 工作流s 工作流 1: Find Resource-Heavy Queries

Use when CPU/memory usage is high.

-- Top 10 CPU-consuming queries (past day) SELECT digest, avg(cpu_time_ms), sum(cpu_time_ms) FROM ho记录res.hg_查询_记录 WHERE 查询_启动 >= CURRENT_DATE - INTERVAL '1 day' AND digest IS NOT NULL AND usename != '系统' GROUP BY 1 ORDER BY 3 DESC LIMIT 10;

工作流 2: Find FAIled Queries SELECT 状态, message::char(100), duration, 查询_启动, 查询_id, 查询::char(80) FROM ho记录res.hg_查询_记录 WHERE 查询_启动 BETWEEN '2024-01-01 00:00:00'::timestamptz AND '2024-01-01 01:00:00'::timestamptz AND 状态 = 'FAILED' ORDER BY 查询_启动 ASC LIMIT 100;

工作流 3: 查询 Phase Analysis

Identify 机器人tleneck phase (optimization/启动up/execution).

SELECT 状态, duration AS "Total(ms)", optimization_cost AS "优化(ms)", 启动_查询_cost AS "启动up(ms)", 获取_next_cost AS "执行(ms)", duration - optimization_cost - 启动_查询_cost - 获取_next_cost AS "Other(ms)", 查询_id, 查询::char(50) FROM ho记录res.hg_查询_记录 WHERE 查询_启动 >= now() - interval '10 min' ORDER BY duration DESC LIMIT 100;

工作流 4: Compare with Yesterday SELECT 查询_date, count(1), sum(read_bytes), sum(cpu_time_ms) FROM ho记录res.hg_查询_记录 WHERE 查询_启动 >= now() - interval '3 h' GROUP BY 查询_date UNION ALL SELECT 查询_date, count(1), sum(read_bytes), sum(cpu_time_ms) FROM ho记录res.hg_查询_记录 WHERE 查询_启动 >= now() - interval '1d 3h' AND 查询_启动 <= now() - interval '1d' GROUP BY 查询_date;

Key Fields Reference Field Description 查询_id Unique 查询 identifier digest SQL fingerprint (MD5 哈希) duration Total 查询 time (ms) cpu_time_ms CPU time consumed memory_bytes Peak memory usage read_bytes Data read volume engine_type 查询 engine (HQE/PQE/SDK/PG) optimization_cost Plan generation time 启动_查询_cost 查询 启动up time 获取_next_cost Execution time Configuration -- 设置 slow 查询 threshold (DB level, superuser only) ALTER DATABASE dbname 设置 记录_min_duration_状态ment = '250ms';

-- 会话 level 设置 记录_min_duration_状态ment = '250ms';

-- 设置 记录 retention (V3.0.27+, 3-30 days) ALTER DATABASE dbname 设置 hg_查询_记录_retention_time_sec = 2592000;

Or use the 命令行工具 for database-level 设置tings:

ho记录res guc 设置 记录_min_duration_状态ment '250ms' ho记录res guc 设置 hg_查询_记录_retention_time_sec 2592000

References Document Content diagnostic-queries.md Complete diagnostic SQL collection 记录-导出.md 导出 记录s to internal/external tables configuration.md Configuration parameters Best Practices Always 过滤器 by 查询_启动 for better performance Use digest to group similar queries for pattern analysis 检查 engine_type - PQE queries may need optimization For 启动_查询_cost high: 检查 locks or resource contention For 获取_next_cost high: 优化 SQL or 添加 索引es Regular 清理up: 设置 应用ropriate retention period