tutorial

Monitoring Google Cloud SQL with Vigilmon: Connection Health, Query Latency & Application-Layer Availability Checks

Practical guide to uptime and health monitoring for Google Cloud SQL — HTTP health endpoints for Cloud SQL-connected services, read replica lag detection, connection pool monitoring, and independent availability checks with Vigilmon.

Google Cloud SQL is a fully managed relational database service supporting MySQL, PostgreSQL, and SQL Server. It handles backups, patching, and failover automatically — but your application's availability depends on more than Cloud SQL's infrastructure guarantees. Connection limit exhaustion, read replica lag, maintenance window disruptions, Cloud SQL Proxy authentication failures, and query performance regressions can all degrade your application while Cloud Monitoring dashboards show the instance as healthy. Vigilmon adds an independent external layer: HTTP health checks on your Cloud SQL-connected services that probe real database connectivity, detect replica lag, and alert on application-level failures before users notice.

This tutorial shows you how to build reliable monitoring for Cloud SQL-backed applications using Vigilmon.

What You'll Build

  • A health endpoint that probes real Cloud SQL connectivity and reports connection pool state
  • Read replica lag detection for high-availability configurations
  • A Vigilmon HTTP monitor with timeout and assertion settings tailored to Cloud SQL
  • A heartbeat pattern for Cloud SQL-backed batch jobs
  • An alerting strategy for unavailability, replica lag, and connection pressure

Prerequisites

  • Google Cloud project with a Cloud SQL instance (MySQL, PostgreSQL, or SQL Server)
  • Service account credentials or Cloud SQL Auth Proxy configured
  • An application connected to Cloud SQL (any runtime)
  • A free account at vigilmon.online

Step 1: Add a Health Endpoint to Your Cloud SQL Service

Add a /health endpoint that performs a lightweight query to verify real connectivity and gather database health signals.

Node.js (pg — Cloud SQL PostgreSQL via Cloud SQL Auth Proxy)

// routes/health.js
import { Pool } from 'pg';

// Cloud SQL Auth Proxy listens locally — connect to 127.0.0.1:5432
const pool = new Pool({
  host: process.env.CLOUD_SQL_HOST || '127.0.0.1',
  port: parseInt(process.env.CLOUD_SQL_PORT || '5432', 10),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 2,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 10000,
});

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

  try {
    const result = await pool.query(`
      SELECT
        version() AS pg_version,
        pg_is_in_recovery() AS is_replica,
        (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections,
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
        (SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock') AS waiting_on_locks
    `);
    const row = result.rows[0];
    const latencyMs = Date.now() - start;

    checks.db = 'ok';
    checks.latencyMs = latencyMs;
    checks.pgVersion = row.pg_version;
    checks.isReplica = row.is_replica;
    checks.activeConnections = parseInt(row.active_connections, 10);
    checks.maxConnections = parseInt(row.max_connections, 10);
    checks.waitingOnLocks = parseInt(row.waiting_on_locks, 10);
    checks.connectionUtilizationPct = Math.round(
      (checks.activeConnections / checks.maxConnections) * 100
    );

    const maxUtilPct = parseInt(process.env.MAX_CONN_UTILIZATION_PCT || '80', 10);
    if (checks.connectionUtilizationPct > maxUtilPct) {
      checks.connections = `high utilization: ${checks.connectionUtilizationPct}%`;
      ok = false;
    } else {
      checks.connections = 'ok';
    }

    if (checks.waitingOnLocks > parseInt(process.env.MAX_WAITING_LOCKS || '10', 10)) {
      checks.locks = `lock contention: ${checks.waitingOnLocks} waiting`;
      ok = false;
    }

    if (latencyMs > parseInt(process.env.QUERY_WARN_MS || '2000', 10)) {
      checks.db = 'slow';
      ok = false;
    }
  } catch (err) {
    checks.db = `error: ${err.message}`;
    ok = false;
  }

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

Node.js (mysql2 — Cloud SQL MySQL)

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

