4.7 配套代码：数据库、连接池与缓存观测

-- tools/pg-diagnostics.sql

-- ═══════════════════════════════════════════════════════════
-- ① 最贵的语句（同时看 calls 和 total，防止漏掉 N+1）
-- ═══════════════════════════════════════════════════════════
SELECT
    calls,
    round(total_exec_time::numeric, 1)  AS total_ms,
    round(mean_exec_time::numeric, 2)   AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
    rows,
    left(regexp_replace(query, '\s+', ' ', 'g'), 70) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

-- ⭐ 读法：
--   高 calls + 低 mean  → N+1（总量大）
--   低 calls + 高 mean  → 单条慢查询（缺索引）
--   两者都高           → 最优先处理

-- ═══════════════════════════════════════════════════════════
-- ② 现在谁在等什么（wait_event_type 是最有价值的一列）
-- ═══════════════════════════════════════════════════════════
SELECT
    pid,
    round(extract(epoch from (now() - query_start))::numeric, 1) AS duration_s,
    state,
    wait_event_type,          -- Lock / IO / Client / LWLock / Timeout
    wait_event,
    left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY duration_s DESC NULLS LAST
LIMIT 20;

-- ⭐ 读法：
--   wait_event_type=Lock    → 等锁（查下面的阻塞链）
--   wait_event_type=IO      → 等磁盘（查索引与缓存命中）
--   wait_event_type=Client  → 等客户端读取（应用层问题）
--   无等待 + state=active   → 在算（查询本身消耗 CPU）
--   duration_s 很大          → 长事务（可能是连接池被占住的原因）

-- ═══════════════════════════════════════════════════════════
-- ③ 阻塞链（谁在阻塞谁）
-- ═══════════════════════════════════════════════════════════
SELECT
    blocked.pid AS blocked_pid,
    round(extract(epoch from (now() - blocked.query_start))::numeric, 1) AS blocked_s,
    left(regexp_replace(blocked.query, '\s+', ' ', 'g'), 50) AS blocked_query,
    blocking.pid AS blocking_pid,
    left(regexp_replace(blocking.query, '\s+', ' ', 'g'), 50) AS blocking_query,
    blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- ⭐ 典型发现：一个"忘记提交"的事务持有行锁几十秒，
--    导致后续所有同行的更新排队。

-- ═══════════════════════════════════════════════════════════
-- ④ 连接使用情况（对比应用侧的连接池指标）
-- ═══════════════════════════════════════════════════════════
SELECT
    COALESCE(application_name, 'unknown') AS app,
    state,
    count(*) AS connections,
    round(max(extract(epoch from (now() - state_change)))::numeric, 1) AS max_state_s
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY app, state
ORDER BY connections DESC;

-- 与 max_connections 对比
SELECT
    (SELECT count(*) FROM pg_stat_activity) AS used,
    current_setting('max_connections')::int AS max_conn,
    round(100.0 * (SELECT count(*) FROM pg_stat_activity)
          / current_setting('max_connections')::int, 1) AS used_pct;

#!/usr/bin/env bash
# tools/diagnose-pool.sh <APP_METRICS_URL> [PG_CONN_STRING]
#
# 自动判断"是等连接慢还是查询慢"，并给出建议方向。
set -uo pipefail

METRICS_URL="${1:-http://127.0.0.1:8080/metrics}"

echo "═══ 连接池诊断 ═══"
echo

M=$(curl -s --max-time 5 "$METRICS_URL" 2>/dev/null)
[ -z "$M" ] && { echo "❌ 无法获取指标（$METRICS_URL）"; exit 1; }

ACTIVE=$(echo "$M"  | awk '/^db_pool_active/{printf "%.0f", $2; exit}')
IDLE=$(echo "$M"    | awk '/^db_pool_idle/{printf "%.0f", $2; exit}')
PENDING=$(echo "$M" | awk '/^db_pool_pending/{printf "%.0f", $2; exit}')
TOTAL=$(echo "$M"   | awk '/^db_pool_total/{printf "%.0f", $2; exit}')

echo "连接池状态："
echo "  active  = ${ACTIVE:-?}"
echo "  idle    = ${IDLE:-?}"
echo "  pending = ${PENDING:-?}   ← 关键指标"
echo "  total   = ${TOTAL:-?}"
echo

