Online DDL in MySQL: ALGORITHM=INSTANT vs. INPLACE vs. COPY verstehen

1. Warum der ALGORITHM-Parameter bei ALTER TABLE entscheidend ist

Vor MySQL 5.6 kannte der Server bei praktisch jeder Schemaänderung nur einen Weg: die betroffene Tabelle komplett neu aufbauen. Eine neue Spalte, ein neuer Index, eine geänderte Spaltenreihenfolge, all das führte zu einer vollständigen Tabellenkopie mit exklusivem Lock für die gesamte Dauer der Operation. Bei einer catalog_product_entity mit mehreren Millionen Zeilen bedeutete das im Zweifel Stunden mit gesperrtem Schreibzugriff.

Mit dem Online-DDL-Framework, das seit MySQL 5.6 existiert und in 8.0 kontinuierlich erweitert wurde, unterscheidet der Server bei jeder unterstützten ALTER-TABLE-Operation zwischen drei Algorithmen: INSTANT, INPLACE und COPY. Welcher Algorithmus tatsächlich zum Einsatz kommt, hängt nicht vom Willen des Entwicklers ab, sondern strikt von der konkreten Operation. Wer den Unterschied nicht kennt, verlässt sich blind auf den vom Optimizer automatisch gewählten Standardalgorithmus und wird von langen Laufzeiten überrascht.

2. ALGORITHM=COPY: die teuerste und langsamste Variante

COPY ist historisch der einzige verfügbare Algorithmus und bleibt bis heute der Fallback für alle Operationen, die weder instant noch inplace unterstützt werden. Der Server legt eine komplett neue, leere Tabelle mit der Zielstruktur an, kopiert Zeile für Zeile aus der alten in die neue Tabelle, baut sämtliche Indizes neu auf und tauscht am Ende beide Tabellen atomar aus. Während der gesamten Kopierphase wird zwar lesender Zugriff meist noch toleriert, aber schreibender Zugriff blockiert.

Der Speicherplatzbedarf verdoppelt sich temporär, da alte und neue Tabelle parallel existieren, bevor die alte Version gelöscht wird. Bei einer 80 Gigabyte großen sales_order_grid bedeutet das kurzfristig 160 Gigabyte freien Plattenplatz, nur um eine einzige Spalte umzubenennen, falls MySQL aus irgendeinem Grund auf COPY zurückfällt. Operationen wie das Ändern der Zeichenkodierung einer ganzen Tabelle oder bestimmte Änderungen an Fremdschlüsseln zwingen den Server auch in MySQL 8.0 noch in diesen Modus.

3. ALGORITHM=INPLACE: kein Rebuild, aber auch nicht kostenlos

INPLACE vermeidet die vollständige Tabellenkopie auf SQL-Ebene, arbeitet aber intern trotzdem oft mit einem Rebuild der Datendatei, nur eben innerhalb der bestehenden Tabellenstruktur und ohne temporäre Zweittabelle im Datenverzeichnis. Während der Operation lässt InnoDB parallele DML-Anweisungen zu und protokolliert sie in einem sogenannten Row-Log, der nach Abschluss der eigentlichen Umbauarbeit auf die neue Struktur angewendet wird. Am Ende steht nur noch ein kurzer exklusiver Metadaten-Lock für den finalen Austausch.

Das klingt nach einer fast kostenlosen Operation, ist es aber nicht immer. Das Hinzufügen eines sekundären Index etwa läuft zwar inplace, erzeugt aber vollen I/O-Aufwand, weil der komplette Index von Grund auf sortiert und aufgebaut werden muss. Bei einer sehr schreiblastigen Tabelle kann der Row-Log zudem an seine konfigurierte Obergrenze innodb_online_alter_log_max_size stoßen, was die gesamte, unter Umständen stundenlange Operation mit einem Rollback beendet, ohne dass die Änderung übernommen wurde.

4. ALGORITHM=INSTANT: wenn wirklich nur Metadaten geändert werden

