Wenn die Replik nicht mehr hinterherkommt

Den Rückstand richtig messen

Seconds_Behind_Source ist der Wert, den fast alle ablesen, und er ist mit Vorsicht zu genießen. Das Handbuch nennt die Fälle selbst: Er kann 0 zeigen, obwohl die Netzverbindung zur Quelle abgerissen ist und der Empfänger es noch nicht bemerkt hat. Er zeigt 0, solange der Anwenderprozess alles verarbeitet hat, was empfangen wurde, auch wenn auf der Quelle längst mehr passiert ist. Und bei Ereignissen mit alten Zeitstempeln wechselt er bei mehreren Aufrufen kurz hintereinander zwischen 0 und einem großen Wert.

Die belastbare Messung steht im Performance Schema. In replication_applier_status_by_worker liefert APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP den Zeitpunkt, zu dem die gerade angewendete Transaktion auf dem Ursprungsserver bestätigt wurde, LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP dasselbe für die zuletzt fertige. Die Differenz zur aktuellen Uhrzeit ist der Rückstand, und weil der Ursprungszeitstempel weitergereicht wird, stimmt er auch in einer mehrstufigen Kette.

Für die Trennung zwischen Empfang und Anwendung ist replication_connection_status zuständig. Dort steht, welche Transaktion zuletzt in die Warteschlange gestellt wurde und wann der letzte Lebenszeichen-Impuls von der Quelle kam. Liegt die zuletzt eingereihte Transaktion weit vor der zuletzt angewendeten, ist die Anwendungsseite die Bremse. Hinkt schon der Empfang hinterher, liegt es an Netz oder Bandbreite, und dort hilft die Komprimierung der Übertragung über binlog_transaction_compression auf der Quelle.

Warum eine Replik langsamer ist als die Quelle, obwohl die Hardware gleich ist

Auf der Quelle arbeiten hunderte Verbindungen gleichzeitig. Auf der Replik wird dieselbe Arbeit von einer begrenzten Zahl an Worker-Prozessen erledigt, und ob diese wirklich parallel arbeiten können, hängt daran, welche Abhängigkeitsinformationen im Binärprotokoll stehen. Genau hier sitzt der häufigste Denkfehler: Die entscheidende Einstellung liegt auf der Quelle, nicht auf der Replik.

In MySQL 8.0 musste dafür binlog_transaction_dependency_tracking auf der Quelle auf WRITESET gesetzt werden. Dann vermerkt der Server, welche Zeilen eine Transaktion berührt hat, und Transaktionen ohne Überschneidung dürfen auf der Replik gleichzeitig angewendet werden. Ohne diese Einstellung galt nur die Bestätigungsreihenfolge als Maßstab, und das reicht in einer Anwendung mit kurzen, sequenziellen Transaktionen kaum für Parallelität. Mit MySQL 8.4 hat Oracle die Variable entfernt und das Verhalten fest eingebaut, die Quelle erzeugt die Abhängigkeitsinformation seitdem immer.

Auf der Replikseite kommen replica_parallel_workers für die Zahl der Worker und replica_preserve_commit_order hinzu. Letzteres erzwingt, dass Transaktionen in derselben Reihenfolge bestätigt werden wie auf der Quelle. Das ist die Voraussetzung dafür, dass Lesevorgänge auf der Replik einen konsistenten Zustand sehen, kostet aber Parallelität, weil ein schneller Worker auf einen langsamen vor ihm warten muss.

Was jenseits der Replikation bremst

Repliken werden oft kleiner ausgestattet als die Quelle, weil sie ja nur nachvollziehen, was dort schon passiert ist. Der Denkfehler steckt im Pufferpool. Jedes Zeilenereignis muss die betroffene Seite finden, und wenn sie nicht im Speicher liegt, wird sie von der Platte gelesen. Auf der Quelle war dieselbe Seite warm, weil die Anwendung ohnehin darauf zugreift. Eine Replik mit halbem Arbeitsspeicher wendet deshalb nicht halb so schnell an, sondern deutlich langsamer.