if [ "${PENDING:-0}" -gt 0 ]; then
  echo "结论：❌ 存在排队（pending > 0）"
  echo
  echo "下一步（按顺序）："
  echo "  ① 查慢查询（不要先加池！）："
  echo "     psql -f tools/pg-diagnostics.sql   # 看第 ① 与 ② 节"
  echo "  ② 查长事务（可能是连接被占住的原因）："
  echo "     SELECT pid, now()-xact_start AS xact_age, state, left(query,50)"
  echo "     FROM pg_stat_activity WHERE xact_start IS NOT NULL"
  echo "     ORDER BY xact_age DESC LIMIT 10;"
  echo "  ③ 只有在【查询正常】且【并发确实高】时，才用 Little's Law 计算新池大小："
  echo "     所需连接 = 目标 QPS × 单次查询 P99"
else
  echo "结论：✅ 没有排队"
  echo
  if [ "${ACTIVE:-0}" -ge "${TOTAL:-1}" ] && [ "${TOTAL:-0}" -gt 0 ]; then
    echo "⚠️  但 active == total（池已满、但没有排队）"
    echo "   说明池容量刚好够用，没有余量应对突发。建议观察一段时间。"
  else
    echo "   延迟问题不在连接池，转向："
    echo "   - 依赖级指标（dep.postgres 的 P99）"
    echo "   - 慢查询统计（pg_stat_statements）"
    echo "   - 网络与下游"
  fi
fi

echo
echo "参考：pool 总连接数应 ≈ 目标QPS × 平均查询耗时（Little's Law）"

#!/usr/bin/env bash
# tools/redis-diagnostics.sh [REDIS_CLI_ARGS]
set -uo pipefail

R="${REDIS_CLI_ARGS:--h 127.0.0.1}"

echo "═══ Redis 诊断 ═══"
echo

echo "① 基本信息"
redis-cli $R INFO server | grep -E "redis_version|uptime_in_days"
redis-cli $R INFO clients | grep -E "connected_clients|blocked_clients"
redis-cli $R INFO memory | grep -E "used_memory_human|maxmemory_human|mem_fragmentation_ratio"
echo

echo "② 命中率（低命中率说明缓存设计有问题）"
redis-cli $R INFO stats | grep -E "keyspace_hits|keyspace_misses" | awk -F: '
  /hits/{h=$2} /misses/{m=$2}
  END{ if (h+m>0) printf "  命中率: %.1f%% (hit=%d, miss=%d)\n", 100*h/(h+m), h, m }'
echo

echo "③ 慢查询日志（最近 10 条）"
redis-cli $R SLOWLOG GET 10
echo

echo "④ 延迟事件"
redis-cli $R LATENCY LATEST 2>/dev/null || echo "  （无延迟事件或无权限）"
echo

echo "⑤ 大 key 扫描（耗时操作，生产慎用）"
echo "  提示：redis-cli --bigkeys 会遍历所有 key，建议在从库或低峰期执行"
echo

echo "⑥ 命令统计 Top 10（按累计耗时）"
redis-cli $R INFO commandstats | sed 's/^cmdstat_//' | sort -t= -k4 -rn | head -10
echo

echo "判读要点："
echo "  used_memory 接近 maxmemory → 频繁淘汰，命中率下降"
echo "  mem_fragmentation_ratio > 1.5 → 内存碎片严重"
echo "  blocked_clients > 0 → 有客户端在阻塞命令（如 BLPOP）"
echo "  慢查询里有 KEYS/HGETALL 大集合 → 典型的 O(N) 命令误用"

## 现象
订单查询接口 P99 从 80ms 涨到 400ms，CPU 45%，QPS 未变。

## 排查过程

### ① 依赖级指标
dep.postgres 的 P99 从 20ms 涨到 350ms  ← 问题在数据库方向
dep.redis 的 P99 正常（2ms）

### ② 连接池（关键分叉）
db_pool_active  = 10
db_pool_idle    = 0
db_pool_pending = 18      ← 有排队

### ③ 但先不急着加池——查为什么连接被占住
psql -f tools/pg-diagnostics.sql

第 ② 节（pg_stat_activity）显示：
  10 个 backend 全部 state=active, wait_event_type=NULL（在算），已运行 3 秒
  → 不是等锁、不是等 IO，是【查询本身慢】

第 ① 节（pg_stat_statements）显示：
  某条查询 calls=50000, mean_ms=3400, total_ms=170000000
  → 单条查询慢

### ④ EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status='PAID' ORDER BY created_at DESC LIMIT 20;
→ Seq Scan，Rows Removed by Filter: 900000
→ 行数估计 rows=20 但 actual rows=900000（统计信息过期）

### ⑤ 结论
缺索引（status）+ 统计信息过期。
连接池 pending=18 是【结果】而不是【原因】——连接被慢查询占住了。

### ⑥ 修复
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders(status, created_at DESC);
ANALYZE orders;

### ⑦ 验证
组件基准复测：P99 从 3400ms 降到 12ms
连接池 pending 回到 0
全链路 P99 从 400ms 降到 85ms