How to Design Metrics That Answer Questions

The previous two posts were about the collector — a Kotlin engine and its Rust successor, both of which exist to move numbers around. This one is about the numbers themselves: the ninety-one task scripts that decide what gets measured, why a DBA would care, and what it costs to ask the database.

What this post is about. It is about the shapes a metric can have — a counter, a gauge, a health check — and the scripts that produce them: which question each family answers, what a sample looks like on its way to the database, and where my own scripts go wrong.

What you need to know first. A metric is one number about one system at one moment, with a name a human can look up later — mysql_qps means “how many queries per second is this MySQL server answering”. A DBA is a database administrator, the person whose phone rings when a database misbehaves. Riemann is the time-series database the metrics go to: it keeps each measurement as a timestamped event so you can graph it later.

Because the scripts are real, this post contains real bugs — some of them mine.

The situation

A monitoring collector is usually specified as a list of metrics. That is the wrong starting point. A metric is only worth collecting if it answers a question somebody actually asks, and those questions cluster into a small number of shapes:

The question The metric shape Examples
Is it there at all? health check mysql_up, oracle_dg_up, mssql_database_status
What is this thing? identity oracle_info, mysql_info, host_info
Am I about to run out? capacity oracle_tablespace_bytes, mysql_capacity_bytes
Am I about to be overloaded? saturation mysql_connections_used, mssql_block_count
Who is hurting whom? contention oracle_long_lock, mysql_lock_trans, mssql_un_commit_sql
How far behind am I? replication oracle_dg_delay_seconds, mssql_always_on_delay
What is the system doing? throughput mysql_qps, mysql_tps, oracle_default_system_metrics
Where did the time go? diagnosis oracle_awr_report, oracle_sql_monitor_report, oracle_alert_log

Almost every design decision falls out of this grouping. Health checks must be cheap and must never raise a false alarm. Capacity metrics are expensive (they stat every file) and are scheduled less often. Diagnosis metrics are so expensive that they run on demand, not on a timer.

Why each family exists

Health: the cheapest possible question

The liveness probe is the simplest thing in the system and the easiest to get wrong. The shared helper commits metric = 0 on success and a mapped error code on failure:

1
2
3
4
5
6
7
function up_schema_task(schema, stat_callback)
    local err = handler_error();
    if err.code > 0 then
        request:commit({ metric = err.code, message = err.msg });
        return;
    end
    request:commit({ metric = 0, message = "" });

scripts/lua/common.lua:185. The function checks for a stored error first: if there is one, it commits the code and stops. Otherwise it runs the probe — the actual check, hidden in the middle of the original — and commits metric = 0. The error codes are classes, not measurements: 1 = SQL error, 2 = host unreachable, 3 = database unreachable, 5 = auth failed, 8 = timeout. So a health metric is 0 for healthy and a small integer for how it broke.

The Oracle and PostgreSQL variants do not even run a SELECT — they write: UPDATE BP_CHECK_UPDATE SET CHECK_TIME=SYSDATE (SYSDATE is Oracle’s current-time function). A heartbeat row proves not just that the connection works but that the database accepts a write. The cost: the PostgreSQL variant fails if the table does not exist, manufacturing an alarm on a healthy server.

Capacity: “will I run out of disk?”

Capacity metrics answer a question that matters once a week and has consequences that matter once a month. They are also the most expensive kind: “how much disk is used” means walking a directory tree.

The MySQL version takes the size of the data directory from the OS rather than from the database — the right instinct, since the database knows how big its tables are, not how much room is left on the filesystem. Inside a match_schema("agent", ...) block it calls agent:diskus(dir, 10), which reports the directory size in KiB (units of 1024 bytes), then stores row["size"] * 1024 to get bytes.

The Oracle tablespace version computes a real free-space figure and a headroom figure — but publishes only the headroom: rs["free_bytes"] = max_bytes - used_bytes is headroom to MAXSIZE, not free space, alongside rs["all_bytes"]. “Free space inside the datafile” and “room before the datafile hits its MAXSIZE” are different questions, and the metric publishes the second answer under the first one’s name.

Contention: who is blocking whom

Lock waits are the metric a DBA most wants during an incident, and the hardest to extract well. The Oracle version joins holder and waiter by matching type/id1/id2 — the three columns that identify a lock resource. It has to: “there is a lock wait” is not an answer; “session 4711 is blocking session 4822 for 340 seconds on object 90123” is. The script commits the container name, the blocker and holder session counts, and the blocker age fields. Beneath, the V$LOCK self-join pairs every holder row with every waiter row and filters by resource identity — the classic way to find the blocking pair, and why it costs so much.