INSTANT ist der jüngste und schnellste der drei Algorithmen. Statt Daten überhaupt anzufassen, ändert der Server ausschließlich einen Eintrag im Data Dictionary und merkt sich intern, welche physischen Zeilen noch im alten Format vorliegen. Erst beim nächsten regulären Zugriff auf eine solche Zeile interpretiert InnoDB sie transparent mit einer sogenannten Instant-Metadaten-Version, ohne die Datei selbst umschreiben zu müssen. Die Laufzeit einer instant-fähigen Operation ist damit unabhängig von der Tabellengröße und liegt praktisch immer im Millisekundenbereich.

Seit MySQL 8.0.12 funktioniert das für das Hinzufügen einer Spalte ans Tabellenende, seit 8.0.29 auch für das Hinzufügen und Entfernen von Spalten an beliebiger Position sowie für das Sichtbar- und Unsichtbarmachen von Spalten. Bei einer mehrere hundert Millionen Zeilen großen url_rewrite-Tabelle ist der Unterschied zwischen einer INSTANT-Operation, die in unter einer Sekunde durchläuft, und einer COPY-Operation, die den Shop stundenlang lahmlegt, geschäftskritisch.

-- Explizit anfordern, dass eine Operation nur INSTANT laufen darf
-- Schlaegt kontrolliert fehl, statt unbemerkt auf COPY zurueckzufallen
ALTER TABLE catalog_product_entity
  ADD COLUMN internal_note VARCHAR(255) NULL,
  ALGORITHM=INSTANT;

-- Pruefen, ob der Server INSTANT tatsaechlich verwendet hat
SELECT NAME, TABLE_ID
FROM information_schema.INNODB_TABLES
WHERE NAME = 'mironsoft/catalog_product_entity';

5. Welche Operationen welchen Algorithmus tatsächlich nutzen

Die konkrete Zuordnung ist in der MySQL-Dokumentation vollständig aufgelistet, in der Praxis lohnt sich aber ein Blick auf die häufigsten Fälle im Magento-Alltag. Spalten ans Ende anfügen, Spaltendefaults ändern, Spalten umbenennen und Indizes umbenennen laufen praktisch immer instant. Einen sekundären Index hinzufügen oder entfernen läuft inplace, ebenso das Ändern bestimmter Zeilenformat-Optionen. Eine Spalte in einen inkompatiblen Datentyp umzuwandeln, etwa von INT auf VARCHAR, oder die gesamte Tabelle auf ein anderes Zeichenkodierungsschema umzustellen, erzwingt weiterhin COPY.

Wichtig ist außerdem: den Primärschlüssel einer Tabelle zu ändern läuft grundsätzlich als Rebuild, weil sich damit die physische Sortierreihenfolge sämtlicher Zeilen in der clusterten InnoDB-Struktur ändert. Selbst wenn MySQL das technisch als INPLACE klassifiziert, ist der I/O-Aufwand vergleichbar mit einer vollständigen Kopie. Wer unsicher ist, sollte die konkrete Operation nicht raten, sondern gegen eine Kopie der Produktionstabelle mit realistischer Zeilenzahl testen, bevor sie im Wartungsfenster live läuft.

6. Algorithmus explizit erzwingen und Laufzeit messen

Statt sich auf die automatische Auswahl von MySQL zu verlassen, sollte jede produktive ALTER-TABLE-Anweisung den erwarteten Algorithmus explizit über die Klausel ALGORITHM= anfordern. Passt die tatsächliche Operation nicht zum angeforderten Algorithmus, bricht der Server sofort mit einer Fehlermeldung ab, statt stillschweigend auf die nächstlangsamere Variante auszuweichen. Das verhindert die häufige Überraschung, bei der ein vermeintlich harmloses ALTER TABLE im Wartungsfenster plötzlich eine volle Tabellenkopie auslöst.

Zusätzlich lohnt sich die Klausel LOCK=NONE, die verlangt, dass parallele Lese- und Schreibzugriffe während der gesamten Operation weiterhin möglich bleiben. Auch hier gilt: unterstützt die konkrete Operation das nicht, bricht der Befehl kontrolliert ab, statt den Shop während eines Deploys unbemerkt für Bestellungen zu sperren.