const pool = mysql.createPool({
  host: process.env.CLOUD_SQL_HOST || '127.0.0.1',
  port: parseInt(process.env.CLOUD_SQL_PORT || '3306', 10),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 2,
  connectTimeout: 5000,
});

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

  try {
    const [rows] = await pool.execute(`
      SELECT
        @@version AS mysqlVersion,
        @@read_only AS readOnly,
        @@max_connections AS maxConnections,
        (SELECT count(*) FROM information_schema.processlist WHERE command != 'Sleep') AS activeConnections
    `);
    const row = rows[0];
    const latencyMs = Date.now() - start;

    checks.db = 'ok';
    checks.latencyMs = latencyMs;
    checks.mysqlVersion = row.mysqlVersion;
    checks.activeConnections = row.activeConnections;
    checks.maxConnections = row.maxConnections;
    checks.connectionUtilizationPct = Math.round(
      (row.activeConnections / row.maxConnections) * 100
    );

    if (row.readOnly === 1) {
      checks.db = 'read-only — instance may be a replica or in failover';
      ok = false;
    }

    const maxUtilPct = parseInt(process.env.MAX_CONN_UTILIZATION_PCT || '80', 10);
    if (checks.connectionUtilizationPct > maxUtilPct) {
      checks.connections = `high utilization: ${checks.connectionUtilizationPct}%`;
      ok = false;
    } else {
      checks.connections = 'ok';
    }

    if (latencyMs > parseInt(process.env.QUERY_WARN_MS || '2000', 10)) {
      checks.db = 'slow';
      ok = false;
    }
  } catch (err) {
    checks.db = `error: ${err.message}`;
    ok = false;
  }

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

Python (psycopg2 — Cloud SQL PostgreSQL)

# routers/health.py
import os, time, psycopg2
from psycopg2 import pool as pg_pool
from fastapi import APIRouter

router = APIRouter()

db_pool = pg_pool.SimpleConnectionPool(
    1, 2,
    host=os.environ.get('CLOUD_SQL_HOST', '127.0.0.1'),
    port=int(os.environ.get('CLOUD_SQL_PORT', '5432')),
    user=os.environ['DB_USER'],
    password=os.environ['DB_PASSWORD'],
    dbname=os.environ['DB_NAME'],
    connect_timeout=5,
)

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

    try:
        conn = db_pool.getconn()
        with conn.cursor() as cur:
            cur.execute("""
                SELECT
                    version(),
                    pg_is_in_recovery(),
                    (SELECT count(*) FROM pg_stat_activity WHERE state = 'active'),
                    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'),
                    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS replica_lag_seconds
            """)
            row = cur.fetchone()
            version, is_replica, active_conns, max_conns, replica_lag = row
            latency_ms = int((time.monotonic() - start) * 1000)

            checks['db'] = 'ok'
            checks['latencyMs'] = latency_ms
            checks['pgVersion'] = version
            checks['isReplica'] = is_replica
            checks['activeConnections'] = active_conns
            checks['maxConnections'] = max_conns
            utilization_pct = round(active_conns / max_conns * 100)
            checks['connectionUtilizationPct'] = utilization_pct

            if replica_lag is not None:
                checks['replicaLagSeconds'] = round(replica_lag, 2)
                max_lag = float(os.environ.get('MAX_REPLICA_LAG_SECONDS', '30'))
                if replica_lag > max_lag:
                    checks['replication'] = f'lag high: {replica_lag:.1f}s'
                    ok = False
                else:
                    checks['replication'] = 'ok'

            max_util = int(os.environ.get('MAX_CONN_UTILIZATION_PCT', '80'))
            if utilization_pct > max_util:
                checks['connections'] = f'high utilization: {utilization_pct}%'
                ok = False
            else:
                checks['connections'] = 'ok'

    except Exception as e:
        checks['db'] = f'error: {e}'
        ok = False
    finally:
        if conn:
            db_pool.putconn(conn)

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

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.

Tip: If you use Cloud SQL Auth Proxy, include a readiness probe check for the proxy process itself. If the proxy crashes, your application can't reach Cloud SQL even if the database instance is healthy. A separate /health/proxy endpoint that confirms the proxy socket is listening saves valuable debugging time.

Step 3: Read Replica Lag Monitoring