The long-transaction metric is the sibling: not “who is blocked” but “who is holding undo” — the before-image data Oracle keeps so other sessions can read a consistent picture while a transaction runs. The expensive part is the outer join onto V$SQL to attach the offending statement, written S.SQL_ID_new = Q.SQL_ID(+); the query carries a hint because that join must not be re-optimised, and filters with T.START_DATE < SYSDATE - 10.

The MSSQL equivalent asks a different question — “who is idle in a transaction“, the session that will block everyone later. It keeps sessions with a transaction (a row in dm_tran_session_transactions) but no current request (none in dm_exec_requests).

Replication: the metric that looks broken but is not

Replication lag — how far behind a standby copy is — is the most instructive metric in the set, because the obvious implementation is wrong in a way that only shows up under load. In Oracle Data Guard the primary takes the writes; the standby keeps a copy by replaying the redo log, the change records the primary ships to it.

The obvious implementation is a difference of two numbers: the last log the standby has received minus the last it has applied. The collector ships exactly those two numbers, split by process type:

1
2
3
SELECT THREAD#,MAX(CASE WHEN PROCESS LIKE 'MR%' THEN SEQUENCE# ELSE 0 END) RECOVER_SEQ,
       MAX(CASE WHEN PROCESS LIKE 'MR%' THEN 0 ELSE SEQUENCE# END) LOG_SEQ
FROM GV$MANAGED_STANDBY WHERE THREAD# > 0 GROUP BY THREAD#;

scripts/perl/BP_CK_DGLogSeq.pl:50. CASE WHEN is SQL’s per-row if, wrapped in MAX to take the highest sequence number per thread. MR% is media recovery — the apply process, the MRP. Everything else is the receiver.

Why is the gap a bad metric? Standby apply is serial and redo-consistent. A long transaction on the primary holds a row lock, so the standby’s MRP must apply that same transaction and blocks on the same lock. MRP stays pinned at one sequence number while the receiver keeps shipping newer logs, and the gap grows — and yet nothing is wrong: transport, standby and primary are healthy. The standby is waiting for a transaction that cannot commit.

A missing archive log produces the same symptom: MRP waits for a log that will never arrive. So the gap conflates “not applied yet” (lock wait, slow apply) with “not received yet” (network, archive). And it counts logs, not seconds, so turning it into a duration needs the log switch interval — which swings with workload.

That is why the collector also ships the authoritative version, in seconds, from V$DATAGUARD_STATS:

1
2
3
4
5
SELECT 'DGLAGDATA'||D.DB_UNIQUE_NAME|| '|' || G.NAME
       || '|' || NVL( (TO_NUMBER(SUBSTR(G.VALUE, 2, instr(G.VALUE, ' ')-1)) * 86400 + ... )
       , NVL((CASE WHEN G.NAME = 'transport lag' THEN M.MAX_ARCHIVED ELSE M.MAX_APPLIED END), 0)
       )
       || '|' || TRUNC(SYSDATE - TO_DATE(DATUM_TIME,'MM/DD/YYYY hh24-mi-ss'))*24*60*60

scripts/perl/BP_CK_DGLag.pl:84. NVL is Oracle’s “use this fallback if null”; the 86400 and the 24*60*60 both convert days to seconds. The third field is the age of the statistic itself — a staleness flag, because V$DATAGUARD_STATS goes stale if the DMON process dies, and a stale zero is worse than no number.

The two are kept separate on purpose, in the Lua layer: the parser matches DGLAG_INFO: lines, splits them on |, takes the larger parsed value with math.max, and stores transport lag and apply lag under separate names. Different failure modes, different remedies; they must never be collapsed into one number.

MySQL adds a third layer of the same lesson: Seconds_Behind_Master reads 0 while the SQL thread is stuck on a long transaction, and is NULL until the first event replicates. So it ships alongside a health check, mysql_slave_clone_interrupt, that reports whether the replication threads are actually running. The lag metric alone would have said “perfectly caught up” while the replica was minutes behind.

Throughput: the only two metrics that are actually rates

Almost every number a database hands you is a monotonically increasing counter — a total that only counts up from the last statistics reset and never comes down. V$SYSSTAT, pg_stat_database, SHOW GLOBAL STATUS and the base counters in dm_os_performance_counters all work that way. Plot one raw and you get a ramp that looks like “traffic is growing”, on a completely idle server.

Only two metrics in the whole set are differenced, and they do it themselves:

1
2
3
4
5
6
7
8
local prev_qps_time = get_cache("qps_time");
local prev_qps_queries = get_cache("qps_queries");
...
if prev_qps_time ~= nil and prev_qps_queries ~= nil then
    request:commit({
        metric = (qps_queries - prev_qps_queries) / ((qps_time - prev_qps_time) / 1000.00)
    });
end

scripts/task/mysql_qps.lua:6. The previous sample comes from a cache — a store persisted to disk, scoped by instance.id .. "_" .. instance.schema .. "_" .. key. If it exists, the script commits the counter difference divided by elapsed seconds; 1000.00 converts milliseconds to seconds. The rest of the original stores the current values back into the cache.

Two consequences, both visible in production. First, the first sample after a restart emits nothing — there is no previous value, so there is no rate, and the series has a hole right where you are most likely to be looking. Second, the window is whatever elapsed since the last successful run: if the collector was down for an hour, “queries per second” is the average over that hour, not over the nominal 60-second interval.

How a sample becomes an event

A task script opens a schema, runs a query such as mysql:query("SHOW GLOBAL STATUS like 'Queries'", 10), and commits rows with request:commit. The first commit is where the metric acquires its shape: it defines the columns and their types, and every later row is aligned to them. The Rust implementation builds that column list on first commit, sends it downstream once, then streams rows.

Then the rows become Riemann events, and this is where the convention bites:

1
2
3
4
5
6
7
// monitoring metric value handling
let metric = get_value!(context.columns, self, "metric").unwrap_or(Value::Number(0.0_f64));
for (i, (name, _)) in context.columns.iter().enumerate() {
    let value = self.get_value(i)?;
    // exclude the metric field (so Riemann's default fields are not replaced)
    if name != "metric" { context.event.attributes(name, value.clone()); }
}

bee/src/features/riemann/send.rs:272 (comments translated from the Chinese original). The first line reads the column named metric, falling back to 0.0; the loop copies every other column into the event’s attributes. Only a column literally named metric becomes the Riemann metric number. Every other number travels as a string attribute. So oracle_tablespace_bytes publishes free_bytes and all_bytes as attributes with the metric field left at 0.0, and a dashboard must key its series on service + attribute name, not the metric field.

The event’s service is the task id — the file stem: host_memory_bytes, oracle_dg_delay_seconds, mysql_qps. The suffix is a unit contract: _bytes for a size, _seconds for a duration, _up for a health check, _info for labels rather than measurements.

There is a third outcome besides a value and an error. If the script produced no rows, the collector still sends an event with state = "blank" — “I ran and there was nothing” is information, and it is how a monitoring system tells “no data” from “not running”. If the script errored, nothing is sent at all: request:error(...) puts an error state in the stream and discards the already-committed rows. No partial metric, no blank event — a half-collected metric is worse than none.

Three ways to reach the target

The transport is chosen per metric, and the choice changes the cost profile.

  • JDBC — Oracle, MySQL, PostgreSQL/PolarDB and MSSQL; the second argument to query() is the JDBC query timeout in seconds.
  • Perl + sqlplus over SSH — Data Guard, archive usage, partition checks, CRS state, patch inventory and the OS facts go through a Perl helper on the target host that returns pipe-delimited text (fields separated by |). The Oracle standby is never queried over JDBC at all: it has no listener.
  • The native agent — a small Rust binary on the monitored host, speaking the same wire protocol. It replaces SSH + Perl + df/free/vmstat parsing with direct sysinfo calls, which makes the host metrics cheap enough to collect often.

The agent is why the host metrics look the way they do. It cannot parse free -m — that output is localised and column-order-dependent — so it calls heim for memory and CPU, and diskus for directory sizes:

1
2
3
4
5
6
7
pub async fn cpu_usage(delay_time: u32, promise: &mut Promise<'_, CPUUsage>) -> Result<()> {
    let time1 = heim::cpu::time().await?;
    tokio::time::sleep(Duration::from_millis(delay_time as u64)).await;
    let time2 = heim::cpu::time().await?;
    let total = get_total_time(&time1);
    ...
    let total_time = get_total_time(&time2) - total;

schemas/agent/src/agent/cpu_usage.rs:16. The agent measures CPU the honest way: two samples of the CPU clocks, one sleep apart, differenced. The hidden lines pick the system, user and idle slices out of each sample; total_time is the change in total CPU time between them.

The bugs I found while writing this

These are my scripts, so it is only fair to list what is wrong with them.

oracle_io_wait_milliseconds is off by a factor of ten thousand. V$EVENTMETRIC.TIME_WAITED is in microseconds, but the expression applies the classic centisecond-to-millisecond conversion, ROUND(10 * TIME_WAITED / DECODE(WAIT_COUNT,0,1,WAIT_COUNT)). For V$SYSTEM_EVENT, where TIME_WAITED really is centiseconds, ×10 gives milliseconds; for V$EVENTMETRIC, in microseconds, ×10 gives ten-thousandths of a millisecond. The name says milliseconds; the number is not.

It is also a lifetime average. TIME_WAITED ÷ WAIT_COUNT is cumulative divided by cumulative, so it only moves when statistics are reset. It answers “the average wait since the last stats reset”, not the question anybody asks during an incident.

One metric family ships three different units. In the Oracle system-metrics decoder, DB CPU and parse time elapsed are divided by 10⁶ (seconds), redo size and physical read bytes by 1024 (KiB), and a synthesised cluster traffic figure is produced in bytes. A consumer assuming one unit per family is wrong three times over.

mysql_files_bytes converts twice. The agent path stores row["size"] * 1024 (KiB → bytes) and the commit path multiplies by 1024 again, so the agent-reported value is 1024× too large. The SSH path, storing raw KiB, is correct.

mysql_capacity_bytes returns nil on the host path. The helper takes the span from "bytes" back to "(", but the diskus output is 420.51 MB (420,507,648 bytes), so the start index is greater than the end index, Lua returns an empty string, and tonumber("") is nil. The mock’s assertions omit the field for this metric while asserting it for the PostgreSQL equivalent, which is how I found it.

The MSSQL *_delay metrics are not delays. mssql_always_on_delay and mssql_mirror_delay publish log_send_queue_size and redo_queue_size — bytes of queued log. A queue size is also the wrong shape for “how far behind am I”: a slow-but-steady consumer shows a small queue, a stalled one a growing queue, and seconds is what an SLA — a service-level agreement, the promised bound on delay — is written in.

mssql_database_size_bytes reports one file. The per-database query selects FILEPROPERTY(name,'SpaceUsed') per row, but the Lua commits only the first row and the filter excludes the log file. The published “database size” is the size of one data file.

mysql_connections_used divides by max_connections without guarding zero, and uses Threads_connected where Threads_running is the saturation signal.

The host path and the agent path disagree about “total”. On the SSH path all_bytes = Used + Available, which excludes blocks reserved by the filesystem and under-reports the real size. The agent path returns the true statvfs total (the OS call that reports filesystem totals). Same name, two meanings.

Windows IOPS is pinned near 100%. busy_time = 1.00 - (idle_time / used_time) / 100.00 — the stray division makes the value ≈1.0 regardless of load.

No query has a row limit. The JDBC layer never calls setMaxRows, so information_schema.TABLES, pg_stat_database and the AWR result sets (AWR is Oracle’s bundled performance report) are fully materialised in the collector’s memory.

What I would do differently

Move the differencing into the collector. The rate logic is duplicated in two Lua scripts, depends on a disk cache that silently drops the first sample, and has an unbounded window. A metric descriptor — type = counter, unit = queries/second, window = 60s — would let the collector difference every counter correctly, clamp the window, and emit a zero-with-a-flag instead of a hole.

Give every metric an explicit unit and an explicit kind. The suffix convention is good but only convention. mssql_always_on_delay should be mssql_always_on_queue_bytes and oracle_io_wait_milliseconds should be oracle_io_wait_avg_microseconds, because the current names mislead.

Make the health check a read. The heartbeat write buys “the database accepts writes” — a real property, but a different metric from “the database answers”. It should be its own task with its own name, not a side effect of the liveness probe.

Fix the free-versus-headroom distinction. Publish both free_bytes and headroom_bytes for tablespaces. They answer different questions, and only one is what the alert threshold is written against.

And put a LIMIT on everything. The most dangerous queries scan a data dictionary or a directory tree — exactly what a monitoring system runs on someone else’s production database at 3 a.m. A collector that can saturate the thing it monitors has failed at its one job.

References