pg-perf-insight

# pg-perf-insight：PostgreSQL 性能诊断报告生成器 给定实例连接信息、快照 schema、分析时间段，从历史快照中定位最佳快照对，计算差值指标，产出一份 AWS Performance Insights 风格的中文深度性能报告（AAS、Top SQL、等待事件推断、按库/用户拆解、根因分析、优化优先级）。 本技能**依赖**已通过 `pg-stat-snapshot` 技能（或同构的快照采集机制）持续采集的历史快照数据，自身不采集快照、不修改任何数据。 ## 前置要求 - 已安装 `psql` 客户端（`which psql` 确认）。 - 目标实例已存在快照 schema（默认 `stat_snapshot`），且包含至少两条覆盖分析时间段的快照记录。 - 连接凭据只能通过环境变量 `PGPASSWORD` 传递，**禁止**在命令行参数、日志或报告中明文出现密码。 - 首次执行前用 `\d {schema}.*` 确认实际表结构；下文 SQL 假设的表名/字段名如与实际不符，需据实调整（见"表结构假设"一节）。 ## 输入参数（向用户确认或从对话中提取） | 参数 | 说明 | 默认值 | |---|---|---| | host / port / user / dbname | 连接信息 | 无，必需 | | PGPASSWORD | 密码，仅通过环境变量传入 | 无，必需 | | schema | 快照所在 schema | `stat_snapshot` | | start_time / end_time | 用户指定的分析时间段 | 无，必需 | | vcpu 数量 | 用于计算 CPU 利用率，若未提供则从 `pg_settings_snapshot` 中查 `guc` 或提示用户提供 | 自动探测/询问 | ## 表结构假设（与 pg-stat-snapshot 对齐） - `{schema}.snapshots(snapshot_id, snapshot_time, source_reset_time)` - `{schema}.pg_stat_statements_snapshot(snapshot_id, dbid, userid, queryid, query, calls, total_exec_time, rows, shared_blks_hit, shared_blks_read, blk_read_time, blk_write_time, wal_bytes)` - `{schema}.pg_stat_activity_snapshot(snapshot_id, pid, usename, application_name, datname, state, wait_event_type, wait_event, query_start)` - `{schema}.pg_stat_database_snapshot(snapshot_id, datid, datname, xact_commit, xact_rollback, blks_read, blks_hit)` - `{schema}.pg_settings_snapshot(snapshot_id, name, setting)` 若实际列缺失（如 `wal_bytes` 在 PG13 以下不存在，`blk_read_time` 依赖 `track_io_timing=on`），对应分析项在报告中标注「当前版本/配置不支持该指标，跳过」，不要臆造数值。 ## 工作流程 ### Step 1：只读会话建立 所有查询必须在只读事务中执行，使用 `scripts/run_analysis.sh` 统一封装： ```bash PGPASSWORD="$PW" psql -h "$HOST" -p "$PORT" -U "$USER" -d "$DBNAME" -v ON_ERROR_STOP=1 <<'SQL' SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; -- 具体查询 SQL ``` 不使用超级用户专属操作，不执行任何 INSERT/UPDATE/DELETE/DDL。 ### Step 2：定位最佳快照对 执行 `scripts/01_find_snapshot_pair.sql`（需替换 `{schema}`、`{start_time}`、`{end_time}`），得到： - `snap_begin`：`snapshot_time <= start_time` 中最大的一条 - `snap_end`：`snapshot_time >= end_time` 中最小的一条 若查不到满足条件的快照对，按此格式告知用户并终止： > 指定时间段内没有足够的快照数据。快照范围是 [最早快照时间] 到 [最晚快照时间]，请调整分析时间段。 找到后，向用户报告实际使用的窗口 `[snap_begin.snapshot_time, snap_end.snapshot_time]`。 ### Step 3：快照有效性验证 执行 `scripts/02_validate_reset.sql`，比对两条快照的 `source_reset_time`： - **不一致** → 说明窗口内发生过 `pg_stat_statements_reset()` 或实例重启，累计计数器差值无效。用如下格式提示，并终止分析，不得继续计算差值： > 快照 #X（时间 A）和快照 #Y（时间 B）的统计计数器重置时间不同（分别为 T1 和 T2），说明在分析窗口内发生过统计重置，累计计数器差值无效。 再执行该脚本中的第二段查询，定位 reset 发生的精确时间点，给出可用的备选时间段建议。 - **一致** → 继续 Step 4。 ### Step 4：窗口长度检查 计算 `window_seconds = snap_end.snapshot_time - snap_begin.snapshot_time`： - `< 60s`：提示"分析窗口过短，统计样本可能不足，建议扩大时间范围"。 - `> 24h`：提示"分析窗口较长，汇总数据可能掩盖瞬时尖峰，建议缩短至 1-6 小时"。 - 两种情况均**继续**分析，只是在报告开头附加提示，不中断流程。 ### Step 5：核心差值计算 依次执行 `scripts/03_aas_overview.sql`、`scripts/04_top_sql_multi_dim.sql`、`scripts/05_wait_event_inference.sql`、`scripts/06_by_database.sql`、`scripts/07_by_user_app.sql`，全部基于 `snap_begin.snapshot_id` 与 `snap_end.snapshot_id` 在 SQL 内部做差值（`snap_end.x - snap_begin.x`），**不要**把两份原始快照拉到外部再用程序语言相减。 关键指标口径： - `AAS = SUM(delta_total_exec_time) / window_seconds / 1000` - CPU 占比：`delta_total_exec_time` 占比作为 CPU 消耗近似 - 缓存命中率：`delta_shared_blks_hit / NULLIF(delta_shared_blks_hit + delta_shared_blks_read, 0)` - 每个维度（总耗时/调用频率/平均延迟/IO/WAL/行数）各取 TOP 10 ### Step 6：瓶颈根因诊断（推理，非 SQL） 对每个维度 TOP 3 的 SQL，结合以下模式判断根因（参考 `references/root_cause_patterns.md`）： - WHERE 有过滤条件但 `shared_blks_read` 很高 → 疑似索引缺失 - WHERE 条件含函数包裹字段/隐式类型转换 → 疑似索引失效 - `rows` 很大但 `calls` 不高 → 分析型大查询 - `calls` 很高但单次延迟很低 → 高频小查询（ORM N+1 / 缓存未命中） - 高频 UPDATE/DELETE + `pg_stat_activity_snapshot` 中 `wait_event_type='Lock'` 频次高 → 锁竞争 资源瓶颈判断： - CPU 瓶颈：AAS 接近或超过 vCPU 数，且 Top SQL 以低 IO/低 WAL 为主 - IO 瓶颈：`shared_blks_read` 占比高、缓存命中率低 - 内存瓶颈：缓存命中率持续偏低 - 连接数瓶颈：`pg_stat_activity_snapshot` 中活跃连接数接近 `max_connections` - WAL 瓶颈：`delta_wal_bytes` 异常高 异常检测：执行 `scripts/08_anomaly_detection.sql`，检测相邻快照间 AAS 突增、同一 `queryid` 平均延迟翻倍、`snap_end` 中新增的 `queryid`。 ### Step 7：生成报告 使用 `references/report_template.md` 的结构生成完整中文报告，章节顺序：性能总览仪表盘 → 按维度 Top SQL（4 张表）→ 等待事件分布 → 按数据库拆解 → 按用户/应用拆解 → 关键发现与根因分析 → 负载趋势与异常检测 → 优化优先级 Top 5 → 后续监控建议。 ## 执行约束 - 所有分析仅对 `{schema}` 下的历史快照表做只读查询，不访问生产表，不执行任何写操作。 - 差值计算必须在 SQL 内完成。 - PG 版本不支持的指标（如 `wal_bytes`）在报告中明确标注"跳过"，禁止编造数值。 - 若 `pg_stat_activity_snapshot` 只采集了非空闲连接，报告中需注明"活跃连接数可能偏低，仅统计非空闲连接"。 - SQL 文本截取前 200 字符展示，但完整 `queryid` 必须保留供用户自行查询。 - 报告使用中文输出。 - 密码只通过 `PGPASSWORD` 环境变量传递，禁止回显、禁止写入报告或日志文件。 ## Pitfalls & Solutions | 坑点 | 解决方案 | |---|---| | 快照对 reset_time 不一致却硬算差值 | Step 3 必须先验证，不一致立即终止并给出替代窗口建议 | | `wal_bytes` 在 PG13 以下不存在导致报错 | 先查 `server_version_num`，`< 130000` 时跳过该维度并标注 | | `blk_read_time` 全为 0 | 检查 `track_io_timing`，关闭时说明该指标不可用，改用 `shared_blks_read` 做 IO 压力近似 | | 快照窗口内数据库重启，`pg_stat_statements` 计数器清零但被误判为"低负载" | 依赖 Step 3 的 reset 校验而非单纯看数值大小 | | 多库实例遗漏跨库聚合 | Step 5 的 by_database 脚本按 `datname` 分组，不要只看当前连接的库 | | 密码明文出现在 shell 历史或报告中 | 一律通过 `PGPASSWORD` 环境变量注入，禁止 `-W` 交互式外的任何明文传递方式 | ## 注意事项 - 本技能不采集快照、不做任何 DDL/DML，纯只读分析。 - 若目标库为多机（主备/只读实例），确认连接的是采集快照的那台实例，避免跨实例对比。 - 报告中的"建议"仅供参考，涉及生产变更（建索引、改写 SQL）前需在业务低峰期验证并保留回滚方案（如 `DROP INDEX CONCURRENTLY` 撤销新建索引）。 - 分析窗口跨越大促/业务高峰与平峰时，聚合指标会被平均掉，应提示用户按更细粒度窗口复查。