Monitoring Metrics

Often times mission-critical metrics need to be monitored in real-time to ensure operations are running smoothly. This is often done through polling or streaming aggregated data, resulting in delays and slow queries when you want to drill in to the finer details - e.g. going from a 5 minute period to a 10 second period.

ElectricSQL provides a simple way to monitor metrics, with the relevant data synced locally for fast access while retaining real-time updates.

This recipe demonstrates how to build a chart for a monitoring dashboard that measures system metrics, such as CPU usage.

• Schema
• Data Access
• Usage

Schema

Adapt the schema and DDLX commands below to match your specific use-case.

-- Create a monitoring table for generic numerical metrics.
CREATE TABLE IF NOT EXISTS monitoring (
  id UUID PRIMARY KEY NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  type TEXT NOT NULL, -- e.g. CPU, Memory, Disk, Network
  value DOUBLE PRECISION NOT NULL
);

-- Index for type and timestamp columns
CREATE INDEX monitoring_idx_type_timestamp ON monitoring(type, timestamp);

-- ⚡ Electrify the table
ALTER TABLE monitoring ENABLE ELECTRIC;

Data Access

Adapt the headless components below and enhance them with additional features.

import { useEffect, useState } from 'react'
import { useElectric } from '../electric/ElectricWrapper'
import { useLiveQuery } from 'electric-sql/react'

export const useMonitoringMetric = ({
  metricType,
  viewWindowSeconds = 60,
  aggregationWindowSeconds = 5,
}: {
  metricType: 'CPU' | 'Memory' | 'Disk'
  viewWindowSeconds?: number
  aggregationWindowSeconds?: number
}) => {
  // oldest time to select data from in UNIX ms time
  const [oldestTimeToSelect, setOldestTimeToSelect] = useState(0)

  const { db } = useElectric()!

  // perform an aggregation on the timestamps by dividing the Unix Epoch
  // format by [aggregationWindowSeconds] and grouping by that amount, and
  // only show data older than [oldestTimeToSelect]
  const { results: timeSeries = [] } = useLiveQuery<
    {
      timestamp: string
      value_avg: number
      value_max: number
      value_min: number
    }[]
  >(
    db.liveRawQuery({
      sql: `
        SELECT
          timestamp,
          AVG(value) as value_avg,
          MAX(value) as value_max,
          MIN(value) as value_min
        FROM monitoring
        WHERE CAST (strftime('%s', timestamp) AS INT) > ?
        AND type = ?
        GROUP BY strftime('%s', timestamp) / ?
        ORDER BY timestamp ASC
      `,
      args: [
        oldestTimeToSelect,
        metricType,
        aggregationWindowSeconds
      ],
    }),
  )

  // update oldest time to show every second or so, or when the
  // view window changes
  useEffect(() => {
    // use a buffer of at least 10sec in front of the data being selected
    // to avoid the time range changing too often
    const viewBufferSeconds = Math.max(viewWindowSeconds * 0.1, 10)
    const updateOldestTimeToShow = () => {
      const steppedTimeSeconds =
        Math.floor(Date.now() / 1000 / viewBufferSeconds) * viewBufferSeconds
      const bufferedStartTimeSeconds = steppedTimeSeconds - viewWindowSeconds
      setOldestTimeToSelect(bufferedStartTimeSeconds)
    }

    updateOldestTimeToShow()
    const interval = setInterval(updateOldestTimeToShow, 1000)
    return () => clearInterval(interval)
  }, [viewWindowSeconds])

  return {
    timeSeries: timeSeries.map((ts) => ({
      timestamp: new Date(ts.timestamp),
      max: ts.value_max,
      min: ts.value_min,
      avg: ts.value_avg,
    })),
  }
}

Usage

Connect the schema and headless components with your UI library of choice to get a working component.

import { useState } from 'react'
import { MonitoringChartView } from './MonitoringChartView'
import { useMonitoringMetric } from './use_monitoring_metrics'

export const MonitoringChart = () => {
  // The size of the time window to show data for
  const [viewWindowSeconds, setViewWindowSeconds] = useState(60)

  // The size of the "buckets" for which the data will be aggregated
  const [aggregationWindowSeconds, setAggregationWindowSeconds] = useState(5)

  const { timeSeries } = useMonitoringMetric({
    metricType: 'CPU',
    viewWindowSeconds,
    aggregationWindowSeconds,
  })

  return (
    <MonitoringChartView
      dataset={timeSeries}
      dataKeyConfig={{
        avg: { label: 'Average' },
        min: { label: 'Minimum' },
        max: { label: 'Maximum' },
      }}
      timestampKey="timestamp"
      aggregationWindowSeconds={aggregationWindowSeconds}
      onAggregationWindowSecondsChanged={setAggregationWindowSeconds}
      viewWindowSeconds={viewWindowSeconds}
      onViewWindowSecondsChanged={setViewWindowSeconds}
    />
  )
}