Cloud SQL supports read replicas to scale read-heavy workloads. Stale replicas serve outdated data silently — surface this in a dedicated health check.

// Separate health check for replica lag — query the replica directly
const replicaPool = new Pool({
  host: process.env.CLOUD_SQL_REPLICA_HOST || '127.0.0.1',
  port: parseInt(process.env.CLOUD_SQL_REPLICA_PORT || '5433', 10),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 1,
  connectionTimeoutMillis: 5000,
});

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

  try {
    const result = await replicaPool.query(`
      SELECT
        pg_is_in_recovery() AS isReplica,
        EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lagSeconds,
        pg_last_xact_replay_timestamp() AS lastReplayAt
    `);
    const row = result.rows[0];

    checks.replicaMode = row.isReplica;
    checks.lagSeconds = parseFloat(row.lagSeconds || 0).toFixed(2);
    checks.lastReplayAt = row.lastReplayAt;
    checks.latencyMs = Date.now() - start;

    const maxLag = parseFloat(process.env.MAX_REPLICA_LAG_SECONDS || '30');
    if (parseFloat(checks.lagSeconds) > maxLag) {
      checks.replication = `lag high: ${checks.lagSeconds}s`;
      ok = false;
    } else {
      checks.replication = 'ok';
    }
  } catch (err) {
    checks.replication = `error: ${err.message}`;
    ok = false;
  }

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

Configure this as a second Vigilmon monitor on /health/replica with a 2-minute check interval.

Step 4: Heartbeat for Cloud SQL-Backed Workers

Cloud SQL-backed background jobs are invisible to HTTP monitors. Use Vigilmon's Heartbeat monitor to detect when batch processing stops.

# worker/batch_processor.py
import os, requests

def run_processing_cycle():
    conn = get_cloud_sql_connection()
    try:
        records = fetch_pending_records(conn)
        for record in records:
            process_record(conn, record)
        conn.commit()

        # Ping Vigilmon only after successful commit
        requests.post(os.environ['VIGILMON_HEARTBEAT_URL'], timeout=5)
        print(f'Processed {len(records)} records — heartbeat sent')
    except Exception:
        conn.rollback()
        raise  # Don't ping — Vigilmon will alert when grace period expires
    finally:
        conn.close()

Set the heartbeat grace period to 3× your expected processing interval. A missed heartbeat means the batch failed, the Cloud SQL Auth Proxy lost its connection, or the job scheduler stopped.

Step 5: Alerting Strategy

| Alert channel | When to use | |---|---| | Email / PagerDuty | Service returns 503 (database unavailable) | | Slack webhook | Connection utilization exceeds 80% or lock contention detected | | Slack + Email | Replica lag exceeds threshold | | PagerDuty | Heartbeat missed (batch worker stopped) | | Status page | Any user-facing service backed by Cloud SQL |

Maintenance window awareness: Cloud SQL runs automated maintenance on a configurable schedule. Set a Vigilmon maintenance window to suppress alerts during planned downtime. Find your maintenance window in the Cloud SQL instance settings in the Google Cloud Console.

What Vigilmon Catches That Cloud Monitoring Misses

| Scenario | Cloud Monitoring | Vigilmon | |---|---|---| | Application can't connect (Auth Proxy crashed) | No application-layer check | HTTP monitor catches 503 immediately | | Connection pool exhausted — new requests fail | No pool-level visibility | Health endpoint reports utilization — alert fires | | Read replica lag growing silently | replication/replica_lag metric exists | Replica health endpoint probes actual lag in real time | | Lock contention causing query queuing | No end-to-end query check | Health endpoint surfaces waiting sessions | | Batch worker stopped silently | No worker-level check | Heartbeat grace period expires → alert | | Cloud Monitoring alert policy delivery fails | Alerts silently lost | Vigilmon is external — unaffected | | Cloud SQL Auth Proxy version incompatibility | No proxy-level check | Health probe fails when proxy breaks — 503 alert fires |

Cloud SQL handles the hard parts of database operations, but connection management, replica lag, and proxy reliability are still your application's responsibility to monitor. External health checks from Vigilmon give you the independent, application-layer signal that Cloud Monitoring can't provide.

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

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles