Metrics

Machbase Neo collects server status and request processing statistics and stores them in SYS._NEO_STATZ. Query the metrics and time range you need with SQL, then visualize the results by combining TQL’s SQL() and CHART().

Run the examples in this document while connected as sys. The server creates and manages _NEO_STATZ automatically; you do not need to create the table or insert data yourself.

Storage Structure and Collection Interval

_NEO_STATZ is a regular transaction table with the following three columns, not a TAG table.

A unique index covers (NAME, TIME). Values for the current interval update the same row. A new row is stored for the next interval.

• Collection interval: Periodically collected items, such as system status, are measured every 10 seconds. HTTP request statistics are collected as requests are processed.
• Storage resolution: One minute. Even when samples are collected every 10 seconds, each sample is not stored as a separate row.
• Current interval: The in-progress one-minute interval is also stored, so VALUE can change for the same TIME.
• Retention: Eight days. Old data is cleaned up at 15-minute intervals during writes, so rows older than eight days can remain until the next cleanup.

Interpreting Values

Not every VALUE is a cumulative total since server startup or a one-minute average. Interpret each item according to how it is collected.

For example, http:latency stores the number of samples in the interval, not a latency. Actual latency values are stored in http:latency:p50, http:latency:p90, and http:latency:p99. Divide a value in ns by 1000000 to convert it to ms.

An interval with no requests may have no HTTP metric rows. A missing row is not the same as a value of zero. Sums, differences, and percentiles for the current interval represent values collected so far, not the completed interval. Do not interpret a one-minute interval’s difference directly as throughput per second.

Finding Metric Keys

The keys collected depend on your configuration and the features in use. First, check which keys are actually stored on the current server.

SELECT NAME FROM _NEO_STATZ GROUP BY NAME ORDER BY NAME;

Use LIKE to filter for a specific category.

SELECT NAME FROM _NEO_STATZ WHERE NAME LIKE 'http:%'
GROUP BY NAME
ORDER BY NAME;

You can also check the stored time range and row count for each key.

SELECT NAME, MIN(TIME) AS FIRST_TIME, MAX(TIME) AS LAST_TIME, COUNT(*) AS POINTS
FROM _NEO_STATZ
GROUP BY NAME
ORDER BY NAME;

HTTP keys appear after requests have been processed. MQTT keys appear when the MQTT broker is in use, and rollup keys appear when there are rollups eligible for collection. Previously stored keys can remain queryable throughout the retention period even after the corresponding feature is no longer used.

Querying with SQL

The following query retrieves Go heap usage over the last hour in chronological order. Change SYSDATE - 1h to SYSDATE - 30m, SYSDATE - 1d, or another range as needed.

SELECT TIME, VALUE
FROM _NEO_STATZ
WHERE NAME = 'runtime:heap_inuse'
  AND TIME >= SYSDATE - 1h
ORDER BY TIME;

To query only completed intervals, excluding the current one-minute interval, add this condition:

SELECT TIME, VALUE
FROM _NEO_STATZ
WHERE NAME = 'runtime:heap_inuse'
  AND TIME >= SYSDATE - 1h
  AND TIME < DATE_TRUNC('minute', SYSDATE)
ORDER BY TIME;

Visualizing with TQL

Run the following examples in the web UI’s TQL editor. Pair the TIME and value returned by SQL() using list() to create a point on a time-axis chart. To plot another metric, change the NAME condition, unit conversion, and chart labels. Sort query results with ORDER BY TIME.

Heap Usage

Convert heap usage from bytes to MiB for display.

SQL(`
    SELECT TIME, VALUE / 1048576.0 AS HEAP_MIB
    FROM _NEO_STATZ
    WHERE NAME = 'runtime:heap_inuse'
      AND TIME >= SYSDATE - 1h
    ORDER BY TIME
`)
MAPVALUE(1, list(value(0), value(1)))
CHART(
    size("800px", "350px"),
    chartOption({
        animation: false,
        xAxis: { type: "time" },
        yAxis: { type: "value", name: "MiB" },
        tooltip: { trigger: "axis" },
        legend: {},
        series: [
            { name: "Go heap", type: "line", showSymbol: false, data: column(1) }
        ]
    })
)

Database Connection Pool

To display different keys on one chart, use CASE and GROUP BY TIME to combine values with the same timestamp into a single row. The following example compares open connections with connections in use. Here, MAX() selects the value for each key at the same TIME; it does not calculate a maximum across different timestamps.

SQL(`
    SELECT TIME,
        MAX(CASE WHEN NAME = 'sys:pool:open' THEN VALUE END) AS OPEN_CONNS,
        MAX(CASE WHEN NAME = 'sys:pool:in_use' THEN VALUE END) AS IN_USE_CONNS
    FROM _NEO_STATZ
    WHERE NAME IN ('sys:pool:open', 'sys:pool:in_use')
      AND TIME >= SYSDATE - 1h
    GROUP BY TIME
    ORDER BY TIME
`)
MAPVALUE(1, list(value(0), value(1)))
MAPVALUE(2, list(value(0), value(2)))
CHART(
    size("800px", "350px"),
    chartOption({
        animation: false,
        xAxis: { type: "time" },
        yAxis: { type: "value", name: "Connections", minInterval: 1 },
        tooltip: { trigger: "axis" },
        legend: {},
        series: [
            { name: "Open", type: "line", showSymbol: false, data: column(1) },
            { name: "In use", type: "line", showSymbol: false, data: column(2) }
        ]
    })
)

HTTP Response Latency

Compare p50, p90, and p99 latency for all HTTP requests in milliseconds. The percentiles represent the distribution of samples collected in each one-minute interval. Results may be empty if there have been no requests, so use the server’s HTTP API before running the example.

SQL(`
    SELECT TIME,
        MAX(CASE WHEN NAME = 'http:latency:p50' THEN VALUE END) / 1000000.0 AS P50,
        MAX(CASE WHEN NAME = 'http:latency:p90' THEN VALUE END) / 1000000.0 AS P90,
        MAX(CASE WHEN NAME = 'http:latency:p99' THEN VALUE END) / 1000000.0 AS P99
    FROM _NEO_STATZ
    WHERE NAME IN ('http:latency:p50', 'http:latency:p90', 'http:latency:p99')
      AND TIME >= SYSDATE - 1h
    GROUP BY TIME
    ORDER BY TIME
`)
MAPVALUE(1, list(value(0), value(1)))
MAPVALUE(2, list(value(0), value(2)))
MAPVALUE(3, list(value(0), value(3)))
CHART(
    size("800px", "350px"),
    chartOption({
        animation: false,
        xAxis: { type: "time" },
        yAxis: { type: "value", name: "ms" },
        tooltip: { trigger: "axis" },
        legend: {},
        series: [
            { name: "p50", type: "line", showSymbol: false, data: column(1) },
            { name: "p90", type: "line", showSymbol: false, data: column(2) },
            { name: "p99", type: "line", showSymbol: false, data: column(3) }
        ]
    })
)

Change the query’s keys to http:query:latency:p50 and the corresponding p90/p99 keys to compare only /db/query requests. Extending the query range does not change the stored one-minute resolution. Also, a simple average of p99 values from multiple intervals is not the p99 for the entire period.

Main Metrics

The following lists describe the main built-in collection items. Use the SQL above to check which keys actually exist. In the tables, “Gauge” means the last measured value, “Counter” means the sum within an interval, and “Odometer” means the difference in a cumulative counter.

System and Go Runtime

CPU and memory percentages and TCP connection counts describe the host system. Go runtime items describe the Machbase Neo process.

Database

sys:execute:time:avg is the average of each session’s MAX_MSEC, not the average duration of all individual SQL executions.

Server Internal Connection Pool

These statistics describe the server’s default database/sql connection pool. Distinguish them from sys:session:count, which counts DB sessions across the server.

HTTP

Request counts, byte counts, and status code counts are sums within an interval. The base latency key stores the sample count; the :p50, :p90, and :p99 keys store latencies in nanoseconds.

MQTT

All items below store the last measured broker statistic. In particular, cumulative statistics such as received/sent bytes and messages store the cumulative values provided by the broker, unlike HTTP’s per-interval sums.

TQL Cache

These items also store the last measured value. items is the current number of items; the others are cumulative counts tracked by the cache.

Rollups

<rollup_table> is replaced by the rollup table name converted to lowercase. All items store the last measured value.

sys:rollup_global:last_elapse is 0 if no rollup ran during the preceding collection interval.