45 lines
2.2 KiB
SQL
45 lines
2.2 KiB
SQL
-- 节点健康 — per-minute rollup of satellite host vitals.
|
|
--
|
|
-- Granularity is the whole design. Satellites heartbeat every 10s; writing a row
|
|
-- per heartbeat would be ~60k rows/day for 7 nodes and grow forever. Rolling up
|
|
-- to one row per node per minute makes it ~10k/day, and the retention sweep
|
|
-- (node_health.ts::pruneNodeMetrics, 30 days) holds the table at a CONSTANT
|
|
-- ~300k rows. A table that doesn't grow is one nobody has to deal with later —
|
|
-- that is worth more here than the resolution we give up.
|
|
--
|
|
-- avg AND max because they answer different questions: avg is what a trend line
|
|
-- plots, max is what a threshold was crossed by. A disk filling up over ten
|
|
-- seconds is a spike a one-minute average swallows whole.
|
|
--
|
|
-- The CURRENT value of each metric is deliberately NOT here. It lives in control
|
|
-- plane memory (same call as nodes.pendingDeploy): the node owns that truth and
|
|
-- re-asserts it every ~10s, so a restart re-learns it within one heartbeat, and
|
|
-- a last-known CPU reading is worthless after a restart anyway.
|
|
|
|
CREATE TABLE IF NOT EXISTS "node_metrics" (
|
|
"node_id" text NOT NULL,
|
|
-- Start of the minute bucket, epoch ms.
|
|
"at" bigint NOT NULL,
|
|
"cpu_avg" double precision,
|
|
"cpu_max" double precision,
|
|
"mem_avg" double precision,
|
|
"mem_max" double precision,
|
|
"disk_avg" double precision,
|
|
"disk_max" double precision,
|
|
"load_max" double precision,
|
|
"swap_max_bytes" bigint,
|
|
-- Samples folded into this bucket; a value below the expected count is how a
|
|
-- reader spots a gap rather than misreading it as a flat line.
|
|
"samples" bigint DEFAULT 0 NOT NULL,
|
|
CONSTRAINT "node_metrics_node_id_at_pk" PRIMARY KEY("node_id","at")
|
|
);
|
|
|
|
-- Every metric column is nullable on purpose: a probe that failed must record
|
|
-- "unknown", never 0. A zeroed disk reading would mean "plenty of room" on
|
|
-- precisely the node we can no longer measure, and the alarm would stay silent.
|
|
|
|
-- The primary key already indexes (node_id, at), which is the panel's range
|
|
-- scan. This second index serves the retention sweep, which deletes by age
|
|
-- across all nodes and would otherwise seq-scan the whole table every hour.
|
|
CREATE INDEX IF NOT EXISTS "node_metrics_at_idx" ON "node_metrics" ("at");
|