Python Monitoring & Alerting Patterns for Galera

This guide extends the architecture reference in MariaDB Galera Core Architecture & Fundamentals into a reusable monitoring layer: a small Python library that scrapes every node’s wsrep_ telemetry on a schedule, normalizes it into a stable metric model, and drives threshold alerting across the whole cluster rather than one node at a time. A single-node probe answers “is this host safe to receive writes?”; a fleet needs the next layer up — a collector that fans out across members, reconciles their views of the same group, exposes the numbers to a metrics store, and raises exactly one actionable alert when a shared failure mode (flow-control saturation, a shrinking primary component, an apply backlog) crosses a threshold. This page is the design pattern for that layer, aimed at platform teams who already have a per-node check and now want a maintainable, testable monitoring service for MariaDB 10.6–11.x with Galera 4.

Concept: A Collector Layer Above the Per-Node Probe

The deterministic per-node evaluator is documented separately in Monitoring Galera Cluster State with Python — that page is the leaf that classifies one host into OK/WARNING/CRITICAL. This page deliberately does not repeat it. Here the unit of concern is the group: a monitoring loop that connects to all members, collects the same wsrep_ variables from each, and derives cluster-level facts a single node cannot see on its own. Whether the Galera cluster is partitioned, for example, is only visible by comparing wsrep_cluster_size and wsrep_cluster_state_uuid across every node — a node in a minority partition reports a perfectly healthy-looking local state while lying about the size of the world.

The design separates three responsibilities that are usually tangled together in a one-off script, and keeping them decoupled is what makes the code reusable and testable:

  • Scrape — open a resilient connection to one node, run SHOW GLOBAL STATUS LIKE 'wsrep_%', and return a typed snapshot. No policy, no alerting, no I/O beyond the query. This is the only part that talks to MariaDB.
  • Evaluate — apply threshold rules to a snapshot (or to the reconciled multi-node view) and emit structured findings. Pure functions over data, so they unit-test without a database.
  • Dispatch — route findings to a metrics store (a Prometheus textfile or an exporter) and to an alerting sink (webhook, PagerDuty, log), with de-duplication so a persistent condition pages once, not every interval.
Fleet monitoring data flow from Galera nodes through a Python collector to metrics and alerting Three Galera nodes each expose wsrep status variables over port 3306. A Python collector fans out and scrapes all three concurrently, reconciles their views into one cluster snapshot, evaluates threshold rules, and then dispatches the result two ways: numeric metrics to a metrics store such as a Prometheus textfile or exporter, and threshold breaches to an alerting sink such as a webhook or pager, with de-duplication so a persistent condition alerts once. GALERA GROUP Node 1 wsrep_% :3306 Node 2 wsrep_% :3306 Node 3 wsrep_% :3306 scrape Python collector scrape (concurrent) reconcile group view evaluate thresholds de-duplicate + dispatch Metrics store Prometheus textfile / exporter Alerting sink webhook / pager (deduped) CLUSTER-LEVEL FACTS DERIVED BY RECONCILING ALL NODES · quorum split → nodes disagree on wsrep_cluster_size · state divergence → differing wsrep_cluster_state_uuid · stalled applier → one node's wsrep_last_committed lags peers · hot member → highest wsrep_flow_control_sent in the group · single alert raised per condition, not per node
The collector is the layer that turns per-node snapshots into cluster-level facts: partition, divergence, and apply-lag are only visible by reconciling every member's view of the same group.

Prerequisites & Environment

The collector is an ordinary Python service; the constraint is what it connects to and with what privilege. Provision the following before running it against production:

Software — Python 3.9+ with either mysql-connector-python or PyMySQL (the patterns below use PyMySQL; the connector is a drop-in with a near-identical API). A MariaDB 10.6+ group running Galera 4, reachable from wherever the collector runs.

A dedicated read-only monitoring account, replicated across the group. Because Galera replicates DDL and DCL, create it once and it lands on every node:

CREATE USER 'galera_mon'@'10.0.2.%' IDENTIFIED BY 'REPLACE_WITH_VAULT_SECRET';
GRANT USAGE ON *.* TO 'galera_mon'@'10.0.2.%';
-- USAGE is enough to run SHOW GLOBAL STATUS / SHOW GLOBAL VARIABLES.

