Interpreting wsrep Certification-Failure Metrics

This guide builds on the Write-Set Certification Process Explained and answers a diagnostic question the parent leaves open: given the wall of wsrep_ numbers a Galera node exposes, which ones actually measure certification health, and what does each value mean when you read it at 3 a.m.? Four counters carry the signal — wsrep_local_cert_failures, wsrep_local_bf_aborts, wsrep_cert_deps_distance, and wsrep_cert_index_size — and every one of them is easy to misread. Two are cumulative counters whose absolute value is meaningless and whose rate is everything; one is an average that guides apply-thread sizing; one is a memory-footprint gauge. This page defines each precisely, gives the threshold that turns it into an alert, and shows how to read them together so you diagnose the cause instead of chasing the symptom.

Context: Rate, Average, and Footprint Are Three Different Reads

The single most common monitoring mistake in Galera is treating every wsrep_ variable the same way. Certification metrics fall into three categories, and the right way to read one is the wrong way to read another. wsrep_local_cert_failures and wsrep_local_bf_aborts are monotonic counters — they only ever increase since the last FLUSH STATUS or server restart, so their raw value tells you nothing; the delta over a fixed interval is the real signal. wsrep_cert_deps_distance is a running average of a structural property of your workload, read as an absolute number. wsrep_cert_index_size is an instantaneous gauge of memory, read as a level that trends up and down. Confuse a counter for a gauge and you will alert on a number that has been climbing harmlessly since boot; confuse a gauge for a counter and you will miss a real memory trend.

These four also answer different questions. The two abort counters tell you whether transactions are colliding — a workload and schema question addressed by reducing certification conflicts in hot tables. wsrep_cert_deps_distance tells you how much apply parallelism the workload can actually use, which sizes wsrep_slave_threads. wsrep_cert_index_size tells you how much memory the certification index is holding, which is a capacity signal, not a conflict signal. Reading them as a set is what separates “we have a hot-row problem” from “we have an apply-throughput problem” from “we have a memory-pressure problem.”

How to read each certification metric and what it diagnoses Four certification metrics map to three reading methods. wsrep_local_cert_failures and wsrep_local_bf_aborts are cumulative counters read as a rate of change, and a rising rate diagnoses key contention on hot rows. wsrep_cert_deps_distance is a running average read as an absolute value, and it sizes wsrep_slave_threads because it measures available apply parallelism. wsrep_cert_index_size is an instantaneous gauge read as a memory level, and a rising trend diagnoses certification-index memory pressure from long transactions or a deep receive queue. METRIC READ AS DIAGNOSES wsrep_local_cert_failures write-sets this node failed to certify rate of change counter · delta / interval key contention on hot rows wsrep_local_bf_aborts local txns aborted by earlier write-set rate of change counter · delta / interval brute-force aborts on this node wsrep_cert_deps_distance avg write-sets applyable in parallel absolute average read the value directly sizes wsrep_slave_threads wsrep_cert_index_size keys held in the certification index memory gauge level · trends up and down certification-index memory pressure reading a counter as a gauge, or a gauge as a counter, is the most common Galera monitoring error
Each certification metric has one correct reading method and one question it answers; the three reading methods — rate, average, and level — are not interchangeable.

Solution: What Each Metric Means and Where to Set the Line

Read all four in one query, then interpret each on its own terms:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'wsrep_local_cert_failures',
  'wsrep_local_bf_aborts',
  'wsrep_cert_deps_distance',
  'wsrep_cert_index_size',
  'wsrep_local_recv_queue_avg',
  'wsrep_flow_control_paused'
);

wsrep_local_cert_failures — write-sets this node originated that lost certification. It increments when a transaction started on this node was rolled back because a remote write-set with a lower sequence number claimed an overlapping key first. Read the delta per minute. A flat delta under load is healthy; a delta that climbs while write volume is steady means real key contention has appeared — usually a newly hot row or a schema change that widened a key footprint.

wsrep_local_bf_aborts — local transactions “brute-force aborted” by an earlier-ordered write-set. It counts the same collisions from the victim’s side: a transaction already executing locally was killed to make way for a higher-priority, earlier-ordered remote write-set. In practice wsrep_local_cert_failures and wsrep_local_bf_aborts move together and both point at hot-row contention; a rising bf_aborts delta is the clearest confirmation that transactions are genuinely colliding rather than failing for some other reason.

wsrep_cert_deps_distance — the average distance between dependent transactions in the ordered stream. This is how many write-sets can, on average, be applied in parallel because they do not depend on each other. A value near 1 means the workload is effectively serial and adding apply threads is pointless; a value of 40 means up to roughly 40 write-sets are independent and you can profitably raise wsrep_slave_threads toward that number. It is the sizing input for apply parallelism, not a health alarm — read it as a design signal.

wsrep_cert_index_size — the number of keys currently held in the certification index. The index retains the keys of recently ordered write-sets so new ones can be checked against them; its size grows with the depth of the certification window and the key footprint of in-flight transactions. A steadily climbing wsrep_cert_index_size that does not fall back signals long-running transactions or a deep receive queue holding the window open, which is a memory-pressure warning worth correlating with wsrep_local_recv_queue_avg.