Dazu kommt die Zweitnutzung. Läuft auf der Replik nachts die Sicherung und tagsüber der Berichtsbetrieb, konkurrieren beide mit dem Anwenderprozess um dieselbe Ein- und Ausgabe. Ein weiterer Hebel sind zwei Einstellungen für die Schreibsicherheit: sync_binlog und innodb_flush_log_at_trx_commit stehen für den sicheren Betrieb beide auf 1 und kosten damit je Transaktion einen erzwungenen Plattenzugriff. Auf 0 beziehungsweise 2 gesetzt, schafft die Replik deutlich mehr Transaktionen je Sekunde. Das ist eine bewusste Abwägung und keine Optimierung nebenbei, denn eine so eingestellte Replik kann nach einem harten Ausfall nicht mehr aufsetzen und muss neu aufgebaut werden.

Der Fall, den man nur mit einem Blick ins Schema findet

Zeilenbasierte Replikation überträgt für ein UPDATE oder DELETE den Zustand der Zeile vor und nach der Änderung. Auf der Replik muss die passende Zeile erst gefunden werden. Das Handbuch beschreibt den Ablauf klar: Gibt es einen Primärschlüssel oder einen eindeutigen Index, in dem alle Spalten NOT NULL sind, wird darüber zugegriffen. Fehlt beides, baut die Replik eine Hashtabelle über die zu ändernden Zeilen auf und geht anschließend die gesamte Tabelle durch.

Der Unterschied in der Laufzeit ist dramatisch. Auf der Quelle findet ein Indexzugriff die Zeile in wenigen Schritten, auf der Replik wird eine Tabelle mit Millionen Zeilen einmal komplett gelesen. Weil das für jedes Zeilenereignis geschieht, kann ein Vorgang, der auf der Quelle Sekunden brauchte, auf der Replik Stunden dauern.

Tabellen ohne Primärschlüssel sind selten Absicht, sie entstehen bei Zwischen- und Protokolltabellen, die jemand schnell angelegt hat. Ein Abgleich zwischen information_schema.TABLES und information_schema.TABLE_CONSTRAINTS zeigt sie. Bevor du einen Primärschlüssel ergänzt, prüf, ob die Tabelle überhaupt repliziert werden muss, denn oft ist die richtige Antwort, sie von der Replikation auszunehmen.

Wann ein Neuaufbau schneller ist als Aufholen

Die Entscheidung hängt an einer Beobachtung über die Zeit. Miss den Rückstand über eine Stunde in gleichmäßigen Abständen. Sinkt er, holt die Replik auf, und du kannst rechnen, wann sie fertig ist. Bleibt er gleich, arbeitet sie genau am Limit, und jede zusätzliche Last auf der Quelle vergrößert den Abstand wieder. Wächst er gleichmäßig, ist die Anwendungsleistung dauerhaft zu klein, und Warten ändert daran nichts.

Im dritten Fall ist der Neuaufbau der schnellere Weg. Mit der Anweisung CLONE INSTANCE kopiert MySQL eine vollständige Instanz auf einen neuen Server, einschließlich der Position, an der die Replikation weiterläuft. Alternativ dient eine physische Sicherung mit vermerkter Position als Ausgangspunkt. Beides ist in der Regel um Größenordnungen schneller, als Tage an Binärprotokollen nachzuspielen.

Die Frist dafür setzt die Aufbewahrungsdauer der Binärprotokolle auf der Quelle, gesteuert über binlog_expire_logs_seconds. Sind die Dateien gelöscht, die die Replik noch bräuchte, entfällt die Wahl und es bleibt nur der Neuaufbau. Deshalb gehört diese Einstellung zum Notfallkonzept: Sie muss die längste Ausfallzeit abdecken, mit der du rechnest, und der Plattenplatz dafür muss vorhanden sein.