WAL 日志来源分析（tencentdb_wal_stat）

CREATE EXTENSION IF NOT EXISTS tencentdb_wal_stat;

SELECT extname, extversion FROM pg_extension 
WHERE extname = 'tencentdb_wal_stat';

tencentdb_wal_stat(wal_num integer)
RETURNS SETOF record

-- 按数据库汇总 WAL 产生量（分析最近50个 WAL 文件）
SELECT database_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size,
       ROUND(SUM(wal_bytes + wal_fpi_bytes) * 100.0 / 
             SUM(SUM(wal_bytes + wal_fpi_bytes)) OVER (), 1) AS pct
FROM tencentdb_wal_stat(50)
GROUP BY database_name
ORDER BY SUM(wal_bytes + wal_fpi_bytes) DESC;

-- 按 schema 维度汇总
SELECT database_name, schema_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size
FROM tencentdb_wal_stat(50)
WHERE database_name = 'func_veri'  -- 替换为目标数据库名
  AND schema_name IS NOT NULL AND schema_name != ''
GROUP BY database_name, schema_name
ORDER BY SUM(wal_bytes + wal_fpi_bytes) DESC;

-- 查看产生 WAL 最多的表 TOP10
SELECT database_name, schema_name, relation_name, relation_kind,
       wal_records,
       pg_size_pretty(wal_bytes::bigint) AS wal_size,
       pg_size_pretty(wal_fpi_bytes::bigint) AS fpi_size,
       pg_size_pretty((wal_bytes + wal_fpi_bytes)::bigint) AS total_size
FROM tencentdb_wal_stat(50)
WHERE relation_kind IN ('table', 'index')
ORDER BY (wal_bytes + wal_fpi_bytes) DESC
LIMIT 10;

-- 按 RMGR 类型查看 WAL 分布
SELECT rmgr_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes)::bigint) AS wal_size,
       ROUND(SUM(wal_records) * 100.0 / SUM(SUM(wal_records)) OVER (), 1) AS pct
FROM tencentdb_wal_stat(50)
GROUP BY rmgr_name
ORDER BY SUM(wal_records) DESC;

-- FPI 占比分析
SELECT 
    pg_size_pretty(SUM(wal_bytes)::bigint) AS wal_data_size,
    pg_size_pretty(SUM(wal_fpi_bytes)::bigint) AS fpi_size,
    pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size,
    ROUND(SUM(wal_fpi_bytes) * 100.0 / 
          NULLIF(SUM(wal_bytes + wal_fpi_bytes), 0), 1) AS fpi_pct
FROM tencentdb_wal_stat(50);

步骤1: 查看整体 WAL 产生概况
    └── tencentdb_wal_stat(50) 按 database 汇总
         │
步骤2: 定位目标数据库
    └── 按 schema 汇总，找到异常 schema
         │
步骤3: 精确到表
    └── 按 relation 查看 TOP10表
         │
步骤4: 分析原因
    ├── Heap 类型多 → 大量 INSERT/UPDATE/DELETE
    ├── Btree 类型多 → 索引维护开销大
    └── FPI 占比高 → checkpoint 过于频繁
         │
步骤5: 采取措施
    ├── 限制异常租户写入速率
    ├── 暂停非核心批量任务
    └── 优化索引策略

-- 每小时记录各数据库 WAL 产生量（可写入监控表）
INSERT INTO wal_monitor_log (check_time, database_name, wal_records, wal_bytes)
SELECT now(), database_name,
       SUM(wal_records), SUM(wal_bytes + wal_fpi_bytes)
FROM tencentdb_wal_stat(10)
GROUP BY database_name;