Monitoring SingleStore with Vigilmon: Distributed SQL Health, Leaf Node Availability & Query Performance Checks

SingleStore (formerly MemSQL) is a distributed SQL database engineered for high-throughput real-time analytics and transactional workloads. Its distributed architecture — aggregator nodes routing queries to leaf nodes — delivers exceptional performance, but it also introduces failure modes that don't exist in single-node databases: a leaf node going offline can silently degrade query performance, a partition imbalance can cause slow query tail latencies, and license quota exhaustion can block new connections. Vigilmon adds an independent external monitoring layer: HTTP health checks on your SingleStore-connected services that detect real query failures, leaf node issues, and connection problems before users hit errors.

What You'll Build

• A health endpoint that probes SingleStore connectivity and cluster health
• A Vigilmon HTTP monitor with appropriate timeout and assertion settings
• Leaf node availability detection in your health checks
• Cron heartbeat monitoring for background SingleStore jobs
• An alerting strategy tuned to distributed SQL failure modes

Prerequisites

• A SingleStore cluster (cloud-managed or self-hosted) with at least one database
• Connection credentials (host, port, user, password, database)
• An application service connecting to SingleStore
• A free account at vigilmon.online

Step 1: Add a Health Endpoint to Your SingleStore Service

Add a /health route that queries SingleStore's management views to verify both query availability and cluster node health.

Node.js (mysql2 driver — SingleStore is MySQL-protocol compatible)

// routes/health.js
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.SINGLESTORE_HOST,
  port: parseInt(process.env.SINGLESTORE_PORT || '3306'),
  user: process.env.SINGLESTORE_USER,
  password: process.env.SINGLESTORE_PASSWORD,
  database: process.env.SINGLESTORE_DATABASE,
  connectionLimit: 3,
  connectTimeout: 5000,
});

export async function healthHandler(req, res) {
  const checks = {};
  let ok = true;
  const start = Date.now();

  try {
    // Check 1: Basic query availability
    await pool.execute('SELECT 1');
    checks.query = 'ok';
    checks.latencyMs = Date.now() - start;

    // Check 2: Leaf node availability via management view
    const [nodeRows] = await pool.execute(`
      SELECT
        COUNT(*) AS totalLeaves,
        SUM(CASE WHEN state = 'online' THEN 1 ELSE 0 END) AS onlineLeaves
      FROM information_schema.mv_nodes
      WHERE type = 'LEAF'
    `);

    const { totalLeaves, onlineLeaves } = nodeRows[0];
    checks.totalLeafNodes = Number(totalLeaves);
    checks.onlineLeafNodes = Number(onlineLeaves);

    if (Number(onlineLeaves) < Number(totalLeaves)) {
      const offlineCount = Number(totalLeaves) - Number(onlineLeaves);
      checks.leafNodes = `degraded: ${offlineCount} of ${totalLeaves} leaf nodes offline`;
      ok = false;
    } else {
      checks.leafNodes = 'all online';
    }
  } catch (err) {
    checks.query = `error: ${err.message}`;
    ok = false;
  }

  res.status(ok ? 200 : 503).json({
    status: ok ? 'ok' : 'degraded',
    service: 'singlestore-service',
    checks,
  });
}

Python (mysql-connector-python)

# routers/health.py
import os, time
import mysql.connector
from mysql.connector import pooling
from fastapi import APIRouter

router = APIRouter()

_pool = pooling.MySQLConnectionPool(
    pool_name='singlestore_health',
    pool_size=3,
    host=os.environ['SINGLESTORE_HOST'],
    port=int(os.environ.get('SINGLESTORE_PORT', '3306')),
    user=os.environ['SINGLESTORE_USER'],
    password=os.environ['SINGLESTORE_PASSWORD'],
    database=os.environ['SINGLESTORE_DATABASE'],
    connection_timeout=5,
)

@router.get("/health")
async def health():
    checks = {}
    ok = True
    start = time.monotonic()

    conn = None
    try:
        conn = _pool.get_connection()
        cursor = conn.cursor(dictionary=True)

        # Check 1: Basic query availability
        cursor.execute('SELECT 1')
        cursor.fetchall()
        checks['query'] = 'ok'
        checks['latencyMs'] = int((time.monotonic() - start) * 1000)

        # Check 2: Leaf node availability
        cursor.execute("""
            SELECT
                COUNT(*) AS totalLeaves,
                SUM(CASE WHEN state = 'online' THEN 1 ELSE 0 END) AS onlineLeaves
            FROM information_schema.mv_nodes
            WHERE type = 'LEAF'
        """)
        row = cursor.fetchone()
        total = int(row['totalLeaves'] or 0)
        online = int(row['onlineLeaves'] or 0)
        checks['totalLeafNodes'] = total
        checks['onlineLeafNodes'] = online

        if online < total:
            checks['leafNodes'] = f'degraded: {total - online} of {total} leaf nodes offline'
            ok = False
        else:
            checks['leafNodes'] = 'all online'

        cursor.close()
    except Exception as e:
        checks['query'] = f'error: {e}'
        ok = False
    finally:
        if conn:
            conn.close()

    return {
        'status': 'ok' if ok else 'degraded',
        'service': 'singlestore-service',
        'checks': checks,
    }

