Measuring RTO with Scheduled PITR Restore Drills #

“We can recover in about an hour” is a sentence with no evidence behind it until a drill produces the number. Recovery time objective is a duration, and the only honest way to know it is to run the full restore-and-replay on a schedule and time each phase. This page builds the per-phase timing harness: it stamps the boundaries between provisioning a target, restoring the base backup, replaying the archived binary logs to a chosen point, and verifying the result, then stores those durations as a time series so a regression — a restore that has quietly doubled, a replay that no longer keeps pace — pages before a real incident exposes it. The output is not a single number but a breakdown that tells you which phase to optimize when RTO drifts.

Context & Prerequisites #

This is the RTO-measurement specialization of the drill harness in RTO/RPO Recovery Drills for MySQL Point-in-Time Recovery; the scheduling, teardown, and pass/fail semantics there apply here. You need MySQL 8.0.22+, Python 3.10+, a base backup source, the archived manifest from Automated Binlog Archiving to Object Storage, and disposable infrastructure to provision a throwaway target. RTO is measured from “decide to recover” (restore start) to “verified ready” (contiguity confirmed), so those two boundaries must be stamped precisely.

Step-by-Step Implementation #

1. Stamp phase boundaries with a monotonic clock #

Use time.monotonic() for durations so an NTP step during the drill cannot corrupt a measurement.

# Python 3.10+
import time
from contextlib import contextmanager

@contextmanager
def timed(store: dict, phase: str):
    t0 = time.monotonic()
    try:
        yield
    finally:
        store[phase] = round(time.monotonic() - t0, 2)

PITR relevance: per-phase timings localize a regression — a slow restore points at backup transport, a slow replay points at target I/O or archive fetch — so you optimize the phase that actually moved.

2. Run the phases and accumulate the RTO #

The measured RTO window spans restore through verify; provisioning is often excluded if a warm target pool exists, but record it either way.

# Python 3.10+
def measure_rto(target, backup, archive, verifier, point: str) -> dict:
    t = {}
    with timed(t, "provision"):
        target.provision()
    rto0 = time.monotonic()
    with timed(t, "restore"):
        coord = backup.restore_latest(target); target.seed_gtid_purged(coord)
    with timed(t, "replay"):
        target.replay(archive.resolve(coord, point), point)
    with timed(t, "verify"):
        ok = verifier.contiguous(target, coord, point)
    t["rto_seconds"] = round(time.monotonic() - rto0, 2)
    t["ok"] = ok
    return t

PITR relevance: excluding provisioning from the headline RTO is only honest if you actually keep a warm target ready; otherwise provisioning is part of your real recovery time and must be counted.

3. Store durations as a time series and compare to budget #

Persist each drill’s phase map keyed by timestamp, and compare the RTO against your SLA budget.

# Python 3.10+
def evaluate(result: dict, budget_seconds: float) -> str:
    match result:
        case {"ok": True, "rto_seconds": r} if r <= budget_seconds:
            return "green"
        case {"ok": True, "rto_seconds": r} if r <= budget_seconds * 1.2:
            return "amber"      # within 20% — investigate before it breaches
        case _:
            return "red"

PITR relevance: an amber band gives you warning before an SLA breach, turning RTO management into a trend you steer rather than a threshold you trip.

Configuration Snippet & Reference Table #

# drill target — MySQL 8.0.22+  (disposable: relax durability to measure realistic apply time)
[mysqld]
innodb_flush_log_at_trx_commit = 2
sync_binlog                    = 0
innodb_buffer_pool_size        = 8G

Verification Checklist #

• Durations use a monotonic clock, immune to NTP steps mid-drill.
• The RTO window is measured from restore start to verify complete.
• Provisioning time is recorded and only excluded from headline RTO if a warm pool truly exists.
• Each phase duration is stored as a time series for regression analysis.
• RTO is compared to the SLA budget with an amber band before the red breach.
• A drill that fails verification is red regardless of how fast it ran.

Gotchas & Version-Specific Caveats #

A suspiciously fast drill usually applied nothing. If replay time is near zero, the resolved segment list was probably empty — assert a non-zero applied-transaction count so an empty replay reads as red, not green.

Wall-clock timing corrupts on NTP steps. Always use time.monotonic() for durations; reserve wall-clock time only for the timestamp key.

Relaxed durability skews the number in your favor only if production matches. Measuring apply time with sync_binlog=0 is realistic for a disposable target, but if your real recovery target must be durable during replay, measure it that way instead.

8.4 variable renames. slave_* variables are replica_* on 8.4; a drill script pinned to the old spelling errors on a newer target.

Frequently Asked Questions #

Related #

• RTO/RPO Recovery Drills — the parent drill harness and scheduling model.
• Deriving RPO Targets from Binlog Archiving Lag — the other half of the recovery-objective pair.
• mysqlbinlog Replay Scripting — the replay engine whose duration you are measuring.

Back to RTO/RPO Recovery Drills.