Never reuse the SST account (wsrep_sst_auth) or a superuser for scraping — the monitoring path should be able to read status and nothing else. The account and its grant model tie back to the identity keys in the wsrep.cnf Configuration Deep Dive.

Network — the collector needs TCP 3306 to each wsrep_node_address, not a load-balanced VIP. Scraping through a VIP hides exactly the per-node divergence the collector exists to detect. The port map and ingress rules are enumerated in Network Security & Firewall Rules for Galera; if you scrape over the replication network, keep it on the segment covered by Setting Up Secure TLS for Galera Cluster Communication.

Step-by-Step: Build the Reusable Monitoring Library

Step 1 — A scraper that returns typed snapshots

The scraper is the only component that touches the database, and it must do exactly one thing: hand back a snapshot of one node’s wsrep_ namespace, coercing numeric strings to numbers so downstream code never re-parses. Isolating it here is what lets every other layer be tested against fixtures instead of a live cluster. It handles the two write-conflict codes Galera raises under contention — 1213 (deadlock, surfaced for certification conflicts) and 1205 (lock wait timeout) — so a busy node reads as reachable, not down.

"""galera_monitor/scrape.py — the only module that talks to MariaDB."""
from __future__ import annotations
import time
from dataclasses import dataclass, field
from typing import Optional
import pymysql

# Variables the collector coerces to float/int; everything else stays a string.
NUMERIC = {
    "wsrep_cluster_size", "wsrep_local_recv_queue", "wsrep_local_recv_queue_avg",
    "wsrep_flow_control_paused", "wsrep_flow_control_sent", "wsrep_flow_control_recv",
    "wsrep_local_cert_failures", "wsrep_local_send_queue_avg", "wsrep_last_committed",
    "wsrep_cert_deps_distance",
}

@dataclass
class NodeSnapshot:
    host: str
    reachable: bool
    ts: float = field(default_factory=time.time)
    values: dict = field(default_factory=dict)
    error: Optional[str] = None

    def num(self, key: str, default: float = 0.0) -> float:
        v = self.values.get(key)
        return float(v) if v is not None else default


def _coerce(raw: dict) -> dict:
    out = {}
    for name, value in raw.items():
        if name in NUMERIC:
            try:
                out[name] = float(value) if "." in str(value) else int(value)
            except (TypeError, ValueError):
                out[name] = value
        else:
            out[name] = value
    return out


def scrape_node(host: str, user: str, password: str,
                port: int = 3306, timeout: int = 4) -> NodeSnapshot:
    """Return one node's wsrep_ snapshot. Never raises for an unreachable node."""
    try:
        conn = pymysql.connect(host=host, user=user, password=password, port=port,
                               connect_timeout=timeout, read_timeout=timeout)
    except pymysql.err.MySQLError as exc:
        return NodeSnapshot(host=host, reachable=False, error=str(exc))
    try:
        with conn.cursor() as cur:
            cur.execute("SHOW GLOBAL STATUS LIKE 'wsrep_%'")
            raw = {name: val for name, val in cur.fetchall()}
        return NodeSnapshot(host=host, reachable=True, values=_coerce(raw))
    except pymysql.err.OperationalError as exc:
        code = exc.args[0] if exc.args else None
        if code in (1213, 1205):
            # Contention, not a fault: the node is up and answering.
            return NodeSnapshot(host=host, reachable=True,
                                error=f"transient wsrep conflict {code}")
        return NodeSnapshot(host=host, reachable=False, error=str(exc))
    finally:
        conn.close()

The wsrep_local_recv_queue_avg and wsrep_flow_control_paused values are interval metrics — MariaDB resets them each time the status is read — so a snapshot taken every 15 s reports the average over that 15 s window. That property is central to the alerting design and is what makes a fixed scrape interval matter.

Step 2 — Reconcile the group into one view

