Caddy Metrics 采集入库 SQLite 优化笔记

一、背景与目标

对 Caddy 开启 metrics 后，通过 Python 脚本每 5 分钟抓取一次 /metrics，解析后写入 SQLite 数据库，用于统计各节点的域名访问量、状态码分布等。

核心关注点：

1. 脚本到底写入了哪些字段
2. 数据库文件大小是否正常
3. 如何优化表结构以减少空间占用
4. 优化后相关 SQL 查询是否需要调整
5. 优化后数据库是否会变小

二、原始脚本写入的字段

2.1 数据库表结构

CREATE TABLE IF NOT EXISTS http_requests (
    timestamp     INTEGER,
    node          TEXT,
    host          TEXT,
    code          TEXT,
    count         INTEGER,
    delta_count   INTEGER DEFAULT 0,
    PRIMARY KEY (timestamp, node, host, code)
);

共 6 个字段，全部由脚本生成，并非原样存入 Caddy 的原始 metrics 文本。

2.2 字段来源

2.3 只抓取了一个指标

关键正则：

r'^caddy_http_requests_total\{[^}]*host="([^"]+)"[^}]*\}\s+(\d+(?:\.\d+)?)'

只匹配 caddy_http_requests_total 这一个指标。Caddy /metrics 中的其他指标全部被丢弃，不写入数据库，例如：

• caddy_http_request_duration_seconds_*（延迟）
• caddy_http_request_size_bytes_*（请求大小）
• caddy_http_response_size_bytes_*（响应大小）
• caddy_http_requests_in_flight（并发）
• caddy_http_response_duration_seconds_*
• Go runtime 相关指标（go_*、process_*）

2.4 未利用的标签

caddy_http_requests_total 通常带有多个 label，例如：

caddy_http_requests_total{host="a.com",code="200",method="GET",path="/api",server="srv0",...}

脚本只提取了 host 和 code，其他标签如 method、path、server、handler 都没有提取，不会入库。

2.5 写入方式

• 主键为 (timestamp, node, host, code)
• 使用 INSERT OR REPLACE，重复会覆盖
• 每次运行会删除 30 天前的记录（RETENTION_DAYS = 30）

2.6 小结

脚本只从 Caddy /metrics 中提取 caddy_http_requests_total 的 host、code、计数值，加上脚本生成的 timestamp、node 和计算出的 delta_count，共 6 个字段写入 SQLite，其余所有指标和标签全部丢弃。

三、数据库文件大小是否正常

3.1 初步估算

1800 行、220KB，约 125 字节/行。结合表结构，单行净数据约 50 至 80 字节，加上 SQLite 行头、B-tree 页内开销、索引页、WAL 文件等，实际落盘约 100 至 150 字节/行。

结论：1800 行 220KB 属于正常范围，甚至偏小，说明字段值较短、没有大量索引、页利用率较高。

3.2 需要注意的隐藏大小

SQLite 运行时会产生附属文件，统计总占用时要一起看：

检查命令：

ls -lh /data/jishu/vqq2.db*
du -h /data/jishu/vqq2.db*

3.3 何时算不正常

3.4 维护操作

查看真实占用（含空闲页）：

PRAGMA page_count;
PRAGMA page_size;
PRAGMA freelist_count;

page_count 乘 page_size 等于文件逻辑大小。

定期整理：

VACUUM;
PRAGMA wal_checkpoint(TRUNCATE);

查看表与索引占用：

SELECT name, SUM(pgsize) AS bytes
FROM dbstat
GROUP BY name;

需要 SQLite 编译时开启 dbstat 虚拟表。

四、dbstat 结果分析（改前）

4.1 原始结果

4.2 关键发现

索引占了接近一半，索引与数据比例约 85%，非常高。

4.3 原因

主键是四字段联合主键：

PRIMARY KEY (timestamp, node, host, code)

SQLite 会为这个主键建一个 B-tree 索引，索引里要完整保存这四个字段的值，所以索引体积接近表本身。

索引每条约 50 至 60 字节，与表里存的其他字段差不多大小。

4.4 优化建议（按收益排序）

1. 用 INTEGER 代替 TEXT：code 存 INTEGER
2. node 用整数 ID 代替字符串，配合维表
3. 缩短 host，如可行可存 host_id 或哈希
4. 重新考虑主键顺序，按最常用查询条件排
5. 定期 VACUUM
6. 使用 WITHOUT ROWID 表，适合纯主键表

4.5 改造成本对比

4.6 小结

164KB 索引接近表数据本身，是因为四字段联合主键里 node、host、code 都是文本且被完整复制一份。改成 WITHOUT ROWID 表可以直接省掉这 164KB；再把 code 转 INTEGER、node 转 ID，总体可压到 100KB 以内。

五、Python 脚本是否需要修改

5.1 只改 WITHOUT ROWID

脚本几乎不用改，只需在 CREATE TABLE 末尾加 WITHOUT ROWID。

def init_db(conn):
    with conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS http_requests (
                timestamp INTEGER,
                node TEXT,
                host TEXT,
                code TEXT,
                count INTEGER,
                delta_count INTEGER DEFAULT 0,
                PRIMARY KEY (timestamp, node, host, code)
            ) WITHOUT ROWID;
        """)

其他逻辑全不用动：

• INSERT OR REPLACE INTO ... VALUES (?,?,?,?,?,?) 不变
• SELECT ... WHERE node=? 不变
• DELETE ... WHERE timestamp<? 不变

注意：WITHOUT ROWID 表不能有 AUTOINCREMENT，不能用 rowid 列，脚本未用到，无影响。

5.2 code 改成 INTEGER

解析处改一行：

code = int(code_m.group(1)) if code_m else 200

建表改：

code INTEGER,

兼容写法：

try:
    code = int(code_m.group(1))
except (ValueError, AttributeError):
    code = 0

Caddy 的 code 标签一般是 "200"、"404"、"0" 等，都能 int() 成功。

5.3 node 改成整数 ID

需要引入维表，脚本要改 3 处。

配置里给每个节点加 id：

NODES = [
    {"id": 1, "name": "zcaddyjishu1", "url": "...", "user": "...", "pass": "..."},
    {"id": 2, "name": "zcaddyjishu2", "url": "...", "user": "...", "pass": "..."},
]

建维表加主表用 node_id：

def init_db(conn):
    with conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS nodes (
                id INTEGER PRIMARY KEY,
                name TEXT UNIQUE
            );
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS http_requests (
                timestamp INTEGER,
                node_id INTEGER,
                host TEXT,
                code INTEGER,
                count INTEGER,
                delta_count INTEGER DEFAULT 0,
                PRIMARY KEY (timestamp, node_id, host, code)
            ) WITHOUT ROWID;
        """)
        conn.executemany(
            "INSERT OR IGNORE INTO nodes(id, name) VALUES (?, ?);",
            [(n["id"], n["name"]) for n in NODES]
        )

读写处把 node 换成 node_id：

cursor.execute("""
    SELECT host, code, count FROM http_requests
    WHERE node_id = ? AND timestamp = (
        SELECT MAX(timestamp) FROM http_requests WHERE node_id = ?
    )
""", (node_id, node_id))

rows.append((now, node_id, host, code, current_count, delta))

query_db 要 JOIN 回名字：

cursor.execute("""
    SELECT datetime(r.timestamp,'unixepoch','localtime'),
           n.name, r.host, r.code, r.delta_count, r.count
    FROM http_requests r
    JOIN nodes n ON n.id = r.node_id
    ORDER BY r.timestamp DESC
    LIMIT 10;
""")

5.4 改动量总览

5.5 旧数据迁移

SQLite 不能直接改字段类型或加 WITHOUT ROWID，必须重建表：

BEGIN;
CREATE TABLE http_requests_new (
    timestamp INTEGER,
    node TEXT,
    host TEXT,
    code INTEGER,
    count INTEGER,
    delta_count INTEGER DEFAULT 0,
    PRIMARY KEY (timestamp, node, host, code)
) WITHOUT ROWID;

