MySQL 8 DDL ohne Ausfallzeit: ALTER TABLE auf großen Produktionstabellen ausführen

Der Ausfall beginnt fast immer gleich. Eine Migration, die gegen die Staging-Datenbank in zwei Sekunden durchlief, wo die Tabelle vierzigtausend Zeilen hielt, wird gegen die Produktion ausgerollt, wo dieselbe Tabelle neunzig Millionen Zeilen enthält. Wenige Sekunden später beginnt die Anwendung, in Timeouts zu laufen, der Connection-Pool ist ausgereizt, und der Bereitschaftsingenieur starrt auf eine Query mit dem Wort ALTER TABLE und einen Metadata-Lock, der nicht loslässt. Ein ALTER TABLE in MySQL 8 gegen eine große, stark ausgelastete Produktionstabelle ist eine der wenigen Operationen, die ein gesundes System in unter einer Minute lahmlegen können, und sie tut das leise, weil technisch nichts kaputt ist. Die Schemaänderung hält schlicht einen Lock, hinter dem sich nun jede andere Query staut.

Die gute Nachricht: Das ist ein gelöstes Problem. MySQL 8 liefert echtes Online-DDL, und wo die eingebaute Unterstützung aufhört, füllen ausgereifte externe Werkzeuge die Lücke. Was Teams, die Schemata ruhig ändern, von Teams unterscheidet, die Wartungsfenster planen und beten, ist kein Glück. Es ist das Wissen, welche Operationen sich sicher an Ort und Stelle ausführen lassen, welche sperren und welche ein Copy-and-Swap-Werkzeug brauchen. Dieser Beitrag geht alle drei durch.

Warum ALTER TABLE in MySQL 8 sperrt, wenn du es am wenigsten erwartest

Vor MySQL 5.6 baute nahezu jedes ALTER TABLE die gesamte Tabelle neu auf und hielt für die ganze Dauer einen exklusiven Lock. MySQL 8 ist deutlich besser, aber die Verbesserung ist ungleichmäßig, und genau diese Ungleichmäßigkeit ist die Falle. Manche Operationen laufen sofort als reine Metadaten-Änderung. Manche laufen online und kopieren Daten im Hintergrund, während Lese- und Schreibzugriffe weiterlaufen. Und manche bauen die Tabelle weiterhin unter einem Lock neu auf, der alles blockiert.

MySQL klassifiziert jede DDL-Operation nach zwei Eigenschaften, die über dein Schicksal entscheiden. Die erste ist der Algorithmus: INSTANT, INPLACE oder COPY. Die zweite ist, ob die Operation gleichzeitiges DML erlaubt, während sie läuft. Eine INSTANT-Änderung, etwa das Anhängen einer Spalte am Ende der Tabelle in neueren MySQL-8-Releases, berührt nur Metadaten und ist in Millisekunden fertig, unabhängig von der Tabellengröße. Eine INPLACE-Änderung, etwa das Hinzufügen eines Sekundärindex, baut Indexstrukturen neu auf, lässt aber im Allgemeinen Lese- und Schreibzugriffe weiterlaufen. Eine COPY-Änderung baut die ganze Tabelle neu auf und blockiert Schreibzugriffe für die gesamte Kopie, was auf einer Tabelle mit neunzig Millionen Zeilen Minuten effektiver Ausfallzeit bedeuten kann.

Der gefährliche Teil ist, dass MySQL stillschweigend auf einen teureren Algorithmus zurückfällt, wenn der von dir angenommene für diese konkrete Änderung nicht unterstützt wird. Du glaubst, du führst ein sofortiges Spalten-Add aus; MySQL entscheidet, dass eine vollständige Kopie nötig ist, wegen einer Spaltenposition oder einer Row-Format-Beschränkung, und du erfährst es in der Produktion. Die Verteidigung ist, nie zu raten. Gib Algorithmus und Lock-Level explizit an und lass MySQL das Statement ablehnen, wenn es sie nicht einhalten kann:

ALTER TABLE orders
  ADD COLUMN fulfilled_at DATETIME NULL,
  ALGORITHM=INSTANT;

Kann MySQL das nicht als sofortige Änderung ausführen, schlägt das Statement sofort mit einem Fehler fehl, statt die Tabelle klammheimlich neu aufzubauen. Dieser Fehlschlag ist ein Geschenk. Er passiert im Code-Review oder in einem Migrations-Trockenlauf, nicht um 15 Uhr unter Spitzenlast. Mach ALGORITHM=INSTANT oder ALGORITHM=INPLACE, LOCK=NONE zum expliziten Bestandteil jeder Migration und behandle jedes Statement, das diese Klauseln nicht akzeptiert, als Statement, das eine andere Strategie braucht.

Die Operationen, die wirklich sicher an Ort und Stelle laufen