Individual snapshots become a diagnosis only when compared. The reconciler takes the list of snapshots and derives the facts no single node can report: whether every reachable member agrees on the Galera cluster size and state UUID (disagreement is a partition or a diverged history), which member is applying behind the group, and which member is the flow-control culprit. Keeping this pure — a function from a list of snapshots to a verdict — means it unit-tests entirely on fixtures.

"""galera_monitor/reconcile.py — pure functions over snapshots, no I/O."""
from dataclasses import dataclass
from typing import List
from .scrape import NodeSnapshot

@dataclass
class GroupView:
    reachable: int
    expected: int
    sizes: dict            # host -> reported wsrep_cluster_size
    uuids: set             # distinct wsrep_cluster_state_uuid values
    max_committed: int
    laggards: dict         # host -> seqno gap behind the group leader
    fc_culprit: str        # host with the highest wsrep_flow_control_sent
    partitioned: bool
    diverged: bool


def reconcile(snaps: List[NodeSnapshot], expected: int) -> GroupView:
    live = [s for s in snaps if s.reachable]
    sizes = {s.host: int(s.num("wsrep_cluster_size")) for s in live}
    uuids = {s.values.get("wsrep_cluster_state_uuid") for s in live
             if s.values.get("wsrep_cluster_state_uuid")}
    committed = {s.host: int(s.num("wsrep_last_committed")) for s in live}
    leader = max(committed.values(), default=0)
    laggards = {h: leader - c for h, c in committed.items() if leader - c > 0}
    fc_sent = {s.host: s.num("wsrep_flow_control_sent") for s in live}
    fc_culprit = max(fc_sent, key=fc_sent.get, default="")

    # Any reachable node that reports a size below the reachable count, or a
    # size that disagrees with its peers, indicates a split view of the group.
    partitioned = bool(sizes) and (min(sizes.values()) < len(live)
                                   or len(set(sizes.values())) > 1)
    return GroupView(
        reachable=len(live), expected=expected, sizes=sizes, uuids=uuids,
        max_committed=leader, laggards=laggards, fc_culprit=fc_culprit,
        partitioned=partitioned, diverged=len(uuids) > 1,
    )

A diverged result — more than one distinct wsrep_cluster_state_uuid — is the most serious verdict the collector can produce: it means two sets of nodes have separately-evolving histories, the split-brain condition that quorum enforcement exists to prevent. The quorum arithmetic behind it is laid out in Designing Multi-Master Topologies.

Step 3 — Evaluate thresholds into structured findings

The evaluator turns the reconciled view plus per-node numbers into a list of findings, each with a severity and a stable key used later for de-duplication. Rules are data, so adding a metric is a table edit, not new control flow.

"""galera_monitor/evaluate.py — thresholds -> findings, still no I/O."""
from dataclasses import dataclass
from typing import List
from .scrape import NodeSnapshot
from .reconcile import GroupView

WARN, CRIT = "warning", "critical"

@dataclass(frozen=True)
class Finding:
    key: str          # stable identity for de-duplication
    severity: str
    summary: str

# Per-node numeric rules: (metric, warn, crit).
NODE_RULES = [
    ("wsrep_flow_control_paused", 0.10, 0.25),
    ("wsrep_local_recv_queue_avg", 20.0, 100.0),
    ("wsrep_local_cert_failures", 1.0, 10.0),
]

def evaluate(snaps: List[NodeSnapshot], view: GroupView) -> List[Finding]:
    out: List[Finding] = []
    if view.reachable < view.expected:
        sev = CRIT if view.reachable <= view.expected // 2 else WARN
        out.append(Finding("cluster.size", sev,
                   f"{view.reachable}/{view.expected} nodes reachable"))
    if view.partitioned:
        out.append(Finding("cluster.partition", CRIT,
                   f"members disagree on size: {view.sizes}"))
    if view.diverged:
        out.append(Finding("cluster.diverged", CRIT,
                   f"multiple state UUIDs present: {view.uuids}"))
    for host, gap in view.laggards.items():
        if gap > 500:
            out.append(Finding(f"apply.lag.{host}", WARN,
                       f"{host} is {gap} write-sets behind the group"))
    for snap in snaps:
        for metric, warn, crit in NODE_RULES:
            val = snap.num(metric)
            if val >= crit:
                out.append(Finding(f"{metric}.{snap.host}", CRIT,
                           f"{host_metric(snap, metric, val)}"))
            elif val >= warn:
                out.append(Finding(f"{metric}.{snap.host}", WARN,
                           f"{host_metric(snap, metric, val)}"))
    return out