Parameter / Metric Reference

Metric Read as Healthy Alert / action threshold What a breach means
wsrep_local_cert_failures counter delta flat under steady write load rising delta at constant write volume New key contention; apply the hot-table fixes
wsrep_local_bf_aborts counter delta flat under steady write load rising delta tracking cert_failures Confirmed transaction collisions on hot rows
wsrep_cert_deps_distance absolute average matches your wsrep_slave_threads ≈ 1 (serial) or ≫ threads (under-provisioned) Re-size apply parallelism, not an incident
wsrep_cert_index_size memory gauge stable, oscillating sustained upward trend without decline Long transactions or deep queue holding the window open
wsrep_local_recv_queue_avg gauge < 1.0 sustained > 1.0 Apply pressure; correlate with flow control
wsrep_flow_control_paused fraction (self-windowing) < 0.05 sustained > 0.05 The apply/certification pipeline is throttling writers

The abort counters share a threshold philosophy: there is no “good” absolute number, only a good rate. A Galera cluster that has served traffic for a month will show millions of cumulative bf_aborts and be perfectly healthy — what matters is whether the per-minute delta is flat. wsrep_cert_deps_distance has no alarm threshold at all; it is a tuning input that you compare against your configured thread count, following the parallelism guidance in the wsrep.cnf configuration deep dive.

Verification

Because the two most important metrics are counters, a correct reading requires sampling twice and dividing by elapsed time. This Python 3.9+ probe computes per-second rates for the abort counters, reads the average and gauge directly, and handles the contention codes a busy node returns — 1213 (deadlock / certification failure) and 1205 (lock wait timeout):

import sys
import time
import pymysql

CERT_FAIL_RATE_ALERT = 5.0   # certification failures per second before paging
BF_ABORT_RATE_ALERT  = 5.0   # brute-force aborts per second before paging

COUNTERS = ("wsrep_local_cert_failures", "wsrep_local_bf_aborts")
GAUGES   = ("wsrep_cert_deps_distance", "wsrep_cert_index_size")

def read(cur, names):
    out = {}
    for name in names:
        cur.execute("SHOW GLOBAL STATUS LIKE %s", (name,))
        row = cur.fetchone()
        out[name] = float(row[1]) if row else 0.0
    return out

def certification_report(host: str, window_s: float = 15.0) -> dict:
    conn = pymysql.connect(host=host, user="monitor", password="secret",
                           connect_timeout=5, read_timeout=5)
    try:
        with conn.cursor() as cur:
            first = read(cur, COUNTERS)
            t0 = time.monotonic()
            time.sleep(window_s)
            second = read(cur, COUNTERS)
            gauges = read(cur, GAUGES)
    except pymysql.err.OperationalError as exc:
        # 1213 = certification conflict, 1205 = lock wait timeout: transient.
        if exc.args and exc.args[0] in (1213, 1205):
            print(f"[RETRY] {host}: transient wsrep conflict {exc.args[0]}", file=sys.stderr)
            return {"host": host, "retryable": exc.args[0]}
        raise
    finally:
        conn.close()

    elapsed = time.monotonic() - t0
    rates = {k: (second[k] - first[k]) / elapsed for k in COUNTERS}
    report = {"host": host, "rates_per_s": rates, "gauges": gauges}
    report["alert"] = (
        rates["wsrep_local_cert_failures"] > CERT_FAIL_RATE_ALERT
        or rates["wsrep_local_bf_aborts"] > BF_ABORT_RATE_ALERT
    )
    return report

if __name__ == "__main__":
    r = certification_report(sys.argv[1] if len(sys.argv) > 1 else "127.0.0.1")
    print(r)
    sys.exit(1 if r.get("alert") else 0)

Feed the computed rates and gauges into your scrape pipeline rather than the raw counters, so dashboards show a per-second conflict rate instead of an ever-climbing line — the exporter pattern is built out in building a Prometheus exporter for wsrep status and wired to alerts in monitoring Galera cluster state with Python.

Edge Cases & Gotchas

  • A restart or FLUSH STATUS resets the counters to zero, which looks like a drop. A rate computed across a restart boundary goes sharply negative and can suppress a real alert or fire a false one. Guard the rate math against a decreasing counter — if the second sample is lower than the first, treat it as a reset and skip that interval rather than reporting a negative rate.
  • wsrep_cert_deps_distance reads low during quiet periods and misleads thread sizing. Dependency distance is workload-dependent, so sampling it at 3 a.m. under trickle load reports a low number that undersizes wsrep_slave_threads for the daytime peak. Measure it under representative peak load, not at the trough, before setting apply parallelism.
  • A high wsrep_cert_index_size with flat abort rates is memory pressure, not contention. The two are independent: the index can grow large because a long transaction holds the certification window open even though nothing is colliding. If cert_index_size climbs while bf_aborts stays flat, hunt for a long-running transaction with information_schema.innodb_trx, not for a hot row. When both climb together, tackle the contention first using reducing certification conflicts in hot tables.