Deriving RPO Targets from Binlog Archiving Lag #

Recovery point objective is the amount of committed work you would lose if you had to recover right now, and it is almost always quoted rather than measured — “we lose at most a few seconds” is a hope, not a metric. The real number is computable at any instant: it is the age of the newest transaction that is safely archived, which equals the lag between the primary’s gtid_executed and the newest GTID confirmed in object storage. This page derives RPO from that archiving lag: it samples the two coordinates, computes the transaction and time delta between them, and turns it into a monitored seconds-of-exposure number that alerts before it breaches your objective. Unlike RTO, RPO is not measured by a drill — it is a continuous property of the archiving pipeline.

Context & Prerequisites #

This is the RPO-derivation companion to RTO/RPO Recovery Drills for MySQL Point-in-Time Recovery, and it reads the same archive manifest produced by Automated Binlog Archiving to Object Storage. You need MySQL 8.0.22+ with gtid_mode=ON, Python 3.10+, read access to the primary’s status and to the manifest, and the archiving pipeline’s confirmed-upload records. The lag you measure here is the same signal the archiver tracks as its primary SLI; this page turns it into a recovery objective.

Step-by-Step Implementation #

1. Sample the two coordinates #

Read the primary’s newest committed GTID set and the newest GTID confirmed durable in the archive.

# Python 3.10+
def sample_coordinates(conn, manifest) -> tuple[str, str]:
    cur = conn.cursor()
    cur.execute("SELECT @@GLOBAL.gtid_executed")     # MySQL 8.0.22+
    primary = cur.fetchone()[0]
    cur.close()
    archived = manifest.newest_confirmed_gtid()      # last line whose upload verified
    return primary, archived

PITR relevance: the archived coordinate must be the newest confirmed one — a segment mid-upload does not count, because a recovery cannot use it. Counting unconfirmed uploads understates real exposure.

2. Compute the transaction gap #

Use GTID_SUBTRACT to find exactly which transactions exist on the primary but not yet in the archive.

-- MySQL 8.0.22+  — transactions committed but not yet safely archived
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, :archived_gtid) AS at_risk;

PITR relevance: this set is precisely the data you would lose in a recover-now scenario. Its cardinality is the RPO expressed in transactions; its oldest member’s age is the RPO expressed in time.

3. Translate the gap to seconds of exposure #

Map the oldest at-risk transaction to its commit timestamp (from the primary’s binlog or the archiver’s rotation record) and subtract from now.

# Python 3.10+
import time

def rpo_seconds(at_risk_oldest_commit_epoch: float | None) -> float:
    if at_risk_oldest_commit_epoch is None:
        return 0.0                       # nothing at risk: archive is caught up
    return round(time.time() - at_risk_oldest_commit_epoch, 2)

PITR relevance: seconds-of-exposure is the number stakeholders understand and SLAs are written against; transactions-at-risk is the number engineers debug with. Publish both.

4. Alert against the objective with headroom #

Compare the derived RPO to the objective and page before it breaches, not after.

# Python 3.10+
def rpo_status(rpo: float, objective_seconds: float) -> str:
    if rpo <= objective_seconds * 0.7:
        return "green"
    return "amber" if rpo <= objective_seconds else "red"

PITR relevance: archiving lag climbs before it breaches — a throttled bucket, a stalled worker — so an amber band bought by the 0.7 factor is your window to intervene before real exposure exceeds the promise.

Configuration Snippet & Reference Table #

Verification Checklist #

• The archived coordinate is the newest confirmed upload, not an in-flight one.
• The at-risk set is computed with GTID_SUBTRACT(primary, archived), not a file:position diff.
• RPO is published in both seconds and transactions-at-risk.
• An empty at-risk set reports RPO of zero (archive fully caught up).
• Alerting fires at an amber threshold below the objective, not only on breach.
• The RPO sample rate is frequent enough to catch a rising trend within the objective window.

Gotchas & Version-Specific Caveats #

Counting unconfirmed uploads understates RPO. A segment that is uploading but not yet checksum-verified cannot satisfy a recovery, so it does not reduce exposure. Always measure from the newest confirmed archived GTID.

Replication lag is not archiving lag. A replica being caught up says nothing about whether logs are in object storage. RPO is about the archive horizon, not the replication horizon — do not substitute Seconds_Behind_Source.

Clock skew between primary and archiver. If commit timestamps come from the primary and “now” from the archiver, a skewed clock distorts the seconds figure; sync both to NTP and prefer the primary’s own clock for both endpoints where possible.

A quiet database can look caught up while broken. If no writes occur, the at-risk set is empty even if archiving has silently stopped. Cross-check with a heartbeat write or the archiver’s last-success timestamp so a stalled pipeline on an idle primary is still detected.

Frequently Asked Questions #

Related #

• RTO/RPO Recovery Drills — the parent that pairs this RPO signal with measured RTO.
• Measuring RTO with Scheduled PITR Restore Drills — the duration half of the objective pair.
• Binlog Retention Boundaries — retention math that must stay ahead of archiving lag.

Back to RTO/RPO Recovery Drills.