def host_metric(snap: NodeSnapshot, metric: str, val: float) -> str:
    return f"{snap.host} {metric} = {val:g}"

The flow-control and receive-queue thresholds above are the ones that predict a group-wide stall; the reasoning behind the exact numbers, and the PromQL equivalents, are developed in Alerting on Flow Control & Receive-Queue Depth.

Step 4 — Orchestrate the loop and dispatch with de-duplication

The orchestration loop ties the layers together: scrape every node concurrently (so one slow node never delays the others), reconcile, evaluate, then dispatch. De-duplication is the piece that separates a usable alerting service from a pager that fires every interval: a finding fires on its rising edge and clears on its falling edge, keyed by the stable Finding.key.

"""galera_monitor/loop.py — the orchestration entry point."""
import time
from concurrent.futures import ThreadPoolExecutor
from typing import Dict, List
from .scrape import scrape_node, NodeSnapshot
from .reconcile import reconcile
from .evaluate import evaluate, Finding

def collect_once(nodes, creds) -> List[NodeSnapshot]:
    with ThreadPoolExecutor(max_workers=len(nodes)) as pool:
        futures = [pool.submit(scrape_node, host, creds["user"],
                               creds["password"]) for host in nodes]
        return [f.result() for f in futures]

def run(nodes, creds, expected, interval=15, dispatch=print):
    active: Dict[str, Finding] = {}          # key -> currently firing finding
    while True:
        snaps = collect_once(nodes, creds)
        view = reconcile(snaps, expected)
        current = {f.key: f for f in evaluate(snaps, view)}
        for key, f in current.items():        # rising edges
            if key not in active:
                dispatch(f"FIRING [{f.severity}] {f.summary}")
            active[key] = f
        for key in list(active):              # falling edges
            if key not in current:
                dispatch(f"RESOLVED {key}")
                del active[key]
        time.sleep(interval)

if __name__ == "__main__":
    run(nodes=["10.0.2.11", "10.0.2.12", "10.0.2.13"],
        creds={"user": "galera_mon", "password": "s3cret"}, expected=3)

Replace the dispatch=print sink with a webhook, an Alertmanager push, or an append to a Prometheus textfile — the loop does not care, which is the point of keeping evaluation and dispatch on opposite sides of a function boundary. Wiring the same numbers into Prometheus with a long-lived HTTP endpoint instead of a push loop is covered in Building a Prometheus Exporter for wsrep Status.

Parameter Deep-Dive: The Knobs That Govern the Collector

These are the collector’s own tunables, distinct from any Galera setting. Getting the scrape interval and connection timeout wrong is what turns a monitor into a false-alarm generator or a blind spot.

Knob Typical value Why it matters
Scrape interval 10–15 s Sets the averaging window for wsrep_local_recv_queue_avg and wsrep_flow_control_paused; too long hides short stalls, too short adds query load and noise
connect_timeout 3–5 s A node mid-SST refuses connections; a short timeout marks it unreachable fast instead of stalling the whole scrape
read_timeout 3–5 s Bounds a SHOW GLOBAL STATUS that hangs behind a saturated node, so one bad member cannot freeze the loop
Concurrency one worker per node Fan-out keeps total scrape time near the slowest single node, not the sum of all nodes
De-dup window rising/falling edge Fires once per condition and resolves when it clears, instead of paging every interval a condition persists
Consecutive-breach count 2–3 intervals Requires a metric to breach across successive scrapes before alerting, filtering single-sample flow-control spikes

The consecutive-breach discipline deserves emphasis: wsrep_flow_control_paused legitimately spikes for one interval during a write burst. Alert only when a numeric finding persists across two or three scrapes; wrap the evaluate output in a small counter keyed by Finding.key before dispatch to implement it. The underlying throttle these metrics measure is tuned in Tuning Flow Control to Prevent Cluster Stalls.