-- Sicherstellen, dass Schreibzugriffe während des Alters weiterlaufen
ALTER TABLE sales_order_grid
  ADD INDEX idx_customer_email (customer_email),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Laufzeit und Fortschritt einer laufenden Online-DDL-Operation beobachten
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter table%';

7. Row-Format-Voraussetzungen und Grenzen von INSTANT

INSTANT-Operationen setzen voraus, dass die Tabelle im Zeilenformat DYNAMIC oder COMPRESSED vorliegt. Tabellen im veralteten Format COMPACT oder REDUNDANT, wie sie gelegentlich noch aus sehr alten Magento-1-Migrationen übrig sind, unterstützen instant hinzugefügte Spalten nicht und fallen automatisch auf COPY zurück. Ein einmaliger ALTER TABLE ... ROW_FORMAT=DYNAMIC, selbst als teure Einmal-Operation, zahlt sich hier langfristig aus.

Zusätzlich begrenzt InnoDB die Anzahl instant hinzugefügter Spalten pro Tabelle auf 64 zusätzliche Versionen, bevor ein interner Rebuild erzwungen wird. In der Praxis erreicht das kaum ein Magento-Projekt, wichtig ist die Grenze trotzdem bei sehr aktiv gepflegten, EAV-nahen Custom-Tabellen mit vielen kleinen, über Jahre angesammelten Schemaänderungen. Ein weiterer Punkt: Datenbank-Dumps und Downgrades auf ältere Server-Versionen können mit instant erzeugten Zeilenformaten Probleme bekommen, ein sauberer mysqldump mit vollständigem Rebuild der Zieltabelle umgeht das zuverlässig.

8. Auswirkung auf große Magento-Tabellen in der Praxis

Gerade Tabellen wie catalog_product_entity, catalog_product_index_price, sales_order oder url_rewrite wachsen in etablierten Shops schnell auf zweistellige Gigabyte-Größen. Magentos deklaratives Schema in db_schema.xml überlässt die konkrete Wahl des Algorithmus dem Server, ein Modul-Update während setup:upgrade kann also unbemerkt eine COPY-Operation auf einer riesigen Tabelle auslösen und den Deploy-Prozess um Stunden verlängern, ohne dass das im Vorfeld sichtbar wird.

Vor jedem produktiven Modul-Update, das Schemaänderungen an großen Tabellen mitbringt, lohnt sich deshalb ein Testlauf gegen eine aktuelle Kopie der Produktionsdatenbank mit realistischer Zeilenzahl, inklusive Messung der tatsächlichen Laufzeit. Ist absehbar, dass eine Operation zwingend COPY erfordert, etwa eine Typänderung an einer stark frequentierten Spalte, sind externe Werkzeuge wie pt-online-schema-change oder gh-ost oft die bessere Wahl als das native, aber blockierende ALTER TABLE.

9. Fehlgeschlagene Online-DDL-Operationen erkennen und beheben

Auch eine grundsätzlich instant- oder inplace-fähige Operation kann hängen bleiben, meist weil sie zu Beginn einen Metadata Lock benötigt, den eine lange laufende Transaktion blockiert. SHOW PROCESSLIST zeigt in diesem Fall den ALTER-Befehl im Zustand Waiting for table metadata lock. Genauere Einsicht liefert die Tabelle performance_schema.metadata_locks, die alle aktuell gehaltenen und angeforderten Locks samt der jeweiligen Session-ID auflistet.

Findet sich dort eine seit Stunden offene, unbeendete Transaktion aus einem alten Admin-Tab oder einem hängenden Cron-Prozess, hilft in der Regel nur, diese Session gezielt mit KILL zu beenden, bevor die ALTER-Operation fortfahren kann. Bei einer bereits laufenden INPLACE-Operation, die am Row-Log-Limit scheitert, hilft nur ein sauberer Rollback und ein zweiter Versuch mit temporär erhöhtem innodb_online_alter_log_max_size oder in einem Wartungsfenster mit weniger paralleler Schreiblast.

10. Zusammenfassung

11. FAQ: Online DDL: INSTANT vs. INPLACE vs. COPY