INSERT INTO http_requests_new
SELECT timestamp, node, host,
       CAST(code AS INTEGER),
       count, delta_count
FROM http_requests;

DROP TABLE http_requests;
ALTER TABLE http_requests_new RENAME TO http_requests;
COMMIT;
VACUUM;

5.6 小结

• 只加 WITHOUT ROWID：脚本只需在 CREATE TABLE 后加 4 个字，其他完全不动。
• code 改 INTEGER：解析处改 1 行，建表改字段类型。
• node 改 ID：要加维表、改查询、改 JOIN，约 10 行，收益最小。
• 建议先做前两项，节点 ID 化可以以后再说。

六、删表重建方案

由于数据刚建立、没有历史包袱，最干净的做法是：删库，改脚本，重新抓取。

6.1 删库

先停脚本，避免写入冲突：

# 1. 确认没有脚本在跑
ps aux | grep -i "import sqlite3\|vqq2" | grep -v grep

# 2. 删除主库加 WAL 加 SHM（三个一起删）
rm -f /data/jishu/vqq2.db /data/jishu/vqq2.db-wal /data/jishu/vqq2.db-shm

# 3. 确认
ls -lh /data/jishu/vqq2.db*

如果脚本是用 cron 或 systemd 定时跑的，先把定时任务停掉或注释，跑完新脚本再恢复。

6.2 改造后的完整脚本

已做三项优化：

• WITHOUT ROWID（省掉 164KB 索引）
• code 改 INTEGER
• node 保留 TEXT（节点少，收益小，暂不引入维表）

import sqlite3
import time
import re
import urllib.request
import ssl
import base64

NODES = [
    {
        "name": "zcaddyjishu1",
        "url": "https://zcaddyjishu1.zhaopeng.site/metrics",
        "user": "zadmin",
        "pass": "P4mG"
    },
    {
        "name": "zcaddyjishu2",
        "url": "https://zcaddyjishu2.zhaopeng.site/metrics",
        "user": "zadmin",
        "pass": "P4mG"
    }
]

DB_PATH = "/data/jishu/vqq2.db"
RETENTION_DAYS = 30


def init_db(conn):
    with conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS http_requests (
                timestamp   INTEGER,
                node        TEXT,
                host        TEXT,
                code        INTEGER,
                count       INTEGER,
                delta_count INTEGER DEFAULT 0,
                PRIMARY KEY (timestamp, node, host, code)
            ) WITHOUT ROWID;
        """)


def fetch_metrics(node_cfg):
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode = ssl.CERT_NONE

    req = urllib.request.Request(node_cfg["url"])
    auth_str = f"{node_cfg['user']}:{node_cfg['pass']}"
    b64_auth = base64.b64encode(auth_str.encode('utf-8')).decode('utf-8')
    req.add_header("Authorization", f"Basic {b64_auth}")

    with urllib.request.urlopen(req, context=ctx, timeout=10) as response:
        return response.read().decode('utf-8')


def parse_and_save(node_name, metrics_text, conn):
    now = int(time.time())
    rows = []

    cursor = conn.cursor()
    cursor.execute("""
        SELECT host, code, count
        FROM http_requests
        WHERE node = ? AND timestamp = (
            SELECT MAX(timestamp) FROM http_requests WHERE node = ?
        )
    """, (node_name, node_name))
    last_records = {(row[0], row[1]): row[2] for row in cursor.fetchall()}

    pattern = re.compile(
        r'^caddy_http_requests_total\{[^}]*host="([^"]+)"[^}]*\}\s+(\d+(?:\.\d+)?)',
        re.MULTILINE
    )

    for match in pattern.finditer(metrics_text):
        labels_str = match.group(0)
        host = match.group(1)
        current_count = int(float(match.group(2)))

        code_m = re.search(r'code="([^"]+)"', labels_str)
        try:
            code = int(code_m.group(1)) if code_m else 200
        except (ValueError, AttributeError):
            code = 0

        last_count = last_records.get((host, code))
        if last_count is None:
            delta = 0
        elif current_count >= last_count:
            delta = current_count - last_count
        else:
            delta = current_count

        rows.append((now, node_name, host, code, current_count, delta))

    if rows:
        with conn:
            conn.executemany("""
                INSERT OR REPLACE INTO http_requests
                (timestamp, node, host, code, count, delta_count)
                VALUES (?, ?, ?, ?, ?, ?);
            """, rows)

            expire_time = now - (RETENTION_DAYS * 86400)
            conn.execute("DELETE FROM http_requests WHERE timestamp < ?;", (expire_time,))

    return len(rows)


def query_db(conn):
    cursor = conn.cursor()
    print("-" * 80)
    print("多节点增量查询 (最近 10 条)")
    print("-" * 80)

    cursor.execute("""
        SELECT datetime(timestamp, 'unixepoch', 'localtime'),
               node, host, code, delta_count, count
        FROM http_requests
        ORDER BY timestamp DESC
        LIMIT 10;
    """)
    for row in cursor.fetchall():
        print(f"时间: {row[0]} | 节点: {row[1]} | 域名: {row[2]} | 状态: {row[3]} | 增量: +{row[4]} | 累计: {row[5]}")
    print("-" * 80)


def main():
    try:
        conn = sqlite3.connect(DB_PATH)
        init_db(conn)

        total_saved = 0
        for node in NODES:
            try:
                metrics_text = fetch_metrics(node)
                saved_count = parse_and_save(node["name"], metrics_text, conn)
                total_saved += saved_count
            except Exception as e:
                print(f"拉取节点 [{node['name']}] 失败: {e}")

        print(f"成功更新 {total_saved} 条数据项\n")
        query_db(conn)

    except Exception as e:
        print(f"数据库异常: {e}")
    finally:
        if 'conn' in locals():
            conn.close()


if __name__ == "__main__":
    main()

6.3 本次改动对比

注意：code 从 TEXT 改 INTEGER 后，查询或统计时不要再加引号。比如原来可能写 WHERE code='200'，现在要写 WHERE code=200。SQLite 有类型亲和性，一般也能匹配，但统一为整数更规范。

6.4 重新跑一次

# 1. 手动跑一遍，确认正常
python3 your_script.py

# 2. 确认表结构
sqlite3 /data/jishu/vqq2.db ".schema http_requests"

# 3. 确认数据
sqlite3 /data/jishu/vqq2.db "SELECT COUNT(*) FROM http_requests;"

# 4. 确认空间占用（对比之前 360KB）
sqlite3 /data/jishu/vqq2.db "SELECT name, SUM(pgsize) FROM dbstat GROUP BY name;"

6.5 首次运行提醒

1. 第一次跑 delta_count 全是 0，因为库里没有上一周期数据，正常。第二次跑才会出现增量。
2. code=0 的情况：Caddy 偶尔返回 code="0"，脚本会原样存为 0，不影响。
3. WAL 文件：跑完后如果发现 vqq2.db-wal 挺大，可手动 PRAGMA wal_checkpoint(TRUNCATE);。
4. 定时任务恢复：确认无误后再把 cron 或 systemd 定时恢复。

七、优化后 SQL 查询是否需要调整

7.1 必须改的 1 条

最近 1 小时异常响应监控，原写法：

WHERE timestamp >= strftime('%s', 'now', '-1 hour')
  AND code >= '400'

问题：code 已从 TEXT 改成 INTEGER，但这里拿字符串 '400' 去比较。SQLite 会隐式转成整数 400，多数情况下结果恰好正确，但依赖隐式转换不够规范。

改成：

WHERE timestamp >= strftime('%s', 'now', '-1 hour')
  AND code >= 400

code 是 INTEGER 后，GROUP BY code、ORDER BY code 排序也会变成数值排序（200 小于 404 小于 500），比原来的字符串排序更符合直觉。

7.2 其余 5 条确认

5.3.1 24 小时各节点或域名请求总数：正常。delta_count 是 INTEGER，SUM 正常。strftime 返回字符串，和 INTEGER 的 timestamp 比较时 SQLite 会做数值转换，能正确工作。

5.3.2 特定域名近 7 天趋势：正常。date 函数、GROUP BY 别名分组均无问题。

5.3.3 24 小时各节点或状态码分布：正常。code 变 INTEGER 后输出数字更干净。

流量分担比例：正常。* 100.0 保证浮点，|| '%' 拼字符串。唯一小风险是分母为 0 时返回 NULL，可加 NULLIF。

实时高频域名排行榜：正常。-15 minutes 语法正确。

7.3 改后的异常监控 SQL

SELECT 
    node AS "节点",
    host AS "域名",
    code AS "状态码",
    SUM(delta_count) AS "报错次数"
FROM http_requests
WHERE timestamp >= strftime('%s', 'now', '-1 hour')
  AND code >= 400
GROUP BY node, host, code
HAVING SUM(delta_count) > 0
ORDER BY "报错次数" DESC;

小改动：HAVING "报错次数" > 0 改成 HAVING SUM(delta_count) > 0，避免个别 SQLite 版本对 HAVING 里用别名的兼容问题。

7.4 额外建议

流量占比防除零：

ROUND(
  SUM(delta_count) * 100.0 / NULLIF(
    (SELECT SUM(delta_count) FROM http_requests 
     WHERE timestamp >= strftime('%s','now','-1 day')), 0
  ), 2) || '%' AS "流量占比"

异常监控想区分 4xx 或 5xx：

AND code >= 400
-- 或只盯服务端错误
AND code >= 500

7.5 小结

6 条查询里只有"最近 1 小时异常监控"里的 code >= '400' 要改成 code >= 400，因为字段已从 TEXT 变 INTEGER。其余 5 条完全正确，code 变 INTEGER 后排序和显示反而更规范。

八、改后数据库是否会变小

8.1 真实数据量估算

• 每 5 分钟抓一次，每小时 12 次
• 5 小时 60 次抓取
• 每次每节点每个 (host, code) 组合写 1 行

假设有 N 个 (host, code) 组合（如 3 个域名乘 4 个状态码等于 12 种），2 个节点：

总行数 约等于 60 次 乘 2 节点 乘 N 种组合

N 等于 12 时约 1440 行，和 1800 行接近。

8.2 为什么 1800 行会占 220KB

普通表（ROWID 表）：

索引几乎和表一样大，这是最大浪费。

改成 WITHOUT ROWID 后，数据直接按主键顺序存储，不再单独维护那 164KB 索引：

所以 220KB 降到约 190KB，省掉接近一半的索引开销，但表数据本身省不了太多。

8.3 为什么省得没想象中多

WITHOUT ROWID 只是去掉了重复的索引副本，行本身该有的字段还得存：

• timestamp 8 字节
• node "zcaddyjishu1" 12 字节
• host 15 至 30 字节
• code 3 至 8 字节
• count 8 字节
• delta_count 8 字节

单行仍约 60 至 80 字节净数据，加 B-tree 开销约 100 至 120 字节每行。1800 行乘 110 字节约 200KB，跑不掉。

8.4 真正省空间的大头

1. code 改 INTEGER：每行省约 2 字节，1800 行省约 3.6KB，很小。
2. 抓取频率与组合数：真正的大头。行数等于抓取次数乘节点数乘组合数。

降低抓取频率才是省空间最猛的手段。

8.5 长期稳态占用

30 天保留期下，稳态行数约：

1440 行 / 5 小时 乘 (24 乘 30 / 5) 约等于 1440 乘 144 约等于 20 万行

即 30 天后约 20 万行，按 110 字节每行约 22MB（WITHOUT ROWID 后约 20MB；不优化则约 40MB 以上）。

• 短期看：改后 5 小时 220KB 降到约 190KB，变化不大
• 长期看：30 天稳态从约 40MB 降到约 20MB，省一半

8.6 进一步压缩手段

8.7 小结

改 WITHOUT ROWID 后，5 小时的文件会从 220KB 降到约 190KB（省的是索引那 164KB 的一部分），短期变化不大；但 30 天稳态会从约 40MB 降到约 20MB，省一半。真正该关注的是每 5 分钟一次的抓取频率，它决定行数上限，降频比改表结构省得多。

九、改后 dbstat 结果分析

9.1 结果

这是完全正常的，而且说明改造成功。

9.2 为什么这么小

关键点：sqlite_autoindex_http_requests_1 这一行消失了。

对比：

• WITHOUT ROWID 生效，主键索引被合并进表里，不再单独占一份
• 少了 167KB 的索引，这是最大收益

表数据从 192KB 降到 12KB，是因为改前是历史累积（5 小时、多次抓取），改后刚重建、只跑了一两次，行数少。这是数据量差异，不全是结构优化的功劳。

9.3 16KB 对应多少行

SQLite 默认页大小 4096 字节，http_requests 占 3 页等于 12KB。按每行 60 至 80 字节净数据加 B-tree 开销约 100 字节每行估算：

12 KB 除以 约 100 字节每行 约等于 120 行

大概是一次抓取、两个节点、若干 (host, code) 组合的量级。

9.4 判断结构是否真的优化

不要只看一次运行的大小，要看同样行数下的对比。

SELECT name, SUM(pgsize) AS bytes FROM dbstat GROUP BY name;
SELECT COUNT(*) FROM http_requests;

判断标准：

• 如果同样行数下，总 bytes 比改前小 40% 以上，优化成功
• 如果 sqlite_autoindex_http_requests_1 始终不出现，WITHOUT ROWID 确认生效

9.5 验证 WITHOUT ROWID 是否生效

sqlite3 /data/jishu/vqq2.db ".schema http_requests"

应看到末尾有 WITHOUT ROWID：

CREATE TABLE http_requests (
    timestamp INTEGER,
    node TEXT,
    host TEXT,
    code INTEGER,
    count INTEGER,
    delta_count INTEGER DEFAULT 0,
    PRIMARY KEY (timestamp, node, host, code)
) WITHOUT ROWID;

验证没有 rowid：

SELECT rowid FROM http_requests LIMIT 1;
-- 应报错：no such column: rowid，说明确实是 WITHOUT ROWID

9.6 不要被刚建库很小误导

现在只有 16KB，是因为数据少。跑满 30 天后稳态：

• 每 5 分钟一次，每天 288 次乘 2 节点乘 N 组合
• 30 天约 20 万行
• WITHOUT ROWID 下约 20MB（改前结构约 40MB）

现在 16KB 等于数据少加结构优化，两者叠加。将来约 20MB 等于结构优化后的稳态，比改前省一半。

9.7 建议顺手做的两件事

确认 WAL 文件大小：

ls -lh /data/jishu/vqq2.db*

wal 可能比主库还大，跑完可 checkpoint：

PRAGMA wal_checkpoint(TRUNCATE);

记录基线，方便以后对比。跑一天后再看一次 dbstat，确认增长曲线合理。

9.8 小结

改后 16KB（表 12KB 加 schema 4KB）完全正常：sqlite_autoindex_http_requests_1 已消失，证明 WITHOUT ROWID 生效，索引那份空间省掉了。现在小是因为数据少，等 30 天稳态约 20MB，比改前的约 40MB 省一半。

十、整体结论汇总

1. 原脚本只写入 6 个字段，只提取 caddy_http_requests_total 一个指标，其他指标和标签全部丢弃。
2. 1800 行 220KB 正常，但主键索引占了 164KB，接近表数据本身。
3. 优化方案：WITHOUT ROWID 加 code 转 INTEGER，node 保留 TEXT。
4. 脚本改动很小：建表加 WITHOUT ROWID，code 解析加 int 转换，共约 3 处。
5. 数据刚建立，直接删库重建最干净，无需迁移。
6. 优化后 6 条查询里只有异常监控那条要把 code >= '400' 改成 code >= 400。
7. 短期文件从 220KB 降到约 190KB，长期 30 天稳态从约 40MB 降到约 20MB。
8. 真正省空间的大头是降低抓取频率，而不是改表结构。
9. 改后 dbstat 显示主键索引消失，证明 WITHOUT ROWID 生效，结构优化成功。
10. 建议后续加定期 checkpoint 与 VACUUM 维护逻辑，并记录空间基线。