Verification & Health Checks

Prove each layer independently. First confirm the scraper reads real numbers from a live node:

python3 -c "from galera_monitor.scrape import scrape_node; \
  s = scrape_node('10.0.2.11', 'galera_mon', 's3cret'); \
  print(s.reachable, s.num('wsrep_cluster_size'), s.num('wsrep_flow_control_paused'))"

Cross-check the raw source of truth so any wrong verdict is attributable to the thresholds, not the data:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'wsrep_cluster_size',           -- must equal the node count on every member
  'wsrep_cluster_state_uuid',     -- must be identical across all members
  'wsrep_last_committed',         -- must advance uniformly across members
  'wsrep_flow_control_sent',      -- identifies the member requesting pauses
  'wsrep_local_recv_queue_avg'    -- the apply backlog since the last read
);

Then force each alerting path without a real incident: point the collector at a node isolated on 4567/tcp and confirm a cluster.partition finding fires and later resolves; hold SHOW GLOBAL STATUS open behind a long transaction and confirm the read-timeout marks that node unreachable rather than hanging the loop. Because reconcile and evaluate are pure, most of this is covered by unit tests over NodeSnapshot fixtures — no cluster required.

Automation Integration

Run the collector as a long-lived systemd service, not a cron one-shot, so its de-duplication state survives between scrapes:

[Unit]
Description=Galera fleet monitor
After=network-online.target

[Service]
Type=simple
User=galera-mon
EnvironmentFile=/etc/galera-monitor/creds.env
ExecStart=/opt/galera-monitor/.venv/bin/python -m galera_monitor.loop
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target

Keep credentials in the EnvironmentFile (mode 0400, owned by the service user), never in the unit or the repo. Render the node list from the same inventory that provisions the Galera cluster so the monitor and the fleet never drift — the idempotent pattern is in Automating Node Provisioning with Ansible. For teams already running a metrics stack, prefer exposing the snapshots through a Prometheus exporter and expressing thresholds as alert rules, which moves alert state out of the Python process and into a system built for it.

Troubleshooting

Symptom Root cause Fix
Every scrape reports wsrep_cluster_size as expected but a node still can’t be written Collector scrapes a load-balanced VIP, masking the partitioned member Scrape each wsrep_node_address directly, never a VIP
wsrep_flow_control_paused reads far lower than expected The value is reset on read; another scraper is reading status between your intervals Ensure one collector owns the status read, or switch to the cumulative wsrep_flow_control_paused_ns counter
Alert storm every interval during a batch load No de-duplication or consecutive-breach filter Fire on rising edge only and require 2–3 successive breaches for numeric findings
Loop freezes when one node saturates Missing read_timeout, so a hung SHOW GLOBAL STATUS blocks the future Set read_timeout and scrape concurrently so one node cannot stall the batch
reconcile never flags a real partition Collector cannot reach the minority node, so it is absent from the snapshot list, not disagreeing Alert on reachable < expected independently of the size-disagreement check

Frequently Asked Questions

How is this different from a per-node health probe? A per-node probe classifies one host in isolation and is the right tool for a load balancer’s backend check. The collector on this page connects to every member, reconciles their snapshots, and derives facts no single node can report — partition, history divergence, and which member is applying behind the group — then raises one alert per condition. Use the probe at the edge for routing and this collector centrally for cluster-wide observability; they are complementary layers, not substitutes.

Why scrape each node directly instead of through the load balancer? Scraping a VIP gives you whichever node the balancer picked, so a partitioned or desynced member is invisible exactly when you most need to see it. The collector’s entire value is comparing per-node views, which requires a direct connection to each wsrep_node_address. Reserve the VIP for application traffic and give the monitor its own list of real node addresses.

Why does wsrep_flow_control_paused sometimes read lower than I expect? It is an interval metric that MariaDB resets when the status is read, so it reports the paused fraction only since the previous read. If a second tool reads status between your scrapes, your collector sees a smaller window and a lower value. Either make one collector the sole reader of SHOW GLOBAL STATUS, or base alerting on the monotonically increasing wsrep_flow_control_paused_ns counter and compute the rate yourself.