Note on information_schema.mv_nodes: This view is available on SingleStore 7.1+. If your version is older, replace the leaf check with a simpler SHOW STATUS LIKE 'uptime' query to still verify the aggregator connection while removing the per-leaf visibility.

Step 2: Configure the Vigilmon HTTP Monitor

1. Log in to Vigilmon → Add Monitor → HTTP.
2. URL: your service's health endpoint (e.g. https://api.example.com/health).
3. Check interval: 60 seconds.
4. Response timeout: 10 seconds.
5. Expected status: 200.
6. JSON assertion: path status, expected value ok.
7. Click Save.

Tip: SingleStore's distributed query planner routes work through aggregator nodes. If your aggregator is healthy but a leaf node is offline, some queries will fail while simple SELECT 1 probes succeed. The leaf node check in Step 1 surfaces this split-state correctly.

For production workloads, create a second HTTP monitor pointing to a /health/deep endpoint that runs a representative analytical query against your actual data tables. This catches query plan regressions and partition skew that a simple SELECT 1 cannot detect.

Step 3: Heartbeat Monitoring for Background SingleStore Jobs

SingleStore is often used for ETL pipelines, batch aggregations, and real-time data ingestion jobs that run on a schedule. Use Vigilmon's Heartbeat monitor to confirm these workers complete successfully.

1. In Vigilmon, click Add Monitor → Cron Heartbeat.
2. Set the expected ping interval to match your job frequency.
3. Copy the heartbeat URL (e.g. https://vigilmon.online/heartbeat/abc123).
4. Ping the heartbeat at the end of each successful job:

// Node.js — nightly SingleStore aggregation job
import mysql from 'mysql2/promise';
import fetch from 'node-fetch';

export async function runNightlyAggregation() {
  const conn = await mysql.createConnection({
    host: process.env.SINGLESTORE_HOST,
    user: process.env.SINGLESTORE_USER,
    password: process.env.SINGLESTORE_PASSWORD,
    database: process.env.SINGLESTORE_DATABASE,
  });

  try {
    await conn.execute(`
      INSERT INTO daily_summary (date, total_events, avg_latency_ms)
      SELECT DATE(created_at), COUNT(*), AVG(latency_ms)
      FROM events
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
      GROUP BY DATE(created_at)
    `);

    // Signal success to Vigilmon
    await fetch(process.env.VIGILMON_HEARTBEAT_URL, { method: 'POST' });
    console.log('Aggregation complete — heartbeat sent.');
  } catch (err) {
    console.error('Aggregation failed — heartbeat NOT sent:', err.message);
  } finally {
    await conn.end();
  }
}

# Python — scheduled SingleStore ETL job
import os, mysql.connector, requests

def run_etl_job():
    conn = mysql.connector.connect(
        host=os.environ['SINGLESTORE_HOST'],
        user=os.environ['SINGLESTORE_USER'],
        password=os.environ['SINGLESTORE_PASSWORD'],
        database=os.environ['SINGLESTORE_DATABASE'],
    )
    try:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO daily_summary (date, total_events, avg_latency_ms)
            SELECT DATE(created_at), COUNT(*), AVG(latency_ms)
            FROM events
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
            GROUP BY DATE(created_at)
        """)
        conn.commit()
        cursor.close()

        # Signal success to Vigilmon
        requests.post(os.environ['VIGILMON_HEARTBEAT_URL'], timeout=5)
        print('ETL complete — heartbeat sent.')
    except Exception as e:
        print(f'ETL failed — heartbeat NOT sent: {e}')
    finally:
        conn.close()

Step 4: Alert Routing

| Monitor type | Target | Alert channel | Priority | |---|---|---|---| | HTTP | /health (query + leaf node check) | Email + PagerDuty | Critical | | HTTP | /health (latency assertion) | Slack | Warning | | HTTP | /health/deep (representative query) | Slack + Email | High | | Cron Heartbeat | Nightly aggregation / ETL job | Email + PagerDuty | Critical |

Configure consecutive failures to 2 on the HTTP monitor — SingleStore distributed query coordination can cause brief spikes under heavy load that resolve within a single check interval.

What Vigilmon Catches That SingleStore's Own Monitoring Misses

| Scenario | SingleStore Internal | Vigilmon | |---|---|---| | Leaf node offline — partial query failures | Cluster alert (internal) | Health endpoint leaf check → 503 fires | | Connection limit exhausted | Metric exists, no HTTP alert | Health endpoint probe fails — alert fires | | Long-running query blocking new connections | Query monitor metric | Deep health endpoint times out | | ETL job silently failed and stopped running | No job-level visibility | Heartbeat grace period expires → alert | | Aggregator overloaded but reachable | Internal metric only | Latency assertion on health endpoint | | External connectivity to SingleStore cloud lost | Not externally observable | Vigilmon probe from external network |

SingleStore's distributed architecture delivers the performance your analytics workloads demand, but distributed systems require distributed visibility. External health checks with Vigilmon give you the independent, application-layer signal that catches real user-facing failures.

Start monitoring your SingleStore applications in under 5 minutes — register free at vigilmon.online.