Ein nützliches mentales Modell ist, jede Schemaänderung in drei Kategorien einzusortieren, bevor sie überhaupt die Produktion erreicht. Die erste Kategorie sind sofortige oder nahezu sofortige Operationen, die du jederzeit, auf jeder Tabelle, unter Last ausführen kannst. In MySQL 8 zählen dazu das Hinzufügen einer nullbaren Spalte, das Hinzufügen einer Spalte mit Default in neueren Versionen, das Umbenennen einer Spalte, das Löschen einer Spalte ab 8.0.29 über den Instant-Algorithmus, das Setzen oder Entfernen eines Spalten-Defaults und das Umbenennen eines Index. Diese berühren Metadaten oder laufen an Ort und Stelle und gefährden die Verfügbarkeit nicht nennenswert.

Die zweite Kategorie sind Online-, aber teure Operationen. Das Hinzufügen oder Entfernen eines Sekundärindex, das Ändern einer Spalte in einen kompatiblen Typ und das Hinzufügen eines Fremdschlüssels laufen im Allgemeinen mit ALGORITHM=INPLACE und LOCK=NONE, sodass gleichzeitiger Verkehr weiterläuft, aber sie bauen dennoch erhebliche Strukturen neu auf und verbrauchen IO und CPU. Auf einer großen Tabelle können diese viele Minuten laufen. Sie sind in dem Sinne sicher, dass sie nicht blockieren, aber sie sind nicht kostenlos. Führe sie in verkehrsärmeren Zeiten aus, beobachte den Replikations-Lag und sei auf erhöhte Latenz gefasst, während sie voranschreiten.

Die dritte Kategorie sind die Operationen, die in MySQL 8 weiterhin eine vollständige Tabellenkopie erfordern: das Ändern einer Spalte in einen inkompatiblen Typ, das Ändern des Primärschlüssels, das Konvertieren des Zeichensatzes und bestimmte Row-Format-Änderungen. Das sind die, die sperren, und das sind die, die du nicht mit einem einfachen ALTER TABLE auf einer großen Produktionstabelle ausführen darfst. Sie brauchen ein externes Online-Schemaänderungswerkzeug.

Die Disziplin, die Zwischenfälle verhindert, ist schlicht, jede Migration zuerst durch diese Triage laufen zu lassen. Ein kurzer Abgleich deiner ausstehenden Migrationen gegen diese Kategorien fängt die gefährlichen ab, bevor sie ausgeliefert werden, und ist genau die Art Review, die wir in unsere Arbeit im Code-Quality-Consulting einbetten, wenn die Deployment-Pipeline eines Teams immer wieder Überraschungen produziert.

Wann du pt-online-schema-change oder gh-ost brauchst

Für die dritte Kategorie ist die zugrunde liegende Technik immer dieselbe. Du kannst die Tabelle nicht an Ort und Stelle ändern, ohne zu sperren, also baust du eine neue Tabelle mit dem gewünschten Schema, kopierst die vorhandenen Zeilen in kleinen Batches hinein, fängst alle Änderungen ab, die während der Kopie passieren, und tauschst dann die neue Tabelle in einem einzigen atomaren Rename gegen die alte aus. Zwei Werkzeuge haben diese Technik langweilig und zuverlässig gemacht: pt-online-schema-change von Percona und gh-ost von GitHub.

pt-online-schema-change, meist zu pt-osc verkürzt, nutzt Trigger. Beim Start erstellt es die neue Tabelle und installiert AFTER INSERT-, AFTER UPDATE- und AFTER DELETE-Trigger auf der Originaltabelle, sodass jeder Live-Schreibzugriff in die Kopie gespiegelt wird, während sich das Werkzeug durch die vorhandenen Zeilen batcht. Ein typischer Aufruf sieht so aus:

pt-online-schema-change \
  --alter "ADD COLUMN fulfilled_at DATETIME NULL" \
  --execute \
  D=shop,t=orders

Der triggerbasierte Ansatz ist robust und praxiserprobt, aber die Trigger fügen jeder Transaktion, die die Tabelle berührt, während die Migration läuft, Schreib-Overhead hinzu, und sie vertragen sich schlecht mit Tabellen, die bereits Trigger haben. Auf einer sehr schreibintensiven Tabelle fällt dieser Overhead ins Gewicht.

gh-ost geht einen anderen Weg. Statt Trigger liest es das MySQL-Binärlog und spielt die Änderungen der Tabelle von außerhalb der Datenbank auf die Kopie ein. Weil es keine Trigger installiert, fügt es der Originaltabelle keinen synchronen Schreib-Overhead hinzu, und entscheidend: Es lässt sich im laufenden Betrieb pausieren und drosseln. Du kannst es verlangsamen oder ganz stoppen, wenn es die Produktion zu beeinträchtigen beginnt, und dann fortsetzen:

gh-ost \
  --database="shop" \
  --table="orders" \
  --alter="ADD COLUMN fulfilled_at DATETIME NULL" \
  --max-load=Threads_running=40 \
  --critical-load=Threads_running=80 \
  --execute

Die praktische Wahl zwischen beiden hängt meist von deiner Replikationstopologie und deinem Schreibvolumen ab. gh-ost glänzt auf schreibintensiven Primaries und wenn du feingranulares, beobachtbares Drosseln gegen den Replikations-Lag willst. pt-osc ist der sicherere Standard, wenn du mit Fremdschlüsseln umgehen musst, da gh-osts Umgang mit Fremdschlüsseln bewusst konservativ ist. Beide Werkzeuge führen am Ende denselben atomaren Cut-over aus, und beide verwandeln eine sperrende Operation in eine langlaufende, aber nicht blockierende.

Die Locking-Fallstricke, die dich weiterhin erwischen

Selbst mit dem richtigen Werkzeug verursacht eine Handvoll Fallstricke die meisten verbleibenden Zwischenfälle. Der erste sind Metadata-Locks. Jedes ALTER TABLE, selbst ein sofortiges, braucht kurz einen Metadata-Lock, und es kann diesen Lock nicht erwerben, solange eine langlaufende Transaktion die Tabelle offen hält. Eine Reporting-Query oder eine vergessene offene Transaktion kann deine Migration blockieren, und schlimmer noch: Deine Migration sitzt nun in der Warteschlange und hält ihren eigenen ausstehenden Lock, hinter dem sich jede neue Query stapelt. Setze immer einen kurzen Lock-Wait-Timeout, damit ein blockiertes DDL schnell fehlschlägt, statt eine Warteschlange aufzubauen: SET SESSION lock_wait_timeout = 5; vor dem Statement verwandelt einen stillen Rückstau in einen sauberen, wiederholbaren Fehler.

Der zweite Fallstrick ist der atomare Cut-over selbst. Sowohl pt-osc als auch gh-ost enden mit einem Rename, das einen kurzzeitigen exklusiven Lock braucht. Er ist kurz, meist Millisekunden, aber wenn eine lange Transaktion die Tabelle hält, kann selbst dieser kurze Lock ins Stocken geraten. Führe den Cut-over aus, wenn du bestätigen kannst, dass keine langen Transaktionen unterwegs sind, und nutze die eingebauten Cut-over-Steuerungen der Werkzeuge, statt es zu erzwingen.

Der dritte Fallstrick sind Replikations-Lag und Festplatte. Eine Copy-and-Swap-Migration dupliziert die gesamte Tabelle auf der Festplatte, bevor sie das Original löscht, sodass eine Tabelle, die die Hälfte deines freien Speichers ausmacht, auf diese Weise nicht migriert werden kann, bis du Kapazität hinzufügst. Und die zeilenweise Kopie erzeugt ein großes Volumen replizierter Schreibvorgänge; auf einer knapp bemessenen Replica kann allein das den Lag über akzeptable Schwellen treiben und nachgelagerte Read-after-write-Erwartungen brechen. Drossle auf den Replikations-Lag, nicht nur auf die Primary-Last, und stelle sicher, dass du auf jeder Replica Spielraum hast, bevor du startest.

Der vierte sind Fremdschlüssel, die jeden kopierbasierten Ansatz verkomplizieren, weil die Constraints den alten Tabellennamen referenzieren und gegen den neuen neu aufgebaut werden müssen. Das ist der mit Abstand häufigste Grund, warum eine Schemaänderung, die routiniert aussah, zu einer sorgfältigen, werkzeugspezifischen Prozedur wird. Wenn dein Schema stark auf Fremdschlüsseln und großen Tabellen zugleich beruht, verdient diese Kombination eher ein bewusstes Review als eine Ad-hoc-Migration.

Das in deine Auslieferung einbauen

Die Teams, die Schemata ohne Drama ändern, sind nicht mutiger. Sie haben schlicht die Entscheidung nach vorne verlegt. Jede Migration wird vor dem Review in sofort, online oder Copy-and-Swap trilagiert. Jedes In-Place-ALTER TABLE in MySQL 8 trägt eine explizite ALGORITHM- und LOCK-Klausel, sodass die Datenbank alles Unerwartete ablehnt. Jedes Copy-and-Swap läuft über gh-ost oder pt-osc mit Drosselung, die an den Replikations-Lag gekoppelt ist. Und jede dieser Migrationen läuft zuerst gegen einen produktionsgroßen Datensatz, nie gegen eine winzige Staging-Tabelle, die die Kosten verbirgt.

Wenn deine Migrationen weiterhin Überraschungen produzieren oder du ein Legacy-MySQL-Schema mitschleppst, das jede Änderung riskant erscheinen lässt, ist das meist ein Zeichen, dass Datenmodell und Deployment-Prozess gemeinsam Aufmerksamkeit brauchen statt getrennt. Wir helfen Teams, genau das zu entwirren, durch unsere Arbeit in der Legacy-Code-Optimierung und der individuellen Softwareentwicklung und verwandeln Schemaänderungen von einem vierteljährlichen Wartungsfenster in ein routiniertes Deployment.

Wenn du ein zweites Paar Augen auf eine Migrationsstrategie werfen lassen willst, bevor sie ausgeliefert wird, schreib uns an hello@wolf-tech.io oder lies mehr darüber, wie wir arbeiten, auf wolf-tech.io. Ein kurzes Gespräch vor dem nächsten großen ALTER TABLE ist deutlich günstiger als das Postmortem danach.